asked
Script Task in SSIS
I have a vb script that reads customer numbers from a series of xls file names in a directory. I need to limit the list of file names that is reads to a list in a db - something like `select custID from Customers where send_email_flag = 1` I am a newbie to vb and not really clear what kind of granularity ther is with the sql server inside an ssis package when it comes to vb ' Microsoft SQL Server Integration Services Script Task ' Write scripts using Microsoft Visual Basic ' The ScriptMain class is the entry point of the Script Task. Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Runtime Public Class ScriptMain Enum ScriptResults Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure End Enum Public Sub Main() '#Region "Variable Declarations" Dim Vars As Variables = Nothing Dim FilePathAndName As String = Nothing Dim CustID As String = Nothing Dim OpenParen As Integer = 0 Dim CloseParen As Integer = 0 '#End Region '#Region "Put path in variable" FilePathAndName = CStr(Dts.Variables("User::FilePathAndName").Value) '#End Region '#Region "parse custID from path" OpenParen = FilePathAndName.IndexOf("(") CloseParen = FilePathAndName.IndexOf(")") CustID = FilePathAndName.Substring(OpenParen + 1, CloseParen - OpenParen - 1) '#End Region '#Region "Send custID back to SSIS" Dts.Variables("User::CustID").Value = CustID '#End Region ' TODO: Add your code here Dts.TaskResult = CInt(ScriptResults.Success) End Sub End Class
Your code only extract the CustomerCode and stores it into the variable "CustID" from a single file name provided (stored in FilePathAndName variable). You should probably limit the list in other parts of yhour ETL. you probably have the script task in some kind of foreach loop. So you should limit the list of files in the source for the foeach loop or exclute the codes when processing it in next steps of the ETL. Had to help you exactly without knowing the struture of the ETL. Anyway the script task is not necessary for such simple extraction and can be simply substituted by en expression on the CustID variable. you will save an overhead for the script task.
Then you can eg. put Execute SQL Task after the script task for getting the file name and selet inside it `select custID from Customers where send_email_flag = 1 and custID = ?` and as a parameter put the customer ID retrieved from the filename and stored inside the `User::CustID` variable. Then the result from this Execute SQL Stask you can store into a variable and use this variable in a precedence constraint whether to execute the data flow for reading the excel files or not. THere are also another possibilities, but as I wrote, it hard to tell you exactly as I don't know your exact SSIS structure.
