August 2, 2012 at 12:58 pm
Hi Experts,
Old Server SQL Server 2000
new Sql Server 2008
Requirement : Migrate/recreate DTS to SSIS Package
In DTS Package: Loads data from oracle to SQL using a a Query, before that it checks for a condition
First task --> Execute SQL task:Connected to Oracle:
-- Script in it is
select processvalue, date(processdatetime) dtProcessed from processdate where processcode = 'REPORT'
Second task --> Activex Script:
-- script in it is:
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
dim iProcessDTS
iProcessDTS = cint(DTSGlobalVariables("ProcessDTS").Value)
' Msgbox iProcessDTS
' 0 – Failure, 1 - Successful
if (iProcessDTS = 1) then
Main = DTSTaskExecResult_Success
else
Main = DTSTaskExecResult_Failure
end if
End Function
Third task --> Execute SQL Task: connected to sql 2000
--Script is
truncate table xyz
Fourth task: Other Connection
Connecting to Oralce and loads data from there to SQL
-----------------------------------------------------------------------------------------------
Can someone help me in rewriting the Activex script.
if Activex script is not best option in 2008, suggest me other best practices/tasks
Thanks in advance!
August 3, 2012 at 1:48 pm
I revised the script to compile successfully for the SSIS 2008 Script Task. Below is the revised code. Because the ActiveX Script is discontinued in the SQL Server 2012 (http://msdn.microsoft.com/en-us/library/bb500429.aspx ), I recommend using the Script Task.
Public Sub Main()
'
Dim iProcessDTS As Integer
iProcessDTS = CInt(Dts.Variables("ProcessDTS").Value)
' Msgbox iProcessDTS
' 0 – Failure, 1 - Successful
If (iProcessDTS = 1) Then
Dts.TaskResult = ScriptResults.Success
Else
Dts.TaskResult = ScriptResults.Failure
End If
End Sub
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply