January 13, 2015 at 9:50 am
Hi Jeff (and MWise),
Things are coming to an end on this, though possibly not as efficient as they could be. I did a couple of tasks in SSIS - Download the FTP file
Rename the file (instead of working around a variable name - no sense in doing that when I can just rename the file to a static name the rest of the process can work with).
And I'm working on a procedure that'll systematically do the following (All I'm sure of which are tasks that could be completed in SSIS)
Bulk insert the CSV contents into a table
Delete garbage data
Insert the new data into a master table using the following method:
INSERT INTO MasterTable
select * from FTPData
except
select * from MasterTable
I'm not sure if this method is less ideal than a merge, but after some dev testing it seems to work fine, and it's expedient - and there'll only be around 4,000 rows of data to add to the master table on a daily basis - but this task will be running hourly.
After this the downloaded ftp file is deleted and the FTPData is emptied.
January 15, 2015 at 5:06 pm
I have not seen a mention of the Data Flow task. That is SSIS's bread and butter item for moving data around. Since you mention about 4000 rows per batch it should not cause any performance considerations and is easy to manage. Also I notice also the EXCEPT operator in your query. As this inserts new rows into the destination table, are you not expecting any updates to come down the pipeline to the data already in the master table ?
----------------------------------------------------
January 15, 2015 at 5:17 pm
Yes, that's correct. The data in the master table never gets updated, just new rows added on an hourly basis.
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply