January 22, 2009 at 12:24 pm
Hello Everyone,
I have tax data that must be uploaded to our 6 servers at 12:00:01 on the 1st day of every month. We currently have an SSIS package that does this that is run manually on the last day of the month. This package moves the data into staging tables on each of the servers and then a sql server job runs at the specified time to move the tax data into production tables.
The entire process runs like this:
1) Front end gets a CD that they run which moves all the required tax data to Server1.
2) I run the SSIS package, which takes the data from Server1 and moves it to staging tables on Server2-6.
3) Job runs at 12:00:01 to move data from staging tables to production tables.
Because I love automation (because I would rather do something else really), I am trying to set this SSIS package up to run on a job as well. As such, I did the following:
I have moved the SSIS package to the c:\ drive of Server1. When I setup the job, I tell it that
a) this is an SSIS package
b) Package Source = file system and then the location of the file.
c) I check the boxes for all of the connection managers that show up under the data sources tab.
However, when I move the package to the server, it won't run on a job. I get the following message:
Executed as user: 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. End Error Error: 2009-01-22 14:09:47.74 Code: 0xC0016016 Source: 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. End Error Error: 2009-01-22 14:09:47.75 Code: 0xC0016016 Source: Description: Failed to ... The package execution fa... The step failed.
Any thoughts on what is going on and how to resolve the situation?
Thank you all very much for your assistance.
Sincerely,
Fraggle
January 22, 2009 at 12:31 pm
That's from encryption in the SSIS package.
The simple solution is, when you save the SSIS package, save it to the SQL Server, using the "Save a copy of ..." option on the File menu. In the options you get, change the security to "use system security" (I think it's the last option on that).
Then set up the job to use the package from SQL.
You can probably do that from the file as well, but this will be more secure.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 22, 2009 at 12:41 pm
Forgive me, but I don't have "Save a copy of...." option from the file menu. I do have a Save As command, but I do not get any options menu when I take that route.
Thanks,
Fraggle
January 22, 2009 at 12:43 pm
If you would rather store the package in the files system, as I do, set the pacakge protectionlevel to "EncryptAllWithPassword".
See this thread: http://www.sqlservercentral.com/Forums/Topic619676-148-1.aspx
Greg
January 22, 2009 at 12:59 pm
Sweet, got both options to work. Now I get the following error messages during the job run. I do have access to all the other servers from SSMS and can connect without issue. Thoughts?
SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Login timeout expired". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Named Pipes Provider: Could not ... The package execution fa... The step failed.
January 23, 2009 at 2:21 pm
Since it's timing out on the login, I suspect that it's not being passed a password and/or username, and it expects one. Might need to be part of your configuration.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply