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