Common Examples of SSIS Expressions and Variables

Introduction

This article helps to enrich the functionality of SSIS. It is possible for example to set properties in tasks using expressions and we can assign values and create code to enrich the SSIS functionality.

This article is oriented to SSIS developers who have experience with basic tasks, but are not familiar with variables, scripts to enrich the functionality of the SSIS and to provide dynamic functionality.

This time we will talk about SSIS expressions, variables, and loops in SSIS. We will use expressions to set values in tasks using variables. We will also learn to create Script code in C#.

We will first talk about SSIS expressions and then we will use SSIS with the script task. Finally, we will work with Loop containers and the script task.

Requirements

  1. SQL Server
  2. SSIS Installed (included with the SQL Server installer)
  3. SQL Server Data Tools (SSDT)

Get started

In our first example, we will pass the path to back up a database using a SSIS variable.

Working with SSIS variables

In a SSIS project in SSDT, go to SSIS>Variables:

Figure 1. Working with variables

We will create a variable named path and the value will be the path where we want to store the SQL Server database backup. Note that we are using double \\. This is because \ is a special character:

Figure 2. Creating a variable
Figure 2. Creating a variable

We will use the Back Up Database Task to generate the backup:

Figure 3. Back Up Database Task
Figure 3. Back Up Database Task

Select Full for the Backup type and select the database that you want to back up:

Figure 4. Selecting database and backup type
Figure 4. Selecting database and backup type

Select the Back Up task and go to properties and press the ellipsis button in the expressions property:

Figure 5. Expressions
Figure 5. Expressions

Expressions are used to assign dynamic values, expressions, variables to a property. In this example, we will assign the variable created in figure 2 to the DestinationAutoFolderPath property. This property is the path to store the backup. Press the ellipsis button to create an expression:

Figure 6. Using Expressions for task properties
Figure 6. Using Expressions for task properties

Expand Variables and Parameters. And drag and drop the User:path variable. We are assigning the value c:\sql, which is the value assigned to the variable:

Figure 7. Using variables in an Expression
Figure 7. Using variables in an Expression

If you run the task, you will be able to create a file in the c:\sql path. However, what happens if I want to assign the backup name using expressions?

We will create the backup name using the format backupnamemmddyyyy (where mm is month, dd is day and yyyy is the current year).

To do this, we are going to check the current T-SQL created for the current task:

Figure 8. View T-SQL code
Figure 8. View T-SQL code

You will have the code to create backups:

Figure 9. T-SQL code
Figure 9. T-SQL code

Working with SSIS expressions

In this new Example, we will create a backup with the name testingddmmyyyy.bak. We will use SSIS expressions for this purpose. We will use the Execute SQL Task:

Figure 10. Execute SQL Task
Figure 10. Execute SQL Task

On the Execute SQL Task, go to Expressions and press the ellipsis button:

Figure 11. Expressions
Figure 11. Expressions

Select SqlStatementSource and press the Ellipsis button:

Figure 12. SqlStatementSource property
Figure 12. SqlStatementSource property

In the statement, write the following expression:

“BACKUP DATABASE [testing1] TO DISK = N’c:\\sql\\” +
RIGHT(“0” + (DT_STR,4,1252)DATEPART(“mm” , gedate() ),2) +
RIGHT(“0” + (DT_STR,4,1252)DATEPART(“dd” , gedate() ),2)
+(DT_STR,,1252)DATEPART( “yyyy” ,getdate())+”.bak”

Figure 13. Using an expressions to include dates
Figure 13. Using an expression to include dates

Let me explain the expressions:

+ is used to concatenate strings. Getdate is a function used to get the date. DATEPART is used to extract part of the date information. For example, DATEPART(“mm”,getdate(),2) is used to get the months of a date. DATEPART (“dd”, getdate()…). DT_STR converts dates to strings. 1252 is the code page, which is Latin American code. 4 is the length of the string. RIGHT is used to get the string starting at the first character (character 0) and show the first 2 characters.

If everything is OK, the backup name will include the current date in the format specified when you run the package:

Figure 14. The backup with the current date using SSIS expressions
Figure 14. The backup with the current date using SSIS expressions

Using scripts

In the next example, we will learn to combine expressions with the Script Task. The script task enriches the SSIS functionality and it can work with SSIS variable. You can use C# or Visual basic to create code. In this article, we will use C#.

The following example will show how to create directories in the operative system using the Script Task and SSIS variable.

We will first create a variable named foldername of the Data type string:

Figure 15. SSIS variable
Figure 15. SSIS variable

We will now use the script task:

Figure 16. Script task
Figure 16. Script task

Press the ellipsis button in ReadWriteVariables:

Figure 17. Adding SSIS variables to the Script Task
Figure 17. Adding SSIS variables to the Script Task

Add the foldername variable created in figure 15 and press the Edit Script button:

Figure 18. Editing Script
Figure 18. Editing Script

Add the following namespace in the code:

The following code will create a system directory based on the variable value:

If everything is OK, the directory named c:\myfolder (see figure 15) will be created.

Using loops, variables, and the script task

In the next example, we will create system directories using the paths stored in a table:

Figure 19. Table with the paths
Figure 19. Table with the paths

The following T-SQL code will create the table with the data:

Now, in SSDT, drag and drop the Execute SQL Task. We will retrieve the paths from the table using this task.

In Connection, specify the SQL Server name, authentication information and specify the Database that contains the table folderpath just created. In ResultSet, select Full result-set.

In SQLStatement, write the following query:

SELECT [id],[path]

FROM [dbo].[folderpath]

GO

Figure 20. Execute SQL Task
Figure 20. Execute SQL Task

In Result Set, write 0 as Result Name. In Variable Name, select the variable foldername:

Figure 21. Result set
Figure 21. Result set

We will use the Foreach Loop Container and add the Script Task created in figure 18 to the loop. We will create several system directories from the table using the Execute SQL Task and the Foreach Loop Container with the Script task:

Figure 22. Using the Foreach Loop Container
Figure 22. Using the Foreach Loop Container

In the Foreach Loop Editor, in Enumerator, select Foreach ADO Enumerator. This Enumerator will get the data from the Execute SQL Task. In ADO object source variable, select the foldername variable. In enumeration mode, select Rows in the first table:

Figure 23. Collection tab
Figure 23. Collection tab

In Variable Mappings, select foldername as a variable. In Index, select 1. 1 is the second column (0 is the first one):

Figure 24. Variable Mappings page
Figure 24. Variable Mappings page

In the menu, go to SSIS>Variables and change Data type, select Object:

Figure 25. Object data type variable
Figure 25. Object data type variable

If everything is OK, you will have system directories created according to the rows of the table in figure 19 when you run the package created:

Figure 26. Folders created.
Figure 26. Folders created.

Conclusions

In this tutorial, we learned how to use SSIS expressions to work with variables, we learned how to create expressions and then we learned to use the script task to work with the SSIS variable and extend the SSIS functionality. As you can see, it is possible to create expressions with dates, concatenate values. You can also work with variables using the Script Task and create a customized code for your needs. We also learned how to use loops with variables using the script task.

If you have questions, do not hesitate to write your comments.

 References

Daniel Calbimonte

Daniel Calbimonte

Daniel Calbimonte is a SQL Server Microsoft Most Valuable Professional for 13 years, Microsoft Certified Trainer, Microsoft Certified IT Professional, and Microsoft Certified Technology Specialist. He has written many articles for MSSQL Tips, SQLShack and SQL Central and has created videos about SQL Server. An active blogger on the MSDN web site, Daniel has work experience within government, academia, oil and gas industries, telecommunications, banking, and software and web development with SQL Server.
Daniel Calbimonte

Daniel Calbimonte

Daniel Calbimonte is a SQL Server Microsoft Most Valuable Professional for 13 years, Microsoft Certified Trainer, Microsoft Certified IT Professional, and Microsoft Certified Technology Specialist. He has written many articles for MSSQL Tips, SQLShack and SQL Central and has created videos about SQL Server. An active blogger on the MSDN web site, Daniel has work experience within government, academia, oil and gas industries, telecommunications, banking, and software and web development with SQL Server.