Data

Question

Data

munnabhaikidarho

Old Hand

Points: 332
More actions
January 7, 2009 at 9:47 am

#199841

Hi,
I have an SSIS package that has a Script Task in which I'm calling MYUSP stored procedure with a @Date parameter.
I would like to hard code the date so it retrieves only the data for that date.
Something like exec MYUSP @Date = '20090701'. How can I do that?
Dts.Variables("SQL").Value = "exec MyUSP @Date = '" & Date() & "'"
Dts.TaskResult = Dts.Results.Success
End Sub
The SSIS basically Truncates table using Execute SQL Task, runs Script Task, and then runs passes SQL variable
to the second Execute SQL task.
Thank you for your help in Advance.

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

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

Mukti SSCrazy Points: 2826 More actions · Answer 1

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 · Answer 2

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 · Answer 3

Thanks everyone for such a hand full of help.

Let me try them.

Thanks

Munna