Thursday, June 10, 2010

Executing dtsx on Microsoft SQL 2005 Express with schedule

Let say you need to run scheduled DTS package on Microsoft SQL 2005 Express and
as you probably know there is no SSIS and Agent Services in Microsoft SQL 2005 Express
edition but you can do this job actually very easy :

   Assure that you do have the DTExec util, it should be in
   C:\Program Files\Microsoft SQL Server\90\DTS\Binn folder ,
   if you do not have one means you should install SQL Toolkit .

   Next step is to make a batch file to run the DTS package.
   Create batch file with DTExec formatted command for example
             Set upPath=[Some folder path where you have your package or whatever ]
             Set logFileName=DTS_Batch_Log

             cd "C:\Program Files\Microsoft SQL Server\90\DTS\Binn"

             set startDate=%date%
             set startTime=%time%

             set sdy=%startDate:~10,4%
             set sdm=%startDate:~4,2%
             set sdd=%startDate:~7,2%
             set sth=%startTime:~0,2%
             set stm=%startTime:~3,2%
             set sts=%startTime:~6,2% 0

             DTExec.exe /F "%upPath%\DTS\[MyDTSName].dtsx"
             /SET "\Package.Variables[My DTS Variable name].Value";
             "[Here is comes the My DTS Variable  value]"
              >> "%upPath%\%logFileName%_%sdy%.%sdm%.%sdd%-%sth%.%stm%.log"
             IF %ERRORLEVEL% == 0 GOTO [Some label ] ELSE GOTO End
             : Some label
                   your code comes here
             : End
   The example above shows how to run DTSX Package from File and Log
   the results of execution to some log file.

   Last step is schedule and Yes there is one in Windows it called " Scheduled
   Tasks" you could find it in System Tools – just create one with call of the batch
   that you created earlier to run dtsx packages.

And that's all you need for the job to be done ;)...

No comments:

Post a Comment