Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2002
    Location
    Boston, MA
    Posts
    5

    Question DTS - How to pass a Transformation Task SQL Query parameter from an INI file

    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'

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    Have you looked into using vbscript or javascript in an Active Script task ?

  3. #3
    Join Date
    Feb 2002
    Posts
    2,232
    Where is this ini file located - on the same machine as sql server ?

  4. #4
    Join Date
    Nov 2002
    Location
    Boston, MA
    Posts
    5
    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

  5. #5
    Join Date
    Nov 2002
    Location
    Boston, MA
    Posts
    5
    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.

  6. #6
    Join Date
    Feb 2002
    Posts
    2,232
    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.

  7. #7
    Join Date
    Dec 2002
    Posts
    1

    sample dts package file needed

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •