May 13, 2011 at 4:58 pm
Is it possible to run an SSIS package from my reports server to query our production server and saving the results into a text file on our production server?
The package works just fine in BIDS running it manually. It's when I try and schedule it that is giving me problems. I've read 4 or 5 web pages that have this information (or something like it) but nothing seems to be working after following the suggested resolutions....
http://support.microsoft.com/kb/918760
TIA,
John
May 13, 2011 at 5:09 pm
Trying to get schedule the package to run on the reports server gives me the following...
Message
Executed as user: REPORTS\SYSTEM. ...ion 9.00.5000.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 4:24:06 PM Error: 2011-05-13 16:24:07.39 Code: 0xC0202009 Source: mrsi Connection manager "production.db.sa" 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 'sa'.". End Error Error: 2011-05-13 16:24:07.39 Code: 0xC020801C Source: Data Flow Task OLE DB Source [1] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "production.db.sa" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2011-05-13 16:2... The package execution fa... The step failed.
SSIS isn't installed on our Production server (didn't know that at the beginning) and these were the messages I was getting trying to run the package from there...
Message
Executed as user: production\SYSTEM. ... 9.00.4035.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 4:47:49 PM Error: 2011-05-12 16:47:50.41 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: 2011-05-12 16:47:51.17 Code: 0xC004706C Source: Data Flow Task DTS.Pipeline Description: Component "component "Flat File Destination" (16)" could not be created and returned error code 0x80070005. Make sure that the component is registered correctly. End Error Error: 2011-05-12 16:47:51.45 Code: 0xC0202009 Source: mrsi Connection manager "production.db.sa" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB e... The package execution fa... The step failed.
Can you schedule a dtsx package to run on a server that doesn't have SSIS installed? I'm thinking it isn't possible.
May 16, 2011 at 10:26 am
I would surmise that you are running the package under a different user than is was created with. The default protection mode for packages causes saved credentials to be encrypted with the user's information that created it.
As a side node, you are using SA to do your login. This is an EXTREMELY bad practice and I cannot in strong enough terms tell you not to. Use trusted connectivity, first it doesn't have any saved credentials so the protection mode won't break it and it isn't SA..
CEWII
May 16, 2011 at 5:12 pm
Elliott Whitlow (5/16/2011)
I would surmise that you are running the package under a different user than is was created with. The default protection mode for packages causes saved credentials to be encrypted with the user's information that created it.As a side node, you are using SA to do your login. This is an EXTREMELY bad practice and I cannot in strong enough terms tell you not to. Use trusted connectivity, first it doesn't have any saved credentials so the protection mode won't break it and it isn't SA..
CEWII
Thanks for the response Elliot. I appreciate any and all responses I can get.
When I post in this forum and others, I either come right out and say that I'm using administrator and/or SA or it's shown in the information I give (ie errors etc) so people can't say that I don't have access to what I'm after. In this case, I'm logged in as the domain administrator using the SA account so that I know it's not a rights issue. When I'm able to get it running that way, I'll insert other credentials that don't have those kind of rights. I created this package using the domain administrator account and am trying schedule it under the domain administrator account. I didn't use different accounts.
Here's more information in case it helps. Our production server is running SQL Server 2005 64 bit Enterprise edition. My reports server is running the 32 bit Standard edition. That shouldn't make a difference.....should it?
May 16, 2011 at 6:25 pm
Ok, I could see that.. Unfortunately I see so many bad practices I just have to assume its real.
As far as a 64-bit connecting to a 32-bit or vice versa, no it shouldn't make any difference.
Unfortunately using SA does not resolve permissions issues when opening the SSIS package. If you have a saved passwork in the SSIS package then it is stored in an encrypted format with the key being tied to the user who saved it last. Opening it as SA does not get around this.
If the job is using the quivalent to SA to run the job then the credentials can't be decrypted.
However I seem to remember the flat file destination being a jet-based driver and therefore you must run the package with the 32-bit version of DTExec on a 64-bit server.
CEWII
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply