sql server dts: Hi,

Can anyone help me?

I have a DTS package, which basically consists of a data pump task. The
source for this is a CSV file with various fields, and the destination
is a table in a database (let's call it "Database A")

Now, I've designed this sort of package before and have no problem with
most of this (i.e. the straight Copy Column or Date Time Conversion
types of transformation)...where I'm getting stuck though, is that a
couple of the fields in the csv file require me to do a LookUp style
conversion before passing the resulting value into the destination
fields.

Now, the tables where the values for the lookup would be found, also
exist in Database A, so are readily available...I simply don't know how
to implement this (i.e. do I have to select 'ActiveX Script' in the New
Transformation window and somehow put the SQL into VBScript? I've had a
look at the Lookups tab, but when I try to select the csv file as the
source for that, it doesn't seem to like it ("The selected connection
does not provide SQL support").

Can anyone help?

Ah, I've just figured that I do need to use the Lookup and use the db
as the source (pretty obvious really)...all I need to know now, is how
to specify the csv field in the WHERE clause...?

Hello mj.redfox.mj@gmail.com,


You could actually do this a number of ways

1. Merge Join and then filter out the NULLs using a conditional split transform
2. Use the Lookup transform. This will have the DB table as the reference
table. You can then match the inputs to the lookup columns. The CSV will
not feature in the reference table but will be the input.

Make sense?


Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com

[quoted text, click to view]

Hello Allan,

Thanks for replying. I'd ideally like to use the Lookup transform,
however the only thing I don't know how to do is specify the CSV file
in the code. i.e. when modifying the lookup query, I don't know how to
specify a field in the CSV file - I've tried using something like:

SELECT (field1) from (table1) where (field2) =
<CSVFILENAME>.<CSVCOLUMNNAME>

but it just tells me there's no table in the query with that name. I
really have tried looking absolutely everywhere for an answer to this
but can't seem to find it. I'll keep looking though - thanks again!

Ah, just found it on BOL - I leave a question mark in place of the
input.