none
how to run a ssis package using .bat file

    Question

  • hello,

            I have created a ssis package and it was running successfully when I am trying to run it from BIDS or from command line utility.

    when I am trying to create a batch file and run the package using this batch file, command prompt showing me an error that batch file was not found. Can any one please tell me how to create a batch file and how to run the ssis package using batch file

     

    thanks

    Sasi. 

    Friday, December 31, 2010 8:49 PM

Answers

  • Hi Sasi,

    The code in the batch file should be like:

    "C:\Program Files\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" /f "D:\Package10.dtsx"

    Please also make sure that the folder contains the batch file have a path without space within it. For example, if the full path of the batch file is c:\folder name\batchCommand.bat, and if we run it from a command window with command c:\folder name\batchCommand.bat directly, we will get the error:

    'c:\folder' is not recognized as an internal or external command,operable program or batch file.

    Thanks,
    Jin Chen


    Jin Chen - MSFT
    Monday, January 3, 2011 2:26 AM
    Moderator
  • Hi Sasi,

    Please find a sample batch file to run a package. Folder names have to be replaced.

    D:
    CD D:\FolderWhereThePackageisStored

     

    @ECHO OFF
    REM - LABEL INDICATING THE BEGINNING OF THE DOCUMENT.
    :BEGIN
    CLS
    SET ERRORLEVEL=0

    SET ErrorFilepath=ErrorLog\
    SET sysdate=none
    SET systime=none

     
    FOR /F "tokens=2-4 delims=/ " %%i in ('date /t') do set sysdate=%%i%%j%%k
    FOR /F "tokens=5-8 delims=:. " %%i in ('echo.^| time ^| find "current" ') do set systime=%%i%%j%%k%%l
    SET outputfile=%ErrorFilepath%%Jobname%_%sysdate%%systime%.log


    REM * STEP 1 *
    "C:\Program Files\Microsoft SQL Server\100\DTS\Binn\dtexec.exe" /F "d:\FolderWhereThePackageisStored\mypackage.dtsx" /Connection "my_Data_src;%Environment_variable%" /REPORTING E  >> %outputfile%

    IF ERRORLEVEL 1 GOTO END

     


    EXIT /B 0
    @ECHO ON


    :END

    REM EXIT /B %ERRORLEVEL%
    REM @ECHO ON
    EXIT /B 1


    Regards

    Suresh M. Menon

     

    Monday, January 3, 2011 2:51 AM

All replies

  • What is text in your batch file?  Do you have the right path for the dtexec.exe program?
    Russel Loski, MCT
    Friday, December 31, 2010 9:28 PM
  • Like Russ mentioned, please post the code of the batch file.



    Pradeep Adiga
    Blog: sqldbadiaries.com

    Recent posts on my blog
    • Proposed as answer by a92Anonymous92 Wednesday, June 25, 2014 5:40 PM
    Saturday, January 1, 2011 5:39 PM
  • Hi Sasi,

     

    Please check the paths in the batch file and make sure that the paths are all enclosed in double quotes ("") which will take care of any blank spaces in the path like folder names, file names etc.

    Take a look at the following microsoft knowledge base article: http://technet.microsoft.com/en-us/library/ms162810.aspx

    Also, if you could provide the code, community can get more clarity on the problem you are facing and can provide a quick solution/suggestion.

     

    - Datta


    Dattatrey Sindol
    My BlogDatta's Ramblings on Business Intelligence 'N' Life
    The information provided here is "AS IS" with no warranties, and confers no rights.
    Please mark the post as answered if it solves your problem.
    Sunday, January 2, 2011 12:24 PM
  • Hi Sasi,

    The code in the batch file should be like:

    "C:\Program Files\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" /f "D:\Package10.dtsx"

    Please also make sure that the folder contains the batch file have a path without space within it. For example, if the full path of the batch file is c:\folder name\batchCommand.bat, and if we run it from a command window with command c:\folder name\batchCommand.bat directly, we will get the error:

    'c:\folder' is not recognized as an internal or external command,operable program or batch file.

    Thanks,
    Jin Chen


    Jin Chen - MSFT
    Monday, January 3, 2011 2:26 AM
    Moderator
  • Hi Sasi,

    Please find a sample batch file to run a package. Folder names have to be replaced.

    D:
    CD D:\FolderWhereThePackageisStored

     

    @ECHO OFF
    REM - LABEL INDICATING THE BEGINNING OF THE DOCUMENT.
    :BEGIN
    CLS
    SET ERRORLEVEL=0

    SET ErrorFilepath=ErrorLog\
    SET sysdate=none
    SET systime=none

     
    FOR /F "tokens=2-4 delims=/ " %%i in ('date /t') do set sysdate=%%i%%j%%k
    FOR /F "tokens=5-8 delims=:. " %%i in ('echo.^| time ^| find "current" ') do set systime=%%i%%j%%k%%l
    SET outputfile=%ErrorFilepath%%Jobname%_%sysdate%%systime%.log


    REM * STEP 1 *
    "C:\Program Files\Microsoft SQL Server\100\DTS\Binn\dtexec.exe" /F "d:\FolderWhereThePackageisStored\mypackage.dtsx" /Connection "my_Data_src;%Environment_variable%" /REPORTING E  >> %outputfile%

    IF ERRORLEVEL 1 GOTO END

     


    EXIT /B 0
    @ECHO ON


    :END

    REM EXIT /B %ERRORLEVEL%
    REM @ECHO ON
    EXIT /B 1


    Regards

    Suresh M. Menon

     

    Monday, January 3, 2011 2:51 AM