I have a Table ( BHYearHeaders) which i need to update on a regular basis from a csv file

the BHYearHeader stores header records of transactions from our client and the csv file updates the header records with a voloume number

Table
Licence varchar(6)
Serial varchar(6)
Volume varchar(6)
Trans Int
TransVal Varchar(11)

File

Licence, serial, volume, trans, transvalue

can anybody suggest ( in simple terms ) what is the best way to create a dts so that when it is run the BHYearHeader.BureauVolume is updated with the File.Volume when
BHYearHeader.Licence = File.Licence
&
BHYearHeader.Serial = File.Serial
&
BHYearHeader.Trans = File.trans
&
BHYearHeader.TransVal = File.transval


I was looking at using a textfile DataSource, but couldnt work out how to update the correct records as there are usually over 10,000 recorsd in the csv file

You could use a Data Driven Query task, and two sources, 1 text and 1 SQL.
The DDQ update query would look something like this-
UPDATE BHYearHeaders
SET BureauVolume = ?
WHERE Licence = ?
AND Serial = ?
AND Trans = ?
AND TransVal = ?

The question marks are placeholders for the parameters passed to the
"source" in the script transform.

Have a good read of the Books Online topics surrounding the DDQ as this is a
very short and incomplete description of what is required.


--
Darren Green
http://www.sqldts.com

"Peter Newman" <anonymous (AT) discussions (DOT) microsoft.com> wrote

File.Volume when
csv file

Darren

I'm not getting very far with this as i dont understand the machanics.

is there any info giving a step by step example ?