Appendix B
Problem and Solution Crib Notes
This appendix is a result of the culmination of many student questions over years of teaching SSIS classes. After a week of training, students would typically say, “Great, but can you boil it down to a few pages of crib notes for me?” The following table shows you common problems you’re going to want to solve in SSIS and a quick solution on how to solve them. These solutions are just crib notes, and you can find most of the details throughout this book or in Professional Microsoft SQL Server 2012 Integration Services (Wrox, 2012).
| Problem | Quick Solution |
| Loop over a list of files and load each one. | Tasks Required: Foreach Loop, Data Flow Task Solution: Configure the Foreach Loop to loop over any particular directory of files. You should configure the loop to output to a given variable. Map the given variable to a connection manager by using expressions. You can find more on this in Lesson 43. |
| Conditionally executing tasks. | Solution: Double-click the precedence constraint and set the Evaluation property to Expression and Constraint. Type the condition that you want to evaluate in the Expression box. When you are using the Expression and Constraint option, both the specified execution result and the expression condition must be satisfied for the next task to execute. |
| Pass in variables when scheduling or running a package. | Solution: Use the /SET command in the DTExec command line or change the Property tab in the Package Execution Utility to have the property path like Package.Variables[User::VariableName].Properties[Value]. You can find more on this in Lesson 57. |
| Move and rename the file at the same time. | Tasks Required: File System TaskSolution: Set the File System Task to rename the file and point to the directory you’d like to move it to. This enables you to rename and move the file in the same step. You can find more on this in Lesson 10. |
| Loop over an array of data in a table and perform a set of tasks for each row. | Tasks Required: Execute SQL Task, Foreach LoopSolution: Use an Execute SQL Task to load the array and send the data into an object variable. Loop over the variable in a Foreach Loop by using an ADO Enumerator. You can find more on this in Lesson 43. |
| Perform an incremental load of data. | Tasks Required: Two Execute SQL Tasks, Data Flow TaskSolution: Have the first Execute SQL Task retrieve a date from a control table of when the target table was last loaded and place that into a variable. In the Data Flow Task, create a date range on your query using the variable. Then, update the control table using a second Execute SQL Task to specify when the table was last updated. You can find more on this in Lesson 36. |
| Perform a conditional update and insert. | Components Required: Data Flow Task, Conditional Split, Lookup Transform or Merge Join, OLE DB Command TransformSolution: Use the Lookup Transform or Merge Join to determine if the row exists on the destination and ignore a failed match. If the row yields blank on the key, you know the row should be inserted into target (by a Conditional Split). Otherwise, the row is a duplicate or an update. Determine if the row is an update by comparing the source value to the target value in the Conditional Split. You can perform the update using an OLE DB Command Transform or by loading the data into a staging table. |
| Create a filename with today’s date. | Expression on the ConnectionString property on the Flat File or File Connection Manager:“C:\Projects\MyExtract” + (DT_WSTR, 30) (DT_DBDATE)GETDATE() + “.csv”Results in:C:ProjectsMyExtract2009-03-20.csv |
| Use a two-digit date. For example, retrieve a month in two-digit form (03 for March instead of 3). | RIGHT("0"+(DT_WSTR,4)MONTH(Getdate()),2)Results in:03 (if the month is March) |
| Multiple condition if statement. In this example, the statement determines that if the ColumnName column is blank or null, it will be set to unknown. To make a Logical AND condition, use && instead of the || operator. | ISNULL(ColumnName) || TRIM(ColumnName)== “” ? “Unknown” : ColumnName |
| Return the first five characters from a ZIP code. | Derived Column Transform in the Data Flow:SUBSTRING(ZipCodePlus4,1,5) |
| Remove a given character from a string (example shows how to remove dashes from a Social Security number). | Derived Column Transform in the Data Flow:REPLACE(SocialSecurityNumber, “-”,””) |
| Uppercase data. | Derived Column Transform in the Data Flow:UPPER(ColumnName) |
| Replace NULL with another value. | Derived Column Transform in the Data Flow:ISNULL(ColumnName) ? “New Value”: ColumnName |
| Replace blanks with NULL values. | Derived Column Transform in the Data Flow:TRIM(ColumnName) == “” ? (DT_STR,4,1252)NULL(DT_STR,4,1252) : ColumnName |
| Remove any non-numeric data from a column. | Script Transform in the Data Flow Task with the code as follows:Imports System.Text.RegularExpressionsPublic Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) If Row.ColumnName_IsNull = False Or Row.ColumnName = “” Then Dim pattern As String = String.Empty Dim r As Regex = Nothing pattern = “[^0-9]” r = New Regex(pattern,RegexOptions.Compiled) Row.ColumnName = Regex.Replace(Row.ColumnName, pattern, ““) End IfEnd Sub |
| Convert text to proper case (first letter in each word uppercase). | Script Transform with the line of partial code as follows(note that this code should go on one line):Row.OutputName = StrConv(Row.InputName, VbStrConv.ProperCase) |
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.