13th article Integration Services:ssis variable

This article is the 13th chapter of the Integration Services series, please refer to the original text for details.

Brief introduction
In the previous article we combined the previous learning of bubbling, logging, and parent-child mode to create a custom SSIS package logging pattern.
In this article, we will upgrade our solution for SQL Server Integration Services, demonstrating SSIS variables, variable configuration, and expression management dynamic values. We have used variables in the previous exercise, but we did not delve into it, and we will focus on the SSIS variables.
......
......
Once installed, you can set the theme color, tools---environment----general, 13.1 shows:
Figure 13.1
Figure 13.1
Create a new SSIS solution and Project
To create a new SSIS solution and project, click File-New project. Wait, you can't click New Project, File---new project does not exist (Figure 13.2)
Figure 13.2
Figure 13.2
There are two ways of creating:
1. Do you remember the shortcut keys for creating new items in VS? Many people do not know the shortcut keys, ctrl+shift+n, it can still be used.
2. Start the start page. Click the View-Start page to open the Start page, as shown in 13.3:
Figure 13.3
Figure 13.3
The start page is the Welcome page at vs startup. If you don't remember shortcut keys, it's usually very useful. The start Page 13.4 shows:
Figure 13.4
Figure 13.4
Click the new Project link to create a new SSIS project. If it is not expanded, expand the installed templates in the list on the left. Expand Templates, business intelligence. Select Integration Services in the list on the left and select the Integration Services project from the template. The named items are shown in my_second_ssis_project,13.5:
Figure 13.5
Figure 13.5
You may need to specify a path or accept the default path. Unless I have some reason to modify the default settings, I tick "Create directory for Solution" and remove the "Add to Source control" check box.
When you click on the OK button, the new My_second_ssis_project solution and project will be created. Solution folder under Location text, type the path of the project folder under Solution Folders, as shown in 13.6:
Figure 13.6
Figure 13.6
For more information about the VS Solution folder, please refer to the section on creating the Integration Services project in the first article of this series.
SSIS2012 Brief Introduction
The SSIS2012 is somewhat different from the previous version. VS IDE changes significantly. On the development page there is a zoom slider (Figure 13.7). The picture on the left is the normal appearance of the slider, and when the mouse moves over it, it looks like the right side:
Figure 13.7
Figure 13.7
The button below the zoom slider is the Fit View to Window button, which provides autoscale to display all the contents of the selected tab.
The SSIS Toolbox has been detached/decoupled from the. NET control Library. In order to display the SSIS Toolbox, click the SSIS Toolbox icon in the upper-right corner of the package design area (second from left), as shown in 13.8:
Figure 13.8
Figure 13.8
You can still open the SSIS variable by clicking the ssis-> variable or the control flow blank area. There is a new button to display the variable window, just on the left side of the SSIS Toolbox button, 13.9:
Figure 13.9
Figure 13.9
Perhaps the biggest change in SSIS2012 is in the new deployment model: the project deployment model. Project deployment is a new introduction to SSIS2012, but the package developed by the previous version is supported through the project deployment model SSIS2012.
Before manipulating the variables, let's rename the package to VARIABLESANDPARAMETERS.DTSX (Figure 13.10)
Figure 13.10
Figure 13.10
Note: You will not be prompted to rename the package object again when you rename the dtsx file.
Beginning with Variables
As mentioned earlier, there are several ways to open the Variables window. Use the method you like to open it. The first button is to add a variable (figure 13.11)
Figure 13.11
Figure 13.11
Click the Add Variable button to create a new variable for the SSIS package. Rename it as shown in MyVariable13.12:
Figure 13.12
Figure 13.12
Note the scope of the variable is the name of the SSIS package: Variablesandparameters. A good metaphor for scopes is the container. In Figure 13.13 We see three containers:
Tasks
Container
Package
Figure 13.13
Figure 13.13
Tasks, containers, and packages are executable files. Among other attributes, an executable are a type of object in SSIS this has properties and raises events. A task typically resides within a container. "So what if I put an Execute SQL task directly into the control flow?" ", the package is also a container. SSIS also contains other containers: A sequence container, a For loop container, and a Foreach Loop container. Each container can hold a task, and each type of container is an executable file.
SSIS provides other ways of visualizing scopes. To demonstrate, add a sequence container from the SSIS Toolbox to the control flow, and then add an Execute SQL task to the sequence container, as shown in 13.14:
Figure 13.14
Figure 13.14
Scopes are described in the package Explorer in a tree view. This implication and figure 13.13 represent the same: the package (variablesandparameters) contains the container (Sequence Container), the container contains the task (Execute SQL Task), and 13.15 shows:
Figure 13.15
Figure 13.15
The TreeView helps us visualize what I mean when I say ' up ' or ' down ' in scope. From the perspective of the Execute SQL task, the sequence container is above it. If from the perspective of the sequence container, the Execute SQL task is underneath it or contained within it. I can say that the Execute Package task is contained in the Variablesandparameters package, but this statement ignores the sequence container and provides a description of the incomplete relationship between the package and the task. In order to be more prepared I should state that the Variablesandparameters package contains a sequence container, and the sequence container contains an Execute SQL task.
The scope of a variable is defined by the executable to which the variable is scoped. The scope of our myvariable variable is Variablesandparam Eters Package range. I sometimes weigh these variables into packet scope variables.
Another point in the Package Explorer tree view: ...
The default scope of variables in SSIS2012 is the package scope. We can change the scope of the SSIS variable, but so far I have not found a good example of a variable with a scope below the package level. By clicking the second button on the Variables window toolbar, we can move the variable to a different scope, as shown in 13.16:
Figure 13.16
Figure 13.16
Select the myvariable variable and click the Move Variable button, which displays the Select New Scope window, shown in 13.17:
Figure 13.17
Figure 13.17
The available scopes are tree-like, and you can select the desired scopes by clicking the OK button. To demonstrate, we select the Sequence container and click OK.
Note that the scope of the myvariable variable is changed to the sequence container, as shown in 13.18:
Figure 13.18
Figure 13.18
In the control flow, click the Sequence container, and then observe the Variables window. The myvariable is visible in the variable window, 13.19:
Figure 13.19
Figure 13.19
Now click on the blank area of the control flow and watch the variable window:
Figure 13.20
Figure 13.20
MyVariable is no longer visible in the variable window. Why? The default behavior of SSIS is to only show variables at or above the current scope. " Can I modify this default behavior? "Of course you can." Click the Fourth button "grid Options" on the toolbar of the Variables window:
Figure 13.21
Figure 13.21
The grid options for variables contain many options for the variable window. We tick the "show variables for all scopes" check box, as shown in 13.22:
Figure 13.22
Figure 13.22
Click the OK button to return to the control flow and Variables window. Click on the blank area of the control flow, and note that you can see the myvariable variable in the variables window, even if its scope is below the package (in the Sequence container scope):
Figure 13.23
Figure 13.23
In previous versions of SSIS, variables were scoped to the executable file that was selected when the variable was created. The default behavior of the SSIS package (now still) is to display variables only in the current or above scope. I agree that the variable is the package scope/scope by default, but I disagree because the scope hides the variable.
Why do I disagree with a variable hidden under or outside the execution stack? If I accidentally create a variable below the package scope, I can create a second variable as compensation. These two variables allow different scopes to have the same name, but they are not the same variable.
To demonstrate, delete the Execute SQL task and add a script task to its location. Create a new variable, myvariable, which is automatically the package scope:
Figure 13.24
Figure 13.24
To demonstrate, I deliberately give variables a different default value.
Double-click the Script task to open the editor and set the ScriptLanguage property to Microsoft Visual Basic 2012. Click the ellipsis of the ReadOnlyVariables property text box. When the Select Variable window opens, note that the list is only one myvariable,13.25:
Figure 13.25
Figure 13.25
Select the myvariable variable and click the OK button. The Script task Editor should be shown in 13.26:
Figure 13.26
Figure 13.26
Click the Edit Script button to open the Script Editor and navigate to the public Sub Main () module. Add code 13.1 to public Sub Main ()
Code 13.1
Your script editor should look like 13.27:
Figure 13.27
Figure 13.27
Close the Script Editor and click the OK button on the Script Task Editor. Under debugger, press F5 to execute the SSIS package. The message dialog box displays the value of the myvariable variable, which value is displayed?
Figure 13.28
Figure 13.28
If you follow the walkthrough yourself, the default values provided to the sequence container are displayed. Why is this variable winning?
Think back to the execution stack I mentioned earlier and think about how the stack transmits events from the original executable (task, container, package). Do you still remember how to work? Event is routed up, I call this event bubbling.
Variable behavior in a similar way. SSIS tries to lock the myvariable variable before the Script task executes. What happens if two executables try to use the same variable at the same time? If one executable writes the value of a variable, the other executable simply reads the value of the variable? To ensure that the value of a variable remains static (or determined), SSIS places a lock on the variable, so its value cannot be unintentionally manipulated during use.
One way to think about how to lock variables in SSIS is to imagine a locking mechanism (in Keyholder analogy) polling the execution stack to find myvariable variables. Keyholder start at the Script task and ask It "Script task, do you have a myvariable variable?" "Script Task Response" is not. So Keyholder goes up and asks the sequence container, "Sequence container, do you have a myvariable variable?" "Sequence container responds," there is. "Keyholder stop looking. It found the variable it was looking for.
If my variables are hidden (all SSIS versions default), I might realize that I have two myvariable variables of different scopes. I might accidentally set the myvariable of the sequence container and then forget it. Then create a package-scoped myvariable variable.
In addition, the show variables for all scopes check box is not persisted as part of the solution or project properties. When I close SSDT and open it again, this setting reverts to the default value.
That's why I don't like to hide variables in different scopes: in the Script task I have no way to get the package scope of the myvariable variable. It won't appear in my list when I choose ReadOnlyVariables. I can't use "variablesandparameters.user::myvariable". This option does not exist. And, I don't know if the variable exists unless I open "show variables of all scopes". If The default behavior is-to-set all variables-to-package scope, the default should also are to show variables at all SCOP Es.
Variable data type
SSIS variables have multiple data types: Various numeric data types,date,byte,boolean,string,and character.
Perhaps the most interesting is the object data type.
Figure 13.29
Figure 13.29
Variables of the object data type can hold multiple values including scalars such as integers, strings, and dates. Objects can also hold objects: such as collections, arrays, recordsets, and datasets.
Variable Value
The value column of the variable window shows the default value for the variable. As a practical experience, I'll set the default value to the value used when developing SSIS packages, even when I deploy SSIS packages to a production environment. The reasons for doing so will be explained later in the article. But the main reason is that if the production environment package executes unexpectedly, or someone is wrong, I would rather load my development environment with production data than load my production environment with development data. Figure 13.30 shows the default values for the two variables we use:
Figure 13.30
Figure 13.30
Variable expression
The SSIS expression language is very difficult to learn. Personally, it is more like a mix of curly-brace-languages (C #, Java, etc.) and Transact-SQL. If you're positioning SSIS as an enterprise data integration platform, it's a good fit.
We can manipulate the value of an SSIS variable using an expression. To demonstrate, click the ellipsis of the myvariable variable expression text box, 13.31:
Figure 13.31
Figure 13.31
The Open Expression Builder window contains four parts: variable argument, expression language, expression, evaluation expression, 13.32:
Figure 13.32
Figure 13.32
The variables and Parameters tree view contains a list of all available variables and parameters. Because of the variables in our SSIS package, the only variable under the variables and Parameters virtual folder is the package scope of myvariable,13.33 as shown:
Figure 13.33
Figure 13.33
Why can't we see the myvariable of the Sequence container range? Because the myvariable of the Sequence container range is the variable that we set the expression to. How do we verify it? We can drag the myvariable variable from the variables and parameters into the expression text box, and then click the Evaluate Expression button, which is shown in 13.34:
Figure 13.34
Figure 13.34
The default value of the myvariable variable for the package scope is 0, and the myvariable variable for the sequence container defaults to 42. Therefore, the previous validation is the myvariable variable for the package scope.
The expression language tree view contains SSIS expression language actions and functions, as shown in 13.35:
Figure 13.35
Figure 13.35
In the expression text box, delete "@[user::myvariable.", drag the Dartpart function from the date/Time function tree node to the expression text box. In the Expression text box, replace <<datepart>&gt with "SS"; in the SSIS Expression Language tree view, click the GETDATE () function and drag to the expression text box. Replace <<date>> with GETDATE (). The final expression should be datepart ("ss", GETDATE ()) 13.36:
Figure 13.36
Figure 13.36
This expression assigns the number of seconds in the current time to the myvariable variable in the range of the sequence container. Close the Expression Builder, and note that the variables are shown in window 13.37:
Figure 13.37
Figure 13.37
Note There is an FX icon next to the variable name. This indicates that an expression controls the value of a variable.
The expression we configured appears in the expression column of the variable window. To demonstrate, press F5 to start the package under the SSIS debugger. The Script task displays a message dialog box for the value of the myvariable variable. It is a number between 0-59, the number of seconds in the current time, and 13.38 as follows:
Figure 13.38
Figure 13.38
Before we proceed, stop the debugger, remove the script task from the sequence container, and delete the two myvariable variables.
Create a connection using a variable
Let us apply our knowledge. Variables can be used to generate values for other variables. Let's demonstrate that by using a variable to create a path to a flat file containing some data.
First create the Songs.csv flat file:
......
......
In SSDT, add a data Flow task to the control flow that will connect a precedence constraint from the sequence container to the data Flow task, as shown in 13.39:
Figure 13.39
Figure 13.39
Open the Data Flow task and add a flat file source, as shown in 13.40:
Figure 13.40
Figure 13.40
Open the Flat File Source editor and create a new flat File connection Manager by tapping the new button on the right:
Figure 13.41
Figure 13.41
When opening the flat File Connection Manager editor, enter "Songs Flat file" in "Connection Manager name", "File name" property to browse to the location where songs.csv files are saved; "Text Qualifier" property enter double quotes, 13.42:
Figure 13.42
Figure 13.42
Click OK to close the Flat File Connection Manager editor, note that songs Flat file appears in the Flat File Connection Manager property:
Figure 13.43
Figure 13.43
Click OK to close the flat File Source editor. If you don't have a database called TestDB, you can test your development with a different database, or create a TestDB library with code 13.3:
Code 13.3
Code 13.3
Return to SSDT, drag an OLE DB target to the Data flow panel, and connect to the flat file source, as shown in 13.44:
Figure 13.44
Figure 13.44
Open the OLE DB Destination editor, click the New button to the right of the "OLE DB connection Manager" drop-down list, create a new OLE DB connection Manager and open the Configure OLE DB Connection Manager Editor window, as shown in 13.45:
Figure 13.45
Figure 13.45
Click the New button at the bottom to open the Connection manager. Enter the server name,
In server name, type the name of your DB instance, and in connect to a database select or enter a database name, as shown in 13.46:
Figure 13.46
Figure 13.46
Click OK to close the Connection Manager window. Configuring the OLE DB Connection Manager window displays our new connection-my display as "(local). TestDB ", shown in 13.47:
Figure 13.47
Figure 13.47
Make sure your new connection is selected in the data connection and click the OK button. Our Connection Manager is displayed in the OLE DB connection manager properties of the OLE DB Destination editor.
Click the New button on the right of "table or view name" to display the Create Table window. This window displays a DDL statement (generated from the metadata in the data stream), as shown in 13.48. The table name is derived from the OLE DB target and modifies it to songs. The column definition is determined by the data flow path between the flat file source and the OLE DB destination:
Figure 13.48
Figure 13.48
After you edit the table name, click the OK button. Note that clicking the OK button executes the DDL statement in your database. This is why the new table appears in the name of the table or view, as shown in 13.49:
Figure 13.49
Figure 13.49
At the bottom of the OLE DB destination editor, note the warning message:
Figure 13.50
Figure 13.50
This warning tells us to map the columns on the mapping page, but be aware that the buttons are available. This is newly introduced in SSIS2012, it's one example of something Microsoft calls flexible Order of Authoring or FOA.
I have a mixed mood with Foa. I don't like it in the OLE DB destination. Why is it? It may allow me to click OK right away so that the OLE DB target in my data stream is only partially configured. In previous versions, this was not the case, as only the OK button was available after we had manipulated the mapping. However, when I configure the Lookup transformation, I like Foa very much. The Lookup transformation has five pages, and I hate to remember the previous page operation when something unexpected happens (such as mapping failed ...). FOA gives and FOA takes away.
Go back to SSDT and click on the mapping page on the OLE DB Destination editor. When you do, the columns are automatically mapped, as shown in 13.51:
Figure 13.51
Figure 13.51
The available input columns are columns that flow from the data flow path to the OLE DB destination. The available target columns are the available columns for the table or view that we configured in the target. In this case, the target table we created earlier, where does the OLE db target get the metadata and create the table?
To answer this question, first click OK to complete the configuration of the OLE DB target. Then right-click the data flow path connecting the flat File source and OLE DB destination, select Edit, and open the Data Flow path editor. Click on the Meta data page to display the properties of the data flow path structure, as shown in 13.52:
Figure 13.52
Figure 13.52
Does it look familiar? I think this looks like a table definition. We have column names, data types, lengths. These metadata provide schema information when we click the New button on the OLE DB destination editor to create the table.
Because the columns in the Available target columns (Figure 13.51) are created by the metadata of the available input columns, the names of the columns and the data types can match, so the automatic mapping occurs.
Click OK to close the Data Flow path editor.
Variables in Expressions
Open the SSIS Variables window and add a package-scoped string of type filedirectory variables. Set the value of the variable to the folder where the Songs.csv file is stored, as shown in 13.53:
Figure 13.53
Figure 13.53
The filename variable that creates another package-scoped string type. Set its value to Songs.csv:
Figure 13.54
Figure 13.54
Add a filepath variable for the string type of the package scope, as shown in 13.55:
Figure 13.55
Figure 13.55
Click the ellipsis of the expression text box for the filepath variable to open the variables and Parameters virtual folder in the upper left of the Expression Builder window. Click and drag user::filedirectory to the expression text box, 13.56:
After the variable ("@[user::filedirectory]"), add a space, and then a plus sign ("+"). The plus sign as a string connection operation. Click and drag the User::filename variable into the expression text box, shown in 13.57:
Figure 13.57
Figure 13.57
Click the Evaluate Expression button to view the value of the expression, as shown in 13.58:
Figure 13.58
Figure 13.58
Ouch, there is a backslash missing between the data folder and the Songs.csv file name. Don't worry, we can fix this problem by adding a connection operation ("+") and a backslash to the expression, 13.59:
Figure 13.59
Figure 13.59
Let's click the Evaluate Expression button again and we see the result of Figure 13.60:
Figure 13.60
Figure 13.60
Expression parsing failed? The error appears to have a problem at the 26th character. The 26th character is a backslash in double quotation marks. Why does it break an expression? Because a backslash is an escape string. A backslash and other characters represent some special text. So how do I get a simple backslash? Using "\ \", 13.61 shows:
Figure 13.61
Figure 13.61
Click OK to close the Expression Builder.
Variables in Dynamic property Expressions
We now have a variable (FilePath) that contains the full path to our source file. The filepath variable is constructed from an expression consisting of two other variables (filedirectory, FileName).
Let's use this complete path to dynamically boot our flat File Connection Manager (Songs Flat file) to the files.
Click Songs Flat file Connection Manager, and then press F4 to display the Connection Manager properties (Figure 13.62):
Figure 13.62
Figure 13.62
Click the ellipsis of the expression Value text box to open the property expression Editor. Click the Properties drop-down list to select the ConnectionString property, as shown in 13.63:
Figure 13.63
Figure 13.63
Click the ellipsis of the expression text box to open the Expression Builder. Expand the virtual folder in the variables and Parameters tree view. Drag the filepath variable into the expression text box, shown in 13.64:
Figure 13.64
Figure 13.64
Click the OK button, close the Expression Builder window, and then click the OK button to close the property expression Editor. If you expand the Expression property in the Properties window, you will see the ConnectionString property, which is shown in 13.65:
Introducing breakpoints and variable states
Breakpoints are used to handle fault detection, or simply to view the state during SSIS debugger execution. In articles Nineth and 12th, SSIS uses event/listener mode. Event handlers, logging, progress/Execution Results tab, breakpoints are an example of an event listener. In this section, we set a breakpoint that suspends execution when the data Flow task causes/generates a PreExecute event.
Before we run the test, return to the control flow, right-click on the Data Flow task and tap Edit Breakpoint ..., as shown in 13.66:
Figure 13.66
Figure 13.66
When the set breakpoints-Data Flow Task window appears, click the "break when the container receives the OnPreExecute event" breaks Condition as shown in figures 67:
Figure 13.67
Figure 13.67

13th article Integration Services:ssis variable