Post reply

SSIS Package SQL Agent Scheduling Fails.

  • February 26, 2008 at 9:00 am

    #154151

    My enviroment:

    DEV/TEST = XP Pro, VS 2005 and SQL 2005 (SSIS, Database)

    PROD = Wndows 2003 64bit / SQL 2005 (SSIS, Database)

    My Application:

    Uses FileWatcher, Checks file, moves file, then reads from a flat file, updates a table in SQL, moves flat file to "completed" directory and then kicks off a store procedure.

    I deploy the package into my Test SQL 2005. I use an XML configure file. I store SensitiveData with Password. Everying runs through a manual kickoff and scheduled task.... all is good on my test.

    I do the same on prod, which 64bit and I can manually run the application. BUT I cannot, for the life of me, get it to run as a scheduled task. When the scheduled task kicks off it does the following:

    Uses FileWatcher, Checks file, moves file Then stops....

    It seems that the package cannot connect to the database. I have check the connection 10+ times, but it jut stops without error.

    I thought this was my answer: http://support.microsoft.com/kb/918760

    But the package still does not work.

    4 Days and I still cannot get it to work... HELP!

    Thanks.

    teekay

  • February 27, 2008 at 6:57 am

    #782477

    Here was what I have found as a soluton to the problem I described above:

    The first problem was I need to manually edit the XML Configuration file (MyConfig.dtsConfig)to include the password used in the datasouce connection string. This tool might help if you need to do this http://agilebi.com/cs/files/folders/utilities/entry17.aspx

    Here was the main problem:

    I am deploying on a Windows 2003 64bit. I used Job execute an "Operating System (CmdExec)".

    The command line that SSIS created, but did not work was:

    C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTExec.exe /DTS

    "\MSDB\MYPACKAGE" /SERVER MYSERVER /MAXCONCURRENT

    " -1 " /CHECKPOINTING OFF /REPORTING V

    This one with (x86) did work.

    C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe /DTS

    "\MSDB\MYPACKAGE" /SERVER MYSERVER /MAXCONCURRENT

    " -1 " /CHECKPOINTING OFF /REPORTING V

    Hopefully this helps someone else out!!!!!

Viewing 2 posts - 1 through 1 (of 1 total)

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