Friday, March 9, 2012

Need advice on approach

I am new to DTS/SSIS and would like some input on an approach to solve a problem.

I have a solution using SQL Server 2005 and another legacy solution running on Access. Status records are written to a table in SQL Server and as they are written, I need to write a record to a table in Access. This needs to be as real-time as possible.

I thought about writting a managed stored procedure in C# so I could also access MS Access, but someone pointed me to DTS.

Records are constantly written to the status table and need to remain in there even after they are processed.

Can you recommend an approach or an article to read?

Thanks,


Steve

This could be done with SSIS with a little work but I think if your are talking about a very small amount of data that needs to be written to access each time, then I think the SP idea is better or something in general from the SQL engine to Access rather than involving SSIS. So, you may want to repost in one of the more general SQL forums.

Now if you have SSIS already involved in the pitcure some where, then the SSIS idea might still be worth investigating. For example if the part of your story "Status records are written to a table in SQL Server " was itself being done by SSIS, then enhancing the SSIS scope could be a good approach.

good luck

No comments:

Post a Comment