Hi, I have this problem: In a DTS package I can connect to a DB2 on an AS400 and execute a stored procedure. I can then import this data into a table. I can't do this in SSIS. DTS uses an ODBC connection. I have tried this with SSIS 2008 r2 – I can use a ado.net connection which picks up the ODBC driver. I pass in the stored proc as a sql command. I click preview and all the data is there. I click columns and none are defined. Has anybody gotten this to work? It seems like a step back to me- DTS did it fine. It looks like it is down to the odbc drivers using a .net interface – but that is beyond my scope. Sethhttp://lqqsql.wordpress.com

ODBC is a four-letter word! Yes, I know it's a staple for many data access needs, but if you can move up a notch, do so. With SSIS, have you tried the OLE DB Connectors for IBM?Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

Hi Todd, OLE DB - both the microsoft and IBM drivers will not allow the execution of stored procedures as a data source - whereas the ODBC driver in DTS did. This means - if I want to upgrade my DTS packages to SSIS I have to get the DB2 DBAs to rewrite all of those procedures to export to tables so I can import them. Seth http://lqqsql.wordpress.com

If you are using SQL enterprise, have you tried the downloadable Microsoft OLE DB driver for DB2? It might have more features than the stock connectors that come with SSIS. Just a thought. Or, you could write dynamic SQL via an SSIS Variable and update it with either Expressions or a Script Task and set the Data Access Mode of the Source Connector to "SQL Command from variable"Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

Neither the Microsoft nor the IBM ole DB drivers for DB2 will allow execution of stored procedures on the DB2. I could get all the DB2 data transferred to tables and retrieve them from there but that seems a big step back from the DTS package where I could use an ODBC connection and execute the stored procedure. And this is the problem – I have to tell the business that once we upgrade our DTS packages to SSIS the DB2 DBAs need to go back and convert all the stored procedures to output data to tables so I can download from them. Sethhttp://lqqsql.wordpress.com

Neither the Microsoft nor the IBM ole DB drivers for DB2 will allow execution of stored procedures on the DB2. Actually this isn't quite true - it will allow the exectuion of a stored procedure but it will not return a record set - so it can't be used as part of a data flow. Depending on the OLE DB provider, some limitations apply to the OLE DB source: The Microsoft OLE DB provider for Oracle does not support the Oracle data types BLOB, CLOB, NCLOB, BFILE, OR UROWID, and the OLE DB source cannot extract data from tables that contain columns with these data types. The IBM OLE DB DB2 provider and Microsoft OLE DB DB2 provider do not support using an SQL command that calls a stored procedure. When this kind of command is used, the OLE DB source cannot create the column metadata and, as a result, the data flow components that follow the OLE DB source in the data flow have no column data available and the execution of the data flow fails. http://msdn.microsoft.com/en-us/library/ms141696.aspx http://lqqsql.wordpress.com

I have raised this as an issue with Microsoft here: https://connect.microsoft.com/SQLServer/feedback/details/615901/ssis-db2-and-stored-procedures If you are experienceing the same problem please use that link and vote. Seth http://lqqsql.wordpress.com