SQL Server Performance

DTS to SSIS Migration

Discussion in 'SQL Server 2005 Integration Services' started by munnabhaikidarho, Dec 23, 2008.

  1. munnabhaikidarho New Member

    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 connectionSet oPackage = DTSGlobalVariables.parentDim sSQLStringDim sDateDim dDateDim strResultdDate = GetRevalDatesDate = DateToName(dDate)'SQL StringsSQLString = "exec st_extract_populate_vega_swaption_work_table " & Chr(13) & _"@RevalDate = '" & sDate & "'"DTSGlobalVariables.Parent.Tasks("DTSTask_DTSExecuteSQLTask_2").CustomTask.SQLStatement = sSQLStringMain = DTSTaskExecResult_SuccessEnd FunctionFunction GetRevalDate()Dim dDatedDate = dateIf Weekday(dDate) = 1 ThenGetRevalDate = dDate + 1Else If Weekday(dDate) = 7 ThenGetRevalDate = dDate + 2ElseGetRevalDate = dDateEnd IfEnd IfEnd FunctionFunction DateToName(dDate)'Create a name based on a dateDim sYearDim sMonthDim sDaysYear = Year(dDate) If Month(dDate) < 10 ThensMonth = "0" & Month(dDate)ElsesMonth = Month(dDate)End IfIf Day(dDate) < 10 ThensDay = "0" & Day(dDate)ElsesDay = Day(dDate) End IfDateToName = sYear & sMonth & sDayEnd FunctionThanksMunna Bhai
  2. jason7655 New Member

  3. satya Moderator

  4. munnabhaikidarho New Member

    Thanks everyone for your helpful advices. Let me try those links.

Share This Page