none
Programmatically creating Transformation Script Component

    Question

  • Does anyone have any examples of programmatically creating a Transformation Script Component (or Source/Destination) in the dataflow?  I have been able to create other Transforms for the dataflow like Derived Column, Sort, etc. but for some reason the Script Component doesn't seem to work the same way.

    I have done it as below trying many ways to get the componentClassId including the AssemblyQualifiedname & the GUID as well.  No matter, what I do, when it hits the ProvideComponentProperties, it get Exception from HRESULT: 0xC0048021

    IDTSComponentMetaData90 scriptPropType = dataFlow.ComponentMetaDataCollection.New();

    scriptPropType.Name = "Transform Property Type";

    scriptPropType.ComponentClassID = "DTSTransform.ScriptComponent";

    // have also tried scriptPropType.ComponentClassID =typeof(Microsoft.SqlServer.Dts.Pipeline.ScriptComponent).AssemblyQualifiedName;

    scriptPropType.Description = "Transform Property Type";

    CManagedComponentWrapper instance2 = scriptPropType.Instantiate();

    instance2.ProvideComponentProperties();

     

    Any help or examples would be greatly appreciated!  Thanks!

    Monday, November 20, 2006 5:04 PM

Answers

  • If you have not deduced, the error 0xC0048021 means that the component is not installed basically, so I'd say whatever you are using for the ComponentClassID is not right as you suspect. (http://wiki.sqlis.com/default.aspx/SQLISWiki/0xC0048021.html)

    As a start point, what values have you tried., and did they include this -

    Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost, Microsoft.SqlServer.TxScript, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91

    Is that assembly in the GAC?

    Monday, November 20, 2006 8:17 PM
    Moderator

