January 29, 2008 at 3:00 pm
Hello,
I would like your expertise in overcoming these errors in an SSIS package. Don't ask me why? And now it falls on me to troubleshoot this crap. This package has been developed by one of our earlier developer and was deployed to production. It was executing fine for some days and it was erroring out sometimes. The same is working fine on the development environment which is a standalone and having a linked server to the database server from where the data is being captured.
The production system is a cluster environment, wherein the package source has been deployed to one machine, the metadata is residing on a different box where the SSIS package is writing to and the data resides on another box (linked server) from which SSIS picks the information.
Overall the SSIS package collects the data by processing and writes them to underlying tables after truncating them and in the end it generates .txt files with comma delimited tab.
These are the errors that are being written to the application log.
Event Name: OnError
Message: Executing the query "audit.up_Event_Package_OnError ?, ?" failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Event Name: OnError
Message: component "Batches" (1) failed the pre-execute phase and returned error code 0xC0202009.
Event Name: OnError
Message: "component "Feeds" (1)" failed validation and returned validation status "VS_NEEDSNEWMETADATA".
Event Name: OnError
Message: Error 0xC0012050 while loading package file "R:\Sanctions.dtsx". Package failed validation from the ExecutePackage task. The package cannot run.
I tried to google and was not able to find a resolution for these.
Would appreciate if anybody encountered these and the modifications they made.
Thanks
Lucky
January 30, 2008 at 4:42 am
This is going to be a bit tricky to troubleshoot without being able to look at package and your environment, but I will try to send you in the right direction.
First, interestingly enough, your first error is happening in the package error handler (or a component error handler). There is a procedure "audit.up_Event_Package_OnError" that the package cannot run. This procedure, I presume, logs some package errors to a table to help you troubleshoot your problems. It is probably failing because either the login being used by the pacage does not have enough permissions to run the procedure. This is easy to check - give execute permission on this procedure to "Public" and see if the error handling now works. Or, you could connect to the server using the credentials the package uses and try to run the procedure through Management Studio. You may not have seen this issue before because the package was not getting sent into the error handling routine.
Now, the second real useful error you have sent is the one indicating that the package "R:\Sanctions.dtsx" cannot be loaded. This is an SSIS package that is being called by the package that is giving you the error. Make sure it is actually in the folder. Make sure the login running your SSIS package has permissions to the folder, and make sure the package in that folder is not corrupt (just open it). It is also suspicious that the package in on the "R" drive. Most administrators do not use "R" for a local drive, so this is probably a mapped drive to a network share. Make sure the drive is properly mapped for the login the SSIS package is running under remembering that if the package is running from a job agent that the login may be a service account. I would tend to not use mapped drives for this type of thing.
Finally, what is running the package when it fails? Remember that the package runs from the machine that executes it. So, if you deploy a package to the MSDB database on your production server and open Management Studio on your desktop PC and execute the package, it is running in the context of your desktop PC - meaning you have to have the specified package on your local "R" drive, not the "R" drive of your production server. This is especially important if you store packages on one server and run them from a job agent on another server. The job agent server needs the drive mappings, not the SSIS storage server.
I hope I helped and this was not just a really long-winded useless post.
October 21, 2009 at 7:54 am
The child package ( Error 0xC0012050 while loading package file "R:\Sanctions.dtsx") may have DelayValidation = True set at the package level. Set it to False.
If u need delay validation, set it at specific task level.
October 21, 2009 at 7:56 am
For the other errors see my blog:
Executing the query "audit.up_Event_Package_OnError ?, ?" failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply