Enabling Expression Builder For Custom SSIS DataFlow Source Component
Mar 13, 2007
Hi,
I have implemented a custom source component that can be used as the data source in the Data Flow task.
I have also created a custom UI for this component by using the IDtsComponentUI .
But my component does not have the capability of setting the custom properties via the DTS Variables using the Expression Builder.
I have looked around for samples on how to do this, but I can only find samples of how to do this for custom Control Tasks, i.e. IDtsTaskUI.
My question is, How can implement the Expression Builder in my custom Source component + custom Source UI. Or do you know of any samples which I can look at.
Thank you,
Jameel.
View 1 Replies
ADVERTISEMENT
Mar 30, 2006
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
View 7 Replies
View Related
Aug 14, 2007
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
View 6 Replies
View Related
Apr 2, 2007
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 ?
View 5 Replies
View Related
Aug 14, 2007
This is trivial I'm sure but I'll be dogged if I can find someone who mentions how to do it. I am attempting to develop a Data Flow Transformation that appends a new column (a string value) into the current stream.
I have found plenty of references on how to replace an existing column but I'd really like to just add my new column in there. It doesn't need to be configurable, it can be a static column name. I'll take a solution that allows the column name to be set at design time, don't get me wrong but the magic I'm looking for is how to implement a new column in a stream.
Yes, I am well aware of the derived column task but I will be replacing a few hundred instances and I'd much rather just drag an item onto the designer than to drag a derived column, double click it, type in the column name, set the expression and then set the datatype, etc.
Anyone spare a moment to enlighten me?
Pardon the lack of formatting, this BB doesn't play with Opera (I know, I'm a heretic)
using System;
using System.Collections;
using System.Runtime.InteropServices;
using Microsoft.SqlServer.Dts.Pipeline;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using Microsoft.SqlServer.Dts.Runtime;
namespace Microsoft.Samples.SqlServer.Dts
{
[
DtsPipelineComponent
(
DisplayName = "Nii",
Description = "This is the component that says Nii.",
ComponentType = ComponentType.Transform
)
]
public class Nii : PipelineComponent
{
public override void ProcessInput(int inputID, PipelineBuffer buffer)
{
if (!buffer.EndOfRowset)
{
while (buffer.NextRow())
{
try
{
// do something here to
}
catch (Exception e)
{
ComponentMetaData.FireInformation(0, ComponentMetaData.Name, "There was an error on row " + buffer.CurrentRow.ToString() + ". The error is: " + e.Message + " : " + e.Source + " : " + e.StackTrace, "", 0, ref fireEventAgain);
}
}
}
}
}
View 1 Replies
View Related
Feb 13, 2007
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!
View 1 Replies
View Related
Feb 4, 2008
Hi,
I've created a stand alone custom dataflow component in VB and I need to set up the connection to the Input and Output components and instantiate it. The only way I've seen this done is to create an entire package and Task then use the TaskHost wrapper object to instantiate the Mainpipe (IDTSPipline90 interface) so that you can create the IDTSPath90 interface and setup the connection to the input and output components...
After all that, all that I would like to know is whether it is possible to instantiate the mainpipe interface without creating a package programmatically? I've seen something Darren Green put in an answer to a thread, about accessing the Mainpipe interface in the UI, to the effect that you can access it through IServiceProvider using the interface IDTSPipelineEnvironmentService - I think that's it... But I'd like to know if there is a more straightforward route to instantiating and accessing the Task or data flow directly?
Thanks in advance,
Will.
View 8 Replies
View Related
Mar 22, 2008
I would like to add additional string functions and other types of functions to the expression builder in SQL Server Integration Services. Right now the list of functions is relatively limited to such things as FINDSTRING, RIGHT, LEN, etc.
Is this possible?
Matt
View 6 Replies
View Related
Dec 12, 2007
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
View 5 Replies
View Related
Apr 21, 2006
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.
View 1 Replies
View Related
Aug 13, 2007
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)
View 4 Replies
View Related
May 10, 2006
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.
View 10 Replies
View Related
Mar 23, 2006
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,
View 6 Replies
View Related
May 11, 2006
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
View 5 Replies
View Related
Jan 15, 2008
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
View 1 Replies
View Related
Dec 6, 2007
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
View 5 Replies
View Related
Jul 30, 2007
I am developing SSIS dataflow component.
Extended user interface is based on class IDtsComponentUI. Connection properties are created in both standart and my extended editor (Extended user interface). To set up designtime connection I use standart and my extended editor.
A main PipelineComponent component have two runtime connection:
IDTSRuntimeConnection90 adoConnection1 = ComponentMetaData.RuntimeConnectionCollection.New();
adoConnection1.Name = "Connection 1";
IDTSRuntimeConnection90 adoConnection2 = ComponentMetaData.RuntimeConnectionCollection.New();
adoConnection2.Name = "Connection 2";
----------------------------------------------------------------------
IDTSComponentMetaData90 dtsComponentMetaData; // current designtime component
....
In IDtsComponentUI I use follow fragment of code (for example):
_dtsComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager =
Microsoft.SqlServer.Dts.Runtime.DtsConvert.ToConnectionManager90(_connections[0]);
to set up IDTSRuntimeConnection90[0] connection using connections from a current package. After this operation
the RuntimeConnectionCollection[0] is not null within the method PipelineComponent.AcquireConnections((object transaction)). ! But during next launching of Extended user interface the RuntimeConnectionCollection[0] is null within the method PipelineComponent.AcquireConnections((object transaction)).
Why do I lose the connection? And why the connections which set up in my Advanced editor do not save in standart editor?
Thanks in advance.
View 5 Replies
View Related
Apr 7, 2008
Hi All,
I am using a Data Flow task which copies data from an Excel Source to a SQL Database Table Destination. From 15 columns I require only 10 columns to be imported to the DB Table. So I have mapped those colums. In SQL DB there is a colum called say X, whose value should be the "Remedy" for all the columns which are imported. Is there any task that can achieve it.
Please Help.
View 10 Replies
View Related
Sep 4, 2007
I have several versions of one SSIS Dataflow component. I need to bind some of them( definite version) to my SSIS package. How can I do that?
Thanks in advance.
View 5 Replies
View Related
Sep 5, 2007
Hi,
I developed SSIS Data Flow Component and placed dll file into the DTSPipelinecomponents. Then I registered the component in the GAC.
But when I try to add the required component into toolbox that there is not this one in the list of SSIS Data Flow Items. What does it mean?
Thanks in advance.
View 3 Replies
View Related
Apr 18, 2008
am trying to do the following in SSIS.
I have Execute Process Task, in which I have the values are set for Executable, Arguments and working Directory manually. I keep changing this if the Connection Manager points to Dev or when it points to QA or to Prod. Instead I want to do like this.
If my Connection Manager points to Dev, the Executale , Arguments and Working Directory should automatically change. Else if it points to QA, the Executale , Arguments and Working Directory should automatically change and similarly for Prod.
Is this possible in Expression Builder in Execute Process Task. If so, how?.
Thank you
View 1 Replies
View Related
Aug 17, 2015
I'm writing a custom source component that reads data from a SharePoint list with dynamic mapping to output columns. It's my first custom component and it's based on several samples and tutorials from Internet
Output columns are not created by the component itself, they must be added by user at design time. The component makes dynamically an association between SharePoint fields and available output columns at run-time (based on an mapping table).
I made a very basic skeleton and I encounter a problem when I add a column to output: it has no datatype and when I try to set one I have an the error Property value is not valid, The component xxxxxx does not allow setting output column datatype properties.
Imports System
Imports Microsoft.SqlServer.Dts.Pipeline
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
<DtsPipelineComponent(ComponentType:=ComponentType.SourceAdapter,
DisplayName:="SharePoint Dynamic Assoc List Source",
[Code] ....
View 4 Replies
View Related
Jul 26, 2007
This works:
len ((DT_WSTR,2)DATEPART("mm", getdate()))==1 ? (DT_WSTR,1)0 + (DT_WSTR,1)DATEPART("mm", getdate()) : (DT_WSTR,4)DATEPART("mm", getdate())
This doesn't:
"dataware/"+ len ((DT_WSTR,2)DATEPART("mm", getdate()))==1 ? (DT_WSTR,1)0 + (DT_WSTR,1)DATEPART("mm", getdate()) : (DT_WSTR,4)DATEPART("mm", getdate())
I tried a couple of different things at this point, where am I missing the cast? Before len? Cast to what?
View 4 Replies
View Related
Jun 23, 2006
Hi,
I have problem to extract data from DB2 table using SSIS dataflow task with OLEDB source selecting the data access mode with command. Here is the scenario.(Note:If I select the data access mode as a table then it works fine.)
If I use the data access mode as SQL Command then it is not working. i.e. the flow stays yellow in controlflow nothing happens in dataflow and I noticed the SQL dumper and proc completes. I couldn't find any log to verify the issue including the package execution results.Any of your thoughts will help me to resolve this issue.
Thanks..Mako
View 1 Replies
View Related
Apr 4, 2008
Hi,
I am using SQL Server2005 for SSIS. I want to change the source connection dynamicaly evertime.
Let me clear, I have to extract some column from excel to MS-Access. I am using Data Flow Task and able to successfully complete the job. But problem is that, whenever a new file comes , i must have to reconfigure my Excel Source.
All the time column in file are same, so no need to worry about mapping but how can my package select a file automatically.
I have a directory, suppose "C:dpak". I should able to pick the filename and sheet name from this directory every time when my package will execute.
View 10 Replies
View Related
Feb 26, 2008
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
View 5 Replies
View Related
Feb 8, 2006
Hi,
Near the end of 2005 Microsoft made available some sample C# apps that implemented custom SSIS tasks and components. I think Doug Ladenshlager may have had a heavy hand in building them.
Does anyone know where they are? I can't darned well find them!
-Jamie
View 1 Replies
View Related
Dec 13, 2007
Does anyone here have a favorite site or set of sites with resources on SSIS custom component (tasks, transforms, log providers, etc.) development? I've been searching around to try to find this on my own, but so far I've had little luck.
Darren - I've noticed that you always seem to have the most insightful responses to questions related to SSIS .NET development (such as your recent response to evaluating expressions in a custom component) so I am particularly interested in anything that you have to share.
Thanks in advance, everyone!
View 6 Replies
View Related
Mar 12, 2007
How do I remove a custom component from SSIS Designer pernamently? I can easily remove it from the tool box, but I want to get rid of it completely.
I tried searching in MSDN for an answer to this question but I could not find it.
Thanks
View 6 Replies
View Related
Feb 13, 2006
dear experts,
i'm trying to build a package programmatically from client c# application. I'm working to create three dataflow components:
- OleDB Source
- Derived Column Transformations
- OleDb Destination.
My package works good, but i have several problems when insert an expression as Value of an IDTSCustomPropriety90 object, like this one:
[LEN](#firsname.lineageID) > 5 ? <<if true>> : <<if false>>
Simple expressions like concatenate strings, for example "#firstname.lineageID" + "#lastname.lineageID" seem to work good.
Thanks a lot in advanced.
Paganelli Francesco
View 19 Replies
View Related
Mar 27, 2007
Hi Guys,
I created a SSIS custom component, transformation (Asynchronous) with one Input collection and 2 output collections.
The SSIS Package which includes the Component I created works well in the Business Intelligence Studio, but when the same Package is run in the 'Execute Package utility' It fails to run. ( when you Double click on the dtsx file)
The cause of the failiure is
public override void PrimeOutput(int outputs, int[] outputIDs, PipelineBuffer[] buffers)
method receives only one output buffer when executed using the 'Execute Package Utility' { outputs = 1 , buffer.Length = 1 } ( when executed in the BI studio, the method receives parameters of both the output buffers that I expect { outputs = 2 , buffer.Length = 2 } )
The property ComponentMetaData.OutputCollection.Count = 2 as well. Yet the PrimeOutput method provides only 1 buffer.
The Validation Succeeds on both instances, which I assume means that Meta Data is Provided Properly.
What would be the reason for the same pakage to run in 2 different ways like this,
What might I have missed out to do, to make the package run in different ways on 'Business Intelligence Studio' and 'Execute Package Utility'
Thanks a lot
Below are some of the lines from the ProvideComonentProperties Method which deals with the output Collection, Isn't this sufficient for the PrimeOutput to provide 2 output buffers?
ProvideComponentProperties()
public override void ProvideComponentProperties()
{
RemoveAllInputsOutputsAndCustomProperties();
base.RemoveAllInputsOutputsAndCustomProperties();
base.ProvideComponentProperties();
//other function calls
IDTSOutput90 output1 = ComponentMetaData.OutputCollection[0];
output1.Name = "Output1";
output1.Description = ".......................";
extracted.SynchronousInputID =0;
IDTSOutput90 output2 = ComponentMetaData.OutputCollection.New();
output2.Name = "Output2";
output2.Description = "..........................";
output2.SynchronousInputID = 0;
//other function calls
}
View 3 Replies
View Related
Oct 22, 2007
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.
View 4 Replies
View Related
Oct 26, 2007
Hello,
I have a package that has a data lfow task. this task imports data from a db2 database (using the IBM Ole DB provider fro db2) and adds it to sql server database table. This package was created on the server. then though version control (using TFS source control) I check out the package on my local machine. and when I open the package I get the foll 3 errors.
Error 1 Validation error. Import Account Num from BMGP_BDR: DTS.Pipeline: The component metadata for "component "DataReader Source" (1113)" could not be upgraded to the newer version of the component. The PerformUpgrade method failed.
Error 2 Error loading BMAG Download Xref Tables - bmag.dtsx: Microsoft.SqlServer.Dts.Pipeline.ComponentVersionMismatchException: The version of component "DataReader Source" (1113) is not compatible with this version of the DataFlow. [[The version or pipeline version or both for the specified component is higher than the current version. This package was probably created on a new version of DTS or the component than is installed on the current PC.]] at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostCheckAndPerformUpgrade(IDTSManagedComponentWrapper90 wrapper, Int32 lPipelineVersion)
Error 3 Error loading BMAG Download Xref Tables - bmag.dtsx: The component metadata for "component "DataReader Source" (1113)" could not be upgraded to the newer version of the component. The PerformUpgrade method failed.
Please advice.
Thank you.
View 7 Replies
View Related
