T-SQL Tips: Execute Individual Packages in Project Deployment Model using Dtexec

The above question was the main topic of discussion in one of the recent casual conversations with my fellow colleagues. 

Both the below responses came up during the discussion

1. The package can only be executed by referring to the ispac (project) file as deployment model being specified is project deployment model which implies project itself as the basic unit

2. The package should still be able to be executed individually by referring to the corresponding dtsx file.

I thought of trying it for myself to see which one (or both) of the above responses are true. I’m sharing the illustration and result through this blog for the benefit of the wider audience

For the sake of this illustration I’ve created a very simple package in a project with deployment model being configured as default project deployment.

The package is trying to insert a row into a table in the database by receiving  a value through a parameter. There is a package scope parameter which is declared for the purpose as below

The table is setup as below

Now lets try executing the package once and check the result

As you see the package will populate the table with value that is passed from the parameter

Now lets try executing the package through dtexec. 

If you refer to the documentation of dtexec given below

You can see that the package can be executed using /FILE switch and pass parameter values through /SET switch

Now lets apply this in a sql query using xp_cmdshell as dtexec is a command line tool and see the result

The statement would look like

The execution result is as below

Now if you check the table you can see the value being populated

So it is implied that we can execute the package directly from dtexec using /FILE switch and passing package parameters through /SET Package.Variables[$Package::ParameterName] switch

In addition to this we can also execute this by specifying the ispac file. 

Lets see how we can write the query for this

Execution result as shown

Now check the execution result and you will see the below

As you see it will successfully populate the values. So we can see that we can execute the package either by referring to dtsx file directly using /FILE switch or by specifying ispac file using /PROJECT switch in dtexec.

Package with a Project Parameter

Now lets see what would be the behavior in case the package had a project parameter.
For this we will first modify the package parameter to make it a project parameter

Once that’s done we can use a statement as below

But we can see that we will get an error like this

This shows that dtexec is unable to resolve the reference to the project parameter while trying to execute directly from the dtsx file which is understandable and as expected

Now lets try execute package from ispac file and see

And you can see that it works fine and populates the table with the value as shown by the below result

So we can conclude that in the case of package with reference to a project parameter we can only execute it by referring to the ispac (project) file and not by directly referring to the individual dtsx file

As per the illustrations above we can summarize as below

1. Package with parameters defined in the package scope – Can be executed by referring both ispac by using /PROJECT switch or dtsx file by using /FILE switch in dtexec 

2. Package with parameter being defined in the project scope – Can only be executed by referring to the ispac file using /PROJECT switch

References

Sample package

dtexec

SSIS 2012 syntaxes

xp_cmdshell