DTS - bring in only rows that meet a condition

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

DTS - bring in only rows that meet a condition

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-04-21 : 08:09:16

Roger writes "I have the follwoing code that get the data I need and puts it into my table. The problem is it also brings in thousands blank rows. The source data contains many thousand transactions for a 12 hour shift. I only want to import parts of the rows that contain the words LATE or MISSED. The source file (transaction data) contains 1 string, many thousands of them. My DTS should only find about 500 rows, not all of them.
Please Help. I have searched all over for answers. I am pretty new to this.

' Pick the Transaction string apart grabbing all the significant LATE and MISSED events.
Function Main()

Dim strUnit
Dim strEType
Dim IntMins

If InStr(1, DTSSource("Col001"), "LATE" ) Then
strUnit = Mid(DTSSource("Col001"),11,4)
strEType = 1 'Late Event
IntMins = Mid(DTSSource("Col001"),18,5)
DTSDestination("Unit") = strUnit
DTSDestination("EType") = strEType
DTSDestination("Mins")= IntMins
End If

If InStr(1, DTSSource("Col001"), "missed Call Point") Then
strUnit = Mid(DTSSource("Col001"),10,4)
strEType = 2 'Missed Call Point
DTSDestination("Unit") = strUnit
DTSDestination("EType") = strEType
End If

Main = DTSTransformStat_OK

End Function
Windows 2000 SP3, SQL Server 2000 Standard SP2"

Tim
Starting Member

392 Posts

Posted - 2003-04-21 : 10:02:42

It looks like you are grabbing records from a table, then using the activeX code to do the comparison, after the record is fetched. That's why you are processing os many.

I think you will have more luck if you just use a query as your source. This will extract the records you want rather than modifying properties with activeX.

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-21 : 10:10:02

We messed aroud with ActiveX in DTS, and gave up on it. Create a temp table, then use a stored procedure to perform your logic. I think it will be much faster and keep your functions simpler. It would also be easier to troubleshoot.

You might also want to consider not using DTS at all.

MOO

Brett

8-)

crash
Starting Member

1 Post

Posted - 2003-04-22 : 00:26:19

The source file lives on a Unix Server. It is just a flat text file. One string, with about 18000 new rows every 12 hours. I was hoping to filter this at the source somehow but what I have done is bring the data into a temp table. I query the temp table to get rid of the blank records and insrt them into my permanent table. I have modified my DTS package to do all this and it seems to be working well for me.

Roger

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-04-22 : 10:22:09

Two things:

BCP should have no problem with bring that into a staging table and then you can do as X said use T-SQL to filter out only what you need and insert it into the destination tables.

Also you can try scheduling a job and using PERL to get the file ready for you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources