Welcome to VSTSC's Community Server | | Help

Looping through iSeries DB2 Data in SSIS (DB2 for the AS400)

This should be a pretty short post.  I came across a pretty interesting behavior using the Native OLE DB\Microsoft OLE DB Provider for DB2 when its configured to connect to a iSeries DB instance.  (This is the driver provided by MS in an add on pack to SQL Server 2005 sometime earlier this year)  When you try to loop through a record set of data in a for each loop the package will failure with an error of wrong data type for data types that should match.  Numeric to Double, Decimal to Double, Char to Char, Varchar to Char.  I did notice that you can loop through any of the data types if you assign the variable its being dumped into to type Object and that variable can still be used to pull out the value of that field.  I have found the tested with the following data types here are my results:

iSeries DB2 Type    SSIS Type 

Char--------------------->String

VarChar---------------->String

Integer------------------>Int32

TimeStamp----------->DateTime

Numeric--------------->Object

Decimal--------------->Object

Char to Char Failure

 

Char to Object Success

TimeStamp to DateTime Success

TimeStamp to Object Success

Thats it, if you come across any others send me an email and I'll update the list above

Published Friday, October 19, 2007 10:01 AM by steve
Filed under:

Comments

# re: Looping through iSeries DB2 Data in SSIS (DB2 for the AS400)

Saturday, October 20, 2007 9:06 PM by andy

Excellent stuff Steve!

:{> Andy

Anonymous comments are disabled