February 10, 2015 at 10:23 am
I'm currently trying to be able to set up an SSIS package which runs a stored procedure in AS/400 and puts the returning result set in a SQL Server table.
I actually got the MS OLEDB Provider for DB2 to work locally on my machine. However, I then figured out that the SQL Server I would deploy this package to is running on Standard Edition. Apparently, the MS OLEDB Provider for DB2 only works on Enterprise and Developer editions of SQL Server.
So back to the drawing board....
I started trying to get the IBM OLEDB Provider for DB2 to work. I used the following steps:
Connection manager setup
1) Create a connection manager in my SSIS package and chose the Native OLE DB\IBM DB2 for i5/OS IBMDA400 OLEDB Provider
2) Enter all the server-specific information in the connection manager screens.
3) Hit the Test Connection button
4) Get the "Test connection succeeded" pop-up message.
Data Flow task setup
1) Create a Data Flow task
2) Create an OLEDB Source for my Data Flow
3) Edit the OLEDB Source
4) Select my new connection manager in OLE DB Connection Manager dropdown
5) Select Data Access Mode: SQL command
6) For SQL command I put call MYLIBRARY.MYPROC()
When I hit the Preview button, I get the following warning: No column information was returned by the SQL command
I hit OK and then I get an error: Object reference not set to an instance of object
At first I thought maybe I'm not really connecting to the AS/400, but if I change MYLIBRARY or MYPROC to something that doesn't exist, I get a SQL0204 error from IBM.
So it does seem to be making the connection IBM. There is something else that is missing/wrong here.
February 12, 2015 at 7:47 am
I ended up creating a connection manager using the ODBC Data Provider. That works fine considering that I'm receiving only a few rows.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply