SSIS advanced conversion task-Execute SQL statements

The following article describes the advanced conversion tasks in SSIs. Compared with the old SQL Server 2000 DTS, we will find that the previous dark-arrow and data-pump tasks are gone. The method for hiding ActiveX scripts and embedding connection characters in a conversion task is also removed. When you direct a package to a different database, you will not forget to modify the conversion object. This is also true when you modify the object connection. Create a global connection. The conversion task can be managed more easily. You can use the convenient interface to browse the Task Design Interface and modify attributes.

When using SSIS package, you may need to avoid excessive dependencies. There is no need to use ActiveX script tasks in the package to solve some complicated problems. In fact, if you are too dependent on script tasks at the beginning, you need to stop and think about whether there are alternative tasks. There are many tasks in SSIs to solve, such as removing useless data from a flat file, separating data input content, and executing several tasks cyclically. We may still encounter issues that SSIS cannot solve. If so, we need to use flexible. Net script tasks.

Here we will use these new tools to solve the problems encountered in the production environment. Do we need to reset the output parameters of the stored procedure? Set the branch based on the number of rows? How can I use a fuzzy logic task to search for data in a table instead of a table? We will see more complex conversion tasks. Assuming that you are familiar with the common environment settings of bids, we will not detail the specific operation steps of each example in this article, but will only describe the important attribute settings.

Execute an SQL statement task

SQL task execution is not an advanced SSIS task, but SQL Server 2000 is the most common task. You can use it as a starting point to learn variables, expressions, and other advanced attributes in SSIs.

During some tests, SQL tasks are often used to delete data in the storage table. The other is used to call a stored procedure to complete tasks that cannot be completed in DTs. SSIS also provides a tool that allows you to use configuration management to edit the parameter data required for a stored procedure or save the output parameter data.

Create a new package and drag and drop an SQL task in control flow. This task displays a red flag and an error message indicating that the task is not connected to a data connection. To solve this problem, double-click to open the editing page 6-1.

Figure 6-1

This task has four tabs:

• GENERAL: set the task name, description, data connection-related settings, SQL statement execution settings, and the SQL statement to be executed.
• Parameter mapping: a set of variables at the package or container level. Variables provide variable input values for SQL statements or stored procedures.
• Result set: contains the dataset obtained after executing an SQL statement or stored procedure.
• Expressions: contains the expression that sets the attribute of this task. This requires dynamic attribute setting.

The main attributes of this task are as follows:

• Connectiontype and connection: these attributes are used to set data connections, such as types and names.
• SQL statement: Provides the SQL statement to be executed for the task. The SQL statement here can be a simple SELECT statement, a complex go statement, or a stored procedure
• SQL source type: This attribute is newly added. It provides the property configuration sqlstatement to be placed in a variable, file, or directly input an SQL statement
• Resultset property: This property can be set to a single result, multi-row, multi-column result, or an XML data after an SQL statement is executed. If the resultset is set to none in the general label, the table in the result set label interface is unavailable.

Before using this task, you need to understand how SSIS processes variables. There is also an important attribute expressions.

Variable

In SSIs, variables have a range. The package range can only be used in the entire package, which is equivalent to a global variable. Variables can be separated in the range of 6-2. The default namespace is user. Variable names are case sensitive. These details may cause incorrect package logic. Figure 6-2 shows two ssql variables with the same name but different scopes in the same package. Variables can be easily used in SSIs. First, the configuration tool allows you to use XML files, environment variables, and even register settings to configure the package. When the package settings are static and configured before running, these technologies are easier to manage for multi-Environment Development. This technology is not easy to get the setting information from the input stream or modify the attribute at runtime. Three examples of using non-static configuration are:Call a package with Parameters,Modify output file names according to naming conventions,Modify connection attributes at runtime.

  

Figure 6-2

Variables can store values in a task and pass them to another task. You can use the property expression in SSIs to set the properties of the IS (Integrated Service) component. You can use variables to set the information in the task: Tag, counter, or control the package string. Due to the flexibility of variables, you can use more variable settings.

Expression

Most tasks, transformations, and containers have an attribute that is an expression, and the package itself has this attribute. The expression set uses logical expressions to display attributes such as containers. In the SQL statement execution task, the attribute set by the expression is SQL statementsource, which is the source of the SQL statement or stored procedure to be executed. Another connectionstring attribute sets the connection string for the ole db connection. Right-click the task and select properties. In the expression column, click the button next to the expression to open the edit expression dialog box.

6-3 shows the SQL task execution attribute editing dialog box. Click the property drop-down list box to display the attribute expression.

Figure 6-3

Click the button next to the expression column to create an expression. 6-4. Use this tool to create a logical expression. You can use the tool to select the item including the literal value, system, user-defined variables, operators, and built-in functions. Functions and operators in expression language are similar to C #, C, and tsql, but they are different. When using the VB Language =, it indicates that the judgment is equal. & represents the logic and. In tsql, double quotation marks are used to contain strings rather than single quotation marks. The expression language is not case sensitive, so C # And CProgramYou do not have to worry about the case sensitivity of variable names. It takes a little time to get familiar with the expression language.

Figure 6-4

We will continue to discuss how to use two variables of different ranges in the execution of SQL tasks. Figure 6-4 shows the expression attribute of the sqlstatementsource attribute. The expanded variable node displays all variable values. Click the variable User: ssql and drag it to the expression text box to create an expression. The value of the expression at runtime replaces the value of the sqlstatementsource attribute. Click the evaluate expression button below to view the expression value. The expression value is select 2. In this example, the two variables have the same name and different ranges. When a task is executed, the attribute value is select 2.

Dynamically Change Package Settings Using SQL output parameters

In this example, we export the data in [HumanResources]. [shift] In the adventureworks database to a TXT file and modify the attribute value at runtime. Use the output expression to modify the export path in the SQL task.

Assume that the customer needs to integrate a package with a database containing system configuration information, which stores the development, testing, and product environments respectively. When loading data, all the settings must be extracted from the database using the stored procedure at runtime. When migrating from the development environment to the test environment, you can modify the configuration information stored in the database to modify the migration purpose.

Because you need to get the attribute settings at runtime, you need to use the stored procedure to re-obtain the settings. The setting information cannot be obtained in the package. Here we use expressions to solve this problem.

2. Create a package and drag and drop a task to execute SQL statements on the control flow interface.
4. On the Control Flow Design page, right-click a variable and name it myfile. The value is c: \ Execute SQL sample.txt. Note that this value should not be enclosed by quotation marks.
5. Create a stored procedure and place it in the same database for simplicity. This stored procedure is used to simulate running the following statements in the adventureworks database. Note that this stored procedure modifies the input parameters.

    Use  Adventureworks
     Go  
    Create     Proc  DBO. usp_getconfigparamvalue (
     @ Applicationname    Varchar  (  30  ),  --  The name of the application.  
     
     @ Parametername   Varchar  (  50  ),  --  The name of the Parameter  
       @ Parametertypename     Varchar  (  30  ),  --  The name of the parameter type 
       @ Parametervaluevar     Varchar  (  255  ) Output  --  Output buffer for Value  
     )
   
     As  
     Set  Nocount On  
   
     --  Dummy proc to simulate the real usp_getconfigparamvalue  
       --  Always outputs 'C: \ Execute SQL sample changed.txt'  
       Set     @ Parametervaluevar  =  ' C: \ Execute SQL sample changed.txt  '  
   
     Set  Nocount  Off 

7. Set the connection type for SQL task execution to ADO. net, first create a new ADO. net connection, enter ". "indicates the local server. In the drop-down list box, select the adventureworks database to maintain the default access mechanism NT authentication. Note: In this example, the use of ole db connections will no longer apply. Different connections have different methods for processing stored procedure parameters.
9. Set the Execute SQL sqlstatemnet attributeCode: Exec usp_getconfigparamvalue 'myapp', 'myparm', 'string', @ myvalue output
11. Add a ing myfile on the parameter ing label interface, set variable name to user: myfile, direction to output, data type to string, parameter name to myvalue, and click OK to save the settings. Note that the variable name must be consistent with @ myvalue in the preceding SQL statement @
13. Drag and Drop a Data Flow task from the toolbar and connect the Execute SQL and data flow.
15. Drag and Drop An ado net source and a flat file destination on the data flow design interface. Note: Check whether you drag and drop a flat file destination instead of a flat file source, which are confusing.
17. Configure ole db source select adventureworks connection set sqlcommand attribute: Select * from [HumanResources]. [shift]
19. Connect ole db source and adventureworks
21. Double-click flat file destination to open the editing page, create a new delimited file, temporarily set the file name to c: \ myfile.txt, click mapping label to view the column name, keep the default settings, and click OK to exit the editing page.
23. Here we want to demonstrate how to modify the attribute of a task at runtime. The task that this package can complete now is to [HumanResources] in the database. [shift] data in the table is imported to c: \ Execute SQL sample.txt. To modify the file name at runtime, you need an expression to modify the connection string of the flat file connection. We use an expression to achieve this purpose.
25. Right-click flat file Connection Manager in connection managers and select Properties. In the attribute column, click the expressions button. The attribute to be modified is connectionstring, on the expression builder interface, select the variable @ [user: myfile]. At runtime, the expression will be assigned a value in the variable myfile.

Run the package check path c: \ ssisdemos to view the Execute SQL sample changed.txt file. We originally wanted to import the data to execute SQL sample.txt and now modified the path, data will be uploaded to the new file Execute SQL sample changed.txt. In this way, the attribute value of flat file connection is dynamically modified, so that you do not need to manually modify the package configuration and directly modify this sentence in the stored procedure: Set @ parametervaluevar = 'C: \ Execute SQL sample changed.txt ', so that you can reset the data storage address every time.

Effect 6-5 after execution is complete

The Application Scenario in this example is the first choice to output data to a development environment, and then set the attribute to output to a production environment using expressions. The advantage of this is that once the setting is made to a different environment, the output path of the package can be modified without modifying all the SSIS embedded environment settings. This example also uses the SSIS expression and uses ADO. Net to demonstrate the output parameters. Note the following in some cases:

• If the data provider does not recognize the name and order of output parameters, a work und is that the stored procedure returns a result set and maps it to a variable. If you cannot modify the stored procedure, You can execute a simple SQL statement to query the result and return it. The statements set by sqlstatement are as follows:
  Declare @ myvalue as varchar (255)
  Exec usp_getconfigparamvalue 'myapp', 'myparm', 'string', @ myvalue output
  Select @ myvalue as myvalue
• Here, the isquerystoredproduced attribute is set to false by default and should be set to true. If set to true, an error is returned, indicating that the stored procedure cannot be found.