Inserting data in dynamically-created columns (columns do not exist at design time, but are computed at run time)
Environment: SQL Server 2005, SQL Server 2005 SSIS, Windows Server 2003. I am looking for some design direction in solving the following challenge: I am creating a data migration package that will move data between two disparate enterprise systems. There are a dozen customers who will be targeted to use this package to migrate from their current system to our platform. One aspect of the migration has challenged me. A set of source tables will not map over easily to a set of destination tables, and I am looking for the best approach to take. To provide a bit more background: The source enterprise system allowed a senior user to extend the schema by defining new table columns (just fields, not new tables) that were then added to the SQL Server database. I will not know in advance (at SSIS design time) which fields were added to which tables. There is a similar schema-extension feature in the target system, but the mapping is moderately complex. So in SSIS, I will create a Unicode Flat File that will contain the result of mapping those field names to new field names in the target enterprise system (they are translated to a form compatible with the target system, and create new fields in the target system. (The reason for the intermediate flat file step is because I understand that SSIS does not like changes to the target schema at runtime – something about strong binding to the schema metadata. Next I need to map the source values from the source custom fields to the just-created fields in the target system. Again, at design time, I do not know how many fields, and exactly which tables were customized. This is where I need advice. I don’t think that SSIS can do the job, and wonder if I need to “go to school” on dynamic SQL using sp_executesql. If so, where is the best place to go read up on exactly how to do that? All suggestions welcome.
June 15th, 2011 9:15pm

I would normally solve this type of scenario by building a package generator rather than a package. As you note SSIS doesn't like dynamic columns, but you have the key info, the column mapping, so you can use to build the package dynamically. There are quite a lot of samples for building packages in code, so you just need to use it drive via your mapping file. Depending on how complex the overall process it you may like to part the a package in the designer to act like a template, a package with the key tasks and connections already added, then you can just load this in your code and finish building it. Some samples and info (and of course check the help) - http://www.sqlis.com/sqlis/post/Creating-packages-in-code-OLE-DB-Source-to-Flat-File-File-Destination.aspx http://blogs.msdn.com/b/mattm/archive/2008/12/30/samples-for-creating-ssis-packages-programmatically.aspx http://blogs.msdn.com/b/mattm/archive/2008/12/30/ezapi-alternative-package-creation-api.aspx - EzApi is set of wrapper classes for building packages, obviously designed to be easier, but I found it a of a frustrating learning curve, but maybe because I was already conformtable doing it the hard way so missed the control.http://www.sqlis.com | http://www.konesans.com
Free Windows Admin Tool Kit Click here and download it now
June 16th, 2011 2:31am

Darren, Thanks for the suggestion. I will try out your sample to get a better picture and see if this wold work for me. One followup question - You said > you may like to part the a package in the designer to act like a template, > a package with the key tasks and connections already added, then you can just load this in your code and finish building it Does that mean there is a straightfoward way (after creating and saving a "template" package in BIDS) to load the package XML in C#, modify the in-memory package structure to complete the set of needed actions, and then immediately run it?
June 16th, 2011 1:42pm

Darren, My development machine is Windows NT (64-bit) I have BIDS, but not MSVS 2005 with C# or VB, in the office. (Note that I am remotely connecting to SQL Server 2005 on a Windows 2003 Server machine for testing, but do not have SSIS locally.) So I downloaded Microsoft Visual C# 2008 Express Edition (SP1) - I thought 2005 Express would be too old ... ;) To get started, I created a .NET 3.5 WPF Project and added "SqlToFlatFile.cs" from your posting to my project. I added to References "Microsoft.SqlServer.DTSRuntimeWrap", which on my workstation is under c:\Program Files (x86)\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.SQLServer.DTSRuntimeWrap.dll However, MSVS complains (line 13, Package package = new Package();) that type or namespace 'Package' could not be found. There is a similar assembly reference issue with other classes, such as ConnectionManager, TaskHost, MainPipe, DtsConvert, IDTSComponentMetaData90, ... So I need something else (individuall .dlls, a different install) in my environment, or perhaps I have a .NET 2.0/3.5 conflict, - any clue?
Free Windows Admin Tool Kit Click here and download it now
June 16th, 2011 4:58pm

I added two more project References (Microsoft.SqlServer.ManagedDTS and Microsoft.SqlServer.DTSPipelineWrap), changed my project's <PlatformTarget> to x86, and am moving forward a bit...
June 16th, 2011 6:48pm

If you can use third-party solutions, check CozyRoc Data Flow Task Plus. It is an extension of the regular Data Flow Task with support of dynamic data flows at runtime. You can also define mapping dictionary object between your source and destination component to guide the column mapping process. The solution has been already successfully used and deployed in production systems.SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
Free Windows Admin Tool Kit Click here and download it now
June 17th, 2011 10:58pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics