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:

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:

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

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

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

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:

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:

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:

You will have the code to create backups:

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:

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

Select SqlStatementSource and press the Ellipsis button:

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”

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:

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 the 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:

We will now use the script task:

Press the ellipsis button in ReadWriteVariables:

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

Add the following namespace in the code:

using System.IO;

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

public void Main()
	{
            // TODO: Add your code here
  
            Dts.TaskResult = (int)ScriptResults.Success;
            string path = Dts.Variables["User::foldername"].Value.ToString();
            DirectoryInfo di = Directory.CreateDirectory(path);
        }

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:

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

CREATE TABLE [dbo].[folderpath](
	[id] [smallint] NOT NULL,
	[path] [nvarchar](500) NULL,
 CONSTRAINT [PK_folderpath] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
INSERT [dbo].[folderpath] ([id], [path]) VALUES (1, N'c:\marketing')
INSERT [dbo].[folderpath] ([id], [path]) VALUES (2, N'c:\sales')
INSERT [dbo].[folderpath] ([id], [path]) VALUES (3, N'c:\hr')
INSERT [dbo].[folderpath] ([id], [path]) VALUES (4, N'c:\accounting')
INSERT [dbo].[folderpath] ([id], [path]) VALUES (5, N'c:\design')

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

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

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:

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:

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

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

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:

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

• Functions (SSIS Expression)
• SQL Server Integration Services C# Script Task Example
• Integration Services (SSIS) Expressions

Useful tools

Devart SSIS Data Flow Components – allow you to integrate database and cloud data via SQL Server Integration Services.

Tags: c#, sql server, ssis Last modified: September 23, 2021