Post reply

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

  • January 3, 2007 at 6:22 pm

    #115852

    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

  • January 8, 2007 at 7:21 am

    #681752

    Hi,

    I had a similar problem when connecting to an Oracle database. I solved it by using passwords to encrypt the sensible information in my packages.

    The ugly thing is that you need to encrypt all packages on the way from your leaf package (the ones called last from the others) up to the root package. Only if the complete path is correctly encrypted, it can be decrypted at runtime.

    If you run the packages in an SQL Agent job, you will need to pass the password to the first package as well (there is a parameter for that, please see BOL for the syntax).

    The next ugly thing is that VS asks for the password without naming the package where it needs the password for. So if you have several packages open, you have no way in telling which one is needed. I was forced to use the same password for alle to avoid this pitfall. Entering the wrong password once will invalidate the information in your package and you will need to enter and save it again. As you don't know which ones, you have to edit ALL packages.

    Cheers,

     

    Michael

Viewing 2 posts - 1 through 1 (of 1 total)

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