SSIS Package Fails as a Job - cannot get it to run!

Question

Post reply

SSIS Package Fails as a Job - cannot get it to run!

sql_jr

SSCommitted

Points: 1612
More actions
March 27, 2009 at 1:25 pm

#384236

I set up a simple SSIS package via the Import/Export wizard, saved with "DoNotSaveWithSensitvePW", scheduled it as a job.
And whether it runs through the scheduled agent, or I invoke it manually, I get this error:
Description: Failed to decrypt protected XML node "DTSassword" 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've seen a lot of confusing google hits, that talks about proxies, etc. so, I need exact steps 1,2,3....etc. The account(s) I use are sysadmin.
I even set up proxy and assign it to the job step, and not working.
Await reply!

Viewing 15 posts - 1 through 15 (of 19 total)

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

Mayank Khatri SSCertifiable Points: 7984 More actions · Answer 1

R u saving ur package as SQL server or as File system.(The radio buttons that comes at the end of the wizard)?

sql_jr SSCommitted Points: 1612 More actions · Answer 2

thx for reply. I saved it to the SQL Server. I can't even kick off the job without erroring out. Suggestions?

sql_jr SSCommitted Points: 1612 More actions · Answer 3

I feel like I tried everything! I went through this article step-by-step and still no success.

http://www.codeproject.com/KB/aspnet/Schedule__Run__SSIS__DTS.aspx

The only thing I couldn't/didn't do is set up the credential for a WINDOWS account. I can only use SQL Authentication here. Is this a necessity? Not sure if this is just the author's example. Please advise, THANKS!

COZYROC One Orange Chip Points: 28499 More actions · Answer 4

What protection level do you use for your package?

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

sql_jr SSCommitted Points: 1612 More actions · Answer 5

I tried both 'Do Not Save Sensitive'.... (ie no protection), and 'Rely on Server and Storage Roles' but no success - with different errors.

I forced the job step to go back to using the SQL Server Agent account instead of proxy.

Could not find file 'C:\Documents and Settings\myprofile\Local Settings\Temp\tmp32.tmp' Why they're lkg for temp file I don't know.

When I force it to use my setup proxy, it says cannot find data for proxy

IDEAS? THX

Mayank Khatri SSCertifiable Points: 7984 More actions · Answer 6

What kind of job is that? Is it just transfering data from one servers database and table to other servers table? Have u tried saving ur package as SSIS file instead of SQL server?(Choosing File system option)

vishal.gamji SSCertifiable Points: 6126 More actions · Answer 7

I know u used DontSaveSensitive, but when u created the pkg, did u enter any password?

ssismaddy SSCarpal Tunnel Points: 4850 More actions · Answer 8

I know, the import and export wizard creates some temporary files, may be you are missing one of those temp files...generally, if you restart the server, all the temp files will be deleted...

bake13 SSC Eights! Points: 955 More actions · Answer 9

I would recommend you add a log file to the SSIS package that will provide some more verbose error information. To do that, in the package, right-click in the Control Flow tab and select logging, add the connection, select some events to log.

FWIW, many of the times I experienced this error had nothing to do with the package protection level.

sql_jr SSCommitted Points: 1612 More actions · Answer 10

I believe I do save my sql authenticated password. I will try to save as a file, and turn on logging.

This is most frustrating, b/c you are now required to go into the package to make all these changes and account for security layers, rather than using the import/export wizard for a simple data transfer. I will post back shorly. Please keep the suggestions coming........Many thanks!

sql_jr SSCommitted Points: 1612 More actions · Answer 11

Hi. This is getting SUPERCRITICAL here. I made the package a .dtsx file. I need some tips on what I should do.

1. Do I need a proxy setup, or can I run it under the SQL Agent Service account

2. Do I need to save my sql password in the package.

3. What other permissions do I need.

Please help!!! thx.

sql_jr SSCommitted Points: 1612 More actions · Answer 12

Executed as user: Domain\mysqluser ... Package Utility Version 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 5:11:30 PM Error: 2009-03-31 17:11:47.61 Code: 0xC0202009 Source: Daily Data Refresh1 Connection manager "DestinationConnection" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'mysqluser'.". End Error Error: 2009-03-31 17:11:47.62 Code: 0xC00291EC Source: NonTransactableSql Execute SQL Task Description: Failed to acquire connection "DestinationConnection". Connection may not be configured correctly or you may not have the right permissions on this connection. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 5:11:30 PM Finished: 5:11:47 PM Elapsed: . The step failed.

H-E-L-P?? I saved pw in package and tested connection successfully - so why the above error???

Mayank Khatri SSCertifiable Points: 7984 More actions · Answer 13

Can u do this:

1)Save the password

2)Test the connection when connecting to another server

3)Use,security "encryptAllwithPassword" option and save ur package.

4)Execute ur package manually first i.e. if its saved as a file then run the package by rt clicking it and then choosing Execute package option. OR double clicking it and run the package using Execute package utility.

If above steps go fine(The package runs manually) then u can think of scheduling it or using SQL server agent to run tha package.

sql_jr SSCommitted Points: 1612 More actions · Answer 14

Thx much for your reply. I will try that. I did the following, right before I saw your post.

1. Go directly on the destination server

2. Create the package (SQL authentication

3. Do Not Save Sensitive Data

4. Save as dtsx file.

5. Schedule new job pointing to new file.

I thought I finally had it working, when after a few minutes, I got:

00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 6:50:31 PM Error: 2009-03-31 18:52:03.47 Code: 0xC0202009 Source: {CB31A447-1C4C-46C1-B985-B245BD421130} OLE DB Destination [2] Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". End Error Error: 2009-03-31 18:52:03.47 Code: 0xC0202025 Source: {CB31A447-1C4C-46C1-B985-B245BD421130} OLE DB Destination [2] Description: Cannot create an OLE DB accessor. Verify that the column metadata is valid. End Error Error: 2009-03-31 18:52:03.47 Code: 0xC004701A Source: {CB31A447-1C4C-46C1-B985-B245BD421130} DTS.Pipeline Descri... The package execution fa... The step failed.