Post reply

using ; (semi colon) within DTEXEC parameter value?

  • January 21, 2010 at 2:42 pm

    #219739

    Dear all,

    I am trying to start a SSIS Package via DTEXEC and pass a database connection through a parameter but DTEXEC seems unhappy with semi-colons within the parameter value...

    My command looks like this

    DTEXEC /DTS "\MSDB\MyPackage" /SERVER "ERIC" /SET \Package.Variables[User::ConfigDbConn].Value;"Data Source=whatever;Initial Catalog=MyDb"

    and I get an error like this

    Argument ""\Package.Variables[User::ConfigDbConn].Value;Data Source=;Initial Catalog=FoxDW_Configuration"" for option "set" is not valid.

    Does someone know how I could pass a value like "Data Source=whatever;Initial Catalog=MyDb" to my SSIS package using DTEXEC?

    Many Thanks

    Eric

  • January 25, 2010 at 10:06 am

    #1108253

    use a config file 😉

  • January 26, 2010 at 1:51 am

    #1108502

    In our context I can't really do this because we may have several instances of the software on one box, hence file paths are dangerous.

    I tried to pass it as a Environment variable configuration via the sql server agent but I then realised that only the first OS command is executed.

    Finally, I sorted it by passing a very simple string identifying my environment, like "Dev", "System Test", etc and looking up into a bootstrap table within the master database.

    I think this solves our problem...

    Many thanks for your help

  • January 28, 2010 at 11:07 am

    #1110245

    Eric,

    You have to quote the complete parameter and double quote, the quotes inside.

    Try this:

    DTEXEC /DTS "\MSDB\MyPackage" /SERVER "ERIC" /SET "\Package.Variables[User::ConfigDbConn].Value;""Data Source=whatever;Initial Catalog=MyDb"""

    or this:

    DTEXEC /DTS "\MSDB\MyPackage" /SERVER "ERIC" /SET "\Package.Variables[User::ConfigDbConn].Value;\"Data Source=whatever;Initial Catalog=MyDb\""

    ---
    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

  • March 14, 2012 at 12:30 pm

    #1459437

    Just wanted to update for the benefit of someone who might find this thread based on a google search like I did. The second syntax style shown below with the "\" does NOT allow special characters, however the first syntax with all the double and tripple double quotes works perfectly! So thanks a lot for sharing, this was driving me crazy and I could not find a solution anywhere else on the internet. In my case it was common query values like ">" that were causing the problem but its the same as the issue the original person was having with ";".

    For what its worth, I just finished writing a stored procedure that can take up to 50 SQL queries as parameters, 10 fixed values, one stored proc name, and it calls an SSIS package that creates any custom excel report you want based on a template you define. I wish someone besides myself could benefit from all this work because I find it to be incredibly useful, you can create extremely complex sql driven custom excel reports that are not possible from something like reporting services, and it is all incredibly easy for the end user creating the automated reports. Reports are generated from just a single stored procedure call. It also has elaborate error notification and optional email notification and report delivery. Maybe I'll create my own detailed article about it on SQL ServerCentral.

    -Gordo

    CozyRoc (1/28/2010)

    Eric,

    You have to quote the complete parameter and double quote, the quotes inside.

    Try this:

    DTEXEC /DTS "\MSDB\MyPackage" /SERVER "ERIC" /SET "\Package.Variables[User::ConfigDbConn].Value;""Data Source=whatever;Initial Catalog=MyDb"""

    or this:

    DTEXEC /DTS "\MSDB\MyPackage" /SERVER "ERIC" /SET "\Package.Variables[User::ConfigDbConn].Value;\"Data Source=whatever;Initial Catalog=MyDb\""

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply