Thanks. This was really helpful. Actually I'm migrating my DTS package to SSIS 2005. The DTS performs the following tasks:

It Truncates table using Execute SQL task, then in ActiveX task it runs the below ActiveX code, and then it executes a stored procedure using Execute SQL task. When I create a ActiveX task in SSIS 2005 and other Execute Tasks to truncate table and run stored procedure, I get an error "Function not found....". Now I'm not sure which function is not found.

I would appreciate all your help in guiding me how to convert this DTS to SSIS.

Thanks

Munna

'**********************************************************************

' Visual Basic ActiveX Script

'************************************************************************

Function Main()

'Change the value of the file name on the connection

Set oPackage = DTSGlobalVariables.parent

Dim sSQLString

Dim sDate

Dim dDate

Dim strResult

dDate = GetRevalDate

sDate = DateToName(dDate)

'SQL String

sSQLString = "exec st_extract_populate_vega_swaption_work_table " & Chr(13) & _

"@RevalDate = '" & sDate & "'"

DTSGlobalVariables.Parent.Tasks("DTSTask_DTSExecuteSQLTask_2").CustomTask.SQLStatement = sSQLString

Main = DTSTaskExecResult_Success

End Function

Function GetRevalDate()

Dim dDate

dDate = date

If Weekday(dDate) = 1 Then

GetRevalDate = dDate + 1

Else If Weekday(dDate) = 7 Then

GetRevalDate = dDate + 2

Else

GetRevalDate = dDate

End If

End If

End Function

Function DateToName(dDate)

'Create a name based on a date

Dim sYear

Dim sMonth

Dim sDay

sYear = Year(dDate)

If Month(dDate) < 10 Then

sMonth = "0" & Month(dDate)

Else

sMonth = Month(dDate)

End If

If Day(dDate) < 10 Then

sDay = "0" & Day(dDate)

Else

sDay = Day(dDate)

End If

DateToName = sYear & sMonth & sDay

End Function