Using T-SQL With SSIS

One of the most exciting features of SSIS is the fact that you can use T-SQL to interact with your packages and projects natively (if you are using the project deployment model). You can write stored procedures or just open SSMS and type in the T-SQL code you need to execute or validate your packages. In this section, you will be using the project from the “Project Deployment Model” section earlier in Administering SSIS Topic.

Learn how to use SSIS, from beginner basics to advanced techniques, with online video tutorials taught by industry experts. Enroll for Free "SSIS Training" Demo!                                                                   

Executing Packages

The first item to learn in the world of T-SQL and SSIS is how to execute a package using T-SQL. If you right-click on the package in the SSIS catalog and click the script button at the top of the execution window, you can copy the script into a new query window. The script window has been available for other SQL commands in past versions of SQL Server, but now you can use this same feature in the SSIS catalog. In just about all of the SSIS windows you see in the catalog, you will have this automatic scripting option. This can do two things for you: speed up your writing of the T-SQL you need and help you learn the T-SQL commands used in this version of SSIS. By scripting out commands and looking at the names of the stored procedures, you can start noting procedures you will use frequently. The following script is an example of this code:

Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution]
@package_name=N’AdminDemo1.dtsx’,
@execution_id=@execution_id OUTPUT,
@folder_name=N’AdminDemo’,
@project_name=N’Ch22′,
@use32bitruntime=False,
@reference_id=Null
Select @execution_id
EXEC [SSISDB].[catalog].[start_execution] @execution_id

GO

You need to understand four sections of this code. The first line is a simple variable declaration that is used to hold the execution ID that you will create in the next section of the T-SQL code.

The next section of the code is the meat of the work. You are using the EXEC command to call the stored procedure in the SSISDB named Catalog.Create_Execution. This command creates an execution ID, which is a unique ID for executing the package. The execution ID is saved in a table in the SSISDB. You won’t usually need to manually touch the internal table in the SSISDB; management should be done from the SSIS catalog using T-SQL, but because this is a professional-level SSIS Tutorial, you will get to see some of the work done by the stored procedure on the SSISDB.

The stored procedure that creates the execution inserts a row on the internal. operations table. Keep in mind that the stored procedures work like any other, so with proper permissions, you can read through them. If you look at the create_execution stored procedure, you will see it calls another stored procedure named internal.insert_operation, which does the actual inserting of the row into the internal. operations table.

In the preceding execution code, the catalog.create_execution stored procedure takes a few parameters: the package name, an output parameter with the execution ID, the folder name in which the package resides, the project name of the package, whether the package should be executed in 32- bit mode, and a reference ID.

The next line of the code is a SELECT statement. This is included just to show you the execution ID in the results pane. You can run the execution without this line.

The last line executes the package by calling another stored procedure: catalog.start_execution. This stored procedure calls another stored procedure named internal.start_execution_internal. As shown in below screenshot, this stored procedure has a small icon of a lock on it in the SSISDB. This indicates that the stored procedure is a SQL CLR stored procedure. If you right-click on the stored procedure, you will see that the modify option is grayed out. As mentioned earlier, you are probably never going to alter these stored procedures. They are calling the internal workings of the SSIS catalog. You can see the code in this stored procedure by right-clicking on it and selecting “Script Stored Procedure as” ⇒ “Alter to” ⇒ “New Query Editor Window.” This shows you the API call to the internal SSIS code.

this stored procedure has a small icon of a lock on it in the SSISDB

Executing the preceding code will insert a row into the InsertTest table. The value inserted will be PackageParam, which is the default value of the package parameter saved in the package. The next section demonstrates how to change the value of this parameter.

SSIS Tutorials

Using Parameters

Now that you have the AdminDemo package deployed to the SSIS catalog, you are ready to set the parameter value and execute the package using T-SQL. The previous section showed the execution query, but it was incomplete because the AdminDemo package has a parameter whose value you want to set. To do so, just add the following lines to the execution query:

Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution]
@package_name=N’AdminDemo1.dtsx’,
@execution_id=@execution_id OUTPUT, @folder_name=N’AdminDemo’,
@project_name=N’Ch22′, @use32bitruntime=False, @reference_id=Null
Select @execution_id
DECLARE @var0 sql_variant = N’PackageParam’
EXEC [SSISDB].[catalog].[set_execution_parameter_value]
@execution_id,
@object_type=30,
@parameter_name=N’strInput’,
@parameter_value=@var0
EXEC [SSISDB].[catalog].[start_execution] @execution_id

