[Tips] A summary of getting started with SSIS package

1. dynamically change the data source

Package runs on different servers from creation to debugging to testing to production. We can define two variables: Environment and server. One is used to change the server and the other is used to receive the actual server value. When running in different environments, you only need to change the value of the environment variable, and the connection between the server and the database will change accordingly.

A. Define environment and server Variables

B. Method 1: Set evaluateasexpression of the server variable to true, and enter the expression as follows:

@[User::Environment]=="DEV"?"sqlServer\\dev":(@[User::Environment]=="TEST"?"sqlServer\\test":(@[User::Environment]=="PROD"?"sqlServer\\prod":"Unassigned"))

B. Method 2: put an SQL Execution task (set the connection type to ADO. net) before the package starts, and write a similar statement:

Select @Server = Server from PackageAdmin where Environment=@Environment && ApplicationName=@ApplicationName

Then add necessary parameter ing to the task. In this way, the database server corresponding to different environments can be dynamically changed based on the database configuration.

C. Bind servername in the expression of the connection object to the server variable.

 

2 ole db SQL task

When the SQL task connection type is OLE DB, the parameter ing and usage are different.

Ing: The parameter name is no longer @ xxx, but the serial number corresponding to it in the SQL statement.

Usage:

A. It is no longer where [email protected], but Server = ?. The question mark corresponds to the parameter name in the parameter ing table in the order of appearance.

B. The Stored Procedure in OLE DB uses the named parameter exec xxx_sp @ Server =? @ Environment =?

 

3. Dynamic scripts

To create different tables or insert data into different tables according to different situations, you can bind sqlstatementsource in SQL task expressions to dynamic statements, as shown in figure

Enter the expression as follows:

 

"create table [dbo].["[email protected][User::TargetTable]+"] ..."

In this way, the SQL statements of SQL tasks are dynamically generated based on your expressions. You can make full use of variables to increase program flexibility.

 

If the same statement appears multiple times in a package, you can bind the statement to a new variable whose evaluateasexpression is true, and then bind the new variable to the entire sqlstatementsource attribute.

 

4. A small bug in foreach Loop

At the beginning, the foreachloop option appears on the file, but the following editing interface is incorrect. You need to change the option to file again.

[Tips] A summary of getting started with SSIS package