March 13, 2014 at 4:05 pm
We have over 500 tables in the source that are getting migrated to a new set of tables with a different table structure into a database using 500+ SSIS packages. In the 500 tables we have over 9000 total columns all together. Multiple people worked on these SSIS packages in the course of last two years. The issue is that we don't know if all the columns in the source are accounted for in the destination.
At the moment we are going off the documentation to figure out if a particular source has been touched by the process. This process is extremely manual and time consuming. My question is does anyone know how can I do a gap analysis to figure out which source columns are already accounted for (either used in transformation or made it through to the target db) vs. which columns still need to be addressed (the columns are not used in any transformations or source queries) in the SSIS so no information gets left behind in the old database.
The SSIS packages are written in BIDS 2008 and servers are in SQL Server 2008R2
March 21, 2014 at 4:35 am
Good Luck 😀
I would say, talk to Red Gate. I don' think there is a specific took in the arsenal that allows you to do this, but they may have a suggestion as to the best way to do it.
Each dtsx package is 'just' XML so in theory you could parse the XML to check for table and field names, but it is hard work because the XML is extremely complex with indefinitely nested loops. If you need code developed, I would suggest that posting the job on oDesk or Freelancer.com
March 21, 2014 at 4:45 am
Pragmatic works have a BI documenter which can crate word docs or html files that document the packages. This might form the basis of that analysis and might be easier to interrogate than the raw XML.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply