Dynamically remapping source to destination in SSIS
This all started off in trying to help out a fellow SSIS enthusiast, and now I have made a complete project as a sample….. catch the MSDN forum thread here.
There are many reasons why you would want to remap source columns to destination at runtime – I would not necessarily recommend this type of solution for some of your larger and more impactful data imports, but there are many cases where being able to modify the mapping metadata at runtime can be incredibly useful.
I have posted the project example in the files directory on my blog, you can download the Visual Studio 2008 project here – keep in mind that I did this in VS 2008 Team System Architecture Edition, so if you don’t have this version, then you might have an issue with the unit test project attached.
This should not pose a problem for many though, as the only reason I threw the unit test in there was to provide a simple and lazy way to call into the class and perform the remapping.
This example demonstrates the following:
- Load an existing SSIS package and indentify the source and destination components
- Instantiate the source and destination components to read their column metadata
- Remap a source column to a destination column
- Save the modified SSIS package
When you open the SSISExamples solution, build it, and then either copy the DynamicRemap.dtsx file from the bin directory to your c:\ root or modify the PackageToLoad string in the CreatePackage class.
Once you call into the CreateAndRunPackage method, you will end up with a copy of the package in your root named PackageModified.dtsx.
Please feel free to e-mail me with any questions. I’ll try to make a few improvements to the example when I have free time over the coming holiday, but for now, enjoy!