[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


SQL DTS Package with a Where Clause or If statement

Posted on 2009-04-16
Medium Priority
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
  • 2
LVL 25

Accepted Solution

reb73 earned 1500 total points
ID: 24159669
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

ID: 24159837
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

ID: 24159910
"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
ID: 24164216
>>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?

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

873 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