My First experience with MS Integration Services (SSIS)

Working with Variables in SSIS (and other tidbits) Finding the Variable Window in the IDE Add a variable in SSIS is pretty easy. Locate your Variables window. It is most likely semi-hidden on one of the dockable areas in your IDE. To locate it, go to the View menu | Other Windows | Variables. If you don't see it there, that usually means that it is already somewhere on your screen. Just look for a tab called Variables. Adding a Variable In the Variable Window you will see an Add icon. When you click it it will add a variable that has the scope set to what you have clicked in your Control or Data Flow tabs. So, to add a global variable be sure you don't have any boxes clicked in either of those tabs, then click the Add icon. If you have any object clicked besides the background the scope will be set to that object and that is not a global variable. I recommend global variables to start with. I find them perfect for most cases. Once the variable is added, you will need to specify the Data Type property and the Value property. Using a Variable to store your SQL Query Add a variable of type String and set the Value to the SQL Query you want to use later. NOTE: You can use a Script Task to manipulate the SQL Query later if you want to. The trick seems to be in order for Data Flow objects to work ok, your variable needs to have a valid query to start with. This is how these objects know what columns need to be mapped, etc. So, you have to be careful what you do with the query variable. The query is not executed exactly, but the database is interrogated to find out the meta data about the columns being requested. So, you don't have to worry about a where clause initially if you are worried when you add the Data Source that it will bring back all rows just to find out the metadata. It appears to be much smarter than that. My guess is that it parses the select statement and determines what the table and column names are. Then it gets the metadata for those columns. Tell Data Source Editor what variable to use After dragging a Data Flow Source such as OLE DB Source on to your Data Flow tab, double-click the icon or border of the square to bring up the OLE DB Source Editor. Select "SQL command from variable" on the Data access mode drop down list. After that, there will then be a "Variable name" drop down list. Select the Variable you added under Adding a Variable section. Modifying the SQL Query Variable This step is optional and not always appropriate. In this example the where clause needs to be dynamic. To change the Value of the SQL Query Variable, you will need to add a Script Task to the Data Flow tab. Insert it into the flow somewhere before the Data Flow square that uses the variable. When you add the Script Task, open it up. You will see three navigation items on the left of the dialog. Click the Script item. Here you need to add any variables you want to use or modify in the script. If you are just reading the value of a variable, you can add it to the ReadOnlyVariables property. The syntax is User::MyVariableHere. If you will be modifying the value of a variable, you need to add it to the ReadWriteVariables. The syntax is the same. In either field you can add multiple variables. To do so just comma separate them. For example: User::MyVar1, User::MyVar2. To actually code you will need to use VB.Net. For those of you not familiar with it, don't be afraid it is very much like C# (at least as far as methods available). Click the Design Script... button to open the editor. To access a variable or set the value you use the following syntax. Dim myVar1 as String ' get value and store in local variable myVar1 = CType(Dts.Variables("MyVar1").Value, String) ' set new value Dts.Variables("MyVar1").Value = "some new value" Selecting a value from the database into a variable There may be times you want to select a column of a particular row in the database and store it in a variable that you define. This is done using the Execute SQL Task. After adding it to you the Control Flow tab, double-click the icon or the border of the square to open the Execute SQL Task Editor dialog. Make sure the following properties are set to the following: ResultSet: Single row (important) Connection: Any connection SQLSourceType: Direct input (assuming you will type it into the textfield in this dialog) SQLStatement: Type the SQL that will select one row and preferably with just one column selected. Now, in the same dialog, click the Result Set navigation item on the left of the dialog. Click the Add button. Enter two values: Result Name: This is the name of the column in the result of your query Variable Name: Choose the variable you want to store the value in. Now you can access it as we described earlier or in any other SSIS item that supports variables. Passing Parameters to SQL Task Sometimes you need to pass a parameter to an update statement, and insert statement or maybe a select statement. The SQL Task (and some other objects) support something called Parameter Mapping. What this does is map a variable you defined in SSIS package, and maps it to a SQL Parameter. The syntax of a parameter in your SQL statement depends on what your Connection Type is and what you are connecting to. Fore example, if you are using OLE DB you need to use ? to designate where a parameter should be. The name of the parameter is basically its zero-based index. For example: UPDATE MyTable set VAL1 = ?, VAL2 = ? In this example to set reference the parameter for VAL1 you would call it 0, and VAL2 would be 1. To map the SSIS variable to these parameters you must first define the variables in SSIS package (as noted above). Then open the SQL Task Editor. In the SQLStatement property type in your SQL (like the above example). Next, go to the Parameter Mapping tab by clicking the Parameter Mapping navigation item on the left pane in the SQL Task Editor. Click the Add button, and select the SSIS package variable, set Direction to Input, and Data Type to appropriate value, and the important part here is the Parameter Name is 0. Do the same for the second parameter, but the Parameter Name is 1. Useful System Variables System::StartTime - This is the local time that the package started running. Partial Debugging The most basic troubleshooting starts with finding where messages are logged. The short answer to that is the Output Window. If a step fails, check here to see why. If you have breakpoints at each point you want to stop when hitting F5 you can inspect variables you defined. While at a breakpoint, just go to the Locals Window and expand the Variables object. Here you see the variables you defined. The IDE supports debugging, but the Step Over (F10), Step Into (F11) don't seem to work. It says it can't step and when it does that you must restart debugging to do anything else. F5 to the next breakpoint seems to work fine though. Not the best debugging, but still better than nothing. You can right click on most any square in either Control Flow or Data Flow and choose Breakpoints to set a breakpoint on that square. I did notice that Step Over and Step Into DO work if you add a breakpoint in your Script Task lines of code. That is really nice! I strongly recommend using a try-catch around your script. The reason is that you must set the return code to failure. If you don't, the step in the package will just hang there indefinitely. Try 'do something here Dts.TaskResult = Dts.Results.Success Catch ex As Exception Dts.TaskResult = Dts.Results.Failure End Try Another helpful thing is that if you right click on an object you can Enable or Disable it. If it is disabled it will not execute. This is helpful if you dont' want all parts of your package to execute while you are developing or testing. References For more information on Execute SQL Task check out: http://www.sqlis.com/58.aspx For more information on using parameters with Execute SQL Task check out: http://msdn2.microsoft.com/en-us/library/ms141003.aspx