GO

The two new lines in the query are the variable declaration, which holds the value of the parameter, and the stored procedure to set the parameter value. The DECLARE creates a variable and sets the value to a string value — in this case, PackageParam.

The next line calls the stored procedure named catalog.set_execution_parameter_value. This stored procedure has a few parameters: the execution ID of the package created in the creative execution procedure, the object type, the parameter name, and the parameter value, which is set to the previously created variable. The object type is set to either 20 (for a project parameter) or 30 (for a package parameter).

If you run the preceding script and then query the InsertTest table in AdventureWorks, you will see a new row added to the table. To ensure that the parameter is working, change the parameter value in the script from PackageParam to Changed with T-SQL. Then run the script again. You should see this row added to the table.

Querying Tables for Parameter Values

The T-SQL statements work great for changing the default value of a parameter for package execution. The value is still basically hardcoded into a script. It would be nice if you could query a table and set the value of a parameter based on the results of the query.

The next bit of code shows how to do that. It’s very similar to the previous TSQL. The only difference is that the value is selected from a table from AdventureWorks.

Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution]
@package_name=N’AdminDemo1.dtsx’,
@execution_id=@execution_id OUTPUT,
@folder_name=N’AdminDemo’,
@project_name=N’Ch22′,
@use32bitruntime=False,
@reference_id=Null
Select @execution_id
DECLARE @var0 sql_variant = (
Select top(1) FirstName from
[AdventureWorks].[Person].[Contact]
Order by LASTNAME
)
EXEC [SSISDB].[catalog].[set_execution_parameter_value]
@execution_id,
@object_type=30,
@parameter_name=N’strInput’,
@parameter_value=@var0
EXEC [SSISDB].[catalog].[start_execution] @execution_id

GO

On the line where the parameter value is set, the hardcoded value of PackageParam has been replaced with a query from AdventureWorks, as shown here:

Select top(1) FirstName from
[AdventureWorks].[Person].[Contact]
Order by LASTNAME

This query returns the results of someone’s first name — in this case, “Syed.” You can run this query separately from the package execution to see if your results differ.

Now you have the tools to set parameter values using queries. This can be very useful in terms of controlling many packages, even in different projects or on different machines. You can update a table, and all the package executions querying the table to set their parameters will be altered, but if you want to change sets of parameter values easily, then you will want to use environments.

Using Environments

Imagine you have a project that runs on a schedule. On weekdays, the packages in the project need to run with a certain set of parameter values, but on weekends, they need a different set of values. This is a project with several packages, and each package has several parameters. All the packages in the project need to use the same parameter values when they run. Environments make it easy to run these packages with different sets of parameter values.

This example project, named EnvironmentDemo, contains three packages: EnvironDemo1, EnvironDemo2, and EnvironDemo3. The project has two project-level parameters.

Creating and Configuring Project Level Parameters

If you want to create project-level parameters, double-click on Project. params under the project in SQL Server Data Tools in Solution Explorer, as shown in the below screenshot. Here, you can see the two project level parameters that already exist in this project: strRunType and intNumber. Each has a default value set, but you do not want to use the default value.

double-click on Project. params under the project in SQL Server Data Tools in Solution Explorer

This package will simply write the two-parameter values to a table named EnvironmentDemo in AdventureWorks. Following is the script to create this table. Deploy this project to a folder named EnvironmentDemo in the SSIS catalog.

CREATE TABLE [dbo].[EnvironmentDemo](
[RunType] [varchar](50) NULL,
[IntParamValue] [nchar](10) NULL
) ON [PRIMARY]

Setting Up Your Environments

Now deploy your project using the project deployment model as described in the “Project Deployment Model” section earlier in Administering SSIS Topic. After the project is deployed, you are ready to set up your environments. In the SSIS catalog, right-click on the Environments folder and click Create Environment. Name the first environment Week Days and then create a second environment and name it Weekends. When you are done, you should see two environments in the EnvironmentDemo folder, as shown below screenshot.

