April 10, 2009 at 7:41 am
Hi everyone. I've been working on this problem for a few days, following suggestions online and nothing has worked for me so far.
I have an SSIS package that executes a perl script (which copies xls files from a linux box to the database server), then it loads each worksheet of each workbook into a table. We have a custom internal environment, so I can't call perl.exe directly, but must call a batch file, aba_syms.bat.
The error I am receiving is:
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.4035.00 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 9:29:00 AM
Error: 2009-04-10 09:29:01.41
Code: 0xC0029151
Source: Execute Process Task 1 Execute Process Task
Description: In Executing "D:\users\abaqus60\udev\source\proc_tls\aba_syms.bat" "&& U:\copyLinToWin.pl -host fellini -source D:\users\abaqus60\udev\source\qa -dest D:\wdir -files std.xls,xpl.xls,std_par.xls,xpl_par.xls" at "", The process exit code was "1" while the expected was "0".
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 9:29:00 AM
Finished: 9:29:01 AM
Elapsed: 1.312 seconds
I am able to run this package just fine from the database server, as well as from Visual Studio. However, when I try to run it from another client, I do get an error indicating that aba_syms is not found.
Does anyone have any suggestions as to what I should try next? I've worked on setting up proxies as well as setting the package to password protected and not encrypting it. None of these actions have helped, and the error doesn't seem to indicate a permissions error. It seems like an empty string is being read in somewhere.
I'm a total newbie when it comes to SSIS, and am fairly new to perl development so any assistance would be greatly appreciated.
Thank you.
Cathy
April 10, 2009 at 8:26 am
bat files needs to run from local copmuter.
If you are running bat files from another client , make sure that that client machine has bat files on it's local path.
April 10, 2009 at 8:31 am
Thanks rpatil22. I understand now why it failed on the client I was using. That client did not have the internal environment installed.
Would that also cause the problem with the job itself? I thought the jobs were run on the database server itself. Do you any ideas why my job is failing but the package itself will run fine from SSMS and from VS?
April 12, 2009 at 8:47 pm
If you execute an SSIS package from the BIDS designer on a workstation that has the SSIS runtime installed and running, the package will be run using the local SSIS service. Under those circumstances, any files referenced in your SSIS package will be retrieved from the local workstation, not on the server itself.
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
April 13, 2009 at 9:07 am
Thank you for reading my post Tim. It was actually your article that I used to get my package developed. Your article was incredibly helpful, but I am stuck on this one last piece.
I cannot get my SSIS to run in a job. It will only run in VS or from SSMS if I manually execute it. I won't be able to deploy my new solution if I can't automate it. I was wondering if the problem was due to our internal environment.
If I run the package manually, when the package runs the bat file, a window pops up for the command to run in. The command is then executed and the window closes. Then the remaining tasks are executed. Would this cause the job to fail? I did also try setting the Window property in the package to hidden, but that didn't seem to do anything. Can you think of anything else that I could possibly try to get the job to run?
Cathy
April 13, 2009 at 9:13 am
Do you have a proxy and credential created? and is that proxy account running the job?
April 13, 2009 at 9:18 am
I do have a proxy and credential. I have other maintenance packages that run just fine in a job. This is the first time that I am trying to perform something other than basic SQL tasks in a package though, and am not able to get the job to succeed. The error that I pasted in my original post seemed to indicate that it was having trouble parsing the command. I could definitely be wrong on that though.
The proxy and credential that I am using is the one I use for other jobs. However, I did try creating a new one and that failed as well. Always with the same error message.
April 13, 2009 at 9:23 am
Does the credential (mapped to the proxy user account) have permissions on the folder aba_syms (read permissions on networked resource?). I had a similar problem, turned out that the credential did not have enough permissions to access a directory.
April 13, 2009 at 9:24 am
Read, Write and Execute permissions in your case.
April 13, 2009 at 9:39 am
Thanks to everyone that tried to help me with this one. I did figure out what the problem was. The U drive is a user mapped drive, and is where my perl script was located. Once I moved my script to the C drive on the database server, the package ran successfully in the job.
Cathy
April 13, 2009 at 9:41 am
Bingo !!
🙂
April 13, 2009 at 10:25 am
Thanks Vishal! I didn't end up seeing your reply until just now, with your recommendation. I hadn't refreshed my screen and our mail server has been acting funky all day.
I figured it out when I tried to add U as the work directory in the execute task. When I did that, I received an error indicating that U did not exist. That's when the light bulb came on and I realized that U means something to me as a user, but not to my agent.
Cathy
April 13, 2009 at 10:31 am
I've seen this issue pop up before on mapped drives. They are deceptively easy to use but almost always cause problems later.
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
April 13, 2009 at 10:40 am
What is the return code being returned from your perl script? If it is not a 0 for success, which is what SQL Server Agent expects, it is going to cause the agent job to report an error even when the job is successful.
Review the script and find out what status codes are being returned from your script.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
April 13, 2009 at 10:42 am
Jeffrey,
My script doesn't return anything. I suppose I should have set it up to return a code and should put some error handling in place there, so that is a great suggestion. The problem was not with the script however. The package ran just fine from VS and from SSMS. It was only problematic when I was trying to run it from a job. This was because I was using a mapped drive. Once I moved my script off of the mapped drive and onto a hard drive, the job ran perfectly.
Cathy
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply