Post reply

SSIS SQL 2005 and SQL Agent Scheduled Job

  • 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!

  • January 11, 2009 at 10:47 pm

    #925107

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

    -Vikas Bindra

  • January 12, 2009 at 3:12 am

    #925150

    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

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • January 12, 2009 at 3:28 am

    #925159

    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

  • January 12, 2009 at 5:53 am

    #925203

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

  • January 12, 2009 at 10:25 am

    #925399

    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/

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

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