SSIS DB2 and Stored Procedures

Posted: October 22, 2010 in Data Flow, DB2, OLE DB, SSIS

The Scenario

Extracting data from a DB2 file using SSIS when the data is accessed through a stored procedure.

With DTS packages this could be done by using a ODBC driver to connect to the DB2.

The Problems

With DTS packages there was an option to choose Other Driver. From this I could set up an ODBC connection to a DB2 database and execute a stored procedure ā€“ this acted as my data source. This ODBC connection used a system DSN which pointed at eh db2 database on an AS400 server.

SSIS does not give you the option of using other driver. What they have instead are OLE DB drivers or .Net drivers.

The OLE DB drivers will not allow the execution of a stored proc on the DB2 to retrieve data.

The IBM OLE DB DB2 provider and Microsoft OLE DB DB2 provider do not support using an SQL command that calls a stored procedure. When this kind of command is used, the OLE DB source cannot create the column metadata and, as a result, the data flow components that follow the OLE DB source in the data flow have no column data available and the execution of the data flow fails..

See: http://msdn.microsoft.com/en-us/library/ms141696.aspx

The Dot.Net connection, in SSIS 2008, will allow you to execute the procedure in an Execute Script Task. It will not allow execution in a data flow task ā€“ although it will allow you to preview the data in data flow.

After setting up a Linked Server to the DB2 I encountered the same problem ā€“ although the error message was slightly misleading and I spent a long time investigating permissions.

So the only options now are to carry out a row by row import by using an Execute TSQL Task to populate a record set object and then rolling through this to import the data or asking the DB2 DBA to use the stored procedures to populate file which I can then import from.

My forum question on MSDN relating to the issue:
http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/7ea40a98-c930-48fc-a961-2fdb977de0d3/#e2edc306-f0f4-45bb-9844-53cf83fb845a

Connect issue raised with Microsoft:
https://connect.microsoft.com/SQLServer/feedback/details/615901/ssis-db2-and-stored-procedures
Please follow the link and vote if the issue effects you and maybe Microsoft will bring out a new driver.

Advertisements
Comments
  1. brunobueno says:

    Hi Seth,

    Sorry, I know this is a very old post, but I am getting the same problem.

    My name is Bruno. I am a C# developer and sometimes SQLServer DBA by accident.

    Searching all over the internet and available documentation but no solution at the moment.

    My scenario is the same that you described above, but I have one complication that is the fact that I don’t have access to the developers who wrote the DB2’s Stored Procedures. So, in other words I cannot ask them to change anything in SPs or even ask to convert the SPs to real tables.

    Again, sorry if I bothering you, but I would know if you could get a solution.
    In the end of the post you wrote:

    “…So the only options now are to carry out a row by row import by using an Execute TSQL Task to populate a record set object and then rolling through this to import the data…”

    Could you solve the problem doing this? I tried that but had the same problem. The record set object asks for input columns and I cannot set it mannually.

    Any help is appreciated. Sorry by the english, that’s not my birth language.

    Thanks

    Bruno Bueno

    • Seth Lynch says:

      Hi,
      Unfortunately I haven’t worked with that system for a few years now – it was at a previous company I worked for.

      Is there anyway you can execute the SP and see the results – using openrowset and linked server via a sql box perhaps?

      Seth

      • brunobueno says:

        Hi,

        Yes, I can execute the SPs and see the results on preview screen using OleDbSource, ADO .Net source, acessing directly DB2 server or attaching this DB2 in my SQLServer via a linked server. In any case I can see the results on screen.

        The main problem is that the return of the SPs don’t provides the column metadata information, then the data flow fails to pass the data for the next SSIS component.

        I need to get these SP results and save to a SQL table that is identical to SP results (columns are exactly equals)

        Any idea ? I am starting with database administration and thinking I got some of the worst scenarios.

        Thanks for your reply.

        Bruno

  2. Seth Lynch says:

    You are stuck with one of the hardest things to work with – and to do it blind. I can’t really answer your question as I don’t use SSIS so much but I think I was going to try and do it in a similar way to this: https://www.simple-talk.com/sql/ssis/ssis-basics-using-the-execute-sql-task-to-generate-result-sets/

    I was luck in that I got the DB2 admin to create a work around – the sp executed eachnight and output the results to a file.

    Seth

    • brunobueno says:

      So much thank you for the article tip! It seems to be very a good solution.

      I already was reading some tutorials that also use Execute SQL Task component but I didn’t find this one from Annette Allen. It’s a good step-by-step.

      I am going to read it all and try to apply the examples to my scenario.

      Congratulations for your blog, it’s full of useful information.

      More one time thank you for your help, it was very enlightening

      Bruno Bueno

  3. Seth Lynch says:

    Good luck

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s