This is the 20th day of my participation in the November Gwen Challenge. Check out the details: The last Gwen Challenge 2021
Translation reference
An Overview of SSIS Variables — Step 13 of The Stairway to Integration Services An Overview of SSIS Variables — Step 13 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!
This article demonstrates the SSIS variable section, variable configuration, and managing dynamic values through expressions. We have used the SSIS variable in many previous exercises, but it has not been given much attention. The SSIS variable will be highlighted below.
New SSIS project
Open the original solution.
Click file – New – Project in the top menu bar. Then select Integration Services in business Intelligence and create a new Integration Services project.
As follows, name the project My_Second_SSIS_Project and select Create new solution. Specify the location as required.
Click “OK” to create.
Rename the SSIS Package Package. DTSX for VariablesAndParameters. DTSX, as follows:
Start with variables (Beginning with Variables)
The use and manipulation of variables
Right – click the control Flow blank on the canvas and select Variables. The Variables window opens
The first button is “Add Variable”, click and create a new Variable, rename it “MyVariable”.
Note that the Scope of the variable is the name of the SSIS package: VariablesAndParameters. A good analogy is that scopes are containers.
As follows, there are three scopes:
- Task
- Container
- Package
Tasks, Containers, and Packages are executable files (or executables). An executable is a type of object in SSIS that has properties and can raise events.
A task is always resides within A Container. “Wait, Andy; What if you just put ‘execute SQL task’ on the control flow?” Good question: A Package is also a container.
SSIS exposes other containers, namely Sequence Container, For Loop Container, and Foreach Loop Container. Each of these containers can hold tasks, and each type of container (such as a package) is also an executable.
SSIS provides additional ways to visualize scopes. To demonstrate, add a sequence container from the SSIS toolbox to the control flow, and then add a “Execute SQL Task” to the sequence container as follows
The Package Explorer describes the scope in the tree view control. The package contains a container (sequence container), which in turn contains a task (execute SQL task)
Tree views help us visualize what “up” or “down” means in scope. From the point of view of “performing SQL tasks,” the “sequence container” is “above” it. If you start from a sequence container, the scope for “Perform SQL Task” is “under” or “contained within”.
Stating that it is technically correct to include the Execute Package Task in the VariablesAndParameters Package, but that the statement ignores the Sequence Container, An incomplete description of the relationship between packages and tasks is provided. To be more precise, you can declare: “The VariablesAndParameters package contains a sequence container that contains an execution SQL task.”
The scope of a variable is defined by the executable on which the variable is applied. Our variable named MyVariable is scoped to the VariablesAndParameters (or package) scope. These variables are sometimes referred to as package scoped variables.
Another point about the tree view in the Package Explorer: I think this is a fair representation of what is called the Execution Stack. I prefer to use the term “Execution Tree,” but that term has been used elsewhere in SSIS.
Execution stack refers to the relationship between executable files. The execution of an executable typically flows the stack “down” from the package to the container to the task. Some messages, such as the events observed in “Advanced Logging,” “move up” on the stack, referring to the events in advanced logging described in Part 12. As you can see from those examples, the Package Explorer can be very beneficial.
SSIS variables default to package scope in SSIS 2012. You can change the scope of SSIS variables, but so far, there are no useful examples of setting variable scope below the package level.
You can move a variable to another scope by clicking the second move Variable button in the Variables window toolbar, as shown in the figure below:
Select the MyVariable Variable and click the Move Variable button to display the select new scope window.
As shown above, we see a tree view that shows the scopes available in the VariablesAndParameters package — executables. You can select the range you want and click ok. For demonstration purposes, select Sequence Container, and then click OK.
Note that the scope of MyVariable has been changed to “sequence container” as shown:
In Control Flow, click Sequence Container and its subroutines. MyVariable is visible in the Variables window. This is because the default behavior of SSIS is to display only variables of the current scope or higher scope.
You may be thinking, “Can I change this behavior?” Of course, you can change this behavior by clicking the fourth button, Grid Options, in the variables window toolbar.
The Variable Grid Options window contains many of the options of the Variable window. We need to check the box marked “Show all scoped variables”, as shown:
Click “OK” to return to the control Flow and variables window. Click on the empty area of the control flow and you will still see the MyVariable variable in the variables window, even though it is located below the package (in the sequence container scope).
A little test about hiding variables by scope
Note: Variables are fetched from low up on the execution stack
I’m not a big fan of hiding variables because of scope.
Why do I disagree with hiding variables under or outside the execution stack? If I accidentally create a variable in scope below the package, I can create a second variable to compensate. Two variables are allowed to have the same name in different scopes, but they are not the same variable.
For demonstration purposes, remove “Execute SQL Task” and add a “Script Task” in its place. Create a new variable named “MyVariable” whose scope is automatically package scoped.
For demonstration purposes, different default values for variables are intentionally provided here.
Double-click Script Tasks to open the editor. Set the script language property to “Microsoft Visual C# 2010”. Click the ellipsis in the text box for the value of the “ReadOnlyVariables” property. When the select variables window displays, note that only one MyVariable variable is listed.
Select the “MyVariable” variable and click the OK button.
Click the “Edit Script” button in the Script task editor to open the VSTAProjects Script editor and locate the Main() function. Under the “Add your code here” comment, Add a line of code:
MessageBox.Show(Dts.Variables["User::MyVariable"].Value.ToString());
Copy the codeClose the VSTAProject script editor and click ok in the Script Task Editor.
Press F5 to execute the SSIS package in the debugger. The message box will display the value of the “MyVariable” variable. What value is displayed?
If you follow suit from the start, the default value for MyVariable provided in the “sequence container” will be displayed. Why does this variable win?
Consider the “execution stack” I mentioned earlier and consider how events traverse the stack from the original executable (task, container or package — Task, container, or package). Remember how this works? Events move up, that is, events bubble.
Variables behave similarly. Before executing the script task, SSIS attempts to lock the MyVariable variable. “Lock it down, Andy?” Yes, lock it down. “Why?” Good question.
Consider what happens if two executables try to use the same variable at the same time. What if one of the executables is writing variable values, and the other is only reading values? It is likely that you want the value of this variable to remain static during reading. To ensure that the value of a variable really remains static (or deterministic), SSIS places a lock on the variable so that the value cannot be unintentionally manipulated during executable use.
One way to think about how variables are locked in SSIS is to imagine the locking mechanism (” keyholder “in this analogy) polling the execution stack to search for a variable named MyVariable.
The keyholder starts with “script task” and asks it: “Script task, do you have a variable named MyVariable?” Script task Answer No. Therefore, the keyholder enters the previous execution stack and asks the “sequence container”. “Sequence container, do you have a variable named MyVariable?” The sequence container answers “yes.” The key holder stopped the search. It found the variable it wanted.
If MyVariable is hidden (the default in all versions of SSIS), I may not be aware that I have two variables named MyVariable in different scopes. I might accidentally set MyVariable’s scope to a sequence container and forget about it, then later create a package-scoped MyVariable (think “Have I created this variable already? ).
In addition, the state of the Show all scoped variables check box is not permanently stored as part of the solution or project properties. When I close the SQL Server data tool and reopen it, this setting will revert to the default.
This is why I don’t like to hide the default behavior of variables in different scopes: a package-scoped variable named MyVariable cannot be accessed through script tasks. When I select ReadOnlyVariables, it does not appear in my list. I can’t specify VariablesAndParameters. User: : MyVariable. This option does not exist. Also, UNLESS “Show variables in all ranges” is enabled, I don’t know if the variable exists because it’s not the default.
If the default behavior is to set all variables to package scope, the default should also be to display variables in all scopes.
Variable Data Types
SSIS variables have several data types available: various numeric data types, dates, bytes, booleans, strings, and characters. Perhaps most interesting is the Object data type:
The Object data type of the SSIS variable can hold various values, including scalars, such as single integers, strings, and dates. Object can also hold objects. Examples are collections, arrays, recordsets and datasets (Collections, Arrays, recordsets and datasets). Details and exercises are not covered here, but you are encouraged to read and practice the examples in an article I wrote called SSIS 101: Object Variables, ResultSets, and Foreach Loop Containers.
Variable Values
The Values column of the Variables window displays the default values for variables.
As a best practice, I set the default value to the one used to develop the SSIS package even when the SSIS package is deployed into production. There are many reasons for this decision, which we will cover more in a future article; But the main reason is that if something “bad” happens or an error occurs during production package execution, I would rather load my development environment with production data than with development data.
The default values for the two variables we have used so far are shown below:
Variable Expressions
The SSIS expression language is difficult to learn. To me, it looks like a mix of curly bracketed languages (C#, Java, etc.) and transact-sql. If you think SSIS is being positioned as an enterprise data integration platform, this is a good fit.
We can use expressions to manipulate the values of SSIS variables. To illustrate, click the ellipsis in the “Expression” text box for MyVariable.
The Expression Builder window displays and contains four areas: Variables and Parameters, Expression Language functions and operators, Evaluated Value and Expression.
As shown in the figure:
The variables and Parameters tree view contains a list of all available variables and parameters for an expression. Since variables are in the SSIS package, the only variable in the variables and Parameters virtual folder is the package-scoped MyVariable, as shown:
Why don’t we see MyVariable in the scope of the sequence container? Because the sequence container scoped MyVariable is the variable that we’re setting up in the “expression” column.
How do we know for sure? You can drag a variable from Variables and Parameters to the Expression text box, and then click the Evaluate Expression button, as shown:
Remember that the package-scoped variable MyVariable has a default value of 0. The default value of MyVariable in the sequence container scope is 42. Therefore, this is a package scoped MyVariable.
The expression language tree view contains SSIS expression language operators and functions,
Remove “@[User::MyVariable]” from the expression text box. Drag the “DATEPART” function from the “Date/Time Functions” node into the expression text box.
In the Expression text box, overwrite << datePart >> with “ss”(double quotes included). In the SSIS Expression language tree view, click the GETDATE() function and drag it to the Expressions text box. Hover over the <
> placeholder and place the GETDATE() function over the << Date >> placeholder. The expression should be DATEPART(“ss”,GETDATE()), as follows:
This expression puts the number of seconds in the current date and time into the sequence container scope MyVariable value. Close the Expression Builder and note the variables window.
Note the FX (” function of X, “” F of X,” or “F sub X”) icon to the left of the variable name. This indicates that the expression is controlling the value of the variable.
The expressions we configured appear in the Expressions column of the Variables window.
For demonstration purposes, press the F5 key to launch the package in the SSIS debugger. The script task displays a message box containing the value of the MyVariable variable. It is a number between 0 and 59 and is the number of seconds of the current date and time, as shown:
Cool stuff, so let’s get started!
Before we get into anything else, let’s set things straight. If the SSIS package is running in SSDT, stop the debugger. Removes “Script task” from the sequence container. Delete two MyVariable variables.
Building Connections Using Variables
Let’s synthesize what we’ve learned. Variables can be used to build other variable values.
Let’s demonstrate this by using variables to create a path to a text file that contains some data.
Get started by creating the following text file called Songs.csv:
Id,Artist,Song
"0","Waylon Jennings","Lonesome, On'ry, and Mean"
"1","Willie Nelson","Blue Eyes Cryin' in the Rain"
"2","Kris Kristofferson","Sunday Mornin', Coming Down"
Copy the codeAdd a flat file source adapter
In the SQL Server Data Tool (SSDT), add the data flow task to the control flow and attach the priority constraint from the sequence container to the new data flow as follows:
Open the data flow task and add a text File Source (Flat File Source) adapter to the interface.
Open the Flat File Source Editor and create a new flat file connection manager by clicking the New button on the right side of the Flat File Connection Manager drop-down menu, as shown:
Once the Flat File Connection Manager editor is displayed, set the Connection Manager Name property to Songs Flat File and the File Name property to the location of the File saved above.
Add double quotes to the Text Qualifier property as shown in the following figure:
English interface:
Click OK to close the Flat File Connection Manager editor. You might have to click on the Columns on the left to click OK
Note that Songs Flat File now appears in the “Flat File Connection Manager” property dropdown.
Click OK to close the flat file source editor.
Create database TestDB
You may not have a database named “TestDB” in your Microsoft SQL Server instance. You can use any database for testing or development, or you can use the following T-SQL script to create your own TestDB:
Use master
go
If Not Exists(Select name
From sys.databases
Where name = 'TestDB')
begin
print 'Creating TestDB'
Create Database TestDB
print 'TestDB created'
end
Else
print 'TestDB already exists'
go
Copy the codeAbout idempotent (idempotent scripts can be analogous to idempotent functions, idempotent interfaces, and idempotent operations)
The above is an example of an idempotent script.
Now I have a confession to make: I don’t know what that word means. Like many of the things I know, I’m from Jamie Thomson learned | @ jamiet (blog). But as Jamie explains, it means you can execute code repeatedly. If you do not have a database named TestDB in either your instance or SQL Server, and you run the script above twice, it will create the database on the first execution of the script and inform you that the database already exists on the second (and subsequent) execution.
There are two points:
- Idempotent scripts are safe and useful;
- Jamie is really smart.
Idempotent idempotence
An idempotent operation in programming is characterized by its arbitrary ****. An idempotent function, or idempotent method, is a function that can be executed multiple times with the same effect as a single execution and can be executed repeatedly with the same parameters with the same result. These functions do not affect system state, nor do they have to worry about system changes caused by repeated execution.
OLE DB source adapter
Configure the connection
Go back to SSDT and drag an OLE DB source adapter to the data flow interface to connect a data flow path between the flat file source and the OLE DB target. As follows:
Open the OLE DB target editor, click the “New” button on the right of the “OLE DB Connection Manager “drop-down box to create a new OLE DB connection manager and open the Configure OLE DB Connection Manager window.
Click the New button to display the Connection Manager Editor. Enter the SQL Server Server name and instance name in the “Server Name” property drop-down box, and enter the database name in the “Connect to a database” drop-down box as follows:
Click OK to close the connection Manager editor window. Configure the OLE DB Connection manager window to display new connections as follows:
Select the newly created connection from the Data Connections list and click OK. In the OLE DB target editor, the name of the selected connection manager is displayed in the OLE DB Connection Manager property drop-down list.
Click The New button on the right of the Table or View name property drop-down list. The Create Table window is displayed. This window displays database Definition Language (DDL) statements built from “metadata” in the data flow.
The name of the table is derived from the name of the OLE DB target adapter — change it to “Songs”. The column definition is derived from the data flow path between “flat file source” and “OLE DB target “:
After you edit the table name, click OK. Note that clicking OK will execute this DDL statement on your database. This is why the table appears in the “Table or view name” property drop-down list, as shown in the figure below:
At the bottom of the OLE DB target editor, there is a warning message.
The warning message shown above tells us that the next step is to map the columns on the “Mapping page.”
Note, however, that the OK button is enabled. This is a new feature in SSIS 2012. It is also an example of what Microsoft calls “Flexible Order of Authoring,” or FOA.
I feel a lot about FOA. I don’t like “OLE DB target “. Why is that? I can now click OK and leave the partially configured “OLE DB target “in the data flow. In previous versions, this was not possible because the OK button was disabled until after the mapping was performed (which we will do in a minute).
But I really like FOA when I’m configuring Lookup transformations. The “Find Conversion” has five pages for when tragic events occur (such as failed mapping due to Unicode and non-Unicode strings, please be patient…). I hate having to remember everything I did in the previous interface. FOA gives, FOA takes away. (FOA gives and FOA takes away.)
To return to SSDT, click on the Mapping page on OLE DB Target Editor. When you do this, an automatic mapping occurs, as shown in the figure below:
Automatic mapping
You might be thinking, “What is auto-mapping?” I’m glad you asked! The available input columns represent the schema of the data flowing into the OLE DB target from the data flow path connected to the OLE DB target input. Available target columns represent the columns that are available in the table or view that is configured as a target. In the current example, we created the target table earlier — where does the OLE DB target get the metadata to create the table?
To answer this question, first click ok to complete the configuration of the OLE DB target adapter. Next, right-click the data flow path that connects the flat file source adapter to the OLE DB target adapter, and then click Edit to open the Data Flow Path Editor. Click the Metadata page to display the structural properties of the data flow path, as shown:
Look familiar? I think this looks like a table definition, with column names, data types, and lengths. This metadata provides schema information when we click the New button to create a new table in the OLE DB Target Editor.
Because the columns in the Available target Columns (above) are built from the metadata of the available input columns, the columns match names and data types, and this alignment of field names and data types allows automatic mapping to occur.
(this alignment of field name and data type allows auto-mapping to occur)
Click OK to close the data flow path editor.
Variables in Expressions
Open the SSIS Variables window and add a new package-scoped String variable named FileDirectory. Set the Value attribute of the FileDirectory variable to the folder where the songs.csv files will be stored.
Create another package-scoped String variable named FileName. Set the Value attribute of FileName to “Songs.csv”;
Add a package-scoped String variable called FilePath. As shown in the figure:
Click the ellipsis of the “FilePath” variable “Expression “(Expression) to open the Expression Builder window. Expand the Variables and Parameters virtual folder in the upper left section.
From the variables and parameter tree view, click and drag “User::FileDirectory” into the “Expressions” text box.
After the variable name (” @[User::FileDirectory] “), add a space followed by a plus sign (” + “). The plus sign acts as the concatenation operator for strings. Then add the backslash string and the plus sign “+”. Click on the User::FileName variable and drag it into the Expression text box, as shown:
Click the Evaluate Expression button to see the value of the Expression:
Backslash. Two backslashes are used to connect backslashes. The reason is that the backslash is an escape character — A backslash plus another character indicates some special text. A backslash plus another character indicates some special text. Using “\” alone will result in an error.
Click OK to close the expression generator.
Variables in Dynamic Property Expressions
Now we have a variable — FilePath — that contains the full path to the source file. The expression on the FilePath variable constructs this path from two other variables: FileDirectory and FileName.
Let’s use this full path to dynamically direct the Flat File connection manager (named “Songs Flat File”) to the File. Click Songs Flat File Connection manager, then press F4 to display connection manager properties (as shown) :
Click the ellipsis in the Expression value text box to display the Property Expression Editor. Click on the Property drop-down list, and then select the ConnectionString Property, as shown:
Click the ellipsis in the Expression text box next to the ConnectionString property to display the Expression Builder. Expand the Variables and Parameters virtual folder in the Variables and Parameters tree view. Drag the FilePath variable into the Expression text box as shown:
Click OK to close the Expression Builder window, and then click OK to close the Property Expression Editor.
If you expand the Expressions property collection in the Properties window, you can view the ConnectionString property, as shown:
Introducing Breakpoints and Variable State
Breakpoints are convenient for troubleshooting (or just viewing status) during SSIS debugger execution. As described in “Control Flow Task error Handling” and “Advanced Logging” in this series, SSIS uses the Event/Listener pattern. Event handlers, logging, the Progress/Execution results TAB, and breakpoints are examples of handling event listeners.
Event handlers, logging, the Progress / Execution Results tab, and breakpoints are examples of event listeners.
In this section, we set a breakpoint to pause execution when a data flow task raises a PreExecute event.
Before running the test execution, go back to Control Flow and right-click Data Flow Task, then click Edit Breakpoint… (Edit Breakpoints), as shown below:
When the “Set Breakpoints — Data Flow Task” window is displayed, Click the “Break when the Container receives the OnPreExecute Event” truncation condition.
Enabling breakpoints when a PreExecute event is raised by a Data Flow Task will cause the Data Flow Task to pause before it can execute.
Click OK to close the Set Breakpoint – Data Flow Task window.
A maroon dot appears on the Data Flow Task to indicate that a breakpoint has been set:
Note: In the figure above, “Data flow task” shows a Red Cross error. Configure and test execution should be error-free. The Red Cross shown here disappears after clicking “OLE DB target “in” Data Flow Task “to open its editor and then clicking OK once more.
Launch the debugger by clicking the “Play” button or by pressing F5.
Packet validation error. Data flow task OLE DB target [22]: This column cannot be processed because multiple code pages (1252 and 936) were specified for column “Id”.
Error “packet validation error” is reported when SSIS is executed.
As follows, a package validation error was encountered during execution
Error 1 Verification error. Data flow task OLE DB target [22]: This column cannot be processed because multiple code pages (1252 and 936) were specified for column "Id". VariablesAndParameters.dtsx 0 0Copy the code936 is the Chinese code and 1252 is the Latin code. Which code to use is to be set in the Flat File Connection Manager editor. In the Chinese version system (and SQL Server), Chinese is selected by default. In this case, an error occurs.
The locale and code page numbers selected in the previous configuration were English (US) and 1252. The reason for choosing it in the first place: The text file Songs.csv contains all English and characters.
The correct Settings for locale and code page number should be: Chinese (simplified, China) and 936. That is, the region Settings are the same as those in Windows.
Songs Flat File connection manager
This parameter is consistent with the system Settings. -936
Test execution package
After the modification, re-execute the SSIS package.
The debug to perform
Executing the SSIS package operation will pause at the Data Flow task when the PreExecute event is raised by the data Flow Task, as indicated by the yellow arrow appearing in the breakpoint indicator (the maroon point), as shown in the figure:
To see the status of the FilePath variable, click DEBUG on the menu, hover over the Window (Windows), and then click the Local Variables window, as shown.
Note that you can also open the “Locals” window by holding down Ctrl and Alt keys and then V and L. Pressing multiple keys in this way is sometimes called a chord.
Once displayed, expand the Variables node in the Local Variables window. Scroll until you find the User::FilePath variable, as shown:
The Value field contains the variable Value of the SSIS package’s debug execution up to this point. Note that this value represents a backslash as a double backslash in the FilePath and FileDirectory variable value fields.
To continue executing the SSIS package in the debugger, press the F5 key or click the Play button. With the package executed, we load three lines from the Songs.csv file, as shown below:
Checking the Execution Result
After the above execution is complete. You can go back to SSMS and see the data loaded into the Songs table. As follows:
You can see that it has been imported correctly.
The comparison is listed here so that you can preview the columns in Flat File Connection Manager or Flat File Source:
You can see that these previews are incomplete. The actual test results must prevail.
conclusion
In this article, we demonstrated several aspects of SSIS variables, variable configuration, and managing values dynamically through expressions.
We demonstrated several facets of SSIS variables, variable configuration, and dynamic value management via expressions.