Data Transformation Services

 I recently came upon a quandry of how to dynamicaly modify the precedence path of a Data Transformation project.  This was not as straight-forward as one would have thought.  Given the design project (see attached photo),  I created an ActiveX script module ‘Determine Path’ to perform this task.
I present to you, the code for that determination:
Function Main()
 Dim oPkg, oStep, oPrecedence
 Dim oFtpStep
‘ Setup access to this runtime Package properties
 Set oPkg = DTSGlobalVariables.Parent
‘ Gain access to the step in which data will be sent out to another device via FTP processing
 Set oFtpStep = oPkg.Steps("DTSStep_DTSActiveScriptTask_3")
‘ Now, gather up all information on that step for any precedence markers attached during
‘ the code design phase and remove all of those markers from this runtime.

 Set oPrecedence = oFtpStep.PrecedenceConstraints
 Do While oPrecedence.Count
  oPrecedence.Remove 1
‘ Disable all steps in which the data is modified prior to going to the FTP step
 Set oStep = oPkg.Steps("DTSStep_DTSDataPumpTask_1")
 oStep.DisableStep = True
 Set oStep = oPkg.Steps("DTSStep_DTSDataPumpTask_2")
 oStep.DisableStep = True
 Set oStep = oPkg.Steps("DTSStep_DTSDataPumpTask_3")
 oStep.DisableStep = True
 Set oStep = oPkg.Steps("DTSStep_DTSDataPumpTask_4")
 oStep.DisableStep = True
‘ Which data processing step do we need to Enable given the user input?
 Select Case DTSGlobalVariables("FileToConvert").Value
  Case 1   
   Set oStep = oPkg.Steps("DTSStep_DTSDataPumpTask_1")
   oStep.DisableStep = False
  Case 2
   Set oStep = oPkg.Steps("DTSStep_DTSDataPumpTask_2")
   oStep.DisableStep = False
  Case 3
   Set oStep = oPkg.Steps("DTSStep_DTSDataPumpTask_3")
   oStep.DisableStep = False
  Case 4
   Set oStep = oPkg.Steps("DTSStep_DTSDataPumpTask_4")
   oStep.DisableStep = False
 End Select
‘  Since we no know which step has been enabled, set the Precedence of the FTP step to this DataPump in 
‘  order to send the generated data out to the remote FTP site.
 Set oPrecedence = oFtpStep.PrecedenceConstraints.New(oStep.Name)
‘ Run the FTP step on a success of the data transformation pump.

 oPrecedence.PrecedenceBasis = DTSStepPrecedenceBasis_ExecResult
 oPrecedence.Value = DTSStepExecResult_Success
 oFtpStep.PrecedenceConstraints.Add oPrecedence
‘ House keeping
 Set oPkg = Nothing
 Set oStep = Nothing
 Set oPrecedence = Nothing
‘ Exit with a success.
 Main = DTSTaskExecResult_Success
End Function
This code module will select one of the 4 exit paths that are attached to this determination step.  It does this by disabling the step from first and then only enabling the datapump step for processing that has been selected by the user through a menu at startup.  It is also important to make sure that the proper precedence step is enabled that will feed the data to the FTP generator for transfering the appropriate data file as done through the oFTPSTep.PrecedenceConstraints.Add oPrecdence.
This entry was posted in Computers and Internet. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s