Dynamic value passed to SSIs package)

To use SQL server integration services (SSIS ),CodeThe same Code set can be used as much as possible to handle multiple situations. I know how to create a dynamic file source using variable settings in the SSIS package, but how can I further transmit the dynamic value to the SSIS package?

Answer:

Like data transformation services (DTS) of SQL Server 2000, SSIS has the function of directly passing parameter values to the SSIS package at runtime. SSIS uses different syntaxes than DTS, but provides more options than DTs.

For example, we want to create a flat file source to import data to the SQL Server table. The first method is to use hard code, call the SSIS package in the command line, and then pass the parameter values to all the places in it.

Create a new SSIS package and use "Data Flow task ".

Then add "flat file source" and "ole db destination ". The flat file source is a CSV file, which contains the following columns: ID, name, address, city, state, and zip. The columns in the SQL Server table are exactly the same as those in the SQL Server table.

After hard code is added, the file package can be executed smoothly.

To better utilize the dynamic features of the SSIS package, we can create a new variable named "FILENAME", which will use the channel and name of the file we passed to the SSIS package.

First, create a new variable named "FILENAME.

After creating the variable, create an expression for the flat file connection, as shown in:

The file name to be imported is "C: \ temp \ test2.csv" (this is a non-existent file), which is based on the variable we just set, if we run the package now, the following error message will appear:

The variable is set to a file that does not actually exist, just to show that although the running package fails to run, it does run when we pass in the appropriate file name. The following is the syntax for executing the SSIS package and passing the variable value for the filename variable.

This syntax creates a command to run the SSIS package, but it uses the file name 'C: \ temp \ test.csv "instead of" C: \ temp \ test2.csv.

You can paste the following code into the query window for execution. (Note: You must enable xp_cmdshell to run the following code)

The following is a reference clip: Declare @ cmd varchar (1000) Declare @ ssispath varchar (1000) Declare @ filename varchar (1000) Set @ ssispath = 'C: \ temp \ package. dtsx' Set @ filename = 'C: \ temp \ test.csv' Select @ cmd = 'dtexec/F "'+ @ ssispath + '"' Select @ cmd = @ cmd + '/set \ package. Variables [user: Filename]. properties [value]; "' + @ filename + '"' Exec master .. xp_mongoshell @ cmd

After the code is run, the query result is displayed as follows:

If we want to pass two variables to the SSIS package, the steps are similar to above.
There are two variables: filename and filepath.

If we look at expression now, we will find that it works in the same way as above, but now we need to use two variables.

The following code transmits two variables to the SSIS package. Most of the encoding syntaxes are the same as above, but now we need two/set commands to pass two variables.

(Note: we must use \ at the end of filepath \\. If we only write "C: \ temp \", a running error occurs.

Now you can execute the SSIS package and the two variables that have been passed in.

The following is a reference clip: Declare @ cmd varchar (1000) Declare @ ssispath varchar (1000) Declare @ filepath varchar (1000) Declare @ filename varchar (1000) Set @ ssispath = 'C: \ temp \ package2.dtsx' Set @ filepath = 'C: \ temp \\' Set @ filename = 'test.csv' Select @ cmd = 'dtexec/F "'+ @ ssispath + '"' Select @ cmd = @ cmd + '/set \ package. Variables [user: filepath]. properties [value]; "' + @ filepath + '"' Select @ cmd = @ cmd + '/set \ package. Variables [user: Filename]. properties [value]; "' + @ filename + '"' Exec master .. xp_mongoshell @ cmd

Here we only give an example of passing User-Defined variables, but you can actually pass dynamic values of any form to control your SSIS package