All replies

  • If you have not deduced, the error 0xC0048021 means that the component is not installed basically, so I'd say whatever you are using for the ComponentClassID is not right as you suspect. (http://wiki.sqlis.com/default.aspx/SQLISWiki/0xC0048021.html)

    As a start point, what values have you tried., and did they include this -

    Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost, Microsoft.SqlServer.TxScript, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91

    Is that assembly in the GAC?

    Monday, November 20, 2006 8:17 PM
    Moderator
  • thanks!  worked like a charm. 

    Now...I have the inputs selected and the outputs added but can't figure out how to add the actual script code.  I don't see any custom properties that look like a script.   I know I have to override the ScriptMain routines, just not sure how. 

    Again any help or examples would be great.  thanks

    Monday, November 20, 2006 9:41 PM
  • Can you find a SourceCode property? Looking in Books Online, it seems that MS have pretty much neglected to document the component object model, and even in the limited component property documentation (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dtsref9/html/56f5df6a-56f6-43df-bca9-08476a3bd931.htm#script) this property is not listed. I’d say they just have not documented this at all, so is it even supported? A think it is rather pants if you cannot build packages entirely in code. Still, have a look at the SourceCode property, and perhaps have a look at what it is if you load an existing package via the object model. Another place to look is the raw XML of an existing DTSX file, but bare in mind this has been XML encoded in various unknown ways and may not be the directly assignable as you see the Xml, hence I suggested looking at an existing component through the object model.

    Tuesday, November 21, 2006 12:42 AM
    Moderator
  • Yes, found the SourceCode custom property after last post, but am having lots of trouble figuring out what to put in there. 

    I am comparing the XML of the a package where the script was created through bids vs the script I'm trying to create programmatically.  In Bids, It goes from an arrayElementCount of 0 with the default script to an arrayElementCount of 4 once you make change to the script.  It adds an element for a .vsaproj, an element for the references, an element for .vsaitem.  No clue how to add those items.  I can see that the Script Task on the Control Flow does something similiar but has built in routines SetUniqueVsaProjectName & CodeProvider.PutSourceCode.  So far, I'm not finding the corresponding routines for the Script Component.  Any clues on that?  thanks

    Tuesday, November 21, 2006 1:44 PM
  • I've been doing some playing with this. The value of SourceCode is just a string array, so you can easily create a this array to set it. There are four items in the array as you note. They seem to be in pairs, a moniker and some detail. You can examine these in detail for yourself, but the monikers seem to be a standard format and, and even the project xml seems fairly sensible, then you just have the VB.Net code. So far there is nothing that could not be easily derived, even the moniker and project format just uses a Guid, albeit formatted ( Guid.ToString("n") ).

    The major issue is that you also need to supply the wrapper code. Look in the project Xml (array element 1, or the second item) and you will see if references 3 files, only one of which is ScriptMain, the code we normally write. The other two are the wrappers. You can see these in the VSA designer if you look, but they are auto-generated at design-time for you.

    So without these wrappers our basic code will never compile. This of course raises the issue of compilation. Normally the PreCompile property is true, so the designer compiles the code. This ends up as base 64 encoded string in another property, BinaryCode (?).

    We can access the VSA compilation engine, and may even be able to get the compiled code back as binary, so we can base64 encode and set the property, but we still lack the wrappers. These seem like a lot of hard work, it seems MS generates them, but in sealed/internal design-time modules. So maybe we just write our own wrappers? Possible, but this means the package will not be maintainable via the UI. Is that an issue?

    Friday, November 24, 2006 8:27 PM
    Moderator
  • Thanks for looking into it.  We were on the same track.  We did get the script component to work but had to set the Precompile to false.  Since we are going to be running it on 64bit, that won't work for us.  We need to set the property "BinaryCode" to the compiled code.   Looking into ways to get the binary code. But we are also pursuing building custom components instead of script components.

    String[] scriptValue = new String[4];

    scriptValue[0] = @"dts://Scripts/" + scriptPropType.CustomPropertyCollection["VsaProjectName"].Value + @"/" + scriptPropType.CustomPropertyCollection["VsaProjectName"].Value + @".vsaproj";

    scriptValue[1] = ConsoleApplication1.Properties.Resources.ProjectFile;

    scriptValue[2] = @"dts://Scripts/" + scriptPropType.CustomPropertyCollection["VsaProjectName"].Value + @"/ScriptMain.vsaitem";

    scriptValue[3] = "' Microsoft SQL Server Integration Services user script component\r\n"

    + "' This is your new script component in Microsoft Visual Basic .NET \r\n"

    + "' ScriptMain is the entrypoint class for script components\r\n"

    + "\r\n"

    + "Imports System \r\n"

    + "Imports System.Data \r\n"

    + "Imports System.Math \r\n"

    + "Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper \r\n"

    + "Imports Microsoft.SqlServer.Dts.Runtime.Wrapper \r\n"

    + " \r\n"

    + "Public Class ScriptMain \r\n"

    + " Inherits UserComponent \r\n"

    + " \r\n"

    + " Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) \r\n"

    + " ' \r\n"

    + " 'Code Here \r\n"

    + " ' \r\n"

    + " End Sub \r\n"

    + " \r\n"

    + "End Class \r\n";

    IDTSDesigntimeComponent90 anIDTSDesigntimeComponent90 = instance2 as IDTSDesigntimeComponent90;

    anIDTSDesigntimeComponent90.SetComponentProperty("SourceCode", scriptValue);

    anIDTSDesigntimeComponent90.SetComponentProperty("PreCompile", false);

    Saturday, November 25, 2006 4:35 PM
  • Yep the 64bit will be a killer for you. Whilst it is possible to genarete your own wrappers, custom components may well be easier. To be honest you could probably write your own component that accepted .Net code, easier than you can simulate the stock component. If the code is reasonably static custom components would be easier, I find them easier anyway.
    Sunday, November 26, 2006 1:28 AM
    Moderator
  • You have done very good investigation. Could you please post full example of how you programmatically created a Transformation Script Component. I have a similar task and would appreciate your help.

    Tuesday, May 29, 2007 3:17 PM
  • I'm also trying to generate a script component programmatically and this post has been very useful, just about the only info I could find on it.

     

    I've followed through what's above and incorporated this into my own project, but I'm stuck with this bit (from the above):

     

    scriptValue[1] = ConsoleApplication1.Properties.Resources.ProjectFile;

     

    I'm guessing this somehow adds the references in - but I can't find the "Properties" of (I'm assuming) the namespace of the package generation class.  Can anyone help here?  Sounds like ProjectFile is a member of CreateTemporaryVCProject - buried deep in the framework and a little short on documentation.

     

    I'm also concerned about the need to supply the wrapper files as discussed.  Did you manage to get around this or would it still be necessary to supply these; even once you've added the XML resources I refer to above?  If so I reckon I may have to take the plunge with custom components.

    Tuesday, June 05, 2007 3:21 PM
  • ConsoleApplication1.Properties.Resources.ProjectFile refers to a property called ProjectFile in the application CarlaC wrote. That is basically a string resource. Look at the Resources tab in your Visual Studio Project, or have a look in the MSDN docs on this.

     

    It is not really important, what is important is the XML that that property contained. As per the name and my description above, it is the "project file", the XML stuff a bit like what you see if you open a csproj in notepad now. The project file will hold the references, and other project level infromation. The best thing to do would be to reverse engineer a package you built in the designer. That is what I did, and to be honest I just don't think this is feasible. So I only spent a few hours on the topic, but there was an awfull lot of code generation logic embeded in the UI that you cannot access. Damn internal methods, seaaled classes etc, and decompiling code to that degree, yuk.

     

    Personally I think this is just too much work. I would find it easier to write my own components. Forget the dynamic stuff, it is too much work, and I cannot see anyone getting eneough reuse from such effort.

    Tuesday, June 05, 2007 8:44 PM
    Moderator
  • CarlaC,

     

    Not sure if you got the answers you were looking for, it's been a while since you posted this message. However, I'm working on a similar scenario and have found the exact solution for creating a script task and embedding the code in it.

     

    To make this all work...

    1. Create a new package in the designer and add the script object (and associated code).

    2. Right-click on the package and select "View Code"

    3. Within the XML will be two "CDATA" tags, one starting with "<VisualStudioProject>" and the other starting with "' Microsoft SQL Server Integration Services Script Task".

    4. Go to the project you are creating to build your script task, I have chosen to add two string variables to the resource file which are used to hold the script in both tags mentioned above (i.e. "ScriptTaskCode" and "ScriptTaskProjFile").

    5. Once you have set the two properties in your resource file (or in the local code page) you can then use the following code to load the scripts into the script component:

    'NOTE: You will need a reference to the following in your class:

    'C:\Program Files\Microsoft SQL Server\90\DTS\Binn\Microsoft.SqlServer.VSAHosting.dll

    'C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.SqlServer.ScriptTask.dll

    'add a scripting task to the package

    scriptTaskHost = TryCast(Me.m_pkg.Executables.Add("STOCKTongue TiedCRIPTTASK"), TaskHost)

    scriptTaskHost.Properties("Name").SetValue(scriptTaskHost, "PkgUpdate")

    scriptTaskHost.Properties("Description").SetValue(scriptTaskHost, "PkgUpdate")

    'load the script task code from the resource file

    scriptTask = TryCast(scriptTaskHost.InnerObject, ScriptTask)

    scriptTask.SetUniqueVsaProjectName()

    scriptTask.CodeProvider.PutSourceCode("dts://Scripts/" & scriptTask.VsaProjectName & "/ScriptMain.vsaitem", My.Resources.ScriptTaskCode)

    scriptTask.CodeProvider.PutSourceCode("dts://Scripts/" & scriptTask.VsaProjectName & "/" & scriptTask.VsaProjectName & ".vsaproj", My.Resources.ScriptTaskProjFile)

    scriptTask.PreCompile = False

    'save everything

    ssisApp = New Application()

    ssisApp.SaveToDtsServer(Me.m_pkg, Nothing, "MSDB\" & Me.m_pkg.Name, serverName)

     

     

     

    Thursday, August 02, 2007 7:31 PM
  • Have any of you managed to create a Source Script Component programmatically.  I seem to only be able to create Transformation Script Components.  Thanks.
    Friday, September 07, 2007 3:37 PM
  •  

    I am working on the creating script component programmatically.

    The code has been created is shown in the following,

     

    Dim SourceCodeValue(3) As String

    Dim scriptPropType As PipeLineWrapper.IDTSComponentMetaData90 = _

    CType(CType(dataflowTask, TaskHost).InnerObject, PipeLineWrapper.MainPipe).ComponentMetaDataCollection.New()

    scriptPropType.Name = "Transform Property Type"

    scriptPropType.ComponentClassID = "{BF01D463-7089-41EE-8F05-0A6DC17CE633}"

     

    scriptPropType.Description = "Transform Property Type"

    Dim instance2 As PipeLineWrapper.CManagedComponentWrapper = scriptPropType.Instantiate()

    instance2.ProvideComponentProperties()

    SourceCodeValue(0) = "dts://Scripts/" & "ScriptComponent_9ddf9eba6f5b488f9710b95e4d7e7c74" & _

    "/" & "ScriptComponent_9ddf9eba6f5b488f9710b95e4d7e7c74" & ".vsaproj"

    SourceCodeValue(1) = My.Resources.ScriptComponentProjFile

    SourceCodeValue(2) = "dts://Scripts/" & "ScriptComponent_9ddf9eba6f5b488f9710b95e4d7e7c74" & _

    "/ScriptMain.vsaitem"

    SourceCodeValue(3) = My.Resources.ScriptComponentCode

    Dim anIDTSDesigntimeComponent90 As Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSDesigntimeComponent90 = _

    CType(instance2, Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSDesigntimeComponent90)

     

    anIDTSDesigntimeComponent90.SetComponentProperty("SourceCode", SourceCodeValue)

    anIDTSDesigntimeComponent90.SetComponentProperty("PreCompile", False)

     

     

    When debugger reach to “anIDTSDesigntimeComponent90.SetComponentProperty("SourceCode", scriptValue)”, NullReferenceException. “Use the “New” keyword to create an object instance” was shown.

     

    Could you please give me some help or suggestion?

     

    Thank you so much in the advance

    Tuesday, November 27, 2007 1:57 PM
  • Forget the previous message please, I have successfully created Script component.

     

    Thanks

    Tuesday, December 04, 2007 9:47 PM
  • Hello SSISGirl,

     

    Could you please tell me how you got this script to work? I'm facing the same problem.

    Thanks in advance

     

    robert.oh.

    Friday, February 29, 2008 9:09 PM
  •  

    Hi,

    I m new to SSIS programming . Can u pls help me how to add script component in dataflowtask programmatically for creating simple SSIS package.

     

    I have created Source component and Destination component. but dont know how to add script component.

    please help me

     

    Thanks and regards

    Suresh A M

    Tuesday, August 26, 2008 10:29 AM
  • Hi SSISGirl

    Could u pls send me the source code for creating script component and adding script component in dataflowtask.??

     

    Thanks in advance

    Suresh A M

    Tuesday, September 16, 2008 8:59 AM
  • Hi,

    I m new to SSIS programming. Can u pls help me how to change the date format using Design script programmatically.?

     

    i have created the source , script and destination components. now if i run the package , it is giving error as "The script component includes no code. Edit the component to include code."

     

    So please give me a idea how to add code in script component programmatically.

     

    Thanks in advance

    Suresh A M

    Thursday, September 18, 2008 10:36 AM
  • HI SSISGirl,

    Please ignore the last 3 messages.

    i have created script component successfully.

     

    I have one issue while mapping sript component's output column and destination component's input column.

    In script component , i have changed one column value , so that the output column name also will be changed. So now i  have to map the changed output column with the destination's input column.

    I would be great thankful if u assit me how to map the column.

    Thanks

    Suresh A M

     

     

    Monday, September 22, 2008 6:59 AM

  • Previous post are helpful, using that I could do the follwoing things

    My purpose is to input the error code and get the error description and give it to output
    this code i have written in Resources.ScriptComponentCode

    I have set the dataconversion components error output to script task and want to set the output to oledb command

    I don't know how to map the input columns to the output columns.


    IDTSComponentMetaData90 scriptTaskComponent =
                    ((dataflowTask as TaskHost).InnerObject as MainPipe).ComponentMetaDataCollection.New();
               
    scriptTaskComponent.Name = "Script Task Component";
    scriptTaskComponent.ComponentClassID = "Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost, Microsoft.SqlServer.TxScript, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91";            
    //scriptTaskComponent.ComponentClassID = "{BF01D463-7089-41EE-8F05-0A6DC17CE633}";

    managedOleInstanceScriptTaskComponent = scriptTaskComponent.Instantiate();
                
    scriptTaskComponent.InputCollection.New();
    scriptTaskComponent.OutputCollection.New();

    managedOleInstanceScriptTaskComponent.ProvideComponentProperties();

    String[] SourceCodeValue = new String[4]; 
    SourceCodeValue[0] = "dts://Scripts/ScriptComponent_0a95be63fd6042b384b7fc088509dabe/ScriptComponent_0a95be63fd6042b384b7fc088509dabe.vsaproj";
     SourceCodeValue[1] = Resources.ScriptComponentProjFile;   
    SourceCodeValue[2] = "dts://Scripts/ScriptComponent_9ddf9eba6f5b488f9710b95e4d7e7c74/ScriptMain.vsaitem";
    SourceCodeValue[3] = Resources.ScriptComponentCode;

    managedOleInstanceScriptTaskComponent.SetComponentProperty("SourceCode", SourceCodeValue);
    managedOleInstanceScriptTaskComponent.SetComponentProperty("PreCompile", true);

    can any one help me for mapping

    any help will be appriciate

    thanks lot

    Wednesday, December 31, 2008 12:16 PM
  • Adding the following gives me an Error:

    System.Runtime.InteropServices.COMException (0xC0204006): Exception from HRESULT: 0xC0204006

    at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.SetComponentProperty(String PropertyName, Object vValue)

    at CustomerImportSSIS.SSISPackage.ImportCustomerFile() in C:\Documents and Settings\XBBJP23\My Documents\Visual Studio 2008\Projects\CustomerImportSSIS\CustomerImportSSIS\SSISPackage.vb:line 403

    Code being Used:

    SourceCodeValue(0) = "dts://Scripts/ScriptComponent_bdd7797047d84251af056f789badef6f/ScriptComponent_bdd7797047d84251af056f789badef6f.vsaproj"
                SourceCodeValue(1) = My.Resources.ScriptComponentProjFile
                SourceCodeValue(2) = "dts://Scripts/ScriptComponent_bdd7797047d84251af056f789badef6f/ScriptMain.vsaitem"
                SourceCodeValue(3) = GenerateCode()
    
    instanceTransform.SetComponentProperty("SourceCode", SourceCodeValue)
    instanceTransform.SetComponentProperty("PreCompile", False)
    instanceTransform.ReleaseConnections()
    Not exactly sure what the problem is with this error? :( Can anyone help?
    I tried adding this as a scriptTask as well (see below) but not sure how to add the ScriptTask object for the sourcecode of the Script Transformation Component instance.

    'scriptTaskHost = TryCast(package.Executables.Add("STOCK:ScriptTask"), TaskHost)
    'scriptTaskHost.Properties("Name").SetValue(scriptTaskHost, "pkgScriptTransform")
    'scriptTaskHost.Properties("Description").SetValue(scriptTaskHost, "pkgScriptTransform")
    'scriptTask = TryCast(scriptTaskHost.InnerObject, ScriptTask)
    'scriptTask.SetUniqueVsaProjectName()
    'scriptTask.CodeProvider.PutSourceCode("dts://Scripts/" & scriptTask.VsaProjectName & "/ScriptMain.vsaitem", GenerateCode())
    'scriptTask.CodeProvider.PutSourceCode("dts://Scripts/" & scriptTask.VsaProjectName & "/" & scriptTask.VsaProjectName & ".vsaproj", My.Resources.ScriptComponentProjFile)
    'scriptTask.PreCompile = False
    Thank you in advance.
    Monday, July 20, 2009 9:51 PM
  • Hi, does anyone still have a code sample of how to put this all together.  I am not sure what to put into the two package resources.  Should it be the code from the script component?

     

     

    Friday, July 30, 2010 2:16 PM