Using ADO.NET for DB2 Provider with SSIS - Forum - DataDirect Connect - Progress Community

Using ADO.NET for DB2 Provider with SSIS

 Forum

Using ADO.NET for DB2 Provider with SSIS

This question is answered

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.

Verified Answer
  • 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
  • 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
     
  • 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>

  • 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
     
  • 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/.../

  • 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
     
    Thanks and Regards,

    Avadhoot Kulkarni
    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.