SSIS to DB2 connectivity

Posted on 2009-02-20
Last Modified: 2013-11-10
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.

Question by:MayankSql
    LVL 22

    Assisted Solution

    In spite of using the Import\Export wizard, you can try do that in a SSIS package.
    Check it!
    LVL 30

    Accepted Solution

    Why don't you use the ODBC driver in SSIS.
    See here for how to use an ODBC driver in SSIS
    LVL 37

    Assisted Solution

    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

    Author Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now