Post reply

Using Stored Procedure with Parameter in SSIS

  • April 22, 2009 at 7:41 am

    #384416

    Hi All, I have one requirement as bellow.

    I have to extract some records from some tables and Export to the .CSV . So I wrote one SP which will do the business rule for the records, based on the input parameter of SP. Since I'm using the SP I will be selecting the SQL Command as the DataAccess mode of the OLEDB source. And I will be using the Flat file as destination.

    So my package contains ,

    One Data Flow Task

    Inside the Dataflow ,OLEDB Source and Flat File Destination.

    But my SP has no. of parameters, so how can I do this ?

    If I'm using Execute SQL task instead of Data Flow Task , I think I cant pass it to Destination which will be in the Data Flow Tab.

    Please give some ideas.

    Thanks & Regards,

    Mithun C

    Thanks & Regards,
    MC

  • April 22, 2009 at 2:28 pm

    #982519

    Are you unable to use the 'Parameters' option in the OLEDB Reader source component? Alternatively, you may use an 'Execute SQL Task' and send the results to a record set variable and then read that as your source in the data flow. This is a little more complicated but will accomplish the same results if you're unable to use the first option.

    Cheers,

    Ben Sullins
    bensullins.com
    Beer is my primary key...

  • April 22, 2009 at 3:06 pm

    #982563

    Hi thanks for the relpy,

    I have tried using Data Flow Task , OLEDB Source , Flat File destination as bellow and was able to do it successfully

    In control flow tab , take the Data flow task,

    Inside the Data Flow drag the OLE DB Source, make DataAccessMode SQL Command,

    In the command section give exec sp_name ? -- if more than one parameter give ? as comma separated ie exec sp_name ?,?,?

    Click Parameters button , give the parameter name(as it in in the SP , say @id) , and then select the Variable (which is already assigned with the value to be passed as input).

    Now select any Destination we need and do the mapping as usual.

    Regards,

    Mithun C

    Thanks & Regards,
    MC

  • April 22, 2009 at 3:26 pm

    #982570

    Yep, be careful on that though, in my experience the OLEDB source object doesn't play well with parameters in sub queries.

    Cheers,

    Ben Sullins
    bensullins.com
    Beer is my primary key...

  • April 22, 2009 at 10:18 pm

    #982645

    Ok , so in that case do you have any other go?

    Thanks & Regards,
    MC

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

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