Difficult times deploying a few packages to SQL Server and running as a job

Hi Guys!

I have created a big list of packages, some calling others. They all work fine from my computer using Visual Studio.

When I try to deploy them (building them with deployment turned on and running them either directly from Management Studio or as a job) I get the errors with the password of connection strings. From what I read so far its the encryption process that kills it.

I have tried to add a password to some packages, but it still didnt work (only when run directly on my computer in management studio after deploying to SQL Server, but not as a job).

I have tried to change ProtectionLevel to SecurityStorage, wouldnt let me save in Visual Studio (I understand it is ot allowed in VS because you are saving to File System, how the hell am I supposed to save it to anything else? why is it showing there if its not even valid?).

If anyone can please give me the steps to doing it properly, that would be awesome. I simply need to run the packages from SQL Server! thats all! I have no idea why it has to be soooo difficult :/

A step-by-step guide would be different depending on several factors. Are you planning to store your packages as file system files? Are you using package configuration? How are you going to run the packages (may be via SQL Agent job)?

Personally, I store package as file system files and use something similar to method 4 in this KB article. I hope that helps

http://support.microsoft.com/kb/918760

|||

Hi Refael!

I don't mind saving it to the file system or putting it on SQL Server, as long as it works! :)

How do you actually create this package configuration? and how do you then indicate to the data process that the password is stored there?
I am replicating from Oracle using Microsoft Oracle provider (for which I need the password) to SQL Server.

Thanks so much for your help

Guy

|||

Weird thing: I have changed one package to have a password and when I import it to 'stored packages' or run it from the file system on Management Studio on the server itself (simply copying the files from the development machine to the server) its not asking me for a password and it runs it. does this makes sense?

|||

Just search for package configurations to set connection strings...it would make your packages nicely portables.

|||

I have tried to find out (google) how to make a configuration file in xml but couldn't find much and have no idea what the format should look like.

If you could help me here I would really appriciate it.

|||

guyguy2003 wrote:

I have tried to find out (google) how to make a configuration file in xml but couldn't find much and have no idea what the format should look like.

If you could help me here I would really appriciate it.

http://msdn2.microsoft.com/en-us/library/ms141682.aspxsql