Hi! I'm using Integration services to automate a process from a industrial
printing system. I've got a TAB delimited tekstfile which contains 29 columns
which is beeing updated every 15 minutes from a remote server. I have one
colomn that should be unique, "JobNumber", but it's not. JobNumber can be
found in upto 3 rows. Therefore I had to split the TAB file into 4 COMMA
delimited files, using DataFlow-->FlatfileSource-->Aggregate-->>OleDB
Destination.

So first I get the TAB file by FTP, then I make 4 COMMA delimited files with
Aggregate and eventually I put these 4 files into 4 tables. The "JobNumber"
is the Unique key in all 4 tables. So instead of having 30.000 rows, I have
now 14.000 rows which are unique.

And so for the question! Every 15 minutes the TAB file are beeing updated on
the remote server. When I run the package, the local TAB file is beeing
replaced, The 4 COMMA files are beeing replaced, but the package ends in
error when trying to write to the 4 tables. Which SQL Statement or procedure
should I use to only update/insert NEW rows into the table? And one other
ting. The column "CompletionCode" can have 2 values, 5 or 6. If the value is
5, the Job is completed/finished and values in the row will not change in the
future. If the value is 6, the job is Incomplete. Either it's aborted or it's
currently printing. So the statement should only update/insert rows which
have "completionCode" = 6.

How do I do that?

Thanks for all hints

Here's a couple of methods for getting only the new rows.
http://www.sqlis.com/default.aspx?311

Most people prefer method 2.


A conditional split transform will help you to eradicate rows where
completionCode<>6.

-Jamie


"Oystein@Norway" wrote:

And what about inserting new rows and UPDATING all the fields besides the
primary key on rows that allready exist?


"Jamie Thomson" wrote:

To update, use the OLE DB Command.

The article shows how you can insert rows.

Regards
Jamie Thomson

"Strider" wrote: