How to execute an SSIS package from the command line or a batch file

  • MWise

    SSCarpal Tunnel

    Points: 4074

    We use environmental variables which point to a standardized package configuration db and table on each of our servers. Then all of our SQL connections are in the package configuration table and use windows authentication. In production, our SSIS packages from a standard file location in SQL jobs and the SQL agent runs under a process id that has access to the necessary objects. All of our packages are also set to "Don't Save Sensitive". If we have to run a production package outside of a job, the production support person makes sure their environmental variable is pointing to the correct server and they run the SSIS package via the GUI package utility.

    MWise

  • Raymond Browning

    SSC Rookie

    Points: 43

    Good morning,
    I am a programer by trade and i hate to expose raw passwords in any text document. I attached a small text file that you will need to rename as a bat file. (The file includes comments.)The batch file will allow the user to enter the password when they want to run the report. 
    You will need to run it from the command line.
    maketest MyPassword

    I assume that most of you will need to write to an XML file and batch files do not like some special characters like < or >. I added code to generate two lines of XML with special characters to show you how to use the special escape character in a batch file. 

    @ECHO OFF
    REM Exit if they didn't enter a password
    IF [%1]==[] Goto BadPassword

    REM Write the XML header and comment line.
    ECHO ^<?xml version="1.0" encoding="UTF-8"?^> >MyTest.dtsconfig
    ECHO ^<!-- Generated dtsconfig file from %0 --^> >>MyTest.dtsconfig

    ECHO User=Test >>MyTest.dtsconfig
    ECHO Password=%1 >>MyTest.dtsconfig

    ECHO Test file created with a password

    Notepad MyTest.dtsconfig

    REM "C:\Program Files\Microsoft SQL Server\110\DTS\Binn\DTEXEC.exe" /File "C:\SSIS\TestPackage.dtxs" /Conf "MyTest.dtsconfig"
    GOTO Done

    :BadPassword
    ECHO ************* Bad Password ****************

    :Done
    Pause

Viewing 2 posts - 16 through 17 (of 17 total)

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