When you are done, you should see two environments in the EnvironmentDemo folder

To set variable values in the environments, you can right-click on the environment and select Properties. Then click the Variables option on the left. Set the name, type, and value of the variables as shown in the below screenshot.

Click the Variables option on the left. Set the name, type, and value of the variables

You can also set the value of the environments using T-SQL queries. The following code shows how this is done. The stored procedure named catalog.create_environment_variable creates a variable in the environment. This stored procedure takes six parameters: variable name, whether the variable is sensitive and therefore needs to be encrypted, the name of the environment, the folder name where the project is found, the value of the variable, and the data type.

DECLARE @var int = N’2′
EXEC [SSISDB].[catalog].[create_environment_variable]
@variable_name=N’intNumber’,
@sensitive=False,
@description=N”,
@environment_name=N’Weekends’,
@folder_name=N’EnvironmentDemo’,
@value=@var,
@data_type=N’Int32′
GO
DECLARE @var sql_variant = N’Weekends’
EXEC [SSISDB].[catalog].[create_environment_variable]
@variable_name=N’strRunType’,
@sensitive=False,
@description=N”,
@environment_name=N’Weekends’,
@folder_name=N’EnvironmentDemo’,
@value=@var,
@data_type=N’String’
GO

Once you have the variables created in the environments, ensure that the values are set.

Configuring the Project to Use Environments

The next step is to configure the SSIS project to use the environments you just created. Right-click on the EnvironDemo1 package and select Configure. In the Scope dropdown menu, select EnvironmentDemo, which is the project. You could create package level parameters and use the package level scope here, but because you want all your packages to use the same value, you will use the project scope.

Frequently Asked SSIS Interview Questions & Answers

Click the References option on the right. Then click Add at the bottom and select the Week Days environment. Add the Weekends environment the same way. When you are done, your window should look like the below screenshot. Click OK to close the window.

Add the Weekends environment the same way

Now that the packages have references to the environments, you need to configure the project to use the variables from the environments. Right-click on the EnvironmentDemo project and then click configure. Ensure that the scope is set to All Packages and Project. Click the ellipses next to the intNumber variable. This will open the Set Parameter Value dialog. At the bottom are three options: Edit value, Use default value from the package, and Use environment variable. Select the third option and choose the intNumber variable from the dropdown menu, as shown in below screenshot. Repeat the same steps for the strRunType variable.

Select the third option and choose the intNumber variable from the dropdown menu

As stated before, all this work in the GUI can be done with T-SQL. If you click the script button before clicking OK and send the script to a new query window, you will see the following code (the stored procedure used here is catalog.set_object_parameter_value):

EXEC [SSISDB].[catalog].[set_object_parameter_value] @object_type=20,
@parameter_name=N’intNumber’,
@object_name=N’EnvironmentDemo’,
@folder_name=N’EnvironmentDemo’,
@project_name=N’EnvironmentDemo’,
@value_type=R,
@parameter_value=N’intNumber’
GO
EXEC [SSISDB].[catalog].[set_object_parameter_value] @object_type=20,
@parameter_name=N’strRunType’,
@object_name=N’EnvironmentDemo’,
@folder_name=N’EnvironmentDemo’,
@project_name=N’EnvironmentDemo’,
@value_type=R,
@parameter_value=N’strRunType’

GO

The only new parameter here is value_type, which is set to R in this example. This indicates that the parameter value is a referenced value and is using an environment variable. You can also set the value_type parameter to V, to indicate the parameter is a literal value.

Now you are ready to execute the package. Right-click on the EnvironDemo1 package in the SSMS Object Explorer window and select Execute. At the bottom of the Execute Package dialog is an environment option. Place a check next to Environment and select Week Days from the dropdown. Click OK to execute the package. When a message box appears asking if you would like to open the overview report, click No.

Query the EnvironmentDemo table in AdventureWorks and you will see the row with the value of Week Days and the number 5. You can truncate or delete from this table if you prefer. Now repeat the same steps to execute the EnvironDemo2 package. Another row with the same values will be inserted.

Right-click on the EnvironDemo3 package and select Execute. Select the Weekends environment this time. Instead of letting the GUI execute the package, click the script button and send the script to a new query window. Click Cancel to close the Execute window. In the newly created query window you will see the following code:

Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution]
@package_name=N’EnvironDemo3.dtsx’,
@execution_id=@execution_id OUTPUT,
@folder_name=N’EnvironmentDemo’,
@project_name=N’EnvironmentDemo’,
@use32bitruntime=False,
@reference_id=5
Select @execution_id
EXEC [SSISDB].[catalog].[start_execution] @execution_id
GO

The new parameter in this code is the reference_id. This is an internal reference to the environment. The only way to find this ID is to create the script as you just did.

Related Article: T-SQL Interview Questions

Setting Environment References

When you create an environment, you may want to use it so that if the package is moved it still points to the same environment. This can be accomplished by creating an absolute environment reference. Basically, environment references can be set to either relative or absolute.

Absolute references always point to the same environment, no matter where the project or package is moved.
Relative references point to the place to which a project or package is moved.
Right-click on the EnvironmentDemo project and select Configure. Click the Reference option on the left, and then click the Add button at the bottom. Note that the Browse Environments window contains a local folder and a duplicate beneath it. In this case, the local folder is the EnvironmentDemo folder, as shown in below screen shot. If you select the local folder environment, the reference is set as a relative reference. If you select the folder below, the reference is set to absolute.

In this case the local folder is the EnvironmentDemo folder

In most situations you will use relative references so that packages moved from development to production will use the environments in production. Absolute references are useful when you have several packages using the same set of parameters in an environment. You can point different projects to the same environments using absolute references.

Using Data Taps

Data viewers are great tools to use while working in SQL Server Data Tools to view data in a Data Flow. In this release of SQL Server Integration Services, you can now place a data tap in a package executing on the server. A data tap acts like a data viewer from SQL Server Data Tools because it lets you see data in a Data Flow, although with different methods. It enables you to see a sample of the rows moving through a Data Flow sent to a file for review.

The sample code included with this Tutorial at Wiley contains a project named DataTapDemo. Deploy this project to a folder named DataTapDemo on the SSIS catalog. Once you have the project deployed to the server, you are ready to execute and add a data tap to the package.

The code to add a data tap is just like all the other commands discussed already; it uses a stored procedure. The stored procedure to add the data tap is catalog.add_data_tap. The following code shows how to add a data tap to the sample package:

exec [SSISDB].[catalog].[add_data_tap]
@execution_id= @execution_id,
@task_package_path = ‘PackageGet Person Data’,
@dataflow_path_id_string = ‘Paths[Person Source.OLE DB Source
Output]’,
@data_filename = datatap.csv’

The preceding code adds a data tap to the Data Flow in the package. The parameters are those you will probably need to retrieve while you have the package open in SQL Server Data Tools. The execution ID comes from the execution creation, which is done before this code. The task package path is the path of the Data Flow in the package. To find this, open the package in SQL Server Data Tools, right-click on the Data Flow, and select Properties. The package path is the property you need.

The Data Flow path ID is found in the Data Flow. Open the Data Flow of the package in SQL Server Data Tools. Then right-click on the Data Flow line where you want to add the data tap and select Properties. The IdentificationString property is the property you need for the Data Flow path_id_string parameter. The last parameter is the filename where you want the rows written. The file path cannot be changed; the data tap files are always created in the data dumps folder in the SQL Server install directory.

The following example shows the complete execution code to run a package with a data tap. There is no GUI interface to create data taps. Creating data taps is a manual process.

Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution]
@package_name=N’DataTap1.dtsx’,
@execution_id=@execution_id OUTPUT,
@folder_name=N’DataTapDemo’,
@project_name=N’DataTapDemo’,
@use32bitruntime=False,
@reference_id=Null
Select @execution_id
exec [SSISDB].[catalog].[add_data_tap]
@execution_id= @execution_id,
@task_package_path = ‘PackageGet Person Data’,
@dataflow_path_id_string = ‘Paths[Person Source.OLE DB Source
Output]’,
@data_filename = ‘Cdatatap.csv’
EXEC [SSISDB].[catalog].[start_execution] @execution_id
GO

 

List of Related Microsoft Certification Courses:

SSRS	Power BI
SSAS	SQL Server
SCCM	SQL Server DBA
SharePoint	BizTalk Server
Team Foundation Server	BizTalk Server Administrator