Hi All, I want to setup SSIS package for Archiving old data and I have 300 tables for archiving and I am new in SSIS .please suggest some suggestion .I have read many article but I have some doubts. 1.for all 300 tables only one package is created then whats impact on log file. 2.In which server, package to be create:-source /destination server,i think destination server is best because it retrieve data.. 3.I have ID and created column for archiving then which one is best for for finding which one new row is insert when job is executed on monthly basis. and how can i use ID and created column means through lookup.. Thanks

Lets take one at a time, but before that; Are you familiar with what is called as Control Flow and Data Flow in SSIS?Please vote as helpful or mark as answer, if it helps Cheers, Raunak | t: @raunakjhawar | My Blog

Thanks Raunak for your early response. yes I know that and I think all 300 tables move from source to destination in Data flow task. And If I am wrong ,Please Let me know and clear my all mentioned doubts ... Thanks

Hi Sunita, Can you please elaborate the requirement more so that we can better understand and suggest? Thanks,hsbal

Hi Harry, I want to setup SSIS package for Archiving old data and I have 300 tables for archiving and I am new in SSIS .please suggest some suggestion for archiving process .I have read many article but I have some doubts in ssis. 1.for all 300 tables only one package is created then whats impact on log file. 2.In which server, package to be create:-source /destination server,i think destination server is best because it retrieve data.. 3.I have ID and created column for archiving then which one is best for for finding which one new row is insert when job is executed on monthly basis. and how can i use ID and created column means through lookup.. and let me know apart of this which type of requirement you need. Thanks

Sunita, Unfortunately, you just copy/pasted the req. again which was not the intent. 1. Anyhow, I would say dont load data for all 300 tables in one task. 2. Package is created in BIDS. It is deployed on server. 3. Yes for ID column, you can use look up task to look for new entries. again, I am replying based on high level understanding of your req. The following link help on Look up task http://www.katieandemil.com/ssis-lookup-transformation-example-sql-server Thanks, hsbal

What is the rule which defines that the data has to be archived? Are you using a time stamp? Moreover, for 300 tables, I would work on a strategy to archive(what will the archival process do?) related tables in one go. Please vote as helpful or mark as answer, if it helps Cheers, Raunak | t: @raunakjhawar | My Blog

Just a couple of pointers: make sure the archiving process runs in parallel. You don't want to run 300 packages/data flows one after the other. Are you sure you want only one package? It's going to be a nightmare to maintain.make sure you know which tables have already been archived. If the process crashes (for example, a network outage), you need to run only those who weren't finished or haven't started yet.run the packages on the destination server. That way SSIS can use shared memory to write to the database, which is pretty fast. Also use the fast load option in the OLE DB Destination.make sure the destination has simple recovery model (it's an archive database, you don't need point-in-time restore) to minimize logging.if possible, use CDC (change data capture) on the source. This makes it much easier for you to determine which rows are inserts or updates.MCSA SQL Server 2012 - Please mark posts as answered where appropriate.

Hi All, First of all Thanks to all of you for your suggestion. yesterday, I was busy that's why not come here. My first point is Let me know how many package is deploy for 300 tables /archiving process run in parallel how? How could package know whose tables left for finished or haven't started yet if some error is occurred? Dont understand why CDC is enabled. Let me know herry what type of requirement do you need .

"Let me know how many package is deploy for 300 tables /archiving process run in parallel how?" That depends on you. You can have one package (not really maintainable), 10 packages (more maintainable) or 300 packages (again not really maintainable) or everything in between. Just make sure everything runs at the same time. "How could package know whose tables left for finished or haven't started yet if some error is occurred?" You need to record that yourself in a table. Put every tablename in it and put a flag IsTransfered to 0. At the end of transferring a table, put the corresponding flag to 1. If you have to restart, take only tables with flag set to 0. "Dont understand why CDC is enabled." Do some research on CDC (change data capture).MCSA SQL Server 2012 - Please mark posts as answered where appropriate.

Hi Koen, Again I am confused. let me know should I create one package for all 300 table or more than 1 then what impact if i don't run it parallel. And let me know flag put on source database.. and condition for flag with in same package. I think CDC is used if something is changed in database then we aware that,then how its link each other. Thanks

You should create more. You don't to put a flag in the source database. You need a flag in a table you create yourself. You use this table to guide your ETL process. If you use CDC on the source system, you already know which rows are inserts and which rows are updates, so you don't have to figure that out yourself.MCSA SQL Server 2012 - Please mark posts as answered where appropriate.

Thanks Koen. can you attached dummy one package:- control flow and data flow.. basically I want to know how flag table guide to me for transferring the data. And I run this package on monthly basis then how CDC helps to me. Thanks

I don't have such a package lying around. Time to put your brain cells to work :) Basically you do a select on the table to find if the tables has a flag 0 (which means it hasn't been processed yet). Put a condition on the precedence constraint between the Execute SQL Task and the dataflow that transfers the table. For example: @processed == 0MCSA SQL Server 2012 - Please mark posts as answered where appropriate.

Actually I have not a single cell for SSIS in my mind :)because I am new in SSIS that's why putting here but trying to do.. Be don't mind Let me know why do you not have :) if you don't have then You just create dummy process :) Thanks

Hi , I know this is too much late but I am facing one problem for make this process ,Please resolve it. As know I am doing Archiving process set up.As mentioned by Koen ,putting a flag 1 or 0 for finding table status . Here I set up this by first Execute SQL task --if all 1 then go to data flow task and insert data ---if few's are 0 then go to another data flow task and then now my question is in this data flow; for all tables i have to check particular table transferred the data or not ;if it is already transferred then nothing to do otherwise insert it. for example I have 10 tables in which 8 has transferred the data and 2 not then i would check for all 10 tables data is transferred or not . Expert guy understand my question.. Thanks