August 23, 2012 at 5:12 pm
Hello - I have an import/export wizard SSIS package that I have saved, and am trying to automate, but it keeps failing whenever I try to run the job (it works the first time, and then never again). It seems that the connection manager(s) available to the package in the Data sources tab of the Agent job, have connection strings that look to point to a path for the user that was used to authenticate the original copy job that I saved. It also has a couple of connection managers in there from attempts that were used to try the sa account instead of this particular Windows Account.
I get one of 2 errors as follows...
Message for trying the sa account
Executed as user: PRO-DB2-DEV\SQLAdmin. ... Execute Package Utility Version 9.00.4035.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 3:56:14 PM Error: 2012-08-23 15:56:16.19 Code: 0xC0202009 Source: TN-Report 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 'sa'.". End Error Error: 2012-08-23 15:56:16.21 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: 3:56:14 PM Finished: 3:56:16 PM Elapsed: 1.641 seconds. The package execution failed. The step failed.
And...
Message for trying the Windows Account
Executed as user: PRO-DB2-DEV\SQLAdmin. Microsoft (R) SQL Server Execute Package Utility Version 9.00.4035.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 3:55:06 PM Error: 2012-08-23 15:55:08.44 Code: 0xC002F304 Source: NonTransactableSql Execute SQL Task Description: An error occurred with the following error message: "Could not find file 'C:\Documents and Settings\SQLAdmin\Local Settings\Temp\1\tmpB.tmp'.". End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 3:55:06 PM Finished: 3:55:08 PM Elapsed: 1.891 seconds. The package execution failed. The step failed.
Any thoughts or suggestions would be greatly appreicated, and if I may provide more details to this - please let me know.
SQL_ME_RICH
August 23, 2012 at 6:16 pm
Two things are going on here, and they're not entirely independent but a few questions.
I assume you originally built this package from your local system, but the server is not local and is on the network somewhere. With that assumption, I'll keep going. If that's incorrect, please fill us in on the details.
Your SQLAgent on that server is probably set to localhost somethingorother instead of an actual NT network account. Thus, it's attempting to do an sa login and failing since the password isn't in the connection of the package you built. Secondly, the temp file it's looking for is being looked for locally on the server, not your system, so it can't function from the server.
What I would recommend doing is taking the dtsx package you've built, opening it up in BIDS, and getting the data source set to a more neutral location, such as a folder on a file system somewhere on the network. Next, if you're an admin on the server, get into the SQLAgent settings in the Services (Computer-Manage) and find out what login it's pretending to be.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 24, 2012 at 6:08 pm
Evil Kraig F (8/23/2012)
Two things are going on here, and they're not entirely independent but a few questions.I assume you originally built this package from your local system, but the server is not local and is on the network somewhere. With that assumption, I'll keep going. If that's incorrect, please fill us in on the details.
Your SQLAgent on that server is probably set to localhost somethingorother instead of an actual NT network account. Thus, it's attempting to do an sa login and failing since the password isn't in the connection of the package you built. Secondly, the temp file it's looking for is being looked for locally on the server, not your system, so it can't function from the server.
What I would recommend doing is taking the dtsx package you've built, opening it up in BIDS, and getting the data source set to a more neutral location, such as a folder on a file system somewhere on the network. Next, if you're an admin on the server, get into the SQLAgent settings in the Services (Computer-Manage) and find out what login it's pretending to be.
Hi Kraig!
Actually - I created the job from an RDP session over to one of the servers involved. I attached to the other instance inside of that server's instance, and created the package from the export wizard.
I just verified that both servers have the SQL Agent using this one account 'SQLAdmin'.
Sorry for the delay in responding to this, but hopefully I can come up with a fix for it soon.
P.S. I do not have BIDS on this system, which is why I am trying to set the package up in either SQL Server or from the file system as the package store is not available. :-/
August 28, 2012 at 12:33 pm
Still really needing anyone's insight into this issue. The package seems to work the very first time I recreate the job, but there after - it fails as described above.
August 28, 2012 at 1:18 pm
Fell off the planet for a few days, RL stuff. The admin issue will be hard to troubleshoot over the net. As to the file disappearing, does the package 'archive' the file? IE: Can it just not find it because the original package moves it somewhere else as a repository?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 28, 2012 at 3:43 pm
Hi Kraig - no worries, I too have been hit-n-miss out here.
No, the package does not disappear or get moved or anything like that. I have tried it from within SQL (not the package store because again BIDS is not installed as part of this install), and from the file system, but in both cases it works the first time I create the job, and then fails every time there after.
I think I am going to try and set the job up on the other server that I am attaching to, and see if I cannot get it to work over there. Will report back in a bit!
August 29, 2012 at 12:31 pm
Just in case anyone is interested (or not), I did finally resolve this issue, but here is what I had to do.
I needed to do the export from an Agent job that I created on the PROD server (for which the data resides that this other server's instance is needing - this other server is a DEV server, and use to receive this data via a Transactional Replication job that has long since been broken).
Even after created the jobs separately (the one on PROD to export the data over, and one on the DEV box to delete the records in those tables being exported to), I kept getting errors. But - the probably was not actually the account I was using. I finally took a closer look at the error in the job history, and what I kept seeing was that the local settings for the Windows account was continually erroring on trying to find a temp file called tmpA.tmp. I had to manually create these files, but even that didn't work. I was able to determine what each one represented from the data sources tab in my Agent job when I went to edit the step, but it wasn't until I recreated them all that the job then created a whole new set of temp files for the job to use. I had to delete the old ones that I had created manually, point to the one that the data sources tab had listed as the .XML tables file (which was not hard to find because it actually populated the tmp file with this XML), and voila! Fully automated and working.
The job is stored in SQL Server (not the file system). I did this for protection to the job itself so no one could accidently mess around with this file.
Very happy and learned a great deal in the process. Thanks again Evil Kraig!
SQL_ME_RICH
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply