SSIS - would these steps to add and use Variables be correct?

  • SSIS - would these steps to add and use Variables be correct?

    in BIDS in the Control Flow's Execute SQL task I selected SQL Command

    and put the following code in it:

    exec dbo.spBordereauxByContract_UW_DS_PolicyMaster

    'DS050109',

    Convert(nvarchar(10), @dtReportingBeginDate, 101) ,

    Convert(nvarchar(10), @dtReportingEndDate, 101)

    I have declared the 2 above variables in BIDS but without the @ symbols because BIDS would

    not accept the syntax.

    Am I on the wrong track passing values to the sp's parameters all together?

    Note the DDL of the SP's date parameters are smalldatetime, but there is only the option of DateTime variable datatype in bids.

    but either way when it outputs to excel it needs to be NVARCHAR anyway.

    I am too lost to just ask a direct single question.

    thanks for any help someone can give me.

    If this helps when this is all said and done I want to right click the package at run time in the filesystem, assign date values to these variables at hit execute.

  • I use following method for similar situation.

    Assume I have a store procedure called 'proc_try' which take two parameters @startDate and @endDate.

    Create two variables in BIRD called startDate and endDate.

    in the Execute SQL Task Editor,

    in General Tab, SQLStatement property, put 'exec proc_try ?, ?'

    in the Parameter Mapping Tab, add two parameter mappings. basically they should look like:

    user::startDate/Input/DATE/0

    user::endDate/Input/DATE/1

    That is it. It works for me. If anybody has better way, please let me know. Thanks in advance!

Viewing 2 posts - 1 through 1 (of 1 total)

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