My package works like a treat in SSIS ( BIDS ) and executes in 4 minutes . Logging onto integrations services in SQL Server management studio and importing it into file system or stored packages and then running it, again takes 6 minutes .
Now moving it to SQL Server Agent . It runs and runs .... Looks like when it comes to a few tasks that mean running a bat file to open an excel file then a vbs file to run a few macros it seems it cannot cope with it . I worked this out using sql server profiler . Killed the job after 40 mins and no meaningful errors . Even looked at the Windows NT logs !
I need to get this package working on sql server agent so it is automated and I can schedule it to run a few times every day .
Can anyone help me resolve this as I am stuck ?
SQL Server User / Credentials / proxy Account has been set up correctly and earlier in the control flow it does run another bat file which copies across a file to another directory .
