{"id":1382,"date":"2012-08-08T00:00:00","date_gmt":"2012-08-08T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/ssis-basics-introducing-variables\/"},"modified":"2021-06-03T16:45:25","modified_gmt":"2021-06-03T16:45:25","slug":"ssis-basics-introducing-variables","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/bi-sql-server\/ssis-basics-introducing-variables\/","title":{"rendered":"SSIS Basics: Introducing Variables"},"content":{"rendered":"
\n

\tIn previous articles in the SSIS Basics series, I showed you how to set up a SQL Server Integration Services<\/a> (SSIS) package. I also showed you how to add a Data Flow <\/strong>task<\/a> that contains the components necessary to extract data from a SQL Server database, transform the data, and load it into an Excel spreadsheet. <\/p>\n

\tIn this article, I will show you how to use variables in your SSIS package. Variables are extremely important and are widely used in an SSIS package. A variable is a named object that stores one or more values and can be referenced by various SSIS components throughout the package’s execution. You can configure a variable so its value is updated at run time, or you can assign a value to the variable when you create it. <\/p>\n

\tThere are two types of variables in an SSIS package: system and user-defined. SSIS automatically generates the system variables when you create your package. Components can then reference the system variables as necessary, usually for debugging and auditing purposes. You create user-defined variables as needed when you configure your package. <\/p>\n

Setting Up Your SSIS Package<\/h2>\n
\n

\tTo try out the examples in this article all you need to do is to create an Excel spreadsheet named Employee, for the purposes of this article it can be an empty file. This does follow on from the previous article in this series “SSIS Basics: Adding Data Flow to Your Package<\/a>” however it is adviseable to start from a new package. \t<\/p>\n<\/p><\/div>\n

Creating Variables<\/h2>\n

\tIn the following sections, I will show you how to create a user-defined variable and reference it within your package. Then I will show you how to create an expression that dynamically updates the variable value at run time. Our starting point is where my previous article left off; however, all you need to do is create an Excel spreadsheet named Employee, I have saved it in the root of my d:\\ drive but you can save it wherever appropriate, once this is created you should have no problems working through these examples. <\/p>\n

The Variables Window<\/h3>\n

\tThe easiest way to create a user-defined variable is by using the Variables<\/strong> window. When you create a variable, you specify a name, data type, and scope. <\/p>\n

\tThe scope controls which package elements can use the variable. You can define a variable’s scope at the package level or at the level of a specific container, task, or event handler. For example, if you configure a variable at the scope of a Foreach<\/strong>Loop<\/strong> container, only that container and any tasks within the container can use the variable. However, if you configure the variable at the package level, any component within the package can reference the variable. One of the most common issues to arise when working with variables is for them to have been inadvertently created at the scope of a container or task, when they should have been created at the scope of the package. <\/p>\n

\tTo view the Variables<\/strong>window, right-click the design surface on the Control<\/strong>Flow<\/strong> tab, and clickV<\/strong>ariables<\/strong>. By default, the V<\/strong>ariable<\/strong>s<\/strong> window appears to the left of the design surface, as shown in Figure 1. <\/p>\n

\t\"1538-Figure1-f46ef5d3-89f9-47a9-aac2-5f6\"<\/p>\n

\tFigure 1: Variables<\/strong><\/strong> window in SSIS Designer <\/p>\n

\tAt the top of the Variables<\/strong> window, you’ll find five buttons (shown in Figure 2) and, beneath those, column headings. <\/p>\n

\t\"1538-Figure2-69996a2b-02bc-4062-a5ae-bbf\"<\/p>\n

\tFigure 2: Buttons at the top of the Variables<\/strong><\/strong> window <\/p>\n

\tFrom left-to-right, the buttons let you perform the following tasks: <\/p>\n

    \n
  • Adding a user-defined variable<\/li>\n
  • Deleting a user-defined variable (available when a variable has been created)<\/li>\n
  • Showing system variables<\/li>\n
  • Showing all variables<\/li>\n
  • Choosing the variable columns to display<\/li>\n<\/ul>\n

    \tBy default, the Variables<\/strong> window displays the following columns: <\/p>\n

      \n
    • Name<\/strong>: The name assigned to the variable. When you first create your variable, the value Variable<\/strong> is used. If there is already a variable named Variable<\/strong>,<\/em> a number is added to the name.<\/li>\n
    • Scope<\/strong>: The scope at which the variable should be set. The majority of the time, you can go with a package-level scope, which means the scope should be the name of the package. To select a different scope, you must select the applicable task or container when you create your variable. It is very easy to inadvertently create a variable at an unintended scope. Before creating a variable, be certain that the correct task or container is selected or, if you want a package-level scope, that no components are selected. <\/li>\n
    • Data<\/strong>Type<\/strong>: The type of data that the variable can store. SSIS supports the following variable types:\n
        \n
      • Boolean<\/li>\n
      • Byte<\/li>\n
      • Char<\/li>\n
      • DateTime<\/li>\n
      • DBNull<\/li>\n
      • Double<\/li>\n
      • Int16<\/li>\n
      • Int32<\/li>\n
      • Object<\/li>\n
      • SByte<\/li>\n
      • String<\/li>\n
      • UInt32<\/li>\n
      • UInt64<\/li>\n<\/ul>\n<\/li>\n
      • Value<\/strong>: The variable’s initial value. This can be populated here or left blank and populated at run time.<\/li>\n<\/ul>\n

        \tIf you click the Choose Variable Columns<\/strong> icons, the Choose Variable Columns<\/strong> dialog box appears, as shown in Figure 3. <\/p>\n

        \t\"1538-Figure3-7b293e93-6694-49f3-a833-d6b\"<\/p>\n

        \tFigure 3: Additional columns available for variables <\/p>\n

        \tAs you can see, the Scope, Data type<\/strong>, and Value<\/strong> columns are selected by default. You can also select one of the following columns to display: <\/p>\n

          \n
        • Namespace<\/strong>: <\/strong>Shows whether the variable is a system or user-defined variable.<\/li>\n
        • Raise event when variable value changes<\/strong>:<\/strong>Boolean (True\/False) field that if selected will fire an OnVariableValueChanged<\/strong> event if the variable value is modified. This will be covered in more detail in a future article when looking at error handling and debugging.<\/li>\n<\/ul>\n

          \tYou are now ready to create a variable. <\/p>\n

          Creating a User-Defined Variable<\/h3>\n

          \tIn this section, I demonstrate how to create a variable and assign a value to it, which in this case, will be a file path. Using the Excel spreadsheet created in the setup, I will save a copy of the spreadsheet to a new folder using a variable to re-name it. <\/p>\n

          \tTo create a variable, click the white space on the Control Flow<\/strong> design surface to ensure that no components are selected, and then click the Add Variable<\/strong> button in the Variables<\/strong> window. A variable is added to the first row of the window’s grid, as shown in Figure 4. <\/p>\n

          \t\"1538-Figure4-e55ef1eb-7e17-4b20-b457-539\"<\/p>\n

          \tFigure 4: A new variable in the Variables<\/strong><\/strong> window <\/p>\n

          \tNotice in Figure 4 the blue button with the black X to the left of the variable name. You can use this button to delete the variable. Also notice that the scope is set to the package name, SSISBasics<\/strong>, which is the scope we want to use for this exercise. <\/p>\n

          \tBy default, the variable is configured with the Int32<\/strong> data type. However, for this exercise, we’ll use String<\/strong>. To change the data type, select String<\/strong> from the drop-down list in the Data Type<\/strong> column. When the data type is updated, the value in the Value<\/strong> column changes from 0<\/strong> to an empty string. <\/p>\n

          \tYou now need to enter the variable value. Simply type in the path name you plan to use for your Excel file. In this case, I used d:\\Demo<\/strong>. I also changed the variable name to DestinationFile<\/strong>Path<\/strong>, as shown in Figure 5. <\/p>\n

          \t\"1538-Figure5-fcde72cd-6d61-4165-8b3c-c0b\"<\/p>\n

          \tFigure 5: Configuring the new variable in the Variables<\/strong><\/strong> window <\/p>\n

          \tYou’re variable should now be ready to go. Because you created it with a package scope, any component in your package can use that variable. <\/p>\n

          Using the Variable<\/h3>\n

          \tI am now going to show you how to reference the new variable from within a File System<\/strong> task. First, you must add the task to the control flow, as shown in Figure 6. <\/p>\n

          \t\"1538-Figure6-d4cace40-f803-40ce-9ab6-ae8\"<\/p>\n

          \tFigure 6: Adding a File System task to the control flow <\/p>\n

          \tThen double-click the File System<\/strong> task to open the File System<\/strong> Task<\/strong> Editor<\/strong>, as shown in Figure 7. <\/p>\n

          \t\"1538-Figure7-b609fc62-6dba-46d1-915c-cd6\"<\/p>\n

          \tFigure 7: Accessing the File System Task Editor<\/strong><\/strong> <\/p>\n

          \tYou now need to configure the task’s properties. Table 1 describes how to configure each of these properties <\/p>\n

          \n\n\n\n\n\n\n\n\n\n\n\n\n
          Property<\/td>\nDescription<\/td>\n<\/tr>\n<\/thead>\n
          IsDestinationPathVariable<\/strong><\/td>\nChange to True<\/strong>.<\/td>\n<\/tr>\n
          DestinationVariable<\/strong><\/td>\nSelect the variable you created earlier from the drop-down list associated with this property. When the variable is added to the property, it is preceded by the namespace name and two colons (User::<\/strong>).<\/td>\n<\/tr>\n
          OverwriteDestination<\/strong><\/td>\nIf you want to overwrite the destination every time the package runs, select True<\/strong>, otherwise leave it as False<\/strong>. However, if you select False<\/strong> and run the package more than once, SSIS will generate an error. As the name of the excel spreadsheet will not change, I suggest you select True<\/strong>.<\/td>\n<\/tr>\n
          Name<\/strong><\/td>\nThe default name is File System Task<\/strong>. You can rename the task to better reflect what it does. For this exercise, I renamed the task Copy to new folder<\/strong>.<\/td>\n<\/tr>\n
          Description<\/strong><\/td>\nYou can type a full description here. I used Copy the Employee’s excel spreadsheet to d:\\Demo\\<\/strong>.<\/td>\n<\/tr>\n
          Operation<\/strong><\/td>\nThe default option is Copy File<\/strong>. Because that’s what we’ll do in this exercise, you can leave this option selected. But note that other options are available, and I will cover some of them in future articles.<\/td>\n<\/tr>\n
          IsSourcePathVariable<\/strong><\/td>\nLeave this option set at its default value, False<\/strong>. Although we’ll be setting the destination to a variable, the source path will directly reference the original Excel file.<\/td>\n<\/tr>\n
          SourceConnection<\/strong><\/td>\nThis is where you select the connection to the source file you want to copy. From the drop-down list associated with this property, select as currently we have no connections set up. The Usage type:<\/strong> defaults to Existing File<\/strong> which is correct, as we have already created the spreadsheet, select the Browse<\/strong> button and browse to the Excel spreadsheet created called Employee.xlsx<\/strong> and click OK<\/strong> to accept.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

          \tTable 1: Configuring the File System task properties <\/p>\n

          \tFigure 8 shows what your File System Task Editor<\/strong> should look like after you’ve configured the properties. Review your settings, and then click OK<\/strong>. <\/p>\n

          \t\"1538-Figure8-6399c94e-9b1e-44a1-ba0e-02d\"<\/p>\n

          \tFigure 8: Configuring properties in the File System Task Editor<\/strong><\/strong> <\/p>\n

          \tThe control flow should now show that the inverted red X has been removed from the File<\/strong>System<\/strong> task and that the task has been renamed to Copy to new folder<\/strong>. <\/p>\n

          \tTo verify whether the task works, run the package by clicking on the green arrow (the Run<\/strong> button) on the menu bar. Then open Windows Explorer and check that the file has been copied into the target folder correctly. <\/p>\n

          Using an expression to define a variable value<\/h3>\n

          \tIn some cases, you might want to generate a variable’s value at run time, rather than assign a specific value, as we did in the previous exercise. This can be useful if the variable value needs to change during the package’s execution or that value is derived from other sources or processes. For example, the Foreach<\/strong> Loop<\/strong> container might use a variable whose value must change each time the container loops through the targeted list of objects. (I’ll explain how to use a variable in this way in a future article.) <\/p>\n

          \tIn this section, I demonstrate how to create an expression that dynamically generates a value for a String<\/strong> variable named DestinationFileName<\/strong>. The exercise uses the same test environment as the one in the previous exercise. We will again copy the original Excel file to the destination folder, only this time we’ll use an expression to define the path and file name. The expression will rename the file by tagging the date to the end of the filename, as in Employee_201265.xlsx<\/strong>. <\/p>\n

          \tYour first step, then, is to create a variable named DestinationFileName<\/strong>, Follow the same steps you used to create the DestinationFilePath<\/strong> variable in the previous exercise, but leave the value blank, as shown in Figure 9. <\/p>\n

          \t\"1538-Figure10-b9eaa6b8-4446-48ba-84d5-49\"<\/p>\n

          \tFigure 9: Creating the DestinationFile<\/strong><\/strong>Name<\/strong><\/strong> variable <\/p>\n

          \tTo populate the value, you’ll create an expression in the variable’s properties. An expression is a formula made up of elements such as variables, functions, and string values. The expression returns a single value that can then be used by the variable. <\/p>\n

          \tTo create an expression for the DestinationFileName<\/strong> variable, open the Properties<\/strong> window to display the variable’s properties. You can open the window by clicking Properties <\/strong>W<\/strong>indow<\/strong> on the View<\/strong> menu. You can also open the Properties<\/strong> window by pressing F4<\/strong>. The Properties<\/strong> window displays the properties for whatever component is selected in your SSIS package. To display the properties for the DestinationFileName<\/strong>variable, select the variable in the Variables<\/strong> window. <\/p>\n

          \tWhen the variable’s properties are displayed in the Properties<\/strong> window, you can create an expression that defines the variable’s value. To do so, first set the EvaluateAsExpression<\/strong> property to True<\/strong>. This enables the variable to use an expression to define its value. Then click the browse button (…<\/strong>) associated with the Expression<\/strong> property, as shown in Figure 10. <\/p>\n

          \t\"1538-Figure10-5f32a733-d9c6-4af1-9623-b5\"<\/p>\n

          \tFigure 10: Launching the Expression Builder<\/strong><\/strong> dialog box <\/p>\n

          \tClicking the browse button opens the Expression Builder<\/strong> dialog box, which is divided into three sections, as shown in Figure 11. <\/p>\n

          \t\"1538-Figure10a-29556a9c-1c87-4202-a5e2-7\"<\/p>\n

          \tFigure 11: The Expression Builder<\/strong><\/strong> dialog box <\/p>\n

          \tNotice in Figure 11 that I’ve labeled the three sections of the Expression<\/strong>Builder<\/strong> dialog box as 1<\/strong>, 2<\/strong>, and 3<\/strong>: <\/p>\n

            \n
          1. The variables available to use in your expression<\/li>\n
          2. The functions and operators available to use in your expression<\/li>\n
          3. The workspace where you create your expression<\/li>\n<\/ol>\n

            \tThere is also a button at the bottom of the screen labeled Evaluat<\/strong>e<\/strong> Expression<\/strong>. When you click this button, the expression is evaluated and, if a value can be returned, it is displayed in the Evaluated value<\/strong> area just above the button. <\/p>\n

            \tIn the previous section, we created the DestinationFilePath<\/strong> variable, which held the target folder into which we copied the Excel file. In this exercise, we’ll again copy the original Excel file to the new folder as described earlier. To do this, we’ll use the DestinationFilePath<\/strong> variable as part of our expression to provide the path name for the new DestinationFileName<\/strong> variable. <\/p>\n

            \tAs a result, the first step you should take to create your expression is to drag the DestinationFilePath<\/strong> variable to the Expression<\/strong> section, as show in Figure 12. If you click the Evaluate Expression<\/strong> button after you add the variable, the Evaluated<\/strong>value<\/strong> field should show the value assigned to that variable, which is the path d:\\Demo<\/strong>. <\/p>\n

            \t\"1538-Figure11-87a45e46-0a7b-4fc6-832e-03\"<\/p>\n

            \tFigure 12: Adding the DestinationFilePath<\/strong><\/strong> variable to your expression <\/p>\n

            \tNext, you’ll need to add to your expression the elements necessary to name the file. First, add a concatenation operator (+<\/strong>), and then add the string value Employee<\/strong>, enclosed in double quotes, as shown in the following script: <\/p>\n

            \t@[User::DestinationFilePath] + \"Employee\" <\/pre>\n
             \tYou can evaluate your expression at any time by clicking on the Evaluate<\/strong>Expression<\/strong> button. For instance, if you evaluate your expression at this point, you should receive the following results: <\/p>\n
            \td\\Demo\\Employee <\/pre>\n
             \tI will now show you how to include the date and time in the expression so that the variable can include them. To create this part of the expression, we’ll use the following functions: <\/p>\n
              \n
            • GETDATE:<\/strong> Returns the current date and time.<\/li>\n
            • YEAR:<\/strong> Returns the year component (as an integer) of a date value.<\/li>\n
            • MONTH:<\/strong> Returns the month component (as an integer) of a date value.<\/li>\n
            • DAY:<\/strong> Returns the day component (as an integer) of a date value.<\/li>\n
            • RIGHT:<\/strong> Returns the number of characters specified counting from the right of the string.<\/li>\n<\/ul>\n
               \tThe GETDATE<\/strong> function returns data as a DT_DBTIMESTAMP<\/strong> value, which is made up of 29 characters that include integers, colons, dashes, a space, and a period. Consequently, using this function in conjunction with the other functions makes it easier to concatenate the values necessary to create the file name. <\/p>\n
              \n
               \tNote:<\/strong>
               \tThere are many ways to build the expression I am about to explain, but this is the method I use and find easiest. \t<\/p>\n<\/p><\/div>\n
               \tWhen building the expression, we need to add the date parts one at a time – year, month, date, hours, and minutes. <\/p>\n
               \tAfter we add each component, we can use the Evaluate<\/strong>Expression<\/strong> button to see what the value looks like to ensure that we’re happy with it up to that point. <\/p>\n
               \tBecause we want the year to appear before the other data parts, we’ll start with that element of the expression. To add the year, we’ll use the YEAR<\/strong> function along with the GETDATE<\/strong> function. This will return a four-digit integer, which we’ll convert to a string. <\/p>\n
               \tAs noted above, the GETDATE<\/strong> function returns the current timestamp. But we can apply the YEAR <\/strong>function to that timestamp to extract only the year value, as shown in the following code fragment: <\/p>\n
              \tYEAR(GETDATE())  <\/pre>\n
               \tThis will return only the year, but as an integer. However, because we will be concatenating that value with a string value, which need to convert the year to a string, as the following code shows: <\/p>\n
              \t(DT_WSTR,4)YEAR(GETDATE())  <\/pre>\n
               \tNotice that, to convert the year value to a string, we must precede the YEAR<\/strong> function with the target data type and length in parentheses. We can then append this code with our original code, as shown in the following script: <\/p>\n
              \t@[User::DestinationFilePath] + \"Employee\"\n+ (DT_WSTR,4)YEAR(GETDATE())  <\/pre>\n
               \tNow if we use the Evaluate Expression<\/strong> button, the results should look like the following: <\/p>\n
              \td:\\Demo\\Employee2012 <\/pre>\n
               \tNext, we need to add the month to our expression. We will use the MONTH<\/strong> function with the GETDATE<\/strong> function in similar to how we used the YEAR<\/strong> function above. However, we must take extra steps to accommodate the fact that the MONTH<\/strong> function returns a single digit for months prior to October and returns two digits from the other months. When the function returns a single digit, we need to precede the returned value with a 0<\/strong> to ensure we always return two characters. That way, dates such as 15 June 2012 will be returned as 20120615, rather than 2012615, which makes it easier to manage files in such applications as Windows Explorer. <\/p>\n
               \tThe trick in doing this is to add the 0<\/strong> only when we need it. That’s where the RIGHT<\/strong> function comes in. The function takes two arguments. The first argument is an expression that returns a character value. The second argument is the number of characters we want to extract from that value. However, those characters are extracted from right-to-left. <\/p>\n
               \tLet’s look at a couple examples of the RIGHT<\/strong> function to demonstrate how it works. The first one includes a simple expression that concatenates 0<\/strong> and 6<\/strong>: <\/p>\n
              \tRIGHT(\"0\"+\"6\",2) <\/pre>\n
               \tThe expression comprises everything before the comma. In this case, it concatenates the two values (rather than adding them) and returns the value 06<\/strong>. The second argument, 2<\/strong>, specifies that only the right two characters be returned by the function. Because the expression returned only two characters, the function will return both of them. <\/p>\n
               \tHowever, suppose your expression returns more than two characters. In the following example, a single digit is concatenated with two digits: <\/p>\n
              \tRIGHT(\"0\"+\"12\",2) <\/pre>\n
               \tThe expression in this case returns the value 012<\/strong>. However, the second argument specifies that the RIGHT<\/strong> function should return only the right two characters, so the function will return only 12<\/strong>. <\/p>\n
               \tNow let’s return to the expression we’re creating to generate the file name. At this point, we’re trying to add the month components. First, we need to extract the month and convert it to a string value, just like we did with the year: <\/p>\n
              \t(DT_WSTR, 2) MONTH(GETDATE()) <\/pre>\n
               \tAs you would expect, this part of the expression will return a one-digit or two-digit integer that represents the month. We can then use this code within the RIGHT<\/strong> function to ensure that we always extract two digits: <\/p>\n
              \tRIGHT(\"0\"+(DT_WSTR, 2) MONTH(GETDATE()) ,2) <\/pre>\n
               \tNotice that the first argument is an expression that concatenates a 0<\/strong> with the month returned by the other part of the formula. That means the expression will also return a two or three characters, depending on the month. However, because 2<\/strong> is specified as the second argument, the RIGHT<\/strong> function will return only the right two characters, thus ensuring that the outer expression always includes two characters for the month. <\/p>\n
               \tWe can then concatenate this code with our original expression, as shown in the following script: <\/p>\n
              \t@[User::DestinationFilePath] + \"Employee\"\n+ (DT_WSTR,4)YEAR(GETDATE())\n+ RIGHT(\"0\"+(DT_WSTR, 2) MONTH(GETDATE()) ,2) <\/pre>\n
               \tAfter the month is added to our outer expression, we use the Evaluate Expression<\/strong> button to view the current value.. The results should look similar to the following: <\/p>\n
              \td:\\Demo\\Employee201207 <\/pre>\n
               \tNext, we will add the day to our expression. Adding the day is similar to what we did to add the month, except that we use the DAY<\/strong> function, as shown in the following code: <\/p>\n
              \t@[User::DestinationFilePath] + \"Employee\"\n+ (DT_WSTR,4)YEAR(GETDATE())\n+ RIGHT(\"0\"+(DT_WSTR, 2) MONTH(GETDATE()) ,2)\n+ RIGHT(\"0\"+(DT_WSTR, 2) DAY(GETDATE()) ,2) <\/pre>\n
               \tAs you can see, we’ve concatenated the day information with the rest of our expression. When you click the Evaluate Expression<\/strong> button, it should now return results similar to the following: <\/p>\n
              \td:\\Demo\\Employee20120715 <\/pre>\n
               \tAll that’s left to do is to add the Excel file extension. Simply add another concatenate operator, followed by .XLSX<\/strong> enclosed in double quotes. Our full expression should now be complete: <\/p>\n
              \t@[User::DestinationFilePath] + \"Employee\"\n+ (DT_WSTR,4)YEAR(GETDATE())\n+ RIGHT(\"0\"+(DT_WSTR, 2) MONTH(GETDATE()) ,2)\n+ RIGHT(\"0\"+(DT_WSTR, 2) DAY(GETDATE()) ,2)\n+ \".XLSX\" <\/pre>\n
               \tIf we click the Evaluate Expression<\/strong> button one more time, we should see the results we’ve been looking for: <\/p>\n
              \td:\\Demo\\Employee20120623.XLSX <\/pre>\n
               \tWe’ve built this expression up in stages so you could better see how all the pieces fit together. The expression, as it appears in the Expression Building<\/strong> dialog box on your system, should now look similar to the one shown in Figure 13. <\/p>\n
               \t\"1538-Figure13-2d1b4801-1302-4786-8e08-32\"<\/p>\n
               \tFigure 13: Your final expression in the Expression Builder<\/strong><\/strong> dialog box <\/p>\n
               \tAfter you’ve evaluated your expression, click OK<\/strong> to close the Expression<\/strong>Build<\/strong>er<\/strong> dialog box. The V<\/strong>ariables<\/strong> window should now show the value of the DestinationFileName<\/strong> variable as the one generated by the expression, as shown in Figure 14. <\/p>\n
               \t\"1538-Figure14-4a7c9cef-cb3a-4c15-98f3-7f\"<\/p>\n
               \tFigure 14: The new value for the DestinationFileName<\/strong><\/strong> variable <\/p>\n
               \tYour final step is to update the File System <\/strong>task so it uses the DestinationFileName<\/strong> variable. To do this, open the File System Task Editor<\/strong> for the task named Copy to new folder<\/strong>. Change the value of the DestinationVariable<\/strong> property to the DestinationFileName<\/strong> variable, as shown in Figure 15. <\/p>\n
               \t\"1538-Figure15-16ceebcd-00af-4b70-b1a0-d2\"<\/p>\n
               \tFigure 15: Using the DestinationFileName<\/strong><\/strong> variable <\/p>\n
               \tClick OK<\/strong> to close the File System Task Editor<\/strong>. Then check that the new variable works correctly by running the package. When you’re finished, open Windows Explorer and verify that the new file has been created and that it uses the correct name. <\/p>\n
              System Variables<\/h2>\n
               \tEach SSIS package includes a large number of system variables that you can use for debugging, error handling, change tracking, and other purposes. Figure 16 shows a list of system variables in our package. <\/p>\n
               \t\"1538-Figure9-46c81a0e-cd46-4617-8bd6-30d\"<\/p>\n
               \tFigure 16: System variables in our SSIS package <\/p>\n
               \tLike user-defined variables, system variables are scope-specific. In other words, they are either related to a task, container, or package. The top red block in Figure 16 are system variables linked to the Data<\/strong>Flow<\/strong> task, and the bottom block are those related to the package as a whole. The variable in the blue box is the user-defined variable DestinationFilePath<\/strong>. <\/p>\n
              Summary<\/h2>\n
               \tIn this article, we created two variables. For the first variable, we assigned a static value, which is used by the variable whenever it is referenced during package execution. For the second variable, we used an expression to define the value. This way, the variable’s value is generated automatically at run time. The article also demonstrated how to use the Copy File<\/strong> function in File System<\/strong> task to copy a file and rename it. We have also looked briefly at system variables. In future articles, I will show you how to use these for error handling and debugging and how to work with user-defined variables when setting up your package’s configuration and deployment. I will also show you how to use variables more extensively. <\/p>\n<\/p><\/div>\n","protected":false},"excerpt":{"rendered":"
              In the third of her SSIS Basics articles, Annette Allen shows you how to use Variables in your SSIS Packages, and explains the functions of the system-defined variables.…<\/p>\n","protected":false},"author":10747,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143528],"tags":[4242,5664,4150,4306,5698],"coauthors":[],"class_list":["post-1382","post","type-post","status-publish","format-standard","hentry","category-bi-sql-server","tag-basics","tag-data-flow","tag-sql","tag-ssis","tag-variables"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1382","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/10747"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=1382"}],"version-history":[{"count":5,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1382\/revisions"}],"predecessor-version":[{"id":91227,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1382\/revisions\/91227"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=1382"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=1382"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=1382"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=1382"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}