I really need to be able to run a full script that checks the first column (which is my key), updates existing records, then adds any new ones.
Maybe I am taking the wrong approach here altogether, but I thought there was a way to do this with SSIS. If it were two tables and I didn't have to use SSIS I would just write the sql and be done with it.
It's really the bridge with using flat files and SSIS (automating the import) that I'm looking for here.
Just to give a quick background of what I ultimately am trying to accomplish: We do exports of our data into flat files and those will go to a ftp server.
So the queue table changes to be: Queue ID BIGINT NOT NULL IDENTITY(1, 1) PRIMARY KEY, My Table Field1 INT NOT NULL, My Table Field2 INT NOT NULL, ...
My Table Field N INT NOT NULL, Create Date DATETIME NOT NULL DEFAULT (GETDATE()) -- or GETUTCDATE() This queue table method isolates the source table (and its processing) from the audit/ETL process (much like CDC does).
You might have to do this in a couple of places if the data flow splits off (like mine does in the screen shot).Depending on what else changed, you may also need to 'click through' some of the pages of some transformations to update metadata (ex: a data type change), but the Resolve References functionality is a huge time-saver.My Blue Granite colleague and friend Meagan Longoria has been convincing me recently that these are the kind of changes that BIML is well-equipped to take care of quickly & efficiently when it regenerates a package.I would redirect any Matched rows to effectively discard them, then continue with just the unmatched rows.Note Caches are stored in memory so try to keep the content lean e.g. As long as you dont exhaust Physical Memory, the runtime performance will be very fast (no I/O).I am trying to update existing records or add any new ones to a SQL Server table using a flat file. I don't get an option to do a source query like I do for Excel, etc.