Hi , I'm new to SSIS Package development. I have task where in i need to merge data in multiple servers. I need to merge the data using a SSIS Package. The package is executed every night. I have created a Dataflow task with a merge join transformation, then i'm loading the output of the merge join transformation into a sql server table. am i doing the correct way. Also, what kind of error handling should i take care of. Can a simple SSIS package that merges data and loads the output to a destination fail? The most important thing, i'm loading the output of the merge join into sql server table. I created indexes on the destination table assuming that it would improve performace. However, when i executed the package, the package failed. Can i anybody let me know, your suggestions for my requirement. -- Praveen

#1 ... what kind of error handling should i take care of. Can a simple SSIS package that merges data and loads the output to a destination fail? #2: The most important thing, i'm loading the output of the merge join into sql server table. I created indexes on the destination table assuming that it would improve performace. However, when i executed the package, the package failed. Can i anybody let me know, your suggestions for my requirement. -- Praveen Re #1: Sure it can, a good package should always have logging enabled, so in case an error occurs you have details on what went wrong, in your case we do not know specifics though how you made it. Hope you have some kind of monitoring mechanism, too. #2: You did not provide any hint on what error you are getting, bear in mind though - you should not introduce indexes because they actually slow down updates/inserts. I assume you conflicted with a primary/unique key.Arthur My Blog

Arthur, Thanks for your feedback. My only only concern is when data in destination table becomes huge. Will that pose a performance issue when retrieving data. I need to retrieve data from the destination table to generate a report.-- Praveen

Yes, it can become an issue. You would need to use special technique to make the reporting robust. I suggest you only make one clustered index may be by ID or date at this stage and look into having this table partitioned.Arthur My Blog