Hi All,
I am new to SSIS. I am working in adding SSIS components programmatically. I have added Data flow task, Lookup transformation, OLEDB Source and OLEDB Destination.
Now, i am facing problems in adding Custom Script tranformation component programatically. Please help me out.
Venkatesh.
I am writing a custom dataflow destination that writes data to a named pipe server running in an external process. At runtime, in PreExecute, I check to see if the pipe exists. If it does not exist I want to be able to fail the component and gracefully complete execution of my package.
Question is, how do I do this? If I make a call to ComponentMetaData.FireError() that will only create an OnError event and not actually stop the execution. So, how to I halt the execution of my component and return the error message?
Thanks,
~ Gary
What I want to accomplish is that at design time the designer can enter a value for some custom property on my custom task and that this value is accessed at executing time.
View 10 Replies View RelatedHi,
I was trying the SSIS programming samples,that comes with installation of SQL Server 2005, I want to load the ChangeCase DLL in the toolbox.
The readMe file gives the direction. It does not work. It says you will see component in once you see DataFlow Task button( clicking Choose Items in the tools box).
I cannot load the custom component to the toolbox, Can I get some help.
Thank you,
Hi,
I am building a custom component have a IDTSCustomProperty90 property that can take the value 'True' or 'False'.
Depending on its setting, I want to include (or not include) a column in the output.
Any advice on how to go about doing this (with some sample code) would be much appreciated!
Here's how I'm declaring the property in ProvideComponentProperties()
IDTSCustomProperty90 IncludeErrorDesc = ComponentMetaData.CustomPropertyCollection.New();
IncludeErrorDesc.ExpressionType = DTSCustomPropertyExpressionType.CPET_NONE;
IncludeErrorDesc.Name = "Some Name";
IncludeErrorDesc.TypeConverter = typeof(Boolean).AssemblyQualifiedName;
IncludeErrorDesc.Value = Convert.ToBoolean(false);
Thanks in advance
-Jamie
I'm building a custom transform component. I want to mark some input columns as keys for deduplicating. In a similar way to the provided Sort component, I want to check those columns and allow pass-throughs (or not) for the others - so next to each input column name I need two checkboxes (1:use for dedupe; 2:include in output if 1 not checked). If a column is checked for use in the dedupe, I want some other attributes to be shown indicating how it will be used. How do I display the checkboxes to let users select which columns to include for deduplication, and then how do I add further attributes underneath (copying the Sort component's look) for selection?
Thanks in advance for guidance and pointers on this.
Hi all,
I saw a couple of other posts here on this topic, but none quite got to my issue.
I'm trying to add error output to a custom source component (not a script, a custom component). The samples all seem to deal with transform components, and my issues seem to be unique to source components.
I have the following code related to error handling ...
Public Overloads Overrides Sub ProvideComponentProperties()
...
Dim output As IDTSOutput90 = ComponentMetaData.OutputCollection.New
output.Name = OUTPUTCOLUMNNAME
output.ExternalMetadataColumnCollection.IsUsed = True
ComponentMetaData.UsesDispositions = True
output.ErrorRowDisposition = DTSRowDisposition.RD_NotUsed
output.ErrorOrTruncationOperation = "Something got truncated or blew up"
Dim errorOutput As IDTSOutput90 = ComponentMetaData.OutputCollection.New
errorOutput.Name = ERRORCOLUMNNAME
errorOutput.IsErrorOut = True
...
End Sub
Public Overloads Overrides Sub ReinitializeMetaData()
Dim output As IDTSOutput90 = ComponentMetaData.OutputCollection(OUTPUTCOLUMNNAME)
Dim outColumn As IDTSOutputColumn90 = output.OutputColumnCollection.New
outColumn.Name = strName
outColumn.SetDataTypeProperties(DataType.DT_I4, 0, 0, 0, 0)
Dim metadataColumn As IDTSExternalMetadataColumn90 = output.ExternalMetadataColumnCollection.New
metadataColumn.Name = outColumn.Name
metadataColumn.DataType = outColumn.DataType
metadataColumn.Precision = outColumn.Precision
metadataColumn.Length = outColumn.Length
metadataColumn.Scale = outColumn.Scale
metadataColumn.CodePage = outColumn.CodePage
outColumn.ExternalMetadataColumnID = metadataColumn.ID
outColumn.ErrorRowDisposition = DTSRowDisposition.RD_NotUsed
outColumn.ErrorOrTruncationOperation = "Something Truncated!"
outColumn.TruncationRowDisposition = DTSRowDisposition.RD_NotUsed
Dim errorOutput As IDTSOutput90 = ComponentMetaData.OutputCollection(ERRORCOLUMNNAME)
Dim errorColumn As IDTSOutputColumn90 = errorOutput.OutputColumnCollection.New
errorColumn.Name = outColumn.Name
errorColumn.SetDataTypeProperties(DataType.DT_I4, 0, 0, 0, 0)
...
End Sub
The confusions I have are:
a) the stock advanced properties editor (I haven't provided a custom one) doesn't seem to "realize" that I have an error output, so provides no method to configure. I am believing it would need to know which Output columns can have their error/truncation redirected. I'd have thought setting ErrorRowDisposition on my output column would have been enough to trigger this ??
b) since I don't have any means of configuring, not surprisingly, when I try to connect my error output, designer complains that, "Ths error output cannot receive any error rows. This occurs for several reasons: Input columns or output columns are not yet defined. Error handling is not supported by the component. Error handling is not configured for the component."
c) UsesDispoistions would seem to be appropriate only for a transform component
Thanks for reading, and appreciate any help or pointers.
Bill
A month or so ago I instigated this thread- http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=243117&SiteID=1 which talked about how to conditionally add a column to my component depending on the value of a custom property. If the custom property is TRUE then the column should appear in the output (and vice versa).
Bob Bojanic said I should use the SetComponentProperty() method to do this and that is working pretty well. However, it bothers me that SetComponentProperty() could be called, the column will then be added, and then the package developer could press 'Cancel'. In this instance the value of my custom property would be inconsistent with the presence of the extra column.
How do you get around that?
Thanks
Jamie
Hi all
I'm into a project which uses a lot of views for joining 2 or more tables. Using the MERGE component in SSIS will be a huge effort coz it only has 2 inputs and I gotta SORT the input too.
Isnt it possible to have a VIEW like component that joins more than 2 tables and DOESNT need sorting??
(I've thought about creating views in database engine but it breaks my data floe in SSIS and is'nt a practical solution)
I am writing a custom dataflow transformation component and I need to get the name of the preceeding component.
I have been trying to find a way to get a reference to the Package object, MainPipe object or IDTSPath90 object (connecting to the IDTSInput90 of my component) from my component because I think from there I can get to the information I want.
Does anyone have any suggestions?
TIA . . . Ed
Need to ask three questions regarding this component I am creating programatically:
1) How do you select "Modify the SQL statement" programatically.
2) How do you change the actual sql.
3) How do you add a parameter, also programatically.
I have manged to select "Enable memory restriction" by using
instance.SetComponentProperty("CacheType", 2);
but can't add a paramater and modify sql statement. Have used
instance.SetComponentProperty("SqlCommand", statement); and
instance.SetComponentProperty("SqlCommandParam", statement);
with no luck. Any ideas, have searched the web but found nothing.
Thanks.
Hi,
I've created a Custom Data Flow Component and added some Custom Properties.
I want the user to set the contents using an expression. I did some research and come up with the folowing:
Code Snippet
IDTSCustomProperty90 SourceTableProperty = ComponentMetaData.CustomPropertyCollection.New();
SourceTableProperty.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;
SourceTableProperty.Name = "SourceTable";
But it doesn't work, if I enter @[System:ackageName] in the field. It comes out "@[System:ackageName]" instead of the actual package name.
I'm also unable to find how I can tell the designer to show the Expression editor. I would like to see the elipses (...) next to my field.
Any help would be greatly appreciated!
Thank you
Hi,
I'm trying to enable Expression for a custom property in my custom data flow component.
Here is the code I wrote to declare the custom property:
public override void ProvideComponentProperties()
{
ComponentMetaData.RuntimeConnectionCollection.RemoveAll();
RemoveAllInputsOutputsAndCustomProperties();
IDTSCustomProperty90 prop = ComponentMetaData.CustomPropertyCollection.New();
prop.Name = "MyProperty";
prop.Description = "My property description";
prop.Value = string.Empty;
prop.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;
...
}
In design mode, I can assign an expression to my custom property, but it get evaluated in design mode and not in runtime
Here is my expression (a file name based on a date contained in a user variable):
"DB" + (DT_WSTR, 4)YEAR( @[User::varCurrentDate] ) + RIGHT( "0" + (DT_WSTR, 2)MONTH( @[User::varCurrentDate] ), 2 ) + "\" + (DT_WSTR, 4)YEAR( @[User::varCurrentDate] ) + RIGHT( "0" + (DT_WSTR, 2)MONTH( @[User::varCurrentDate] ), 2 ) + ".VER"
@[User::varCurrentDate] is a DateTime variable and is assign to 0 at design time
So the expression is evaluated as: "DB189912189912.VER".
My package contains 2 data flow.
At runtime,
The first one is responsible to set a valid date in @[User::varCurrentDate] variable. (the date is 2007-01-15)
The second one contains my custom data flow component with my custom property that was set to an expression at design time
When my component get executed, my custom property value is still "DB189912189912.VER" and I expected "DB200701200701.VER"
Any idea ?
1) We are writing a custome Source component for Oracle with OCI calls, Could some one please let me know how to Enable Error Handling for the Same,
2) Is it possible to write Custome Error Handeling Component for SSIS? if yes could you please help me on how to write it.
Thanks in advance.
hi all,
i am working on a small "Biztalk" engine, by creating dynamic ssis packages that change according to the client source file definition.
in order to create a row-number to each row in my input file, i am tryng to add the Konesans's Row Number component to the dynamic package by using SSIS API, but i get a lot of errors. the component is not created as a"rowNumber" component, but as a General Managed Component, though i use the ComponentClassID as the classID in the RowNumber component.
has anybody try to do this ?
is there any way to get the row number other then this way?
any ideas?
thank you!!! for all your help until now!!
Wanted to enquire how this is done. Tried doing it in the setUsageType method I have overwritten but only allows description to be changed. Basically need to change "Name".
Best option would be to change it instantly when a user selects a column from the inputs in the custom component, ie. it changes the Output Alias to a desired value. (Input tab in advanced editor)
All this is being done in a custom component which I would like to be synchronous, can achieve a similar result asynchronously.
Thanks
I've searched around for a while and can't seem to find out how/where I set the timeout on a bulk insert task programmatically. Here is a snippet of code that works as long as the input file is pretty small....when I attempted to pump 1.3 million rows through the default timeout value was not big enough and a timeout exception occurred.
Package loadPackage = new Package();
Stopwatch timer = new Stopwatch();
timer.Start();
ConnectionManager conMgr = loadPackage.Connections.Add("FLATFILE");
conMgr.ConnectionString = @"C:BWR.txt";
conMgr.Name = "FileInput";
conMgr = loadPackage.Connections.Add("ADO.NET:ODBC");
conMgr.ConnectionString = @"Data Source=FOO;Initial Catalog=DestinationDb;Integrated Security=True;";
conMgr.Name = "DestinationDB";
TaskHost task = loadPackage.Executables.Add("STOCK:BulkInsertTask") as TaskHost;
BulkInsertTask biTask = (BulkInsertTask)task.InnerObject;
biTask.UseFormatFile = true;
biTask.FormatFile = @"C:BWR_1.fmt";
biTask.SourceConnection = "FileInput";
biTask.DestinationConnection = "DestinationDB";
biTask.DestinationTableName = "StageBWR";
biTask.BatchSize = 100;
DTSExecResult result = loadPackage.Execute();
timer.Stop();
Console.WriteLine(string.Concat("Process took(ms): ", timer.ElapsedMilliseconds.ToString()));
Console.WriteLine(result.ToString());
if (loadPackage.Errors.Count > 0)
{
foreach (DtsError error in loadPackage.Errors)
{
Console.WriteLine(error.Description);
}
}
Again, the code above works just fine until we hit a timeout. There is no Timeout property on the BulkInsertTask class, nor can I see a way to set it on the Sql Connection. I don't even see them in the Connectionmanager.Properties collection for either the file connection or the database connection.
Am I overlooking the obvious somewhere?
In my console application code which creates and executes SSIS package I have this code.
Code Block
provider.ConfigString = loggingConnection.Name;
package.LoggingOptions.SelectedLogProviders.Add(provider);
package.LoggingOptions.EventFilterKind = DTSEventFilterKind.Inclusion;
package.LoggingOptions.EventFilter = new string[] { "OnPipelineRowsSent","PackageEnd" };
package.LoggingMode = DTSLoggingMode.Enabled;
I use a custom log component to log the events. However the "PackageEnd" event does not seem to get fired at all.
Am I missing something in this?
Thanks
Hi,
I need to add two oledbcommand tranformations in my package.
i add the commands like this:
-----------------------------------------
this.OledbCommand1 = this.dataflow.ComponentMetaDataCollection.New();
this.OledbCommand1.ComponentClassID = "DTSTransform.OleDBCommand.1";
this.OledbCommand1.Name = "ChangingColumnAttribute";
this.OledbCommand1.Description = "ChangingColumnAttribute";
CManagedComponentWrapper oledbinstance = this.OledbCommand1.Instantiate();
oledbinstance.ProvideComponentProperties();
------------------------------------------------------------
this.OledbCommand2 = this.dataflow.ComponentMetaDataCollection.New();
this.OledbCommand2.ComponentClassID = "DTSTransform.OleDBCommand.1";
this.OledbCommand2.Description = "UpdateHistoryColumnAttribute";
OledbCommand2.Name = "OleDBCommand2";
CManagedComponentWrapper oledbinstance1 = this.OledbCommand2.Instantiate();
oledbinstance1.ProvideComponentProperties();
-------------------------------------------------------------------------------------------------------------
i get the following error message during package validation:
OnError
ErrorCode -1073451000
Source DataFlow
SubComponent DTS.Pipeline
Description The package contains two objects with the duplicate name of "compone
nt "OLE DB Command" (719)" and "component "OLE DB Command" (701)".
Please advice.
Hi everyone,
I am building a search page and am a bit stuck.
The page has an sqldatasource with no select command and i am passing the select command and parameters depending on the optionsm selected.
If a tick box is ticked and a drop down selected i need to add a parameter to the datasource and generate the select command.
then if 1 and or 2 trext boxes are filled in, add the 1 or 2 select parameters then generate the select command.
The select command is correct as far as i can tell , but the parameters are not being passed to the datasource so it doesn't select anything.
my code behind is in the page load handler and at the moment only adds the parameters in one place so i could test.
Does anyone have any idea what i have done wrong?
Cheers
Chris
CODE BEHINDProtected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim SQLstr As String
If cb_Today.Checked = True And dd_Area.SelectedValue.ToString <> "" ThenLocManSearch.SelectParameters.Add("Area", TypeCode.String, dd_Area.SelectedValue.ToString())
SQLstr = "SELECT * FROM [LocMan_CV] WHERE ([area] LIKE '%" & dd_Area.SelectedValue.ToString() & "%') AND ([available] LIKE '%" & Date.Today & "%') AND ([viewable] = 'True')"
ElseIf cb_Today.Checked = False And dd_Area.SelectedValue.ToString <> "" Then
SQLstr = "SELECT * FROM [LocMan_CV] WHERE ([area] LIKE '%" & dd_Area.SelectedValue.ToString() & "%') "
ElseIf txt_FName.Text <> "" And txt_LName.Text <> "" And cb_Today.Checked = False And dd_Area.SelectedValue.ToString = "" Then
SQLstr = "SELECT * FROM [LocMan_CV] WHERE ([FirstName] LIKE '%" & txt_FName.Text.ToString() & "%') and [LastName] LIKE '%" & txt_LName.Text.ToString() & "%')"
ElseIf txt_LName.Text <> "" And cb_Today.Checked = False And dd_Area.SelectedValue.ToString = "" Then
SQLstr = "SELECT * FROM [LocMan_CV] WHERE ([LastName] LIKE '%" & txt_LName.Text.ToString() & "%')"
ElseIf txt_FName.Text <> "" And cb_Today.Checked = False And dd_Area.SelectedValue.ToString = "" Then
SQLstr = "SELECT * FROM [LocMan_CV] WHERE ([FirstName] LIKE '%" & txt_FName.Text.ToString() & "%')"
End If
Response.Write(SQLstr)
LocManSearch.SelectCommand = SQLstr
End Sub
SQL DATA SOURCE
<asp:SqlDataSource ID="LocManSearch" runat="server" ConnectionString="<%$ ConnectionStrings:MYLOCDEVConnectionString %>" >
<SelectParameters></SelectParameters>
</asp:SqlDataSource>
Hi
I am writing a custom transformation component that utilises a user variable.
Before using the variable at run time I am checking that the variable exists, but it would be nice to be able to add it if it does not. I cannot find any documentation on the subject, though I can see that the Variables class is derived from a ReadOnlyCollectionBase.
Is there a way to add a user variable with package scope from a custom component, either at run time or design time?
Thanks . . . Ed
I've run into this a second time now. I'm hoping for some resolution AND guidance for proper build, save, etc. protocols in BIDS.
I've coded a custom component, included post-build procedures to add it to the GAC, selected it from the "Choose Items..." menu, and successfully added to a data flow. I've then been able to succesfully debug it.
This was all Friday afternoon. I shut down my BIDS session(s) and called it a week. This morning when I open BIDS, I get the following error when I attempt to add it to a data flow task:
TITLE: Microsoft Visual Studio------------------------------The component could not be added to the Data Flow task.Could not initialize the component. There is a potential problem in the ProvideComponentProperties method.------------------------------ADDITIONAL INFORMATION:Error at Data Flow Task [DTS.Pipeline]: Component "component "" (16)" could not be created and returned error code 0x80131600. Make sure that the component is registered correctly.------------------------------Exception from HRESULT: 0xC0048021 (Microsoft.SqlServer.DTSPipelineWrap)------------------------------BUTTONS:OK------------------------------
What would explain this? I'm new to VS/BIDS. Am I improperly shutting down my BIDS sessions? Is a save required for my code? Does "Build <component>" not automatically save?
Also, when I re-build a component after making a change to it, what do I need to do in my package's Data Flow task? Delete the current component, re-add it, and configure it again? I'm sure there are certain things that require one type of re-initialization vs. others (i.e., making a change to the Design Time code vs. making a change to code within the ProcessInput method.)
What can I do to properly register this now? I'm dead in the water and have no idea what to do. Any help's appreciated.
Hi
I developed a €śdata flow source€? in my computer and it is ready!
It works in my computer, but how do I deploy this component in to the server? I have any programming environment on it.
Any Idea?
Hi
in the acquireconnection method Using the below statment I can get a connection Object
oledbConnection = cmado.AcquireConnection(transaction) as OleDbConnection;
from the connection object I can get the connectionstring from the object by calling
oledbConnection.connectionstring() property which will have all the details like DataBase, UserName & other Inofrmation but there is no password Info.
How to get the password Information, I need that information since I will use that info to make OCI calls to fetch the data from the Oracle database in m,y custome source component.
any help is much appriciated
thanks in advance.
Hi,I have developed a custom source component to get data from a generic odbc connection with some special caracteristics.
The component works fine by getting and mapping the output fields etc...
The only two problems existing are that when i run the task it says that the data flow has no components inside... how is this possible since i have my source mapped to a flat file inside that data flow?
This is the error:
SSIS package "BVEIT000D.dtsx" starting.
Warning: 0x80047034 at BVEIT000D_<EMPRESA> TXT, DTS.Pipeline: The DataFlow task has no components. Add components or remove the task.
Information: 0x4004300C at BVEIT000D_<EMPRESA> TXT, DTS.Pipeline: Execute phase is beginning.
SSIS package "BVEIT000D.dtsx" finished: Success.
The other problem is that if i want to <ignore> a certain source column the component already shows me an error saying that the no column with ID 0 was found...
Any one with experience in creating custom components?
Regards,
Hi huys,
can you tell me how you create a MSI file for your custom component.
basically i've created a SSIS custom component, and i want to distiribute among a few people, i guess instead of providing step by step instructions it would be easy if they just clicked on packaged msi file.
i've seen many people putting their custom components on the web as MSI, how do you do it?
cheers
Does anyone know how to get destination coulmns to show up in the advanced editor for a custom component? I have a custom flat file destination component that builds the output based on a specific layout. It works as long as the upstream column names match my output names. What I want is to allow non-matching columns to be mapped by the user as they can in a stock flat file destination. The closest that I have been able to come is to get the "column mappings" tab to show up and populate the "Available Input Columns" by setting ExternalmetadataColumnCollection.IsUsed to true on the input. The problem is that the "Available destination columns" box is always empty. I have tried the IsUsed property on the output and pretty much every other property that I could find. On the Input and Output properties all of my columns show up under the output as both External and Output columns. Is there a separate collection for "destination" columns that I can't find? It's getting a little frustrating, is this something that can be done or do I have to write a custom UI to make it happen?
Thanks!
Harry
I've been trying to figure this out on my own for pretty much all of today, and part of last week. I've downloaded samples, searched this forum, blogs, etc. So I figured I would post, since it's the end of the day, and I'm not much further along.
I'm working on a custom transformation component, whose main function is to use SQL encryption/decryption to encrypt/decrypt data from the input columns, into the output columns. The component needs two strings, a key name and a certificate name, as well as the connection manager it should use to connect to SQL which will do the encryption/decryption.
Here's where I'm stuck:
1) How can I provide the key/certificate names via properties? What I'm expecting/looking for is a way to add these two properties at the component-level, which would show up under the "Custom Properties" section of the properties pane (currently, this only has one property, "UserComponentTypeName"). These key/certificate values will be used for all input columns.
2) How do I access the connection managers from within the component? What is the best way to go about using a connection manager from within my component to connect to SQL and perform the encryption/decryption? In a custom task, this was fairly simple, but it seems that same concept won't work on a transformation component.
3) Is there a better way to go about accomplishing this (column encryption via SQL from within SSIS)? Am I going about this all wrong?
As I said, I've searched for direction, but there seems to be next to nothing in the regards of a good reference for creating custom transformation components. I've looked at two MS samples, but can't seem to make any sense out of them.
Thanks in advance.
Jerad
Hello.
I want to test my custom component with unit tests and i thought i must only initilize the component to play around with it. But when i calling the ProviderComponentProperties method and there the RemoveAllInputsOutputsAndCustomProperties method a NullReference exception is thrown. After debugging the test i had seen that the ComponentMetaData of the component is null. Is there a way to initilize the ComponentMetaData?
The Code of the Component looks like this:
Code Block
[DtsPipelineComponent(
DisplayName = "TestSourceAdapter",
ComponentType = ComponentType.SourceAdapter,
IconResource = "TestSourceAdapter.TestSourceAdapter.ico"
)]
public class TestSourceAdapter: PipelineComponent
{
public override void ProvideComponentProperties()
{
RemoveAllInputsOutputsAndCustomProperties();
IDTSOutput90 output = ComponentMetaData.OutputCollection.New();
output.Name = "TestSourceAdapter";
ComponentMetaData.OutputCollection
["TestSourceAdapter"].ExternalMetadataColumnCollection.IsUsed = true;
ComponentMetaData.ValidateExternalMetadata = false;
ComponentMetaData.UsesDispositions = true;
ComponentMetaData.Name = "TestSourceAdapter";
ComponentMetaData.Description = "TestSourceAdapter";
ReinitializeMetaData();
}
...
}
And in test i call only this
Code Block
TestSourceAdapter testAdapter = new TestSourceAdapter();
_testAdapter.ProvideComponentProperties();
i hope anyone can help
regards
kai
Hi all. Can you help me? I'm trying to build a custom component that recieves two datasources (like for instance the union all) . I first started by adding a new IDTSInput90 in the ProvideComponentProperties, but when I tried to use the component I got an error that has very helpful :
===================================
The component could not be added to the Data Flow task.
Could not initialize the component. There is a potential problem in the ProvideComponentProperties method. (Microsoft Visual Studio)
===================================
Error at Data Flow Task [Replica Transformation [1289]]: System.Runtime.InteropServices.COMException (0xC0048004): Exception from HRESULT: 0xC0048004
at Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSOutputCollection90.get_Item(Object Index)
at MyCustomSSISComponent.SampleComponentComponent.ProvideComponentProperties()
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProvideComponentProperties(IDTSManagedComponentWrapper90 wrapper)
===================================
Exception from HRESULT: 0xC0048004 (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
Program Location:
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HandleUserException(Exception e)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProvideComponentProperties(IDTSManagedComponentWrapper90 wrapper)
at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.ProvideComponentProperties()
at Microsoft.DataTransformationServices.Design.PipelineTaskDesigner.AddNewComponent(String clsid, Boolean throwOnError)
This is my ProvideComponentProperties:
Code Snippet
public override void ProvideComponentProperties()
{
RemoveAllInputsOutputsAndCustomProperties();
ComponentMetaData.UsesDispositions = true;
IDTSInput90 input = ComponentMetaData.InputCollection.New();
input.Name = "Staging Data";
input.ErrorRowDisposition = DTSRowDisposition.RD_FailComponent;
IDTSInput90 input2 = ComponentMetaData.InputCollection.New();
input2.Name = "Replica Data";
input2.ErrorRowDisposition = DTSRowDisposition.RD_FailComponent;
// Add the output
IDTSOutput90 output = ComponentMetaData.OutputCollection.New();
output.Name = "Replica Output";
output.SynchronousInputID = input.ID;
output.ExclusionGroup = 1;
// Add the error output
AddErrorOutput("StagingErrorOutput", input.ID, output.ExclusionGroup);
// Adds columns
AddXmlColumn();
IDTSOutputColumn90 column0 = ComponentMetaData.OutputCollection[1].OutputColumnCollection.New();
column0.Name = m_SyncStatusColumnName;
column0.SetDataTypeProperties(DataType.DT_STR, 1, 0, 0, 1252);
IDTSOutputColumn90 column1 = ComponentMetaData.OutputCollection[2].OutputColumnCollection.New();
column1.Name = m_AS400ImportedDateColumnName;
column1.SetDataTypeProperties(DataType.DT_DATE, 0, 0, 0, 0);
}
Since I'm new to SSIS I'm following Josh's SSIS Xmlify Data Flow Task sample.
Thanks.
Is there a way to evaluate an expression (like the derived column component) in a custom component? If so where should I look first? Is there an example?
An extremely simple sample is to put in an expression and evaluate one column and then add that to another column to create a new column. i.e. newcolumn = column1 + column2.
I realize that the derived column allows me to do this but I'm trying to figure out if it is possible to do this in a custom component without having to build my own expression evaluator.
Thanks!
-Thames
Imports System
Imports Microsoft.SqlServer.Dts.Pipeline
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Namespace SSISComp
<DtsPipelineComponent()> _
Public Class UpperCase
Inherits PipelineComponent
End Class
End Namespace
The above is fine, i.e. at least i get no IDE complaints. But, as soon as i try to make the attribute even remotely useful, i get Mr Squiggly and he no wanna go away. (and most of this is pulled from samples so I must be missing someyhing embarrasingly obvious:
This one will get you DisplayName is not declared
Namespace SSISComp
<DtsPipelineComponent(DisplayName = "Hello")> _
Public Class UpperCase
Inherits PipelineComponent
End Class
End Namespace
This one will get you Too many argumnets to Public sub New()
Namespace SSISComp
<DtsPipelineComponent(, , , "Hello", , , , , , )> _
Public Class UpperCase
Inherits PipelineComponent
End Class
End Namespace
So, what is the syntax for this
Note, we have a reference to Microsoft.Sqlserver.Pipelinehost (as well as dtspipelinewrap,dtsruntimerap,and managedts)
Clues please
Dave
I have looked at everything I could find on custom components, and I'm still confused about what has to be implemented in ReinitializeMetadata. The first custom component I'm trying to build is very similar to the Audit component, a derived column synchronous transformation that should be able to create any non-empty subset of four types of output columns.
I haven't found anything that does much with the inputs, the samples all seem to rely on RemoveInvalidInputColumns. I haven't found any substantial explanation of what this method actually does, or whether there are cases it doesn't handle. If it really handles all problems on the input side I don't need to know the details, but I'd like to hear some confirmation that this is the case.
For outputs, most of the sample projects seem to just call the base method. Some projects throw out all the output columns and create new ones to match the input columns one-to-one. Project Real throws away all the output columns and external metadata columns and creates a standard set of output columns with external metadata. The RegEx sample project uses information in a component custom property to tell it what output columns to expect, then adds missing columns and deletes unwanted columns. All of these approaches are using some reference information to inform it which outputs belong and which don't. What do you do when there is no other reference, just the output column and external metadata column collections?
The description for this method is "fix the errors found in Validate", but these projects just delete everything that wasn't perfect and recreate anything missing. In cases where the number and types of output columns require some user configuration, maybe a few custom properties, it seems a little high-handed to throw it all away and make them start from scratch. Or do you assume that this method isn't called unless the component state is really screwed up? It seems to me that simply adding or deleting input and output columns in the Advanced Editor would trigger ReinitializeMetadata because the external metadata would be out of sync. If it reacted by immediately re-adding an optional default column you deleted, or deleting an unconfigured column you just added, it would be pretty annoying.
Assuming that I wanted ReinitializeMetadata to actually fix things, I have some specific questions:
1 If an output column is not mapped to an external metadata column (or mapped to a nonexistent emc), should the output column be deleted or should a matching external column be created?
2 Same question reversed, if an external column is not mapped to any output column, should it be deleted or a matching output column created?
3 Would it make sense to look for compatible unmapped output and external columns and remap them?
4 If the user adds or deletes an output column in the advanced editor, was that an output column or an external metadata column?
And another thing, is there a definative list of which methods should be marked as not cls-compliant?