Thursday, October 4, 2012

Limitation of dtexec utility

For the convenience of developers who wants to automate the SSIS loads, Microsoft has bundled a dtexec.exe utility with SQL Server client installation, which you can use in command line (or in scripts) to run a SSIS packages and set values for the variables. All you need to do is, execute the command, 

dtexec /option value

Sounds simple right?? Well in my case it is not. I am having a strange issue with dtexec utility. I am passing the connection string using a variable. For example, I am trying to call the package with the following:

dtexec /set "Package1.ConnectionString;DataSource=localhost;Initial catalog=Dev;Integrated Security=SSPI" Package.dtsx

This doesn't work. As you know, most of the connection strings will have semi colon in it. If I try with this command, it is throwing me out. I can't figure out why this is happening, so I wrote a small .NET client using Microsoft.SQLServer.DTS.Runtime and solved it temporarily. I have posted this question to MS, I will update this when I get back from them.

No comments: