Data

• Mukti

  SSCrazy

  Points: 2886

  More actions

  January 7, 2009 at 2:10 pm

  #922911

  You can use expression to build this string. Set the EvaluateAsExpression property to true on your 'SQL' variable and use the following expression to build the string

  "exec MyUSP @Date = '" + (DT_WSTR, 4) DATEPART( "Year", getdate())

  + RIGHT("00" + (DT_WSTR, 4) DATEPART( "Month", getdate()),2)

  + RIGHT("00" + (DT_WSTR, 4) DATEPART( "Day", getdate()),2)

  +"'"

  You can build similar logic in your script too.

  Also you can use parameters in the execute sql task to build your parameter variable in a similar way as explained above

  HTH

  ~Mukti

• Ray M

  SSC-Insane

  Points: 21093

  More actions

  January 7, 2009 at 2:17 pm

  #922913

  Dim fDay As String

  Dim fMonth As String

  Dim fYear As String

  Dim RDate As Date

  RDate = Today

  fYear = Convert.ToString(Year(RDate))

  fMonth = Convert.ToString(Month(RDate))

  fDay = Convert.ToString(Day(RDate))

  If Len(fMonth) = 1 Then

  fMonth = "0" & fMonth

  End If

  If Len(fDay) = 1 Then

  fDay = "0" & fDay

  End If

  Dts.Variables("SQL").Value = "exec MyUSP @Date = '" & fYear & fMonth & fDay & """"

• munnabhaikidarho

  Old Hand

  Points: 332

  More actions

  January 7, 2009 at 2:23 pm

  #922917

  Thanks everyone for such a hand full of help.

  Let me try them.

  Thanks

  Munna