SSIS to DB2 connectivity

I am trying to connect  from SQL 2005 to DB2 using Import-Export Wizard using IBM OLE DB Provider for DB2. I am even getting successful "Test connection" message. But when I further go in the Wizard after I choose my destination as SQL server, I get error message as shown in the attachment. So basically I am not able to view any Tables/View to choose for transportation. I have DTS package running on the same computer which uses the same credentials to Log into DB2 and it can access all the required views. But in DTS, I am using "IBM DB2 ODBC Driver", not OLEDB. Any help?Thanks.

Who is Participating?
Why don't you use the ODBC driver in SSIS.
See here for how to use an ODBC driver in SSIS
In spite of using the Import\Export wizard, you can try do that in a SSIS package.
Check it!
that error message indicates that the routine named SQLTables (that is used by the oledb driver) could not be executed, which can be caused by one of two reasons
1) the routine does not exists - this is highly unlikely unless the driver was not installed
2) you don't have permissions to execute the routine - in this case, ask your dba to grant you the permissions

this explanation holds for every error that you will receive with sqlcode=-551
MayankSqlAuthor Commented:
I did design the package to get the data from DB2 views, but the strange thing is none of the views were visible in the drop down. The same error window appeared as shown in the attachment. I ended up using a query method to get the data. I mean In the package, instead of selecting view from the drop down I used a query to get the data i.e. SELECT * FROM TabName.
Another problem I faced later on was I was not able to run the package or schedule the package. Inspite of using save password, the package didnt allow me to save the password. For that I had to go and change the Security setting to "EncryptAllWithPassword or EncryprSensitiveWithPassword" setting to schedule the package in order to save the passowrd for DB2 connectivity. Thanks Guys.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.