I am trying to update existing records or add any new ones to a SQL Server table using a flat file. Also I need it work as an SSIS package. I don't get an option to do a source query like I do for Excel, etc. when setting up the import task and even when I was trying to use Excel my options seemed limited to simple selects for the source query.
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. Can someone point me in the right direction? 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. We then have the customer import from those files into table representations of them so they can report against them.
Best Answer
You can follow either of the below approach :
Refer to this excellent series : Stairway to Integration Services for more details.