Another effective way to make SSIS packages dynamic is to write custom code that perform tasks you cannot perform with the built-in components. Here is a cookbook/cheatsheet that will speed up your learning of customized script tasks or components.
There are two important features you need to determine before you click "Edit Scripts" button. First of all, you need to add existing variables to the ReadOnlyVariables and ReadWriteVariables lists in the Script Transformation Editor or Script Task Editor to make them available to the custom script. Secondly, you need to determine which languages you would like to use: C# or Visual Basic.
Besides these, you need to add necessary libraries at the beginning of scripts. You can refer the article of Adding the Script Task to Your SSIS Packages for detailed steps.
Note that the following examples are all for 2008 version. This is not an exhaustive list. Please stay tuned for more tasks accomplished by Script Task in SSIS:
- How to: Send Email by Script Task in SSIS
- How to: Import HL7 Messages by Script Task in SSIS
- How to: Convert Weekday Number into Weekday Names by Script Task in SSIS
| Problems | Expression |
| Get files' name from readable variables into String sfilePathname. | Solution 1: Dim sfilePathname As String sfilePathname = Dts.Variables("vFilePathName").Value.ToString Solution 2: Dim sfilePathname As String = Dts.Variables("vFilePathName").Value |
| Get the date when the file was created. | Dts.Variables("vFileDate").Value = File.GetLastWriteTime(sfilePathname) |
| Get dateStamp from filename if it contains date info at last 6 strings. | Dim dateStamp As String = sfilePathname.Substring(sfilePathname.LastIndexOf(("_")) + 1, 6) |
| Output value of dateStamp to ReadWriteVariables vYrMon. | Dts.Variables("vYrMon").Value = CInt(dateStamp) |
| Get substring of dateStamp. | Dim sYear As String = dateStamp.Substring(0, 4) |
| Get the system information from system variables. | strSystemErrorEmailFrom = Trim(Dts.Variables("System::MachineName").Value.ToString()) |
| Send SMTP mails. | // Add "Imports System.Net.Mail" at the top Dim myHtmlMessage As MailMessag Dim mySmtpClient As SmtpClient myHtmlMessage=New MailMessage(strSystemErrorEmailFrom, strSystemErrorEmailTo, "Subject", "body") mySmtpClient = New SmtpClient( Dts.Variables("cvarSMTPServer").Value.ToString() ) mySmtpClient.Credentials = CredentialCache.DefaultNetworkCredentials mySmtpClient.Send(myHtmlMessage) Dts.TaskResult = Dts.Results.Success |
| Convert text to proper case (ex. 1st letter in each word is uppercase) | Row.OutputName = StrConv(Row.InputName, VBStrConv.ProperCase) |
| Build Event Handler for OnError Event. | Public Sub Main() Dim arrErrorMessages As Collections.ArrayList Try arrErrorMessages = CType(Dts.Variables("objErrorMessages").Value, Collections.ArrayList) Catch ex As Exception arrErrorMessages = New Collections.ArrayList() End Try arrErrorMessages.Add("Error on Component:[" + Dts.Variables("SourceName").Value.ToString() + "]: " + Dts.Variables("ErrorDescription").Value.ToString()) Dts.Variables("objErrorMessages").Value = arrErrorMessages Dts.TaskResult = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success End Sub |
| Get the filename from a full file path. | Solution 1: Dim fileName As String = "C:\mydir\test.txt" Dim result As String = Path.GetFileName(fileName) Console.WriteLine("GetFileName('{0}') returns '{1}'", fileName, result) Solution 2: RIGHT( @[User::FileName], FINDSTRING( REVERSE( @[User::FileName] ), "\\", 1 ) - 1 ) //Results: GetFileName('C:\mydir\test.txt') returns 'test.txt' |
| Dynamically pre-truncate table. | sSQLScript = "DELETE FROM " + sTableName + vbCrLf + " WHERE " + sPKName + " > 0" Dts.Variables("SQLScript1").Value = sSQLScript |
| Determine a varible's value depending on a file name | If strFileName.Contains("BROUGHT").ToString Then intFacilityId = "1" Else intFacilityId = "0" End If |
| Get the filename without extension from a full file path. | Dts.Variables("vFileName").Value = Path.GetFileNameWithoutExtension(Dts.Variables("vFilePath").Value.ToString) |
| Split strings that delimited by "|". | Dim sAry AsString() = col1.Split("|") |
| Check file exists or not. | Solution 1: Dim directory As DirectoryInfo = New DirectoryInfo("c:\")Dim file As FileInfo() = directory.GetFiles("*.txt")If file.Length > 0 Then Dts.Variables("User::FileExists").Value = TrueElse Dts.Variables("User::FileExists").Value = FalseEnd IfIf (System.IO.File.Exists("C:\mydir\test.txt")) Then |
No comments:
Post a Comment