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 taskQL 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 ?

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

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.

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