SSIS to DB2 connectivity

Hi,
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.

screenshot-1.jpg
MayankSqlAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PedroCGDCommented:
In spite of using the Import\Export wizard, you can try do that in a SSIS package.
Check it!
regards,
pedro
www.pedrocgd.blogspot.com
nmcdermaidCommented:
Why don't you use the ODBC driver in SSIS.
See here for how to use an ODBC driver in SSIS
http://www.experts-exchange.com/Database/Software/ERP/Q_22420763.html
 

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
momi_sabagCommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.