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

• adam spencer

  Hall of Fame

  Points: 3926

  More actions

  September 14, 2009 at 1:28 pm

  #204677

  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.

• wwei

  SSC Eights!

  Points: 961

  More actions

  September 14, 2009 at 8:15 pm

  #1053530

  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!