Post reply

Extracting data from AS400 with SSIS datareader, can I use parameters?

  • March 17, 2008 at 7:04 am

    #379239

    I need to extract data from an AS400 database nightly into SQL Server 2005 tables. The only method I found that works in SSIS is a datareader through an ODBC connection.

    I want to extract only the data from the previous day so I would like to pass a parameter to the text query in my data reader. The data reader does not seem to have the capability to accept a parameter. Am I missing something?

  • April 25, 2008 at 1:13 am

    #806987

    HI, i have the same problem! I had to write script.

  • July 4, 2008 at 7:24 am

    #838354

    Hello,

    yes it seems a script is the only way we have to solve this. Of course there are different scripttypes

    that can be used. My first attempt was to create a Data-source-script inside a dataflow-component.

    The benefit would be it could access the connections existing to read data. But it seems we can use only one connectiontype in there. I tried to make it work with a .NET-ODBC-Dataconnection but I always got an error concerning the conversion of this connection type to the normal .NET-SQLReader-Type. In the end, I created a normal VB-Scripttask with option Strict off using late-binding and ADODB-Objekts (Connection, command, Recordset, etc).

    The bad thing about this: I need to have a package variable with the connectionstring for my datasource inside to be passed to my script for opening the ADODB-connection in the script. This is redundant to an existing connection in my package, which I dislike. Has anyone managed to make a datasource via script with an OBDC/.NET-ODBC-Dataconnection? If so I would be grateful for a hint.

    Thanks,

    Ebi

  • July 8, 2008 at 9:41 am

    #839582

    what Iw ound up doing that worked well was create a new table on the as400, prior to running my package, I inserted a new row into the table with my process date that I wanted to use as my parameter, in my extractSQL I join to my parameter table, works fine for me

  • July 9, 2008 at 9:46 am

    #840303

    Hello,

    that is a god idea too. Meanwhile I found out, that it is possible to create a Datareader_Script using ODBC. I just had to change the Classdefinitions from SqlReader to ODBCReader and SQlCommand to ODBCCommand and all worked fine.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply