Using ADO.NET for DB2 Provider with SSIS

Posted by stoned99 on 09-Nov-2016 10:23

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.

DataDirect Connect - Forum
Posted by alambu on 11-Nov-2016 07:24

Hello,
 
Regarding the Question in previous email,
 
Are you still facing the issue in retrieving the columns from  SSIS using DB2 ADO.NET (DDtek.DB2)?
Does your issue with ProviderDescriptors.xml is resolved?
 
Regarding the Question related to the mappings,
 
To try this out, I need following information,
               Which Data source your using for the Source data source?
               Can you provide us the schema of table you are trying to work on, this will help us try out this complete case, and not just focus on TIMESTAMP issue alone.
 
Thanks,
Amith
 

All Replies

Posted by alambu on 10-Nov-2016 07:50

Hello,
 
Thanks for trying our DB2 provider with SSIS.
 
We were successfully able to configure DB2 ADO.NET (DDtek.DB2) provider with SSIS BIDS 2008, without any changes made to ProviderDescriptors.xml. And were able to run the SSIS job successfully.
We see no issues in getting the list of tables or columns of a table. Screenshots below:
 
Retrieving the Tables list
 
 
 
Retrieving the Column list
 
 
 
May we know what version of BIDS you are trying on?
Also, we are not able to understand why you need to update ProviderDescriptors.xml? Are you trying something different?
 
Thanks,
Amith
 

Posted by stoned99 on 10-Nov-2016 09:28

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 -->

<dtm:DataTypeMapping >

<dtm:SourceDataType>

<dtm:DataTypeName>DT_DATE</dtm:DataTypeName>

</dtm:SourceDataType>

<dtm:DestinationDataType>

<dtm:SimpleType>

<dtm:DataTypeName>TIMESTAMP</dtm:DataTypeName>

</dtm:SimpleType>

</dtm:DestinationDataType>

</dtm:DataTypeMapping>

Posted by alambu on 11-Nov-2016 07:24

Hello,
 
Regarding the Question in previous email,
 
Are you still facing the issue in retrieving the columns from  SSIS using DB2 ADO.NET (DDtek.DB2)?
Does your issue with ProviderDescriptors.xml is resolved?
 
Regarding the Question related to the mappings,
 
To try this out, I need following information,
               Which Data source your using for the Source data source?
               Can you provide us the schema of table you are trying to work on, this will help us try out this complete case, and not just focus on TIMESTAMP issue alone.
 
Thanks,
Amith
 

Posted by stoned99 on 11-Nov-2016 11:08

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.

datatellblog.wordpress.com/.../

Posted by Avadhoot Kulkarni on 14-Nov-2016 08:46

Hi,
 
Thanks for sharing this link. These observations may be true as generic comparison with ADO.NET and OLEDB; But the Connect for ADO.NET Db2 provider supports BulkLoad feature which can considerably increase the performance of your batch job. I think it would perform even better than OLEDB (if the OLEDB is not using the BulkLoad protocol feature) for SSIS job.
 

·       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,
Avadhoot
 

Posted by stoned99 on 14-Nov-2016 12:11

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.

This thread is closed