DTS to SSIS Migration - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

DTS to SSIS Migration

Author

Topic

munnabhaikidarho
Starting Member

4 Posts

Posted - 2008-12-23 : 21:13:22

Hi,

I'm a newbie to ActiveX and trying to convert my DTS packages to SSIS that use ActiveX Script. I thought I can copy and paste ActiveX code from DTS to ActiveX Script Task in SSIS and then create other tasks but seems like I was clearly wrong .

DTS packages only contains 2 Execute Tasks, 1 ActiveX Script Task and one connection.

Here is what the DTS is doing;

It truncates table, Generates SQL using ActiveX script Task, and Runs Extract.

When I copy and paste the ActiveX script in SSIS ActiveX Script Task and run the SSIS package, I get an error "Function not found"....

Can anyone please help me in solving this mystery.... I will greatly appreciate it. I have included the ActiveX Script below.

Thanks in advance for all your help.

'**********************************************************************
' 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

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-23 : 21:58:53

Munnabhai,Read this:
http://pragmaticworks.com/community/blogs/dtsxchange/archive/2007/09/08/Converting-the-ActiveX-Script-Task.aspx

where is circuit?

munnabhaikidarho
Starting Member

4 Posts

Posted - 2008-12-24 : 09:51:06

Thanks Sodeep the very helpful link.

So according to the link I should change DTSGlobalVariables.parent in SSIS to DTS.Variables where ever it is used in my ActiveX Script, right?

Thanks
Munnabhai

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-24 : 11:03:48

Right.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources