Progress Database ODBC Source

Hello,

I need to pull in data from a progress database into a table in SQL server 2005. I have an ODBC conncetion on the server where I am working.

I am new to SSIS but have used DTS. I need to succsefully pull the data into a table in SQL server 2005 from this progress database.

I have setup my ODBC connection but I cannot pull the data using this connection. The connection test succeeded for the connection manager. When I attempt to use the datasource reader and point to the ODBC Connection Manager entry I recieve Cannot acquire a managed connection from the run time connection manager

Any help is appreciated.

Thanks,

Tony

Hi Tony,

you need to configure your connection as ADO.NET for ODBC. DataReader source can only work with ADO.NET connections.

|||Try to create connection using .NET Provider\ODBC Data Provider I have tried it at my end it's working fine.

Programmatically get ScriptTask source code

Hi am trying without luck to load a package which contains a ScriptTask and read the source code of that task.

I can load the package and get the ScriptTask no problem.
However i am not sure how to get the source code.
I know i have to use the ScriptTaskCodeProvider and i assume the GetSourceCode() method.

This is what i have so far

ScriptTask scriptTask = taskHost.InnerObject as ScriptTask;
ScriptTaskCodeProvider codeProvider = new ScriptTaskCodeProvider();
codeProvider.LoadFromTask(scriptTask);
string sourceCode = codeProvider.GetSourceCode(scriptTask.VsaProjectName);

Any assistance greatly appreciated.

Cheers
Richard.

Application application = new Application();

Package package = application.LoadPackage(@."C:\Yukon\ISPlaying\ISPlaying\Package14.dtsx", null);

TaskHost taskHost = package.Executables[0] as TaskHost;

ScriptTask scriptTask = taskHost.InnerObject as ScriptTask;

ScriptTaskCodeProvider codeProvider = new ScriptTaskCodeProvider();

codeProvider.LoadFromTask(scriptTask);

string script = codeProvider.GetSourceCode(String.Format("dts://Scripts/{0}/ScriptMain.vsaitem", scriptTask.VsaProjectName));

Programmatically creating Transformation Script Component

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 = newString[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 asIDTSDesigntimeComponent90;

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 youprogrammatically 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 ofCreateTemporaryVCProject - 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("STOCKCRIPTTASK"), 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.

Programmatically creating Transformation Script Component

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("STOCKCRIPTTASK"), 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.

Programmatically creating Transformation Script Component

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("STOCKCRIPTTASK"), 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.

Programmatically creating Transformation Script Component

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("STOCKCRIPTTASK"), 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)

Programmatically creating Transformation Script Component

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("STOCKCRIPTTASK"), 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 :)

Programmatically creating Source Script Component

Does anyone know how to create a Source Script Component programmatically. I can only seem to create a Transformation Script Component. I have this:

PipeLineWrapper.IDTSComponentMetaData90 sourceComponent =
((dataflowTask as TaskHost).InnerObject as PipeLineWrapper.MainPipe).ComponentMetaDataCollection.New();

sourceComponent.ComponentClassID = app.PipelineComponentInfos["Script Component"].CreationName;

Is there anything I have to do extra to make it a source script component, seeing how it defaults to a transformation.

Try deleting all inputs. That should make it a source.

Thanks,

Bob

Programmatically configuring error and truncation dispositions for row redirection

Hi,

I have created a SSIS package programmatically using C#.

The package should do the following take data from source A, and place rows into destination B, if there are any error rows then redirect the rows to destination C. In my package I have the following components:

DTSAdapter.OLEDBSource.1 - Used as the Source

DTSAdapter.OLEDBDestination.1 - Used for the Destination Output - (let me call this normalOutput)

DTSAdapter.OLEDBDestination.1 - Used for the Destination Error Output - (let me call this errorOutput)

All my mappings appear to be correct, I build and save the package and receive a Successful validation and Success on Execution.

However, When I open the application using the Execute Package Utility I get the warning:

Warning:No rows will be sent to the error output(s). Configure error or truncation dispositions to redirect rows to the error output(s), or delete data flow transformations or destinations that are attached to the error output(s)

How do I get around this?

I have placed on the DTSAdapter.OLEDBDestination.1 (Used for the Destination Output), on the input collection I have placed:

normalOutput.InputCollection[0].ErrorRowDisposition = DTSRowDisposition.RD_RedirectRow;

normalOutput.InputCollection[0].TruncationRowDisposition = DTSRowDisposition.RD_RedirectRow;

normalOutput.OutputCollection[0].ExclusionGroup = 1;

on the DTSAdapter.OLEDBDestination.1 (Used for the Destination Error Output) I have placed:

errorOutput.OutputCollection[0].ExclusionGroup = 1;

However this does not work, I just get the wanring displayed above.

I have also tried to set the

OutputCollection[0].SynchronousInputID for both the error output and the normal output to the same values

so that:

normalOutput.OutputCollection[0].SynchronousInputID = normalOutput.InputCollection[0].ID

errorOutput.OutputCollection[0].SynchronousInputID = normalOutput.InputCollection[0].ID

However, the above scenario does not pass the package validation, in the Execute Package Utiltity, I get the wanring mentioned above and also the error:

Error: The input "OLE DB Destination Input" (16) has an invalid error or truncation row disposition.

So my question is what are the correct configuration settings to have in this scenario?

Thanks

Just looking at your code, I see a couple things to suggest. Where you are setting the Error and Truncation dispositions to redirect, I think that needs to be done on every item in the OutputCollection and not on the InputCollection. I also think you should not be setting anything for the ExclusionGroup.

I suggest you mock up what you're trying to create manually in BIDS and look at the Advanced Editor for OLE DB to get an idea for what properties need to be set where.

Also, in the beginning of your message you listed the same component for the normal and error outputs. Was that a typo?
|||

Thanks JayH, I ended up setting the ErrorRowDisposition to redirect on the InputCollection[0] of the NormalDestination (OLE DB Destination) and then creating an error path from the NormalDestination to the ErrorDestination (OLE DB Destination) .

This works for errors such as trying to place a varchar(20) column into a float column, but for some reason you are not allowed to set the TruncationRowDisposition to redirect at this level.

So, I went ahead a created a package on the VS IDE and set the TruncationRow dispositions to redirect on every column in the Source (OLE DB Source), and made a path from the error output to a ErrorDestination (OLE DB Destination).

and then... nothing...no redirection, when running the package using the Execute Package Utility, I see the warnings that a truncation will occur, but it just goes ahead and truncates the data in the column and placing the row into the NormalDestination and I get no rows redirecting to my ErrorDestination.

Is there something I am missing? Should be using a transformation component?

|||I think I'm missing the bigger picture. I'm not sure how a transformation component could help you. I'm imagining a single source OLE DB and two destination OLE DBs, one "normal" and the other "error".

You are correct that you can't set truncation disposition on an OLE DB destination. You can set an error disposition to redirect, but only if you're not fastloading. If there is an error when fastloading, the entire load will fail.

Your pipeline metadata should have the correct column definitions, and they should match your destination. Thus you should have no truncations or type mismatches detected at the destination. The place to detect truncations is on the source when the data is read and put into the pipeline. The only errors that should get detected at the destination are constraint violations.

If you'd like to send me code, my email is jay underscore hackney at hotmail dot com.
|||

No your spot on that is the final setup I had on Friday.

Point to note: I currently have my MaxInsertCommitSize set to 1000 which allows the fast loading.

Now back to the matter at hand, so, my package now has all the output columns on the source have both the ErrorRowDispositions and TruncationRowDispositions set to redirect and I've removed the ErrorDisposition from the "nomal" destination.

Now when I run the package I receive a truncation error, it should then perform the redirect but no rows are inserted and I get the following error message:

Error: There was an error with input column "City" (109) on input "OLE DB Destination Input" (16). The column status returned was "Text was truncated or one or more characters had no match in the target code page"

Let me break down the City column:

On the OLE DB Source is varchar(50)

On the "normal" destination is varchar(20)

On the "error" destination is varchar(MAX)

I want the truncation error to occur and redirect into the "error" destination where it should be inserted without any issues.

|||Regarding the MaximumInsertCommitSize, this does not enable any type of error redirection when fastloading. All it means is that instead of your whole load failing due to an error, only that 1,000 row chunk containing the error fails, and the rest of the load is considered successful.

Everything you're describing sounds correct to me. Have you saved the programmatically generated package and viewed it in the IDE to verify that everything was created correctly? Have you set the IsErrorOut on your error output?

|||Sorry. Have to correct myself. The failed chunks do get redirected.
|||

Thanks for the information on the fastloading, for the time being I have switched it off...

Regarding the the IsErrorOut field on the error output that is... (source.OutputCollection[1].IsErrorOut), it is already set to true by the framework.

Yes, the package is saved and i've looked at it in the IDE to check it and everything is fine:

However on executing the package I am left with:

Error: An OLE DB error has occurred. Error code 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Descriptioni "Invalid character value for cast specification" Information: The final commit for the data insertion has ended.

Error: There was an error with input column "City" (108) on input "OLE DB Destination Input" (29). The column status returned was: "Text was truncated or one or more characters had no match in the target code page.".

Error: the "input "OLE DB Destination Input" (29)" failed because error code 0xC0209078 occurred, and the error row disposition on "input "OLE DB Destination Input" (29)" specifies failure on error. An error occurred on the specified object of the specified component.

The strange thing is the "OLE DB Destination Input" the error refers to in the last line belongs to the "source" component. However, this should be left to "Fail component", because the redirections have been set on the output items.

Looking at the External and Input columns, on the error destination the DataTypes for the External columns are DT_TEXT and on the Input columns they match the type and length of the "normal" Destination OutputColumns which seems correct to me.

I'm at a loss....

Can you advise of any good books on programming SSIS (and handling error outputs!) using C#?

|||

Ok, I remade the new package with the VS IDE, with only one mapping on the City column.

I map the "source" city (varchar(50)) to "destination" city (varchar(5))

I map the "source" city (Error output - varchar(50)) to "error destination" city (varchar(MAX))

I set the the errorRow and truncationRow dispositions on the "source" on the city column to RedirectRow.

I then run the package and get the warning that a truncation could occur on the column city, but the package just runs through and places all the rows into the destination table, no rows are redirected - (Note that there are is data in the source which should be redirected).

Looking at the "destination" all the truncations have gone ahead leaving me with 5 character strings in the City column.

Surely I must be doing something wrong here?

|||No books that I know of. Darren probably has the most experience doing this type of stuff, but he apparently hasn't seen fit to comment.

Are you controlling your source data for the testing? Do you know that there aren't some invalid characters causing this error?

I think the best way to determine what your code should be doing is by comparing it to the XML of a package created in the IDE. You may also need to simplify your package so you can isolate components until they are working correctly. Maybe you should consider just using dead end components like Unions instead of OLE DB destinations for your normal and error outputs.

These are the disposition-related settings I think you should be using for your Source component. Are you setting the UsesDispositions on your source component?

OLE DB Source Component
UsesDispositions=True

NormalOutput
IsErrorOut=False
ErrorOrTruncationOperation=""
ErrorRowDisposition=RD_NotUsed
TruncationRowDisposition=RD_NotUsed

Normal OutputColumn
ErrorOrTruncationOperation="Conversion"
ErrorRowDisposition=RD_RedirectRow
TruncationRowDisposition=RD_RedirectRow

ErrorOutput
IsErrorOut=True
ErrorOrTruncationOperation=""
ErrorRowDisposition=RD_NotUsed
TruncationRowDisposition=RD_NotUsed

Error OutputColumn
ErrorOrTruncationOperation=""
ErrorRowDisposition=RD_NotUsed
TruncationRowDisposition=RD_NotUsed

|||

Yep, I already had a windiff moment with it to find any differences between a IDE made package and a dynamically created one, thats how I've ironed out initial issues.

I've also used the example shown here:

http://blogs.conchango.com/jamiethomson/archive/2005/08/08/1969.aspx

Setting up the whole package in the IDE and using my database tables instead, mapping just the city column and no other columns, and all the happens is I get a warning about the imminent truncation and the truncation goes ahead. So I get nothing in my error destination.

This is getting me extremely fustrated, is there anyone who has an example of setting up a simple TruncationRow redirect?

Jamie Thompson could you knock one up?

|||I think you just said that if you create a package in the IDE (using your tables), that you can't get the redirection to work there either? That's the second indication I've heard that this may be a different problem.

Let's try to establish a baseline of functionality by creating a simple package in the IDE. If you have AdventureWorks installed, try it with that database first to take your data out of the equation. If not then just use your own table.
OLE DB Source in table mode that loads the Person.Address table from the AdventureWorks database. On the columns tab, select only the City column (it is an nvarchar(30))
On the Error Output tab, set the error and truncation dispostions to redirect row Close the Source, right-click it, and select "Show Advanced Editor" On the "Input and Output Properties" tab, open "OLE DB Source Output", open "Output Columns" Select the City column and change the Length property to 10 and click OK. The Source will now warn about truncation.
Drop two Union All components on the data flow surface, connect one to the normal (green) output from the source, and the other to the error (red) output.|||

Mr JayH you are a godsend.

From reading your points the problem with the my package was made clear in the line:

Select the City column and change the Length property to 10 and click OK. The Source will now warn about truncation.
|||

Just incase anyone else runs into this problem here is my final mappings code for the "Source".

private void CreateMappings()

{

// Map OutColumns to there external metadata columns for my mappings

foreach (SSISMapping mapping in dtsMappings)

{

IDTSOutputColumn90 outputColumn = component.OutputCollection[0].OutputColumnCollection[mapping.ToColumn.Name];

IDTSExternalMetadataColumn90 exMetaDataColumn = component.OutputCollection[0].ExternalMetadataColumnCollection[mapping.FromColumn.Name];

componentInstance.MapOutputColumn(component.OutputCollection[0].ID, outputColumn.ID, exMetaDataColumn.ID, true);

outputColumn.TruncationRowDisposition = DTSRowDisposition.RD_RedirectRow;

outputColumn.ErrorRowDisposition = DTSRowDisposition.RD_RedirectRow;

// Note that this must come AFTER the mapping because otherwise the properties will

//be mapped to the ExternalMetaColumn's properties

outputColumn.SetDataTypeProperties(SSISUtilities.GetDataType(mapping.ToColumn.DataType),

mapping.ToColumn.Length,

mapping.ToColumn.Precision,

mapping.ToColumn.Scale,

SSISUtilities.DEFAULTCODEPAGE);

}

}

Note that SSISUtilities and SSISMapping are not part of the framework.

Programmatically changing shared data sources when running SQL Reporting Service 2005 Repo

Can someone please help me or point me to the right sources on how I can possibly programmatically change the shared data source that was assigned to an RDL when a user runs it from my asp page? I have to create several reports and would like to have the option to let users choose the sql server to connect to and possibly different login credentials. What should I do?

Please help.

Hope these articles can help you -

http://msdn2.microsoft.com/en-us/library/ms252085(VS.80).aspx

http://www.aspfree.com/c/a/ASP.NET/Binding-Data-to-the-ReportViewer-Control-Dynamically-in-ASPNET-20/

Programmatically changing a DataReader's SQL statement

Hi,

I was wondering how I could dynamically change the SQL statement (ie the SQLCommand property) of a DataReader source.

The source is reading an IBM AS400 file, with the SQL statement:
SELECT itrloc, itrdat, itrcen, csreg#, cstrn#, csseq#, inumbr, cstime, itrqty, itrret, itrreg, itrcst, idept, isdept, iclas, isclas, csdosp, cmcust, csacct, salit, cstntp, csldgr
FROM clib.scsaltrn
WHERE itrdat between 50901 and 50931

I want to programmatically change the date figures on the end (50901 and 50931) each time the package is run.

I have tried to use variables in place of the date figures but this didn't work. I've also thought of using the Script task to programmatically modify the DataReader source, but this doesn't seem possible.

Any ideas on how I could achieve what I'm trying to do?Switch back to the control-flow, select the data-flow in which your datareader source resides and press F4. You should be able to see, in the "Misc" section of the properties pane, a property called:
[<DatareaderSourceComponentName>].SQLCommand
This refers to the thing you want to change.

You can set an expression on this property to set it to whatever you want.

If you need any help in doing that, just reply here!!

-Jamie|||

Jamie,

i set an expression on the SQL script to run it works fine on the first iteration (of a for loop), second time arround it throws an error

Error: 0xC0208380 at Run Extract, Run SQL On SPAN [147]: The output column "membernumber" (1304) does not have a matching source column in the DataReader schema.

Error: 0xC0208380 at Run Extract, Run SQL On SPAN [147]: The output column "effectivedate" (1307) does not have a matching source column in the DataReader schema.

Error: 0xC0208380 at Run Extract, Run SQL On SPAN [147]: The output column "salaryamount" (1310) does not have a matching source column in the DataReader schema.

Error: 0xC0208380 at Run Extract, Run SQL On SPAN [147]: The output column "salaryamount2" (1313) does not have a matching source column in the DataReader schema.

Error: 0xC004706B at Run Extract, DTS.Pipeline: "component "Run SQL On SPAN" (147)" failed validation and returned validation status "VS_NEEDSNEWMETADATA".

here it's refering to column names that was in the first SQL statement.

is there a way to reset the settings of the components on every execution?

|||

If you are using a component in a loop, the metadata of the component must exactly match the metadata of the external sources for each iteration. If you external metadata is different, you will need a different DataReaderSrc component.

Thanks
Mark

|||

Mark,

what i'm trying to do is, i have a table which have two fields,

1. [sql script]

2. [destination table]

have about 25 records.

basically i got the table into a result set (ADO variable), then trying to iterate through the recordset.

here each sql script is different from one another (different meta data), and each script has a corresponding destination table (I used a OLE DB destination, set it as a variable)

so how can i tackle this problem if you have different meta data in source(Note: source field names are identical to destination fields)

my source database, data provider is ODBC and i cannot use OLE DB source. (and i have another iteration to go through many databases which works fine)

any solution to this problem...?

please help!!?

cheers

AJ

|||How do I do this ?|||

Andrew wrote:

How do I do this ?

Instructions are above.