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!
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?
|||
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
|||
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.
|||
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
|||
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?
|||
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);
|||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.|||
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.
|||
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.
|||
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.
|||
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("STOCK
CRIPTTASK"), 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)
|||Have any of you managed to create a Source Script Component programmatically. I seem to only be able to create Transformation Script Components. Thanks.
|||Hi everyone.
I got a problem with this type of programmation (i.e. programming a transform script component).
I think I did things like specified on this topic, I get no errors during the programmation, but when I open my package in design mode it says a 'NullReferenceException' on the script component ... and so I'm not able to view/modify it ...
My problem certainly comes from the source code. Here is the string array for setting the SourceCode property :
sourceCodeValue(0) = "dts://Scripts/" & "ScriptComponent_6f92b20c556448d1bfa78340623eb6d2" & _
"/" & "ScriptComponent_6f92b20c556448d1bfa78340623eb6d2" & ".vsaproj"
sourceCodeValue(1) = "<VisualStudioProject><VisualBasic Version = ""8.0.50727.791"" MVID = ""{00000000-0000-0000-0000-000000000000}"" ProjectType = ""Local"" ProductVersion = ""8.0.50727"" SchemaVersion = ""2.0"">" + _
"<Build><Settings DefaultNamespace = ""ScriptComponent_6f92b20c556448d1bfa78340623eb6d2"" OptionCompare = ""0"" OptionExplicit = ""1"" OptionStrict = ""1"" ProjectName = ""ScriptComponent_6f92b20c556448d1bfa78340623eb6d2"" ReferencePath = ""C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SqlServer.TxScript\9.0.242.0__89845dcd8080cc91\;C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SqlServer.PipelineHost\9.0.242.0__89845dcd8080cc91\;C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SqlServer.DTSPipelineWrap\9.0.242.0__89845dcd8080cc91\;C:\WINDOWS\assembly\GAC_32\Microsoft.SqlServer.DTSRuntimeWrap\9.0.242.0__89845dcd8080cc91\"" TreatWarningsAsErrors = ""false"" WarningLevel = ""1"" RootNamespace = ""ScriptComponent_6f92b20c556448d1bfa78340623eb6d2"">" + _
"<Config Name = ""Debug"" DefineConstants = """" DefineDebug = ""true"" DefineTrace = ""true"" DebugSymbols = ""true"" RemoveIntegerChecks = ""false""/></Settings>" + _
"<References><Reference Name = ""System"" AssemblyName = ""System""/>" + _
"<Reference Name = ""System.Data"" AssemblyName = ""System.Data""/>" + _
"<Reference Name = ""Microsoft.SqlServer.TxScript"" AssemblyName = ""Microsoft.SqlServer.TxScript""/>" + _
"<Reference Name = ""Microsoft.SqlServer.PipelineHost"" AssemblyName = ""Microsoft.SqlServer.PipelineHost""/>" + _
"<Reference Name = ""Microsoft.SqlServer.DTSPipelineWrap"" AssemblyName = ""Microsoft.SqlServer.DTSPipelineWrap""/>" + _
"<Reference Name = ""Microsoft.SqlServer.DTSRuntimeWrap"" AssemblyName = ""Microsoft.SqlServer.DTSRuntimeWrap""/></References>" + _
"<Imports><Import Namespace = ""Microsoft.VisualBasic""/></Imports></Build>" + _
"<Files><Include><File RelPath = ""ScriptMain"" BuildAction = ""Compile"" ItemType = ""2""/>" + _
"</Include></Files><Folders><Include/></Folders></VisualBasic></VisualStudioProject>"
'"<File RelPath = ""BufferWrapper"" BuildAction = ""Compile"" ItemType = ""2""/>" + _
'"<File RelPath = ""ComponentWrapper"" BuildAction = ""Compile"" ItemType = ""2""/>" + _
sourceCodeValue(2) = "dts://Scripts/" & "ScriptComponent_6f92b20c556448d1bfa78340623eb6d2" & _
"/ComponentWrapper.vsaitem"
sourceCodeValue(3) = "Imports System" + _
"\nImports System.Data" + _
"\nImports Microsoft.SqlServer.Dts.Pipeline" + _
"\nImports Microsoft.SqlServer.Dts.Pipeline.Wrapper" + _
"\nImports Microsoft.SqlServer.Dts.Runtime.Wrapper" + _
"\nPublic Class UserComponent Inherits ScriptComponent" + _
"\nPublic Connections As New Connections(Me)" + _
"\nPublic Variables As New Variables(Me)" + _
"\nPublic Overrides Sub ProcessInput(ByVal InputID As Integer, ByVal Buffer As PipelineBuffer)" + _
"\nIf InputID =" + componentSyntax.InputCollection(0).ID.ToString + " Then" + _
"\nEntrée0_ProcessInput(New Entrée0Buffer(Buffer, GetColumnIndexes(InputID)))" + _
"\nEnd If" + _
"\nEnd Sub" + _
"\nPublic Overridable Sub Entrée0_ProcessInput(ByVal Buffer As Entrée0Buffer)" + _
"\nWhile Buffer.NextRow()" + _
"\nEntrée0_ProcessInputRow(Buffer)" + _
"\nEnd While" + _
"\nEnd Sub" + _
"\nPublic Overridable Sub Entrée0_ProcessInputRow(ByVal Row As Entrée0Buffer)" + _
"\nEnd Sub" + _
"\nEnd Class" + _
"\nPublic Class Connections" + _
"\nDim ParentComponent As ScriptComponent" + _
"\nPublic Sub New(ByVal Component As ScriptComponent)" + _
"\nParentComponent = Component" + _
"\nEnd Sub" + _
"\nEnd Class" + _
"\nPublic Class Variables" + _
"\nDim ParentComponent As ScriptComponent" + _
"\nPublic Sub New(ByVal Component As ScriptComponent)" + _
"\nParentComponent = Component" + _
"\nEnd Sub" + _
"\nEnd Class"
sourceCodeValue(4) = "dts://Scripts/" & "ScriptComponent_6f92b20c556448d1bfa78340623eb6d2" & _
"/BufferWrapper.vsaitem"
sourceCodeValue(5) = "Imports System" + _
"\nImports System.Data" + _
"\nImports Microsoft.SqlServer.Dts.Pipeline" + _
"\nImports Microsoft.SqlServer.Dts.Pipeline.Wrapper" + _
"\nPublic Class Entrée0Buffer Inherits ScriptBuffer" + _
"\nPublic Sub New(ByVal Buffer As PipelineBuffer, ByVal BufferColumnIndexes As Integer())" + _
"\nMyBase.New(Buffer, BufferColumnIndexes)" + _
"\nEnd Sub"
For Each field As String In fieldArray
sourceCodeValue(5) = sourceCodeValue(5) + _
"\nPublic ReadOnly Property [" + field.ToString() + "]() As String" + _
"\nGet" + _
"\nReturn CType(Me(" + fieldArray.IndexOf(field).ToString + "), String)" + _
"\nEnd Get" + _
"\nEnd Property" + _
"\nPublic ReadOnly Property [" + field.ToString().ToString + "_IsNull] As Boolean" + _
"\nGet" + _
"\nReturn IsNull(" + fieldArray.IndexOf(field).ToString + ")" + _
"\nEnd Get" + _
"\nEnd Property"
Next
sourceCodeValue(5) = sourceCodeValue(5) + _
"\nPublic Sub DirectRowToisValid()" + _
"\nMyBase.DirectRow(" + componentSyntax.OutputCollection(0).ID.ToString + ")" + _
"\nEnd Sub" + _
"\nPublic Function NextRow() As Boolean" + _
"\nNextRow = MyBase.NextRow()" + _
"\nEnd Function" + _
"\nPublic Function EndOfRowset() As Boolean" + _
"\nEndOfRowset = MyBase.EndofRowset" + _
"\nEnd Function" + _
"\nEnd Class"
sourceCodeValue(6) = "dts://Scripts/" & "ScriptComponent_6f92b20c556448d1bfa78340623eb6d2" & _
"/ScriptMain.vsaitem"
sourceCodeValue(7) = "' Microsoft SQL Server Integration Services user script component" + _
"\n' This is your new script component in Microsoft Visual Basic .NET" + _
"\n' ScriptMain is the entrypoint class for script components" + _
"\nImports System" + _
"\nImports System.Data" + _
"\nImports Microsoft.SqlServer.Dts.Pipeline.Wrapper" + _
"\nImports Microsoft.SqlServer.Dts.Runtime.Wrapper" + _
"\nPublic Class ScriptMain Inherits UserComponent" + _
"\nEnd Class"|||Sorry I submited my post too fast.
Thanks to all of you for any kind of help.
Bobby
P.S.: Don't look at my english ;) I'm french and did my best :)
CRIPTTASK"), TaskHost)