This is the 22nd day of my participation in the November Gwen Challenge. Check out the event details: The last Gwen Challenge 2021

Translation reference

An Overview of SSIS Parameters – Level 15 of The Stairway to Integration Services Integration Services aims to have a comprehensive and clear understanding of SSIS. All contents are carried out on the basis of the original text. Due to various reasons such as version differences and personal doubts, the principle of complete translation is not adopted. Hopefully eventually it will be more conducive to learning and understanding SSIS,

Thanks for your support!

In this article, we will explore the first cousin of the SSIS variable: the SSIS parameter. Demonstrates parameter configuration, dynamic property value management through package parameters, and how to configure and use parameters during SSIS package execution.

First of all, open the first project we had before, FirstSSIS.

This section describes SSIS parameters

SSIS Parameters 101

SSIS parameters are very similar to SSIS variables. Variables and parameters can be used interchangeably throughout SSIS 2012. But there are some important differences:

  • After the SSIS package starts execution, the parameter is read-only.
  • Parameters are scoped (or scoped) at the project and package levels (Project and Package level).
  • Parameters have sensitive attributes (Sensitive attribute).
  • Parameters have required attributes (Required attribute).
  • Since the parameters are read-only after the SSIS package starts executing, expressions cannot be used to control parameter values.

Project Parameters

Project Parameters can be used by any SSIS package included in an SSIS Project. They can only be used in the context of the Project Deployment Model.

The project deployment model in SQL Server 2012 Integration Services is new. Project parameters are a new feature of the project deployment model. Additional features include package parameters, project connection manager, and deployment to the new SQL Server 2012 Integration Services directory.

In Solution Explorer, double-click the Project.params object in Solution Explorer to open Project Parameters, as shown:

The Project Parameters window provides a toolbar with three buttons:

  • Add parameters –Add Parameter
  • Delete parameters –Delete Parameter
  • Add the parameter to the configuration –Add Parameters to Configurations

Add Parameter

Click the “Add Parameters” button to add the new project parameters to the FirstSSIS SSIS project.

A new project Parameter named “Parameter” will be added to the SSIS project. This is the default parameter Name, and the default property for this parameter is set:

  • Data Type -Data Type: Int32
  • Value – the Value: 0
  • Sensitive – Sensitive: False
  • Required – the Required: False
  • Description – the Description:[Empty String]

Parameters and variables share many data types, but not all — as shown below, parameter data types on the left and variable data types on the right:

A scan of both lists reveals that the parameter data types are subsets of the variable data types. Parameters and variables share the following data types:

  • Boolean
  • Byte
  • DateTime
  • Decimal
  • Double
  • Int16
  • Int32
  • Int64
  • SByte
  • Single
  • String
  • UInt32
  • UInt64

Variable data types are unique:

  • Char
  • DBNull
  • Object

Once the value cell loses focus, check the data type consistency of the value attribute of the parameter. If you select data type “Int32” and enter “NaN” in the “Value” cell, the error shown below will appear when navigating to another cell in the project.params grid:

The sensitive attribute is a Boolean value (True or False), which defaults to False. Setting this property to True marks the parameter value as sensitive. If True, SQL Server 2012 Integration Services encrypts this value using the methods specified in the “Project ProtectionLevel” setting.

You can access the Project ProtectionLevel property and other Project Properties by right-clicking the Project in Solution Explorer, and then clicking Properties.

Click Properties to display the FirstSSIS property page. After the property page is displayed, you can set the Project ProtectionLevel property by clicking the ellipsis in the ProtectionLevel property text box. Click the ellipsis to display the Project Protection Level window, as shown below:

Note that the Value property of the Parameter is masked when the Sensitive property is set to True.

The Required attribute is a Boolean value (True or False), which defaults to False. When set to False, SSIS project execution does not require this parameter. When set to True, parameter values must be provided for SSIS project execution.

The “Description” attribute provides a Description of the purpose, usage, and optional values of the parameter.

a means of documenting the purpose, usage, and optional values

For demonstration purposes, set the parameters to:

  • Name: Parameter
  • Data Type: Int32
  • Value: 0
  • Sensitive: True
  • Required: True
  • Description: Test Parameter

Delete Parameter

The Delete Parameters button removes the selected parameters from the Project Parameters collection stored in the project.params object.

As shown in the figure, a warning is displayed informing the data integration developer that a package using project parameters may not execute unless it is overridden by selecting the “No more show this message” check box (it is recommended not to check this box).

Package Parameters

Configure the new SSIS package

Create a SSIS package

Before we start discussing the SSIS package parameters, let’s add a new SSIS package to the project.

In Solution Explorer, right-click “SSIS Package “under the project name, and then click” New SSIS Package “as shown:

Rename the new package to “LoadWeatherData”. Open the Package, and the configuration of the Package parameters is located under the “Parameters” TAB.

Before configuring the SSIS package parameters, let’s get some real data. You can use to access the link to access contains the actual data file (andyweather.com/data/Weathe…

Unzip the file to the selected location. It contains a comma-separated values — CSV file named Sensor1-all. As follows, a new folder named Data is created in the SSIS solution directory of FirstSSIS:

Sensor1-all.csv contains measurements collected from my personal weather station in Farmville, Virginia. I use this data whenever I train people to develop data integration solutions using SSIS. I like it because it’s real data, not sample data. It is chaotic. No one collects data as cleanly as most sample databases.

Package parameters

Package parameters are defined almost identically to project parameters. Set the following parameter properties to create a package parameter that contains the sensor1-all.csv storage path, as shown in the figure below:

  • Name: SourceFilePath
  • Data Type: String
  • Value: <the location of sensor1-all.csv on your system>
  • Sensitive: False
  • Required: True
  • Description: Weather data source file

Configuring data Flow

Add a “Data Flow Task” and rename the Data Flow Task” DFT Stage Temperature and Humidity” as follows:

Double-click DFT Stage Temperature and Humidity to open the editor. In the SSIS toolbox, expand the Other Sources category, and drag the Flat File Sources adapter onto the Data Flow Tasks canvas. Rename the flat file source FFSrc Temperature and Humidity.

Double-click the “FFSrc Temperature and Humidity” flat file source adapter to open the editor, as shown in the figure.

Flat file connection manager is not configured in this SSIS package. Click the New button to create and configure a new flat file connection manager.

Note that two things happen when you click the New button:

  1. The new flat file connection manager has been added to the Connection Manager TAB (indicated in the figure);
  2. The editor for this new flat file connection manager has been opened.

Rename connection manager “FFCM Temperature and Humidity”. Click the Browse button to navigate to the folder where the sensor1-all.csv file is stored, and then change the file type filter from “*.txt” (default) to “*.csv” in the “Open File” dialog box. The sensor1-all.csv file will then be displayed in the file list box for selection, as shown:

Select the sensor1-all.csv file and click the open button.

The General for Flat File Connection Manager page now displays the full path to the sensor1-all.csv file in the File Name text box. Make sure the Show column name in the first data row check box is selected.

Do not make any changes on the Columns page. Note that row and column separators can be set, and a preview of the changes is provided below, as shown below:

The “Advanced” page of the Flat File Connection Manager editor is shown below. We won’t make any changes on this page, but we will make some observations.

You can change the Column name here and specify Column Delimiter for each Column. You can also change data type properties. By default, all flat File Connection Manager columns are set to a string data type of length 50:

The Flat File Connection Manager preview page allows you to configure the connection manager to skip some rows. It also provides a grid to preview the first 100 rows of data, as shown below:

Click OK to complete the “FFCM Temperature and Humidity” flat file connection manager configuration and return to the flat file source editor in the “DFT Stage Temperature and Humidity” data flow task.

Click OK to close the flat file source Adapter editor.

Creating a database

Before continuing, open SQL Server Management Studio (SSMS) and connect to an SQL Server instance.

Open a new query window in SSMS and enter the text shown in Listing 1 below:

-- Listing 1
Use master
go
If Not Exists(Select name
              From sys.databases
                  Where name = 'WeatherData')
 begin
  print 'Creating database WeatherData'
  Create Database WeatherData
  print 'WeatherData database created'
 end
Else
 print 'WeatherData database already exists.'
Copy the code

Transact-sql (T-SQL) included in Listing 1 checks for the presence of a database named WeatherData on the SQL Server instance. If WeatherData exists, an existing message is returned. If the WeatherData database does not exist in the SQL Server instance, the message “creating the database” appears, the database is created, and a message is returned notifying you that the database has been created.

Why am I writing T-SQL this way? That’s a good question.

This is an example of an Idempotent Script. Idempotent means you can do it again and again without changing the result. In mathematics, adding 0 to any number will never change the value of the sum. It’s idempotent.

The Transact-SQL script in Listing 1 can be executed and re-executed. Whether it is the first or tenth execution, the WeatherData database will exist at execution completion. It’s idempotent.

Execute this script at SSMS to ensure that the database exists.

Now we can import the “temperature and Humidity “data into the WeatherData database.

Add OLE DB target

Return to SSDT-BI. Drag the OLE DB Destination (OLE DB Destination) adapter onto the Data Flow page, Connect a data stream path from the flat file source adapter “FFSrc Temperature and Humidity” to the “OLE DB target adapter “and rename the “OLE DB target” to “OLEDest Stage Temperature”. As shown in the figure:

Double-click the OLE DB Destination adapter “OLEDest Stage Temperature” to open the editor.

“OLE DB Connection Managers “is not configured in this SSIS package.

Click the “New” button next to the “OLE DB Connection Manager” drop-down menu to open the “Configure OLE DB Connection Manager” window as shown.

“Configuring OLE DB Connection Manager” lists OLE DB connection managers in other SSIS packages under the current project.

Click the New button to create a new connection manager configuration. In the Server Name drop-down list, select or enter the name of the SQL Server instance that contains the WeatherData database. In the Select or Enter database name drop – down list, select or enter WeatherData, as shown.

Click OK to close the connection Manager editor. Click OK to close the Configure OLE DB Connection Manager window. The OLE DB target editor for “OLEDest Stage Temperature” should now display, as shown:

We will leave the Table or View setting for the Data Access Mode drop-down list. If the “Name of the Table or the View” drop-down list is empty, click “New” on the right to open the “Create Table” window:

Edit the statement to remove “OLEDest” and whitespace from the table name and whitespace from the column name, as shown in Listing 2 below.

-- Listing 2
CREATE TABLE [StageTemperature] (
    [Date] varchar(50),Time] varchar(50),
    [MinT] varchar(50),
    [MaxT] varchar(50),
    [AverageT] varchar(50),
    [MinH] varchar(50),
    [MaxH] varchar(50),
    [AverageH] varchar(50),
    [ComfortZone] varchar(50),
    [MinDP] varchar(50),
    [MaxDP] varchar(50),
    [AverageDP] varchar(50),
    [MinHI] varchar(50),
    [MaxHI] varchar(50),
    [AverageHI] varchar(50))Copy the code

