Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS package errors and fails in SQL Agent... Expand / Collapse
Author
Message
Posted Thursday, August 23, 2012 5:12 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, October 17, 2014 5:02 PM
Points: 485, Visits: 1,368
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
Post #1349392
Posted Thursday, August 23, 2012 6:16 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:51 PM
Points: 5,401, Visits: 7,513
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.



- Craig Farrell

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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1349406
Posted Friday, August 24, 2012 6:08 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, October 17, 2014 5:02 PM
Points: 485, Visits: 1,368
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. :-/
Post #1349982
Posted Tuesday, August 28, 2012 12:33 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, October 17, 2014 5:02 PM
Points: 485, Visits: 1,368
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.
Post #1351182
Posted Tuesday, August 28, 2012 1:18 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:51 PM
Points: 5,401, Visits: 7,513
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?


- Craig Farrell

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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1351206
Posted Tuesday, August 28, 2012 3:43 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, October 17, 2014 5:02 PM
Points: 485, Visits: 1,368
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!
Post #1351307
Posted Wednesday, August 29, 2012 12:31 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, October 17, 2014 5:02 PM
Points: 485, Visits: 1,368
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
Post #1351830
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse