[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 863
  • Last Modified:

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
0
MayankSql
Asked:
MayankSql
3 Solutions
 
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
0
 
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
 
0
 
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
0
 
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.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now