November 5, 2015 at 2:16 pm
In my continuing work to automate some data loads (I'm learning SSIS as I go), I want to be able to use the incremental load design pattern that I learned about in the Stairway to SSIS series by Andy Leonard on this very site. I'm speaking specifically of levels 3-5, in which the data sources are always SQL Server tables. I've googled quite a bit and search on this forum but I cannot find an answer. If it can't be done, is there an alternate way to make the load fault tolerant so that if it's run more than once, the data won't load. If there is a way, how?
I was excited that I could follow the steps in levels 3 through 5 until I realized that I have .csvs and a couple of Excel files coming in. I have set up the foreach loop containers, etc. thanks to help generously given by Phil Parkin, so everything loads well but, of course, duplicate rows can easily be added by running again.
Thanks in advance for reading and help.
November 6, 2015 at 2:15 pm
Yes, the incremental load pattern can be used with any data source.
Stick with it for now, you can look for alternative ways later.
November 6, 2015 at 2:27 pm
Thank you for your reply but I am wondering how to do it. I see how to do it via the Stairway on this site for an OLE DB source to an OLE DB destination but the logic uses SQL queries to update, delete, or insert by matching on columns. I know how to import data from flat files to SQL T=tables but in all my research, I cannot find an example of someone using this same technique/design pattern to go from flat file to OLE DB (SQL) directly. I wondered if this meant that it isn't possible.
Now that you've said it is, could you give me a pointer in the right direction of how to do it or if you know of any example. Thanks!
November 9, 2015 at 6:15 am
You mention that you already have a package that imports the files into a database table. Let’s consider this database table to be a staging table for your data. Now, add one more data flow, let’s call it “Incremental Load” to the end of this package and follow the directions from the stairway, with the data source being the staging table.
Once you have that working, make a backup of your package.
Now, delete the first data flow, and delete the data source in your “Incremental Load” data flow and insert a flat file source instead, use your existing connection manager and connect it to the lookup component. That’s it.
If you have a lot of different file types you load into the destination table, it might be easier to use the first approach of loading data files into a staging table first and then load from the staging table to the destination table.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply