View Full Version : DTS - How to pass a Transformation Task SQL Query parameter from an INI file
alfred_wheeler
11-05-02, 14:17
| Data Transformation Services (DTS) Question -
How can I pass a parameter to a
Transformation Task SQL Query
in a DTS Package from an INI file?
Presumably I would use a Global Variable.
My DB connection is through
Microsoft OLE DB Provider for Oracle and
I am executing dtsrun with a command line
reference to an INI file full of goodies
(name/value pairs) from a batch file, e.g.
<Transform task:SQL Query>
SELECT 1 AS my_column_name FROM DUAL WHERE 'true' = ?
<INI file>
[Query Parameters Section]
my_parameter='false' |
Have you looked into using vbscript or javascript in an Active Script task ?
| Where is this ini file located - on the same machine as sql server ? |
alfred_wheeler
11-06-02, 11:44
Originally posted by rnealejr
Where is this ini file located - on the same machine as sql server ?
Yes.
Additionally, it is on the same path as the DTS package.
The location of my INI file is not a problem, because the parameter(s) does make an appearance when the Dynamic Properties Step is executed.
I create the INI file when I run the batch script. Here is my overly complex batch script -
REM --------------------------------------------------
REM PROGRAM: it.bat
REM --------------------------------------------------
REM --------------------------------------------------
REM Define the drive letter and path to use
REM --------------------------------------------------
SET client_path=\Client_Path
SET package_drive=DriveLetterGoesHere:
SET package_path=\Package_Path\DTS_Packages%client_pat h%
SET log_drive=DriveLetterGoesHere:
SET log_path=\Log_Path%client_path%
SET data_drive=DriveLetterGoesHere:
SET data_path=\Data_Path%client_path%
%package_drive%
CD %package_path%
REM --------------------------------------------------
REM Parameter Usage
REM ---
REM p1 = parameter 1
REM
REM pn = DTS_Package_name
REM pp = package_path
REM sn = service_name ( or SID )
REM un = user_name
REM up = user_password
REM
REM su = set up flag
REM --------------------------------------------------
REM --------------------------------------------------
REM Specify the DTS Package
REM --------------------------------------------------
SET pn=It
SET pp=%package_drive%%package_path%\%pn%.dts
REM --------------------------------------------------
REM Get parameter values from command line
REM --------------------------------------------------
SET p1=%1
SET su=%2
REM --------------------------------------------------
REM Set connection parameters
REM --------------------------------------------------
SET sn=service_name
SET un=user_name
SET up=top_secret_password
REM --------------------------------------------------
REM Create INI File to contain DTS Package parameters
REM --------------------------------------------------
ECHO.>%package_drive%%package_path%\%pn%.ini
ECHO [Data Filter Section] >>%package_drive%%package_path%\%pn%.ini
ECHO dfs_p1=%p1% >>%package_drive%%package_path%\%pn%.ini
ECHO.>>%package_drive%%package_path%\%pn%.ini
ECHO [File Section] >>%package_drive%%package_path%\%pn%.ini
ECHO log_file_path_name=%log_drive%%log_path%\%pn%.log >>%package_drive%%package_path%\%pn%.ini
ECHO.>>%package_drive%%package_path%\%pn%.ini
ECHO [DB Connection Section] >>%package_drive%%package_path%\%pn%.ini
ECHO service_name=%sn% >>%package_drive%%package_path%\%pn%.ini
ECHO user_name=%un% >>%package_drive%%package_path%\%pn%.ini
ECHO user_password=%up% >>%package_drive%%package_path%\%pn%.ini
ECHO.>>%package_drive%%package_path%\%pn%.ini
REM --------------------------------------------------
REM Execute the DTS Package with parameters
REM --------------------------------------------------
IF "X%su%" == "XINI" GOTO CONTINUE
DTSRUN /F %pp% /N %pn% /A cl_p1:8=%p1%
:CONTINUE
alfred_wheeler
11-06-02, 11:57
Originally posted by rnealejr
Have you looked into using vbscript or javascript in an Active Script task ?
Using VBScript within the DTS Package IS on my list of alternatives.
If left to my own choice, I would prefer to write a tiny Java application. Oh well... My requirement is to implement the task using DTS without any scripting as you mentioned, IF POSSIBLE, to reasonably minimize the amount of custom code.
I am leaning toward vbscript - you can use the fso object or the getprivateprofilestring win32 api function.
I have been out of town, but I will check it out when I get back and send you a follow up.
dstoner123
12-15-02, 18:34
Hi:
Would you be so kind as to email me a sample DTS package which contains the global variables that match what is established in batch script below?
Thanks so much
dave stoner
Originally posted by alfred_wheeler
Yes.
Additionally, it is on the same path as the DTS package.
The location of my INI file is not a problem, because the parameter(s) does make an appearance when the Dynamic Properties Step is executed.
I create the INI file when I run the batch script. Here is my overly complex batch script -
REM --------------------------------------------------
REM PROGRAM: it.bat
REM --------------------------------------------------
REM --------------------------------------------------
REM Define the drive letter and path to use
REM --------------------------------------------------
SET client_path=\Client_Path
SET package_drive=DriveLetterGoesHere:
SET package_path=\Package_Path\DTS_Packages%client_pat h%
SET log_drive=DriveLetterGoesHere:
SET log_path=\Log_Path%client_path%
SET data_drive=DriveLetterGoesHere:
SET data_path=\Data_Path%client_path%
%package_drive%
CD %package_path%
REM --------------------------------------------------
REM Parameter Usage
REM ---
REM p1 = parameter 1
REM
REM pn = DTS_Package_name
REM pp = package_path
REM sn = service_name ( or SID )
REM un = user_name
REM up = user_password
REM
REM su = set up flag
REM --------------------------------------------------
REM --------------------------------------------------
REM Specify the DTS Package
REM --------------------------------------------------
SET pn=It
SET pp=%package_drive%%package_path%\%pn%.dts
REM --------------------------------------------------
REM Get parameter values from command line
REM --------------------------------------------------
SET p1=%1
SET su=%2
REM --------------------------------------------------
REM Set connection parameters
REM --------------------------------------------------
SET sn=service_name
SET un=user_name
SET up=top_secret_password
REM --------------------------------------------------
REM Create INI File to contain DTS Package parameters
REM --------------------------------------------------
ECHO.>%package_drive%%package_path%\%pn%.ini
ECHO [Data Filter Section] >>%package_drive%%package_path%\%pn%.ini
ECHO dfs_p1=%p1% >>%package_drive%%package_path%\%pn%.ini
ECHO.>>%package_drive%%package_path%\%pn%.ini
ECHO [File Section] >>%package_drive%%package_path%\%pn%.ini
ECHO log_file_path_name=%log_drive%%log_path%\%pn%.log >>%package_drive%%package_path%\%pn%.ini
ECHO.>>%package_drive%%package_path%\%pn%.ini
ECHO [DB Connection Section] >>%package_drive%%package_path%\%pn%.ini
ECHO service_name=%sn% >>%package_drive%%package_path%\%pn%.ini
ECHO user_name=%un% >>%package_drive%%package_path%\%pn%.ini
ECHO user_password=%up% >>%package_drive%%package_path%\%pn%.ini
ECHO.>>%package_drive%%package_path%\%pn%.ini
REM --------------------------------------------------
REM Execute the DTS Package with parameters
REM --------------------------------------------------
IF "X%su%" == "XINI" GOTO CONTINUE
DTSRUN /F %pp% /N %pn% /A cl_p1:8=%p1%
:CONTINUE
vBulletin v3.5.3, Copyright ©2000-2008, Jelsoft Enterprises Ltd.