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 4:58 am
Hi,
In SSIS you shouldn't need any scripting to achieve this.
Create 2 variables. One should be varSQLSelect (string) and the other varImportMode (or whatever works for you).
Then highlight the variable for the select statement and set the property "EvaluateAsExpression" to true. (You can do this by highlighting the variable and hitting F4 to get the properties window up). Then click on the elipsis next to the Expression property. Your expression would be something like:
"SELECT blah, blah, blah FROM dbo.TableName WHERE 1 = 1 " + (@varImportMode == "BULK_TOP_UP) ? "AND Date >= " + @MyDate : ""
Try to evaluate the expression and make sure that the resulting string is a valid SQL statement.
In your dataflow task, set up an OLE DB source, and choose a SQL command from a variable as the source and chose the varSQLSelect as the variable.
Lastly all you need to do is set up the variable varImportMode. You can do this with a configuration, or you can pass it in when you call the job at runtime, or you can derive it from someother logic.
Hope this helps.
Kindest Regards,
Frank Bazan
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply