Hi gurus, I have scenario that I loop through set of files and load them into db and then archive those files into Archive folder., which I did fine. Now, there is a chance that there may be same filename with (same rows + some additional rows) added to it and loaded into the folder. so, I dont want to insert those same rows into my db., I just want to insert new rows. How can I do that.Can anyone explain me in detail. FYI: I also filename into my db for some additional purpose. Please help me with this.

Hi Spartaa, Do you mean you want an incremental load of a file? E.g. you have a file with these rows: a,b,c then you get a,b,c x,y,z so only xyz needs to load? You may want to load the file into an interim /staging table and see this post if you say yes http://www.sqlis.com/sqlis/post/Get-all-from-Table-A-that-isnt-in-Table-B.aspxArthur My Blog

Hi Spartaa, Do you mean you want an incremental load of a file? E.g. you have a file with these rows: a,b,c then you get a,b,c x,y,z so only xyz needs to load? You may want to load the file into an interim /staging table and see this post if you say yes http://www.sqlis.com/sqlis/post/Get-all-from-Table-A-that-isnt-in-Table-B.aspxArthur My Blog

One approach that I have used is to iterate through all of the files in a folder. Before I do a load, I call a stored procedure (Execute SQL task), passing in the name of the file (actually, I first get some dates and the file size, which I pass in as well). The stored procedure checks a table that lists the files that have been loaded to see if the file has been loaded already. If it has, return a row with information that the file exists already. If it hasn't, then return a row with information that the file does not exist yet. Then take that information and decide whether to load the file. Also, you might archive the file differently if it has been loaded already.Russel Loski, MCT, MCITP Business Intelligence Developer and Database Developer 2008 Blog: http://www.bidn.com/blogs/RussLoski/ Twitter: @sqlmovers

Hi Spartaa, Do you mean you want an incremental load of a file? E.g. you have a file with these rows: a,b,c then you get a,b,c x,y,z so only xyz needs to load? You may want to load the file into an interim /staging table and see this post if you say yes http://www.sqlis.com/sqlis/post/Get-all-from-Table-A-that-isnt-in-Table-B.aspx Arthur My Blog I have a file with these rows: a,b,c then Iget a,d,e x,y,z so I update old a,b,c to a,d,e and insert new row. Can you explain me in detail where and how to modify the DF.

One approach that I have used is to iterate through all of the files in a folder. Before I do a load, I call a stored procedure (Execute SQL task), passing in the name of the file (actually, I first get some dates and the file size, which I pass in as well). The stored procedure checks a table that lists the files that have been loaded to see if the file has been loaded already. If it has, return a row with information that the file exists already. If it hasn't, then return a row with information that the file does not exist yet. Then take that information and decide whether to load the file. Also, you might archive the file differently if it has been loaded already. Russel Loski, MCT, MCITP Business Intelligence Developer and Database Developer 2008 Blog: http://www.bidn.com/blogs/RussLoski/ Twitter: @sqlmovers Hi Russ Loski , I understand what you are saying but I am not good with SPs. Can you explain me in detail how to solve this one.

See the link above it is better than what I can write here. It explains how to do the incremental load. It is also possible to use the T-SQL's MERGE statement, very easy to run it against two tables. You need to load the data like I said to an interim table in any case 1st.Arthur My Blog

Hi Spartaa, Please check this post that I have already answered: http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/5d6f4696-7af9-45aa-b0a4-1f4c53ef8c95 Hope this helps.

See the link above it is better than what I can write here. It explains how to do the incremental load. It is also possible to use the T-SQL's MERGE statement, very easy to run it against two tables. You need to load the data like I said to an interim table in any case 1st. Arthur My Blog When I am trying to do the incremental load using lookup and cond split. When I changed one row in excel file and run the pkg my whole table is being inserted/updated with this same changed values. Even the not changed ones are getting replaced by by changed ones. Iam not sure what I am doing wrong. My my DF luks like this: and I used a script like UPDATE [dbo].[table]SET cola=?, [colb]=? WHERE [FileName]=?

You can try Table Difference component from CozyRoc which will give you exactly what data is new, updated, existing and deleted. Mind you will have to pay for a production license. If not, then you can simply extract all your data into a staging table and then execute a sql query which will do UPSERT from the staging table to the destination table. Follow this if you want to understand more about UPSERT.http://btsbee.wordpress.com/