Post reply

calling SP with parameters in ssis

  • August 2, 2009 at 3:02 pm

    #224326

    Hi all,

    I am lost to do this:

    @id = 'select disctint id from idtable'

    @sp1 = 'select sp1 from table where key = '143''

    @sp2 = 'select sp2 from table where key = '143''

    @sp3 = 'select sp3 from table where key = '143''

    @sp4 = 'select sp4 from table where key = '143''

    Exec sp_data_cont 143, 234, @id, @sp1, @sp2, @sp3, @sp4

    how can i pass values to parameters!?

    please guide!!

    Thanks

    pat

  • August 3, 2009 at 12:17 am

    #1033530

    hi

    Take a "Execute SQL Task"

    Give you SP Name as input to SQL Command object. type question marks as placeholder for your parameters.

    In second tab, you can map your parameters through parameter mapping tab.

    Thanks,

    Rupashri

  • August 3, 2009 at 9:00 am

    #1033698

    hi,

    i did that and getting following error.

    I have variable called imp with datatype string and value select sp1 from table where id = '143'

    and in sql command exec spname ? (the paratement in sp is nvarchar)

    error: "Conversion failed when converting the nvarchar value 'select sp1 from table where id = '143'' to data type int.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    what am i doing wrong?

  • August 3, 2009 at 9:02 am

    #1033700

    Not completely sure what you are trying to get at but might try something like this:

    select disctint @id = id from idtable

    select @sp1 = sp1, @sp2 = sp2, @sp3 = sp3, @sp4 = sp4 from table where key = '143'

    Exec sp_data_cont 143, 234, @id, @sp1, @sp2, @sp3, @sp4

    Hope this helps

  • August 3, 2009 at 10:06 am

    #1033758

    thanks that looks like it worked.

    thanks

    Paresh

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

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