Design Question- Dynamic Configuration of Metadata in Dataflow
I have a design question that I'd like some input on. I am trying to archive data from an extremely large production database. The tables to be archived changes quite often. It is currently along the lines of 80-100 tables with the possibility (likelihood) to grow from there. If at all possible, I'd like to avoid writing an individual dataflow transformation for each table. I know that SSIS does not offer the same capabilities to change the metadata at runtime as DTS. I am currently exploring the option of programmatically creating/modifying the packages through the .Net framework. (using this link as a guide: http://msdn2.microsoft.com/en-us/library/ms345167.aspx). I have concerns about the performance of this approach and was wondering if anyone had any feedback, or has implemented something similar, or has any other ideas on a different way to accomplish the same thing. Thanks so much for your help, Jessica
March 20th, 2007 7:47pm

JessicaElise wrote: I have a design question that I'd like some input on. I am trying to archive data from an extremely large production database. The tables to be archived changes quite often. It is currently along the lines of 80-100 tables with the possibility (likelihood)to grow from there. If at all possible, I'd like to avoid writing an individual dataflow transformation for each table. I know that SSIS does not offer the same capabilities to change the metadata at runtime as DTS. I am currently exploring the option of programmatically creating/modifying the packages through the .Net framework. (using this link as a guide: http://msdn2.microsoft.com/en-us/library/ms345167.aspx). I have concerns about the performance of this approach and was wondering if anyone had any feedback, or has implemented something similar, or has any other ideas on a different way to accomplish the same thing. Thanks so much for your help, Jessica Assuming that the package you build programatically is the same as the package that you build in the SSIS Designer then performance will be exactly the same. Generating the package using the API shouldn't take too long - depends how good a coder you are To be honest alot of people have mentioned they are attempting this but nobody has reported that they haveachieved it. But then again why would they? People come on here to report problems, not successes. Does that help at all? Probbaly not!! -Jamie
Free Windows Admin Tool Kit Click here and download it now
March 20th, 2007 8:32pm

Programmatically creating packages for each table is the way to go unfortunately. Have a look at this link: http://www.ivolva.com/ssis_code_generator.html I haven't used it myself, but if it does what it says, it should be a big help in writing the code to dynamically generate packages.
March 21st, 2007 11:19pm

Well I'm reporting a success, Jamie. I got it working in a very base, proof-of-concept form. I've only tested it for very small datasets though. We'll see what happens when I increase the load (and run it multi-threaded). But, in case this can be of any use to anyone else: I created a custom component that will create the a dynamic package based on an inputted source table, destination table and package name (source and destination connection strings are currently hardcoded, but I'm going to change that as I develop the component). I've been successful in using this component to create the package and then using the "Execute Package Task" to execute the package that was just created. Chuck that into a ForEach Loop that goes through a recordset containing a DestinationTable, SourceTable, and PackageName and modifies the expressions of the component and it works. Here's the meat-and-potatoes of the Custom Component I created. Code Snippet using System; using System.Collections.Generic; using System.Text; using Microsoft.SqlServer.Dts.Runtime; using System.IO; using System.Data; using Microsoft.SqlServer.Dts.Pipeline; using Microsoft.SqlServer.Dts.Pipeline.Wrapper ; namespace DynamicDataTransfer { [ DtsTask( DisplayName = "Dynamic Data Transfer Task", Description = "Dynamic Data Transfer.", UITypeName = "DynamicDataTransfer.DynamicDataTransferTaskUI, DynamicDataTransfer, Version=1.0.0.0, Culture=Neutral, PublicKeyToken=xxxxxxxxxxxxxxxx" ) ] public class DynamicDataTransfer : Task { private string SourceTableNameInternal = ""; public string SourceTableName { get { return this.SourceTableNameInternal; } set { this.SourceTableNameInternal = value; } } private string PackageNameInternal = ""; public string PackageName { get { return this.PackageNameInternal; } set { this.PackageNameInternal = value; } } private string DestinationTableNameInternal = ""; public string DestinationTableName { get { return this.DestinationTableNameInternal; } set { this.DestinationTableNameInternal = value; } } /// <summary> /// Executes the task /// </summary> public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, object transaction) { try { Microsoft.SqlServer.Dts.Runtime.Application a = new Microsoft.SqlServer.Dts.Runtime.Application(); String SSISPackageFilePath; SSISPackageFilePath = PackageNameInternal; if (File.Exists(SSISPackageFilePath)) File.Delete(SSISPackageFilePath); Package pkg = new Package(); MainPipe dataFlow; ConnectionManager conMgrSource = pkg.Connections.Add("OLEDB"); ConnectionManager conMgrDestination = pkg.Connections.Add("OLEDB"); conMgrSource.Name = "OLEDBConnectionSource"; conMgrSource.ConnectionString = "Data Source=SOURCESERVER;Initial Catalog=SOURCEDB;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;"; conMgrDestination.Name = "OLEDBConnectionDestination"; conMgrDestination.ConnectionString = "Data Source=DESTINATIONSERVER;Initial Catalog=DESTINATIONDB;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;"; Executable exe = pkg.Executables.Add("DTS.Pipeline.1"); TaskHost th = exe as TaskHost; th.Name = "DynamicDataFlowTask"; dataFlow = th.InnerObject as MainPipe; IDTSComponentMetaDataCollection90 metadataCollection = dataFlow.ComponentMetaDataCollection; IDTSComponentMetaData90 OLEDBSource = dataFlow.ComponentMetaDataCollection.New(); OLEDBSource.Name = "OLEDBSource"; OLEDBSource.ComponentClassID = "DTSAdapter.OLEDBSource.1"; IDTSComponentMetaData90 OLEDBDestination = dataFlow.ComponentMetaDataCollection.New(); OLEDBDestination.Name = "OLEDBDestination"; OLEDBDestination.ComponentClassID = "DTSAdapter.OLEDBDestination.1"; // Get the design time instance of the component. CManagedComponentWrapper InstanceSource = OLEDBSource.Instantiate(); // Initialize the component InstanceSource.ProvideComponentProperties(); // Specify the connection manager. if (OLEDBSource.RuntimeConnectionCollection.Count > 0) { OLEDBSource.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(pkg.Connections["OLEDBConnectionSource"]); OLEDBSource.RuntimeConnectionCollection[0].ConnectionManagerID = pkg.Connections["OLEDBConnectionSource"].ID; } InstanceSource.SetComponentProperty("OpenRowset", SourceTableNameInternal); InstanceSource.SetComponentProperty("AccessMode", 0); //reinitialize the component InstanceSource.AcquireConnections(null); InstanceSource.ReinitializeMetaData(); InstanceSource.ReleaseConnections(); // Get the design time instance of the component. CManagedComponentWrapper InstanceDestination = OLEDBDestination.Instantiate(); // Initialize the component InstanceDestination.ProvideComponentProperties(); // Specify the connection manager. if (OLEDBDestination.RuntimeConnectionCollection.Count > 0) { OLEDBDestination.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(pkg.Connections["OLEDBConnectionDestination"]); OLEDBDestination.RuntimeConnectionCollection[0].ConnectionManagerID = pkg.Connections["OLEDBConnectionDestination"].ID; } InstanceDestination.SetComponentProperty("OpenRowset", DestinationTableNameInternal); InstanceDestination.SetComponentProperty("AccessMode", 0); //reinitialize the component InstanceDestination.AcquireConnections(null); InstanceDestination.ReinitializeMetaData(); InstanceDestination.ReleaseConnections(); //map the columns IDTSPath90 path = dataFlow.PathCollection.New(); path.AttachPathAndPropagateNotifications(OLEDBSource.OutputCollection[0], OLEDBDestination.InputCollection[0]); IDTSInput90 input = OLEDBDestination.InputCollection[0]; IDTSVirtualInput90 vInput = input.GetVirtualInput(); foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection) { IDTSInputColumn90 vCol = InstanceDestination.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READWRITE); InstanceDestination.MapInputColumn(input.ID, vCol.ID, input.ExternalMetadataColumnCollection[vColumn.Name].ID); } a.SaveToXml(SSISPackageFilePath, pkg, null); th = null; pkg = null; a = null; OLEDBSource = null; OLEDBDestination = null; InstanceSource = null; InstanceDestination = null; return DTSExecResult.Success; } catch (Exception exc) { componentEvents.FireError(0, "DynamicDataTransfer.Execute", "Task Errored: " + exc.ToString(), "", -1); return DTSExecResult.Failure; } } } } (Just as a side-note, your blog has helped me a ton since I've started with SSIS. Thanks for posting all of your findings.) Jess
Free Windows Admin Tool Kit Click here and download it now
March 28th, 2007 6:49pm

Hey Jess, I'm impressed. This is the first time I've seen aworking bit of code that does this. Do you mind if I put a link from my blog to this? I think this needs to be shared. One thing, the correct nomenclature is a custom task, not a custom component. My god, I must be in acritical mood Regards -Jamie
March 28th, 2007 8:45pm

Custom Task, got it. Sure thing, link to your blog, I'll be proud.
Free Windows Admin Tool Kit Click here and download it now
March 28th, 2007 8:49pm

JessicaElise wrote: Custom Task, got it. Sure thing, link to your blog, I'll be proud. Cool. Done! Building Packages Programatically http://blogs.conchango.com/jamiethomson/archive/2007/03/28/SSIS_3A00_-Building-Packages-Programatically.aspx Thanks again Jess. -Jamie
March 28th, 2007 9:02pm

Hi Jamie, Since the Script task only support VB.NET, I'd like to know how the above code can be deployed in our SSIS Package Are there any conversion tool available?
Free Windows Admin Tool Kit Click here and download it now
May 3rd, 2007 1:03pm

Subhash512525 wrote: Hi Jamie, Since the Script task only support VB.NET, I'd like to know how the above code can be deployed in our SSIS Package Are there any conversion tool available? Yes, I'm sure there is. Not being a .Net expert myself I don't know where to find them but I'm sure they exist. -Jamie
May 3rd, 2007 5:20pm

Thanks for your reply Jamie, I was successful implementing the above code in VB.NET code with slight modifications and additions required for my package usingonline codeconverter available here . I must thank you both a lot for the code. Subhash Subramanyam
Free Windows Admin Tool Kit Click here and download it now
May 4th, 2007 6:31pm

Jess, I was directed to your post by John Welch. I have a need very similar to yours, however, I am not a developer and I am having difficulties implementing your code snippet. I have VS Express for C# loaded and when I create a class library project I get the following error: The type or namespace name 'Dts' does not exist in the namespace 'Microsoft.SqlServer' (are you missing an assembly reference?) Can someone point me in the right direction? What other code elements are needed to make this "meat and potatoes" portion work? Thanks in advance, Keith
May 14th, 2007 9:42pm

Hi , I am alsofacing this problem, but I didn't understand where I have to write this code. If it is in script task then where is the main() .Can you please tell me or reply on this email address.(ramanandap@gmail.com). Thanks in advance
Free Windows Admin Tool Kit Click here and download it now
August 24th, 2007 4:55pm

KC wrote: Jess, I was directed to your post by John Welch. I have a need very similar to yours, however, I am not a developer and I am having difficulties implementing your code snippet. I have VS Express for C# loaded and when I create a class library project I get the following error: The type or namespace name 'Dts' does not exist in the namespace 'Microsoft.SqlServer' (are you missing an assembly reference?) Can someone point me in the right direction? What other code elements are needed to make this "meat and potatoes" portion work? Thanks in advance, Keith You need to add a reference to the Microsoft.SQLServer.DTSRuntimeWrap assembly to your project. Use Project..Add Reference.
August 27th, 2007 6:46pm

Mahi12345 wrote: Hi , I am alsofacing this problem, but I didn't understand where I have to write this code. If it is in script task then where is the main() .Can you please tell me or reply on this email address.(ramanandap@gmail.com). Thanks in advance This was created as a custom task in VB.NET. That gets compiled to a .NET assembly (DLL), registered in the GAC, and then can be used from your SSIS packages. If you look under "Developing Custom Tasks" in Books Online, you can see more information about this.
Free Windows Admin Tool Kit Click here and download it now
August 27th, 2007 6:51pm

I was using the code in this thread to create a console application which can build the SSIS package dynamically and run the package. If the source column and destination column names are of different cases then the application was failing during the mapping. So I modified the for each loop like below. Still this is not a fool proof method, this will work as long as all characters in the column names are upper or lower. for eg., Source column = empl_id, Destination column = EMPL_ID, in this case the below code will work. if the source column or destination columnis Empl_Id, then the below mapping will fail. Code Block foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection) { IDTSInputColumn90 vCol = destnDesignTime.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READWRITE); try { destnDesignTime.MapInputColumn(input.ID, vCol.ID, input.ExternalMetadataColumnCollection[vColumn.Name.ToLower()].ID); } catch { destnDesignTime.MapInputColumn(input.ID, vCol.ID, input.ExternalMetadataColumnCollection[vColumn.Name.ToUpper()].ID); } } So how can I map the columns irrespective of the cases?Thanks
October 3rd, 2007 1:58am

Hi I will highly appreciate if someone who has already implemented the above solution. If someone can step through how to implement the above solution. Iam really strugglingwith how to go about and after spending so many hours give up. But, everyone is veryoptomistic about this solution and I believe a little help or support will really helpful to me. I even couldn't create package through script. Please help. Many Thanks R
Free Windows Admin Tool Kit Click here and download it now
December 8th, 2008 7:22pm

Hi All,I am really grateful to this post and ofcourse Jessica, to have post her code here this really gave me hope that my requirement of looping through the tables dynamically is possible ( after in encountered the meta data issue of mappings). I am totally new to SSIS (working on it since 3 days), so as a trial, i have used the same code posted by Jessica ( ofcource changed connections strings andPublicKeyToken).I have built the .DLL and added it.. Program Files\Microsoft SQL Server\90\DTS\Tasks folder, added it to GAC and toolbox of BI Management Studio. But when i am doing a drag - drop of this task it is giving me the following error. =================================== Failed to create the task. (Microsoft Visual Studio) =================================== The task user interface specified by type name 'DynamicDataTransfer.DynamicDataTransferTaskUI, DynamicDataTransfer, Version=1.0.0.0, Culture=Neutral, PublicKeyToken=37954c1face298d5' could not be loaded. (Microsoft.DataTransformationServices.Design) ------------------------------ For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.DataTransformationServices.Design.SR&EvtID=CouldNotLoadTaskUIForType&LinkId=20476 ------------------------------ Program Location: at Microsoft.DataTransformationServices.Design.DtrTaskDesigner.InitializeTaskUI() at Microsoft.DataTransformationServices.Design.DtrTaskDesigner.OnNewTaskAdded() at Microsoft.DataTransformationServices.Design.DtsBasePackageDesigner.CreateExecutable(String moniker, IDTSSequence container, String name) =================================== Could not load type 'DynamicDataTransfer.DynamicDataTransferTaskUI' from assembly 'DynamicDataTransfer, Version=1.0.0.0, Culture=Neutral, PublicKeyToken=37954c1face298d5'. (mscorlib) ------------------------------ Program Location: at System.RuntimeTypeHandle._GetTypeByName(String name, Boolean throwOnError, Boolean ignoreCase, Boolean reflectionOnly, StackCrawlMark& stackMark, Boolean loadTypeFromPartialName) at System.RuntimeTypeHandle.GetTypeByName(String name, Boolean throwOnError, Boolean ignoreCase, Boolean reflectionOnly, StackCrawlMark& stackMark) at System.RuntimeType.PrivateGetType(String typeName, Boolean throwOnError, Boolean ignoreCase, Boolean reflectionOnly, StackCrawlMark& stackMark) at System.Type.GetType(String typeName, Boolean throwOnError) at Microsoft.DataTransformationServices.Design.DtrTaskDesigner.InitializeTaskUI() As am new to SSIS, i am totally new to its API, so i am not able to figure out the cause of error. Can anyone please help me in this regard?
March 12th, 2009 2:37pm

Hi,you must develop a user interface for your task. A good start for you is http://msdn.microsoft.com/en-us/library/ms136080.aspx In the example SSIS looks for task user interface "DynamicDataTransfer.DynamicDataTransferTaskUI". Regards Alex
Free Windows Admin Tool Kit Click here and download it now
March 13th, 2009 11:44am

Hello, I added the reference you alluded to, but I still get a number of errors of the following type "The type or namespace name 'Task' could not be found (are you missing a using directive or an assembly reference)". I get other, similar errors where the 'Task' above is replaced by 'Connections', 'VariableDispenser', 'IDTSComponentEvents', etc.... Any assistance would be greatly appreciated. Thanks, Carl Petersen
January 6th, 2011 9:57am

Nevermind, I was compiling using Visual Studio 2008 with Sql Server 2008. I had to add a reference referring to Microsoft.SQLServer.ManagedDTS, and then change all of the references for XXXXXXXX90 to XXXXXXXXX100 ( for example IDTSVirtualInputColumn90 to IDTSVirtualInputColumn100). Also, I had to change ToConnectionManager90 to GetExtendedInterface. Thanks, Carl
Free Windows Admin Tool Kit Click here and download it now
January 6th, 2011 12:59pm

Hello cepete02 I am a beginner in .net ! Can you explain step by step how to use the code and then have a dynamic task ! Thx
May 12th, 2011 5:51pm

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

Other recent topics Other recent topics