SQL DTS Package with a Where Clause or If statement

Posted on 2009-04-16
Last Modified: 2013-11-30
Hello, I have a SQL 2000 table with several columns that will need to get their data from several text files.  These text files are fixed column witdh text.  I'm trying to use DTS packages to accomplish this.  

When I import the data from the first text file, I insert the record ID and the first 2 values for the record.

My problem arises when I need to import the subsequent values so the records can be complete.  In my subsequent DTS package I need to create a where clause or an if statement in the transformation screen so the third value goes to their matching record (all text files have the record ID so I can use this as my clause) and then I need to import the 4th value to their respective column for the same record id and so on.

I am using a DTS package for each value import.  

This is what I'm trying to do in the transformation screen:
Function Main()

	DTSDestination("ORB2") = DTSSource("Col002")

	WHERE DTSDestination("RecordID")  = DTSSource("Col005")  <-- this is my where clause that doesn't work!!!

	Main = DTSTransformStat_OK


End Function

Open in new window

Question by:TheUndecider
    LVL 25

    Accepted Solution

    Its probably easier to import all your files into separate staging tables with structures mirroring the input text files and then use a DTS Lookup Transformation to populate records in the target table using RecordId lookups..

    Say your target table has six columns and you have three files with the following structure, then a possible solution would be as follows -

    File 1  - RecordId, Col1, Col2, Col3
    File 2  - RecordId, Col4, Col5
    File 3  - RecordId, Col6

    1. Create staging table for File 2 and import the entire contents of file 2 into this staging table
    2. Create staging table for File 3 and import the entire contents of file 3 into this staging table
    (Note: Step 1 and 2 can be executed in parallel)
    3. Import from File1 directly into target table and use the DTS Lookup Transformation option to read in the values for Col4, Col5, Col6 from staging tables.

    See link below for tips on using lookups -


    Author Comment

    Hello Reb, this is what our old Access database used to do to accomplish this task.  We had staging tables where we copied all the data from the files and then we built our records from them into the main table.  I'm trying to see if I can skip this step and use the main table for all the imports.  
    LVL 25

    Expert Comment

    "this is what our old Access database used to do to accomplish this task"

    This would be the easiest and probably the quickest way to bring in the data. I would recommend using SQL Server set based logic over ActiveX transformation where possible as this is quicker and will also be pretty robust in the long run..
    LVL 75

    Expert Comment

    by:Anthony Perkins
    >>SQL DTS Package with a Where Clause or If statement<<
    And the big question, why are you using what looks like T-SQL syntax in an ActiveX Script Task.  You do realize the syntax has to be either VBScript or JScript, right?

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now