February 20, 2009 at 1:55 am
I have a DTS package which has an ActiveX script task, which updates the source sql statement of a data transform task by adding a filter depending upon the load mode.
But now in SSIS, how do I accomplish this task?
''**********************************************************************
' Visual Basic ActiveX Script Version 0.5
'************************************************************************
Function Main()
Dim pkg
Dim cnn
Dim tsk
Dim c_task
Set pkg = DTSGlobalVariables.Parent
' Connections manipulations
For Each cnn In pkg.Connections
If Left(cnn.Name, 6) = "SOURCE" Then
cnn.Catalog = DTSGlobalVariables("SOURCE_DB")
cnn.DataSource = DTSGlobalVariables("SOURCE_SERVER")
ElseIf Left(cnn.Name, 6) = "TARGET" Then
cnn.Catalog = DTSGlobalVariables("STAGING_DB")
cnn.DataSource = DTSGlobalVariables("STAGING_SERVER")
ElseIf Left(cnn.Name, 16) = "EXTRACT_FLATFILE" Then
cnn.DataSource = DTSGlobalVariables("OUTPUT_FILEPATH") & "Flatfile_1.txt"
End If
Next
' Data Pump manipulations
For Each tsk In pkg.Tasks
If tsk.Description = "SOURCE_TO_TARGET" Then
Set c_task = tsk.CustomTask
If UCase(DTSGlobalVariables("LOAD_MODE")) = "BULK" Then
c_task.SourceSQLStatement = c_task.SourceSQLStatement & " AND HOMELEAV.CREATE_DTTM <= " & DTSGlobalVariables("CRTODATE")
ElseIf UCase(DTSGlobalVariables("LOAD_MODE")) = "BULK_TOP_UP" Then
c_task.SourceSQLStatement = c_task.SourceSQLStatement & " AND ( ALERTDIST.CREATE_DTTM between " & _
DTSGlobalVariables("CRFROMDATE") & " and " & DTSGlobalVariables("CRTODATE") & " )"
ElseIf UCase(DTSGlobalVariables("LOAD_MODE")) = "REFRESH_TOP_UP" Then
c_task.SourceSQLStatement = c_task.SourceSQLStatement & " AND HOMELEAV.CREATE_DTTM <= " & DTSGlobalVariables("CRTODATE") & _
" AND HOMELEAV.Modif_Dttm >=" & DTSGlobalVariables("MODIFYTODATE")
End If
End If
Next
Main = DTSTaskExecResult_Success
End Function
February 20, 2009 at 2:11 am
Duplicate post. No replies to this thread please. Direct replies to:http://www.sqlservercentral.com/Forums/Topic661114-148-1.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply