Post reply

Can't get SSIS migrated package to run in SQL Server job, due to changes made by BIDS.`

  • December 14, 2010 at 1:04 pm

    #229588

    I have a DTSX/SSIS package that I created on a dev-server and now I'm moving it to the production server. I created the original DTSX with SQL Server Import and Export Wizard. It works fine and runs fine with a SQL Server Agent job on the dev-box. In order to move the DTSX to the new server I had to load it into BIDS and modify the account, server and database in the connection. I figured that out and the DTSX/SSIS runs fine by itself.

    However, when I try to run this in a SQL Server Agent job on the new server, it fails with an error that's reported a lot here:

    Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. I have attached the entire error message.

    See the file: SQL_Agent_Error.txt for the entire error message string.

    I've seen a lot of suggestions that involve proxy accounts, service start-up permissions, etc. These all look rather tedious and might do the job.

    My issue is that I set up my SQL Agent Job on the production server exactly the same way as on the dev-box. However, when I compare the connection strings in the job properties on the dev and production servers I see that they are different. This difference appears to be the cause of the problem.

    See the files:

    .Conn_Man_OLD.jpg Created with SSIE Wizard

    .Conn_Man_NEW.jpg Created with BIDS

    Apparently, BIDS is storing the connection information differently than the SSIE Wizard.

    Is there a way to make BIDS store the connection string the same way that the SSIE Wizard does, so that it will just work without having to use the more involved solutions?

  • December 14, 2010 at 1:25 pm

    #1262449

    Check in the package properties on security. The default protectionlevel is 'EncryptSensitiveWithUserKey'. This encrypts the passwords in the connection managers with a has of your login and machine info. The SQL agent cannot decrypt this. I like to use 'DontSaveSensitive' and pass the passwords in with configuration files.

    Also, any time you open a package for edit you need to go back into the connection managers and re-enter the password information. Even if you don't change anything, it seems to clear those fields when you open the package.

    I hope this helps.

  • December 14, 2010 at 3:47 pm

    #1262576

    Thanks. Can you point me to a web-page that has a concise recipe, set of instructions, for that?

    David

  • December 15, 2010 at 2:33 pm

    #1263165

    I found the following instructional video on MS TechNet. It explains 4 different ways to make this work.

    http://technet.microsoft.com/en-us/library/dd440760(SQL.100).aspx

    (Be careful, it leaves out some steps, so you'll have to draw on experience to make it work.)

    I went through it till I found a method that worked for me. I used method 2, "Save with Encrypted Password".

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

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