Downloading files using SSIS
-
The client I'm working with won't do anything that isn't SFTP, so I had to figure out a way to run a script.
I have a simple script I'm running to start an instance of WinSCP, using a stored session I've already created. The script is really simple:
cd Scorecard
option transfer binary
get *.* e:\SSISImports\
mv *.txt ../ScoreCardBackup/*
close
exit
it just grabs everything in the scorecard directory and downloads it to the e:\ssisimports drive and then moves it to a directory called ScoreCardBackup. Works fine in BI Studio (VS2005). I save copy as and put it on my SSIS machine. I open up SSMS and look at the Integration Services under MSDB and the package is there. I run the package from there and it's fine.
I log into SSMS into the db and set up a job to be run as the sql server agent. I run it and get:
Executed as user: SSIS01\SYSTEM. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 2:18:35 PM Error: 2009-04-17 14:18:38.23 Code: 0xC0029151 Source: WinSCP Execute Process Task Description: In Executing "C:\Program Files\WinSCP\WinSCP.com" "EHealth/console /script=c:\winSCPScript.txt" at "", The process exit code was "1" while the expected was "0". End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 2:18:35 PM Finished: 2:18:38 PM Elapsed: 2.969 seconds. The package execution failed. The step failed.
I don't understand why it won't run.
-
Permissions?
Running through BI dev studio it'll be running under your credentials. Running as a SQL job it will be running either as the SQL Server service account or the SQL Agent service account.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
GilaMonster (4/17/2009)
Permissions?Running through BI dev studio it'll be running under your credentials. Running as a SQL job it will be running either as the SQL Server service account or the SQL Agent service account.
I had a feeling, but how do I check/set the permissions for the SQL Server service or Agent accounts? I'm in the db in security and I don't see the accounts. Unfortunately, I'm the dba/developer/portal admin/15 other IT related jobs for this company, so there's no one I can ask.
-
Wrong place.
I'm talking about the windows accounts that the two services run as. First you need to find out what they are, check the services app from Administrative tools. If they are domain accounts, you'll have to check with the domain administrator or look in Active Directory. Also check the permissions on the directory where the app is, the directory where the script is, the directory where it'll be writing files to, etc.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
GilaMonster (4/17/2009)
Wrong place.I'm talking about the windows accounts that the two services run as. First you need to find out what they are, check the services app from Administrative tools. If they are domain accounts, you'll have to check with the domain administrator or look in Active Directory. Also check the permissions on the directory where the app is, the directory where the script is, the directory where it'll be writing files to, etc.
What kind of permission do they need? If I look at SQL Server Integration Services, under Log On, it has NT Authority\NetworkService
SQL Server Agent has Local System account, which seems a bit funky.
I think this is the root of the problem because I have the same issue when I try running another SSIS package I wrote. That one blows up for a totally different reason (Mostly because Progress DB stinks :))
Thanks!
//edit - is this what I need? http://msdn.microsoft.com/en-us/library/ms178142(SQL.90).aspx Wondering if I need to set the SQL Server Agent to the NT Authority\NetworkService account.
-
Yep, you need to change Local System Account to something which has the appropriate access - a domain user.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
The error message is telling you what the problem is:
The process exit code was "1" while the expected was "0". End Error
Most likely, the utility/script you are using is returning a 1 instead of a 0. SQL Server Agent Jobs expect a 0 return value to indicate a success.
If I read this correctly, your script is starting an SFTP session and using stored credentials to connect to the FTP site (not Windows Authentication - which would be unusual). Have you verified whether or not the files are actually getting to the local directory?
The permissions on the services appear to be correct - and shouldn't need to be changed. I would recommend reviewing and setting up a service line account (domain user) to run both SQL Server and the Agent, but in this case it really shouldn't be necessary.
Back to the script you are running - review the script and see if you can force a specific return value. If not, then you need to modify your SSIS package to accept the failure and continue.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Jeffrey Williams (4/17/2009)
The error message is telling you what the problem is:...
The permissions on the services appear to be correct - and shouldn't need to be changed. I would recommend reviewing and setting up a service line account (domain user) to run both SQL Server and the Agent, but in this case it really shouldn't be necessary.
...
Shouldn't be necessary? If the e: drive is on the network and SQL Agent runs under Local System, how can the access possibly work?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
Phil Parkin (4/17/2009)
Jeffrey Williams (4/17/2009)
The error message is telling you what the problem is:...
The permissions on the services appear to be correct - and shouldn't need to be changed. I would recommend reviewing and setting up a service line account (domain user) to run both SQL Server and the Agent, but in this case it really shouldn't be necessary.
...
Shouldn't be necessary? If the e: drive is on the network and SQL Agent runs under Local System, how can the access possibly work?
Because he is not accessing the E: drive on that server. If he was accessing an E: drive using the drive letter, that drive would be local anyways and would not require network credentials to access it either.
He is accessing the E: drive on an FTP site using SFTP, which does not require SQL Server or the Agent to be running under a particular account.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
I changed the account over to my domain account and everything worked fine. We tried creating a new domain service account (only used for this stuff) but it got to be too late before everything populated on AD and all that. Going to give it a shot on Monday.
-
Matthew Cushing (4/17/2009)
The client I'm working with won't do anything that isn't SFTP, so I had to figure out a way to run a script.I have a simple script I'm running to start an instance of WinSCP, using a stored session I've already created. The script is really simple:
cd Scorecard
option transfer binary
get *.* e:\SSISImports
mv *.txt ../ScoreCardBackup/*
close
exit
it just grabs everything in the scorecard directory and downloads it to the e:\ssisimports drive and then moves it to a directory called ScoreCardBackup. Works fine in BI Studio (VS2005). I save copy as and put it on my SSIS machine. I open up SSMS and look at the Integration Services under MSDB and the package is there. I run the package from there and it's fine.
I log into SSMS into the db and set up a job to be run as the sql server agent. I run it and get:
Executed as user: SSIS01\SYSTEM. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 2:18:35 PM Error: 2009-04-17 14:18:38.23 Code: 0xC0029151 Source: WinSCP Execute Process Task Description: In Executing "C:\Program Files\WinSCP\WinSCP.com" "EHealth/console /script=c:\winSCPScript.txt" at "", The process exit code was "1" while the expected was "0". End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 2:18:35 PM Finished: 2:18:38 PM Elapsed: 2.969 seconds. The package execution failed. The step failed.
I don't understand why it won't run.
Check SFTP Task for third-party commercial solution, without the hassle.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
-
I would love to. Expenses and budgets are close to nada right now. I'll keep the link.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply