Ok, so I've looked near and far and have found nothing but info that says data flow properties can not be changed at runtime....then I see in this in the SSIS documentation under ADO.NET Source Custom Properties:
View 15 RepliesHi,
I am creating a custom transformation component, and a custom user interface for that component.
In
my custom UI, I want to show the custom properties, and allow users to
edit these properties similar to how the advanced editor shows the
properties.
I know in my UI I need to create a "Property Grid".
In
the properties of this grid, I can select the object I want to display
data for, however, the only objects that appear are the objects that I
have already created within this UI, and not the actual component
object with the custom properties.
How do I go about getting the properties for my transformation component listed in this property grid?
I am writing in C#.
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
Does anyone happen to know if it is possible to set a custom data flow component property to be non-browsable? I have a number of custom component properties, and would prefer that they only be updateable through my custom UI as opposed to via the property grid on the SSIS designer,
thanks
I am attempting to set my custom properties in the UI I have created for my custom transformation. I can access them in the ProcessInput, but if I try to assign them a new value in my UI the values dont change.
I set the properties up in ProvideComponentProperties
public override void ProvideComponentProperties()
{
// Perform component setup operations
ComponentMetaData.UsesDispositions = false;
// Add Input
IDTSInput90 input = ComponentMetaData.InputCollection.New();
input.Name = "TrimInput";
input.ErrorRowDisposition = DTSRowDisposition.RD_NotUsed;
// Add Output
IDTSOutput90 output = ComponentMetaData.OutputCollection.New();
output.Name = "TrimOutput";
output.SynchronousInputID = input.ID;
IDTSCustomProperty90 Trimproperty = ComponentMetaData.CustomPropertyCollection.New();
Trimproperty.Name = "Trim Values";
Trimproperty.Description = "Selected Trim Values";
IDTSCustomProperty90 Colproperty = ComponentMetaData.CustomPropertyCollection.New();
Colproperty.Name = "Col Names";
Colproperty.Description = "Column Names";
}
btn Click event in UI form
IDTSCustomProperty90 Trimproperty = _dtsComponentMetaData.CustomPropertyCollection["Trim Values"];
Trimproperty.Value = sConcatTrim;
IDTSCustomProperty90 Colproperty = _dtsComponentMetaData.CustomPropertyCollection["Col Names"];
Colproperty.Value = sConcatColNames;
Any suggestions or please point me to any example would be greatly appreciated.
Hi everyone!
How do I create my own descriptions as shown in the buttom of the Properties pane in Visual Studio 2005 when I create a Custom Task?
Thanks in advance!
Hello to everyone, I've a question about UDTs and the way I can use them to access tables and columns where they are applied in a SQL Server 2005 DB.
I've already spent 2 days googling and MSDN reading but nothing helped me to solve my problem, thats why I'm posting it here (this is the second post, maybe the last one was in the wrong Forum).
The scenario follows:
I've created a UDT called MyUDT that exposes 2 properties MyTable, MyColumn, here its the code:
[Serializable]
[SqlUserDefinedType(Format.UserDefined, IsByteOrdered = true, MaxByteSize = 8000, Name = "MyUDT")]
public class MyUDT : INullable, IBinarySerialize
{
private string _myTable;
private string _myColumn;
....
/// <summary>
/// Set or Get the Table Name where the UDT is applied.
/// </summary>
public string MyTable {
get { return this._myTable; }
set { this._myTable = value; }
}
/// <summary>
/// Set or Get the Table's Column Name where the UDT is applied.
/// </summary>
public string MyColumn
{
get { return this._myColumn; }
set { this._myColumn = value; }
}
....
}
And here it's my question/s:
How can I expose the defined Properties (MyTable, MyColumn) in order to be directly used from
SQL Server Management Studio within the Column Properties Panel?
Pls take a look to the print screen placed below:
[img=http://img81.imageshack.us/img81/7193/untitledip1.th.jpg]
If it is not possible, is there a way for any UDT to get back from the sql server execution context
the table and the column where it is applied/used?
I need to solve that in order to later retrieve via SQL the Extended Table Properties where the UDT is used
and make some work on presented MetaData. Thanks in advance, every answer/help will be very much appreciated.
I'm building a custom component and UI and am a bit confused on where I
need to create and/or set custom column
properties?
My UI will have a datagrid with three
columns: 1) a check box to select a column for use by the component, 2)
the input column name, and 3) a "differentiator" checkbox that indicates
an extra property about some of the columns that have the first column
checkbox checked (For example, my component may be using five input columns, but
three of those need to be used in a slightly different
way.)
The problem is, I don't understand when or
where I'm supposed to create the custom property for the input
column. SetUsageType is where I've been thinking, but I don't
know if I'm supposed to be creating it for an input column or a virtual
input column. I'd appreciate any guidance.
Hi,
I saw some thing called custom properties for the "Derived transformation" in the msdn site. I tried to use them in a simple package, but I am getting an error as "can't write to derivedoutputcolumnname.friendlyexpression". Friendly expression is one of the custom properties available for the derived transformation output columns.
The steps I followed to get to this error are as follows:
1) Get data from a table using OLEDB Source. Suppose I am getting firstName, LastName etc.
2) Derived column input is values from the above OLEDB Source.
3) I have added a new column called "Concatenated name" which is concatenated value of first and last names.
4) Then in the properties editor of this data flow task in expressions option I clicked on ellipse available. I got an editor for property expression, which contained two columns called "Property" and "Expression". Property column contains dropdown with friendly expressions propety for the derived columns and expression column is a text box, where in we can enter expression to be evaluated for the corresponding friendly expression property.
5) Now when I click on OK and try to debug it gives an error as "Can't write to concatenatedname.friendlyexpresiion".
If anybody has already faced this problem and solved it please let me know, because I am struck here a long time.
Thanks&Regards,
Sreekanth Ammisetty
Is there a way of disabling a custom property in a component so that during design-time the property is grayed out? I looked around the properties of IDTSCustomProperty90 and nothing sticks out.
Thanks
Mike
I have a custom component that takes in unicode stream and converts it to ascii text. However I would like to make my default string length and code page editable in the standard GUI editor. Right now I can set the default to 1000 characters, but when I try to change it, it says "Property value is not valid"
Any ideas?
Thanks!
Ive been using SSIS for a month or two and now find I need to create some custom tasks to perform some performance logging. in the the overloaded ProviderComponentProperties section I am trying to create a property which has the same look as the Expressions properties you find elsewhere (Little + on the left and a group of sub properties when expanded).
Ive have played with creating a IDTSCustomPropertyCollection90 collection then adding my sub properties to it but I cant seem to then add my new collection to the ComponentMetaData.CustomPropertiesCollection.
Im assuming the Expressions parameter is a collection added to the properties collection but I cant figure out how. Any help would be much appreciated.
Hello All Experts,
I have created one custom task with PropertyGrid Control and two button on it. I have everything under one class library project.
Problem I am facing is when i load task and clik on Edit I can not see those properties into that GUI and even functionlity of those two buttons (OK and Cancel) not working but I am able to see those properties in default property window.
If I create this GUI as a seperate window application then I am able to see those properties in GUI and buttons also working but in SSIS I am not able to load the task.
After reading on internet about SSIS they suggest to create everything under one project which I did.
Basically I am trying to populate connection managers like Source Connection and Destination Connection when I load this task and there are much more backend functionlity but at first step i m stuck and not able to see those properties in GUI.
Please help and give your input on it. I was following "Increment Task" example given by MSDN.
If you need more info let me know.
Thanks
Hello,
I'm working on a custom dataflow destination component. It makes use of the External Metadata Collection. I also use Custom Properties with the external metadata collection.
When I open the destination component using the Advanced Editor, and select an External Metadata Collection and change the Custom Property it always changes back to the original value.
Additionally the method SetExternalMetadataColumnProperty never gets called.
Here is a little Test Component that surfaces the problem:
[DtsPipelineComponent(ComponentType=ComponentType.DestinationAdapter, DisplayName="Test Destination")]
public class Class1 : PipelineComponent
{
public override void ProvideComponentProperties()
{
base.ProvideComponentProperties();
ComponentMetaData.InputCollection.RemoveAll();
IDTSInput90 input = ComponentMetaData.InputCollection.New();
input.Name = "Name";
input.ExternalMetadataColumnCollection.IsUsed = true;
IDTSExternalMetadataColumn90 ext = input.ExternalMetadataColumnCollection.New();
ext.Name = "ExtName";
IDTSCustomProperty90 customProp = ext.CustomPropertyCollection.New();
customProp.Name = "Custom Property";
customProp.Value = "Hallo";
}
public override IDTSCustomProperty90 SetExternalMetadataColumnProperty(int iID, int iExternalMetadataColumnID, string strPropertyName, object oValue)
{
return base.SetExternalMetadataColumnProperty(iID, iExternalMetadataColumnID, strPropertyName, oValue);
}
}
Any ideas how to make that work?
Georg
I implemented a custom source adaptor. I want to be able to associate custom properties with each of the output columns. I want them to be passed downsteam. The idea is to be able to retrieve these information in a downstream custom transformations of ours and process the various columns accordingly. How do I go about doing this?I noticed that the IDTSCustomProperty90 seems to have a local scope only.
View 1 Replies View RelatedI am converting the contents of 64 lookup tables from individual tables (each called lookup_xxxxx) into a single LookupReference table. The individual lookup tables are my OLE DB Source objects. I want to derive the variable part of the lookup_xxxxx table name from the OLE DB Source 'OpenRowset' property as a variable and make it into a derived column (which will be the lookup Type column in the output table). For example, extract "SpeciesType" from the input source called 'lookup_SpeciesType' and put it into the derived column.
I cannot find a System variable that refers to the input data source. Does anyone know how I can do this?
Any help much appreciated. Thanks
HI,
I need to open a File through File connection manager and want to assign these file properties to SSIS precreated varibale or Newly created varibale. I want to show file properties in Propertygrid. Properties grid will conatin File Propeties Column and SSIS varibale Combobox column. The combo box will contain New variable field. When user select New Variable field, then a new SSIS varibale window will open and we can able create New variable and that Newly created variable should add to that property comboBox.
For Instance if we create a new varibale Name "Creationdate" by clicking on New Varible in ComboBox, then that CreationDate variable should add to Property ComboBox in PropertyGrid. After adding when we select that variable Name "Creationdate" then that selected file Creation date should assign to SSIS varibale "Creationdate" field.
Any Comments or sample will help me.
Nitin
Hi everyone
I need help with my data reading source which is XML Source. I have a variable which specifies the URL that the XML needs to be grabbed from and I am generating the xsd based on the contents of the URL.
Now as long as the URL has the data which is coming from webservice, it works fine. But if the URL is not correct or need additional information. The XML Schema changes to let the user know that "city" information for example is needed. This message and XML is completely different if the URL sends the required data.
I want to know how to send an error message or some kinda of message so that another activity would be done if the URL needs the city information.
I tried placing an XML source another one, for that error message. But because thats one line, just asking for city information. XML souce has no columns in it. So I have a feeling that even before getting into the XML source... data in the XML file needs to be checked. And how can I do taht I dont know..
Please help me with this
Thanks
-Sarah
As the title states I have a 64bit DSN but when I try to create ODBC datasource in Reporting Services it does not appear in the dropdown box. I have created as a system DSN.
SQLServer 2008R2
Reporting Services 2008
Everyone,
I've been fighting a problem all day now. I have given in. I need help.
Here's the problem. I have a SSIS Custom Data Flow Source, for retrieving data from a proprietary data format. The component works great. I can use the Advanced Editor to select the connection to use (also a Custom Connection Manager) and a custom property. The custom property that is the name of a table to select data from.
I am in the process to building a simple UI that has a ComboBox of Connections (by name) and a ListBox of table names available in that connection.
I want to be able to pre-select the connection in the ComboBox if one is already populated. The problem is, I can't figure out how to get access to the connection name of the currently selected connection. When I look at the RuntimeConnectionsCollection[0], that connection is not acquired. I can't get access to the name, or any of its custom properties (see above that connection is Custom also).
Assume I use the Advanced Editor to select a connection to use, then open the custom UI.
How do I figure out what the name of the connection selected is? I know it is possible. The OLE DB Source does it.
Thanks,
Frank
I am currently writing a custom source adapter that extracts data from a JD Edwards OneWorld system. In the custom user interface of the source component I need to allow the user to set a query (a custom property), and then refresh a list of output columns that will be extracted based upon that query (similar to the list shown in the advanced editor).
My question is, can I apply the custom property change to the component and build my output column list without closing and restarting my custom UI form? I understand that the IDTSComponentUI interface being implemented allows for transactional editing of the component, in that the changes to the component are not applied until I have returned a result in the implemented Edit() method. However is there a way to apply changes without returning this result (and closing my UI)?
Essentially I am looking to have similar behaviour to that of the 'Refresh' button in the advanced component editor form.
Thanks
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,
I'm having alot of trouble figuring out the proper way to log from inside my custom source adapter.
I couldn't find my useful information in MSDN, it mostly applies to logging from inside a script task.
I'd like to log my messages along with all the other SSIS package log entries, which I have going to the dts log table.
I'm assuming I should be using some functionality from Microsoft.SqlServer.Dts.RunTime, probably the LogProvider.
Can anyone advise?
I need VB.NET source code showing how to create a Custom Component. in SSIS (All the ones I've seen are written in C#.)
TIA,
barkingdog
I programmed a Source extension, that accesss a text file. In my code I have the following code to open a file:
protected override void reader(PipelineBuffer buffer)
{
TextReader reader = File.OpenText(_filename);
The komponnet works well. How do I grant permissions to access this text file? Because I got problems when I try to run my package using a task created in the SQL Server Agent.
For the Custome source Component ErrorOutput, should I go for asynchronous / synchronous Output.
If i go for synchronous output
// Create the error output.
IDTSOutput90 errorOutput = ComponentMetaData.OutputCollection.New();
errorOutput.IsErrorOut = true;
errorOutput.Name = "ErrorOutput";
errorOutput.SynchronousInputID = What Id is required here;
errorOutput.ExclusionGroup = 1;
Is it the IDTSOutput90 InPut.ID / OutPut.ID which should be assigned.
Thanks Regards
Anil
I would like to write a custom data source component for SSIS. I was wondering if there are any tutorials / examples for this that are available.
Thanks
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
Hello all ...
I'm looking at writing some customized insert, update and delete stored procs for a replication target. For various reasons I would like to write a "one size fits all" custom stored proc for each of these tasks.
It looks like I can get the data values passed as parameters just fine.
I was wondering if there's a way to also pass the source schema and table name as parameters, or to determine these on the fly in my all purpose stored procs. Some replication products refer to these types of values as "tokens" that can be included in the replication data stream sent to the target.
I can adjust the source database replication publications, and article definitions, but I cannot modify the actual source database tables to include these as values in data columns. It is possible a view that contains these elements as strings might fly, but I was hoping to avoid cluttering the source database.
A handy trick or technique would be helpful!
Thanks!
DB
[One day I will come back here and answer questions, rather than only ask.]
I have a custom connection manager that returns an OleDbConnection. The only thing "custom" about the connection manager is how it determines its connection string. The object returned in AcquireConnection is an OleDbConnection.
Code:public override object AcquireConnection(object txn)
{
UpdateConnectionString();
OleDbConnection oleDbConnection = new OleDbConnection(this.connectionString);
oleDbConnection.Open();
return oleDbConnection;
}
Some of the objects within SSIS only work with an OleDbConnection, but mine is not recognized as such. Is there any way to have my connection recognized as a valid connection?
Thanks in advance.
-bms
I would like to fire a pre execution event, grab the name of the stored procedure (source of the sql task), insert a record with the name and datetime, and then fire a post event that would update the record with a modified dated.
What is the best way to capture the source value name in the execute sql task.
Hi,
I'm developing a Custom Report Item and would like to fill my properties dialog in design mode with real data. I have the name of the data source and the command text for the corresponding report dataset, read from the report design.
Is it possible to create an ado.net dataset so that I can display and use real data in my properties dialog box? First problem I have is to get the connection string from the data source string. I found no docs for that, especially the Microsoft.ReportingServices.DataExtensions.ReportDataSource class is not documented. Am I on the right way with that?
Thomas