SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS package errors and fails in SQL Agent...


SSIS package errors and fails in SQL Agent...

Author
Message
SQL_ME_RICH
SQL_ME_RICH
SSC Eights!
SSC Eights! (926 reputation)SSC Eights! (926 reputation)SSC Eights! (926 reputation)SSC Eights! (926 reputation)SSC Eights! (926 reputation)SSC Eights! (926 reputation)SSC Eights! (926 reputation)SSC Eights! (926 reputation)

Group: General Forum Members
Points: 926 Visits: 1595
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
Evil Kraig F
Evil Kraig F
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8561 Visits: 7660
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
SQL_ME_RICH
SQL_ME_RICH
SSC Eights!
SSC Eights! (926 reputation)SSC Eights! (926 reputation)SSC Eights! (926 reputation)SSC Eights! (926 reputation)SSC Eights! (926 reputation)SSC Eights! (926 reputation)SSC Eights! (926 reputation)SSC Eights! (926 reputation)

Group: General Forum Members
Points: 926 Visits: 1595
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. :-/
SQL_ME_RICH
SQL_ME_RICH
SSC Eights!
SSC Eights! (926 reputation)SSC Eights! (926 reputation)SSC Eights! (926 reputation)SSC Eights! (926 reputation)SSC Eights! (926 reputation)SSC Eights! (926 reputation)SSC Eights! (926 reputation)SSC Eights! (926 reputation)

Group: General Forum Members
Points: 926 Visits: 1595
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.
Evil Kraig F
Evil Kraig F
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8561 Visits: 7660
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
SQL_ME_RICH
SQL_ME_RICH
SSC Eights!
SSC Eights! (926 reputation)SSC Eights! (926 reputation)SSC Eights! (926 reputation)SSC Eights! (926 reputation)SSC Eights! (926 reputation)SSC Eights! (926 reputation)SSC Eights! (926 reputation)SSC Eights! (926 reputation)

Group: General Forum Members
Points: 926 Visits: 1595
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!
SQL_ME_RICH
SQL_ME_RICH
SSC Eights!
SSC Eights! (926 reputation)SSC Eights! (926 reputation)SSC Eights! (926 reputation)SSC Eights! (926 reputation)SSC Eights! (926 reputation)SSC Eights! (926 reputation)SSC Eights! (926 reputation)SSC Eights! (926 reputation)

Group: General Forum Members
Points: 926 Visits: 1595
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search