Has anyone managed to use the DDTEK.DB2 provider with SSIS?
I managed to reference DDTek.DB2.DB2Connection in the ProviderDescriptors.xml and retrieve a list of tables for an ADO destination using (Note I reduced NumberOfColumnRestrictions from 4 to 3 to make it work)
<dtm:TableSchemaAttributes TableCatalogColumnName="TABLE_CATALOG" TableSchemaColumnName="TABLE_SCHEMA" TableNameColumnName="TABLE_NAME" TableTypeColumnName="TABLE_TYPE" TableDescriptor="TABLE" ViewDescriptor="VIEW" SynonymDescriptor="SYNONYM" NumberOfTableRestrictions="3" />
but I'm unable to return the columns. I keep getting the error
Message: More restrictions were provided than the requested schema ('Columns') supports. I've tried reducing the NumberOfRestrictions as I did with the tables schema call and no luck.
<dtm:ColumnSchemaAttributes NameColumnName = "COLUMN_NAME" OrdinalPositionColumnName="ORDINAL_POSITION" DataTypeColumnName = "DATA_TYPE" MaximumLengthColumnName = "CHARACTER_MAXIMUM_LENGTH" NumericPrecisionColumnName = "NUMERIC_PRECISION" NumericScaleColumnName = "NUMERIC_SCALE" NullableColumnName="IS_NULLABLE" DateTimePrecisionColumnName="DATETIME_PRECISION" NumberOfColumnRestrictions="4" />
I'm doing this so that I can use SSIS's dynamic mapping to control how dates are formatted (the default includes a time component even for basic dates)
For the record the MS OLE DB2 provider does work, and I used the mapping files as my starting point, but I wanted to use the ADO.NET provider for performance reasons.
The problem is in the mapping process, when you have a string that needs to be defined as date it actually sends SQL that includes a timestamp and generates a -181. I'm using DB2 z/os (not LUW).
The mapping is controlled by the mapping files found in
C:\Program Files (x86)\Microsoft SQL Server\120\DTS\MappingFiles
I'm using VS 2013 as my SSIS development platform
The file SSIS10ToDB2.XML which I believe is the mapping file being used by default is as follows:
<!-- DT_DATE -->
I just tried to recreate the issue and oddly enough it's working as expected. I thought I was using 64bit drivers previously, but all my tests only allowed me to select the 32bit versions. I had initially started developing in 2013 before realizing I had to use Visual Studio 2012 to deploy to our SQL Server environment so at this point I a little confused.
The entire exercise was not wasted though as I've decided to go forward using OLE for my SSIS jobs as I read a number of threads that suggested OLE actually out performs ADO.
What OLEDB driver are you using for your SSIS jobs?
Can you please try with the connect for ADO.NET driver with BulkLoad setting enabled? We would be happy to help you fine tune the performance
with right set of settings.
Thanks and Regards,
Lead Principal Software Engineer
Progress Software Development Private Ltd.
I'm using the Microsoft DB2 driver 4.0, it does not support the BulkLoad that you mentioned, so you may have a point. I'm finished with the task for now, but I'll keep it in mind.