March 15, 2016 at 10:51 am
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
August 31, 2018 at 7:40 am
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 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply