SSIS SQL 2005 and SQL Agent Scheduled Job

Question

Post reply

SSIS SQL 2005 and SQL Agent Scheduled Job

Jolly Ollie

SSC Journeyman

Points: 99
More actions
January 11, 2009 at 9:04 pm

#198131

We just moved from SQL 2000 to SQL 2005. I have created 6 SSIS packages in Visual Studio 2005 that pull data from several databases, export the data to an ASCII flat file, and then FTP the file to a remote server. The packages run without a hitch in Visual Studio. I tried to put the packages into the SQL Agent scheduler and the job simply fails. I have no idea where I went wrong and some assistance would be fantastic. Maybe you can point me to a resource such as a Microsoft article that I can not find or a published article or maybe a step by step process that you use to do this.
HELP! Thanks - Bryan
Updated 1/12/2008 6:15 EST
Wow! I have been blown away by the number and quality of the responses here. I am a first time user but this will soon become my favorite place to hang out - geeze - but I must get a life.
Here is a bit more info on this very odd situation.
- SQL Data Extraction: The SQL Agent job runs, the data is extracted from the databases, the ASCII files are written to the correct folder on the SQL Server.
- FTP: Not one of the ASCII files are written to the folder on the remote server. On the remote server there is an error message "The server was unable to logon the Windows NT account 'Admin' due to the following error: Logon failure: unknown user name or bad password. The data is the error code." So what is happening is the FTP Logon ID is correct but the Password is not so the FTP dies during logon to the remote server.
When I run the job manually from Visual Studio the job runs correctly without fail. However, running the job in SQL Agent scheduled or manually the ASCII file is good but the file via FTP fails. The password is not touched in either case.
Very Very Odd! Thanks for any thoughts you have on this!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Old McDonald SSCertifiable Points: 5768 More actions · Answer 1

Could be a security issue? Can you post the errors from the log/application event log?

-Vikas Bindra

Phil Parkin SSC Guru Points: 246959 More actions · Answer 2

Sounds like security to me too. Try running the jobs in VS, but while logged in as the SQL Agent service user.

Phil

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Ashok.SQL SSC Rookie Points: 27 More actions · Answer 3

I am also sailing in the same boat..and still working on this..When I searched, I landed on few pages which may be useful for you to ponder.

http://support.microsoft.com/kb/918760

All I can say it wouldn't be as simple as creating job for DTS package, especially if your SSIS involves accessing data from network drives which requires windows authentication.

Let me know how you getting on..

Good luck

mamsay Say Hey Kid Points: 701 More actions · Answer 4

i think it is with the security issues.. the solution is to create a proxy account.. follow this link to create a proxy..

COZYROC One Orange Chip Points: 28499 More actions · Answer 5

BryanCompton (1/11/2009)
We just moved from SQL 2000 to SQL 2005. I have created 6 SSIS packages in Visual Studio 2005 that pull data from several databases, export the data to an ASCII flat file, and then FTP the file to a remote server. The packages run without a hitch in Visual Studio. I tried to put the packages into the SQL Agent scheduler and the job simply fails. I have no idea where I went wrong and some assistance would be fantastic. Maybe you can point me to a resource such as a Microsoft article that I can not find or a published article or maybe a step by step process that you use to do this.
HELP! Thanks - Bryan

What package ProtectionLevel do you use?

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/