The CREATE TABLE statement is built using metadata sent to the OLE DB target adapter from the data flow path that connects the flat file source adapter to the OLE DB target adapter.

When you click OK to close the CREATE TABLE window, the Data Definition Language (DDL) CREATE TABLE statement is executed against the database to CREATE the StageTemperature TABLE.

Click on the Mappings page in the OLE DB Destination Editor, as shown.

Note that the automatic mapping occurs between the “Date and Time” input columns and the target columns. Why is that? These columns have the same name and data type, respectively.

Rules for automatic mapping are not yet determined. Automatic mapping occurs if the column names and data types match between the input and the target column. If not, automatic mapping sometimes occurs and sometimes it does not. Why is that?

My best guess for the rule is that automatic mapping occurs if the input column name matches the target column name, and SSIS can force the input data type to be the target data type. I based this hypothesis on eight years of observation. I believe that what is and is not mandatory has changed over the years. The only thing I’m sure of is this: I don’t know.

The target table is created from metadata collected from the input of the “OLE DB target “adapter. If you double-click on the data flow path connected from the flat file source adapter “FFSrc Temperature and Humidity” and the OLE DB target adapter “OLEDest Stage Temperature”, you will find a “metadata” page.

However, I changed the column names by removing Spaces. Therefore, input column names contain Spaces, while most target column names do not.

There are two ways to map columns on the Map page. One way is to drag and drop column names from the available input Columns grid onto the Available target Columns grid (or drag and drop from the Available target columns grid onto the available input columns grid – drag projection works both ways). The following figure shows another approach. Each Input column can be assigned to the Destination column through a drop-down list.

When the mapping is complete, click ok to close the OLE DB target editor.

Using package parameters

We’re almost ready to execute. The last step is to map the SourceFilePath package parameters to the “FFCM Temperature and Humidity” flat file connection manager’s ConnectionString property.

To do this, click “FFCM Temperature and Humidity” flat file connection manager, then press F4 to display the connection manager properties. Note that the ConnectionString property is currently hard-coded as the path we selected earlier. To dynamically bind this property to the SourceFilePath package parameter, click the ellipsis in the Expressions property value text box, as shown:

The Property Expression Editor will display. Expressions enable developers to dynamically map Expressions to attribute values at run time.

The “Properties” column starts with the ConnectionString attribute and maps the SourceFilePath package parameter value to the “FFCM Temperature and Humidity” flat file connection manager’s ConnectionString attribute. Click the ellipsis of the ConnectionString property in the Expressions text box to display the Expression Builder window:

Expand the Variables and Parameters virtual folder, and then drag the Package parameter $Package::SourceFilePath into the Expressions text box. Click OK to close the Generator window.

You can also click Calculate Result Value (Evaluated value) button to view$Package::SourceFilePathThe value contained in the parameter.

Click OK to close the property expression editor.

The “FFCM Temperature and Humidity” flat file connection manager’s ConnectionString property is now dynamically coupled to the value of the SourceFilePath Package parameter ($Package::SourceFilePath).

Press F5 to execute the SSIS package and test the loader, and if all goes according to plan, your data flow should look something like this:

Stop the debugger, then move the source file. In the current location of the file, create a new folder named Dec08 and move the sensor1-all.csv file to the new Dec08 folder.

Go back to the SSIS package, and press the F5 key to re-execute the package. It should fail and display an error similar to that shown in the figure.

Stop the debugger. Return to the Package Parameters TAB and change the value of the SourceFilePath parameter to include the Dec08 folder, as shown in the figure.

Now re-execute the package. The result should look like the figure below.

Test successful! We have set the path to the source file to dynamic and used the Package Parameter to do this.

conclusion

In this article, we have explored SSIS parameters, parameter configuration, dynamic property value management through package parameters, and how to configure and use parameters during SSIS package execution.