Sr SQL developer - Tuesday, December 30, 2008 11:52 AM

My solution is as follows:1. Create a user variable sSQLString2. Create a Script Task (not ActiveX Script Task because they are deprecated in SQL 2008) as follows:Name = Set SQL CommandPrecompileScriptIntoBinaryCode = FalseReadWriteVariables = sSQLStringDesign Script is based on your ActiveX script:----------------------------------------------------------' Microsoft SQL Server Integration Services Script Task' Write scripts using Microsoft Visual Basic' The ScriptMain class is the entry point of the Script Task.Imports SystemImports System.DataImports System.MathImports Microsoft.SqlServer.Dts.RuntimePublic Class ScriptMain' The execution engine calls this method when the task executes.' To access the object model, use the Dts object. Connections, variables, events,' and logging features are available as static members of the Dts class.' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.' ' To open Code and Text Editor Help, press F1.' To open Object Browser, press Ctrl+Alt+J.Public Sub Main() Dim sSQLString As String Dim sDate As String Dim dDate As Date Dim strResult As String dDate = GetRevalDate sDate = DateToName(dDate) 'SQL String sSQLString = "exec st_extract_populate_vega_swaption_work_table " & Chr(13) & _ "@RevalDate = '" & sDate & "'" Dts.Variables("sSQLString").Value = sSQLString Dts.TaskResult = Dts.Results.SuccessEnd Sub Function GetRevalDate() As Date Dim dDate As Date dDate = Now() If Weekday(dDate) = 1 Then GetRevalDate = DateAdd(DateInterval.Day, 1, dDate) ElseIf Weekday(dDate) = 7 Then GetRevalDate = DateAdd(DateInterval.Day, 2, dDate) Else GetRevalDate = dDate End If End Function Function DateToName(ByVal dDate As Date) As String 'Create a name based on a date Dim sYear As String Dim sMonth As String Dim sDay As String sYear = CStr(Year(dDate)) If Month(dDate) < 10 Then sMonth = "0" & Month(dDate) Else sMonth = CStr(Month(dDate)) End If If Day(dDate) < 10 Then sDay = "0" & Day(dDate) Else sDay = CStr(Day(dDate)) End If DateToName = sYear & sMonth & sDay End FunctionEnd Class----------------------------------------------------------3. Create an Execute SQL Task as follows:Name = Execute SQL CommandConnectionType = ADO.NETConnection = {your ADO.NET connection name}SQLSourceType = Direct inputSQLStatement = {empty}IsQueryStoredProcedure = FalseExpressions:Property = SqlStatementSourceExpression = @[User::sSQLString]4. Connect the workflow from Set SQL Command to Execute SQL Command and test.