DTS

Hi,

What is good way of executing .dtsx jobs in sql 2005, when stored in file format, or msdb database. Can my sql agent read jobs from file format as well as msdb database ?

Thanks &Regards

Nitu

Yes SQLAgent can execute file system or msdb packages. I'm surprised you could not find that information in Books Online. The online version is here ...

http://msdn2.microsoft.com/en-us/library/ms141701(SQL.90).aspx

At the bottom of every Books Online page there is an option to vote for the usefulness of the content. You can do that to help us improve the content - I'm concerned that you are not finding this information there, so any help you can give us would be appreciated.

Donald Farmer

Dtexecui.exe

When I try to run a .dtsx file or even the Dtexecui.exe I get following error:System.IO.FileNotFoundException.

Thanks

Bill

How are you running to dtsx?

At what stage of using dtexecui do you get the error?

Does it say what file cannot be found?

Is SSIS installed correctly on that machine? An open question, but do you think it is?

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;\"

DTExec is the only redist for executing dtsx file?

Question:

trying to execute SSIS package "Package.dtsx" from another machine. What are the requirements to do this without installing full SSIS?
I presume that we need .Net framework 2.0, but what are the minimum component or files I need to run a package?

thanks
HorseshoeMy understaning is that SSIS is not redistributable as DTS was. Either way there are more files than just dtexec. All the tasks and components are spread accross several assemblies for example. If it is redistributable, then it will be covered in redist.txt or the equivalent if that has changed from SQL 2000. I believe it is now licencsed as server component, so you need a server licence for any machine you install it on.|||Darren's absolutely right. For a headless install (without UI) on a server, you can select to not install the Tools, but you still need a license and the SQL Server setup to install SSIS on another machine.

regards
ash

DTExec and Pasword Prompting

A colleague of mine is having problems when trying to schedule package execution via a .bat file that executes a .dtsx on a sql server (not file system). The scheduling system that is employed by the company requires a .bat file to execute the package. The packages themselves move data from AS400 servers to SQL Servers. Occasionally (very randomly) when the scheduling system runs the statement in the bat files, the package prompts for password information (for connectivity).

We've tried a number of solutions to this, mostly on the "ProtectionLevel" property of the package itself. We did some research and it seems as though there are a few options.What is the best solution to eliminate this from happening? We certainly don't want to have people checking to make sure the packages don't prompt for passwords.

Thanks.

SSIS Amateur wrote:

A colleague of mine is having problems when trying to schedule package execution via a .bat file that executes a .dtsx on a sql server (not file system). The scheduling system that is employed by the company requires a .bat file to execute the package. The packages themselves move data from AS400 servers to SQL Servers. Occasionally (very randomly) when the scheduling system runs the statement in the bat files, the package prompts for password information (for connectivity).

We've tried a number of solutions to this, mostly on the "ProtectionLevel" property of the package itself. We did some research and it seems as though there are a few options.What is the best solution to eliminate this from happening? We certainly don't want to have people checking to make sure the packages don't prompt for passwords.

Thanks.

I recommend setting ProtectionLevel=DontSaveSensitive and then using configurations to store your conenction strings. This is the "least hassle" choice in my experience.

-Jamie

|||

OK, we'll try that.

Couple more things, in a whole series of packages, it only prompts for a password on one or another, and it's not always the same packages, even though they always run in the same order, but they all hit AS400 data. Just wondering if you had any input on that? They are all set up the same security wise.

Also, we just came across the connection manager property "ProtectionLevel". I didn't research it yet, but if we changed the setting from the default "0", could this have an impact?

|||

SSIS Amateur wrote:

OK, we'll try that.

Couple more things, in a whole series of packages, it only prompts for a password on one or another, and it's not always the same packages, even though they always run in the same order, but they all hit AS400 data. Just wondering if you had any input on that? They are all set up the same security wise.

Seems strange but I can't explain it. Sorry.

SSIS Amateur wrote:

Also, we just came across the connection manager property "ProtectionLevel". I didn't research it yet, but if we changed the setting from the default "0", could this have an impact?

Yes, it could. You should read the BOL topic on it.

-Jamie

|||

Jamie,

We're currently trying to implement your suggestion to set the Package Level Security "ProtectionLevel" to "DontSaveSensitive" and create a configuration to hold the connection strings. I seem to not be able to even get it to run in debug mode (let alone deploying and testing), because a login is failing. I noticed that the connection string does not store the password, and it's not possible to put it in there (just an observation - maybe not relevant).

It runs fine when I changed the ProtectionLevel back to the default setting. I'm know I'm missing something really stupid here. Your help is much appreciated, you know your stuff.

|||

SSIS Amateur wrote:

Jamie,

We're currently trying to implement your suggestion to set the Package Level Security "ProtectionLevel" to "DontSaveSensitive" and create a configuration to hold the connection strings. I seem to not be able to even get it to run in debug mode (let alone deploying and testing), because a login is failing. I noticed that the connection string does not store the password, and it's not possible to put it in there (just an observation - maybe not relevant).

It runs fine when I changed the ProtectionLevel back to the default setting. I'm know I'm missing something really stupid here. Your help is much appreciated, you know your stuff.

Good observation about the password. You have to manually edit the connection string yourself and put the password in there.

As you may know there was a big push in Microsoft not long ago around tightening security holes in its products and this is one of things that came out of that - it is considered a security risk for SSIS to store a password for you. Microsoft want you to responsible for creating security risks, not them.

-Jamie

|||Jamie, when you say "manually edit the connection string", where are you talking about? Actually opening the .dtsConfig file and editing? Because I can't seem to be able to successfully store the password in the Package editor.|||

OK, I have seemed to have figured my problem out. For those of you who may be a little new to SSIS or new to configuration files, here's a run down on my problem and how I fixed it.

My company scheduling software was getting prompted for a password when a .bat file containing a DTExec command was initiated, this was due to connecting to AS400 servers while transferring data. So we had to look into a way to prevent the scheduled .bat file executions from prompting for a password. Here is the solution, in detail for those of you who are lacking in SSIS experience like myself, and have a similar situation. Some of it is very obvious to most, but was not to me, so I included everything I could think of. Thanks once again to Jamie for all the help.

1. In the package, change protection level to "DontSaveSensitive" (click anywhere in the control flow, off of a task and look at properties).
2. Create a package configuration to store connection string (there are many examples on the web, here is one).
http://msdn2.microsoft.com/en-us/library/ms140213.aspx

3. You will have to manually edit the resulting .dtsConfig to include passwords. Open in notepad and add passwords to each connection. It will be stored in the project folder.
4. Build the package and deploy it, save the configuration file where you feel best. I saved on folder a sql server itself (this is an option when deploying).
5. Change (or create) your .bat file to include the /CONF option, this will be the fully qualified path each of your .dtsConfig files.
6. Rerun you .bat to make sure it works properly.

|||

SSIS Amateur wrote:

Jamie, when you say "manually edit the connection string", where are you talking about? Actually opening the .dtsConfig file and editing?

Yep. That's exactly what I mean.