DtExec: setting user-defined properties with whitespace?

Hi there.

I'd like to call dtexec with something like this:

dtexec /f myPackage.dtsx /Set \package.variables[User::connStr].Value;Source=localhost;Provider=blah;Integrated Security=SSPI;

I get an error along the lines of

Option "Source=localhost;Provider=blah;Integrated" is not valid".

How do I pass in a property containing spaces? I've tried all of the usual quote-encasing patterns I can think of.

Thanks,

Jon

JonB_QRM wrote:

Hi there.

I'd like to call dtexec with something like this:

dtexec /f myPackage.dtsx /Set \package.variables[User::connStr].Value;Source=localhost;Provider=blah;Integrated Security=SSPI;

I get an error along the lines of

Option "Source=localhost;Provider=blah;Integrated" is not valid".

How do I pass in a property containing spaces? I've tried all of the usual quote-encasing patterns I can think of.

Thanks,

Jon

Double quotes.

dtexec /f myPackage.dtsx /Set \package.variables[User::connStr].Value;"Source=localhost;Provider=blah;Integrated Security=SSPI;"|||

Thanks for getting back so quickly. Your suggestion worked for a date string passed in as a property, but not a connection string.

When I run

dtexec /f package.dtsx /Set \package.variables[User::effectiveDate].Value;"2007-04-30 09:00" /Set \package.variables[User::connStr].Value;"Data Source=localhost\2005;Initial Catalog=QRMDB1_MRKT_SVC;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;"

I get the following complaint:

Argument ""\package.variables[User::connStr].Value;Data Source=localhost\2005;Initial Catalog=QRMDB1_MRKT_SVC;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;"" for option "set" is not valid

It doesn't complain about the quotes around the date value.

Thoughts?

|||As an aside, if I write these props to a file and specify it using the /Com <filename> switch, dtexec takes them.|||

you may need a backslash to escape the double quotes.

Try this:

/Set \package.variables[User::connStr].Value;\"Data Source=localhost\2005;Initial Catalog=QRMDB1_MRKT_SVC;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;\"

DT_Str and trailing whitespace?

I have a OLE DB Source going to a flat file destination. My source is a sql variable with "select * from tablename" which have varchar datatypes. Yet I'm getting trailing spaces at the end of some of my columns (for instance, my address column).

I've checked the data by doing a "select Max(Len(address)) from tablename" and the max is only like 34 chars, yet each of them have 100 chars total.

Taking a look at my flat file connection, the outputColumnWidth is 100, and datatype is string [DT_STR]. Am I crazy? What's the problem here? Is the DT_STR datatype the equivalent of char, and not varchar?

Any help, of course, will be appreciated.
LEN does not count trailing spaces.

Try this: select max(len(replace(address,' ','*')))
|||What about using a derived column in the dataflow using ltrim/rtrim/trim function?|||Are you using a fixed length flat file format, or a delimited format?|||Yeah, are you doing a fixed width format on your file? Please share the format of your destination flat file. Fixed width will be 100, in this case. No way around that.|||Thanks for the responses...

The flat file destination is delimited, not fixed width.

As I checked the select max(len(replace(),' ','*')), I see that the data is bad... thanks so much for the help!!! Think that should fix it.

|||I'm curious to know what's bad about the data. Varchars don't store trailing spaces so I'm curious to just know the end result.|||Basically I have about 4 SSIS Packages taking data through a long set of processes. In one of the initial processes, I found that the address field was being stored as a char(500). Later on in the process, it's run through a data conversion that put it as a varchar(100), which is where it remained through the rest of the packages. But at this point, I guess, the trailing spaces were already in there, and remained in there.

I had thought the data was fine this whole time, mainly because when I used the TSQL len() function, the data seemed fine. Same with in Management studio, I'd double-click the separators between column headers to let it auto-resize the column to fit the largest address, without spaces at the end.

Glad I found it... thanks for all the help. =)