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

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

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.

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

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

Custom Task, got it. Sure thing, link to your blog, I'll be proud.

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

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?

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

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

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

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

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.

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.

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

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

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?

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

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

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

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