My Own Dataflow Component. How Make A Property With List Of Predefined Values?
Mar 24, 2007I am creating of may of dataflow component.
How make a property with list of predefined values?
Thanks in advance.
ADVERTISEMENT
Custom Dataflow Component: How Do You Make Properties Editable?
Feb 13, 2007I 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!
Why Dataflow Component Doesn't Appear In The List Of SSIS Data Flow Items?
Sep 5, 2007Hi,
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.
List Of Values That Make SQL Blow
Sep 27, 2006I am allowing users to assign values in a table to field names in a table
for
EAV table transpostion
Does anyone know where I can find a list of ascii valeus or characters that sql server does not like in fieldnames.
IE (ticks, #, :, -,)
I need to allow the user to define fieldnames for values which then I will alter existing tables with that FieldName that they assign.
I know this is a nightmare but it is the contraint I am working under.
Reference To Preceeding Component From Custom Dataflow Transformation Component
Mar 30, 2006I 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
Make 2 Data Source In Dataflow
Oct 14, 2007
Hi,
I want to create pckg with two data source,
the pckg will know to take data from the right DS after IF will check if getdate() returned endweekday it will take from DS1 else DS2.
any suggestion how to do it??
10X.
Custom Dataflow Component---add New Column To Buffer
Aug 14, 2007This 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);
}
}
}
}
}
Global Variables And The Script Component In DataFlow
Apr 12, 2006I can't find anything on how to get to a global variable in a script component in the dataflow. I can get to it in a script task with no problem by using dts.variables but i doesn't appear you can do the dts variables in the script component.
I did add it to the readwrite variable list but I haven't been able to access it.
Access Read Only Variables In Script Component In A Dataflow
Dec 6, 2006I have a set of comma separated variables in a Script Component list. I want to access them in Script code and use them to build string in the code.
View 9 Replies View RelatedStand Alone Custom Dataflow Component Trying To Access MainPipe...
Feb 4, 2008Hi,
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.
How Can I Initialize Runtimeconnection For My SSIS Dataflow Component From My IDtsComponentUI Correctly?
Jul 30, 2007I 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.
Dates In Slow Changing Dimension Dataflow Transformations Component
Mar 31, 2008I€™m trying to populate a table with fields of date type [DT_DATE] using the Slow Changing Dimension Transformation component. When I add the date fields to the component it would not build the stream. The wizard fails and tells me the date fields are not of the same type. The fields in the destination table are of type €œdate€? and the input columns are of type [DT_DATE]. Am I missing something?
View 3 Replies View RelatedModify Component Property
Apr 9, 2008i need some help modifying a custom control flow task...
I am currently working on an export solution, and since some of the consumers of the export files can not handle unicode, i need to change the Codepage of the output based on config file parameters.
I downloaded a custom dataflow component published by MS (please find the link below - 1). It works fine for changing codepages, but it cannot be configured via expressions.
So i decided to modify the component the way MS describes it (link below again - 2):
Thus, added one row to the method "ProvideComponentProperties()":
customProperty.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;
But this changes nothing, i am still not able to use an expression for setting this property. I could need some help on this...
Thanks a lot,
Regards
Jan
Links:1)SQL Server SSIS Sample Component: CodePageConvert:https://www.microsoft.com/downloads/details.aspx?familyid=9e56417e-23d1-4fd3-8d6d-61314faa2de3&displaylang=en2) Design-time Methods of a Data Flow Component:http://technet.microsoft.com/en-us/library/ms135969.aspxComplete Method:
public override void ProvideComponentProperties()
{
base.ProvideComponentProperties();
IDTSCustomPropertyCollection90 customProperties =
this.ComponentMetaData.CustomPropertyCollection;
IDTSCustomProperty90 customProperty = customProperties.New();
customProperty.Name = "CodePage"; // do not localize
customProperty.Value = 850;
customProperty.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY; //My Code
}
Immediate Validation On Component Property - Is This Possible
May 29, 2008Can you fire a warning / error validation immediately after a value is entered (user clicks away / clicks different property) in a component property without having to press ok button or switch tabs.
Have tried to call validate in setcomponentproperty but only takes effect when press ok button / switch tabs.
Thanks
How Can I Bind Assemblies Of DEFINITE Version Of SSIS Dataflow Component To A Package.
Sep 4, 2007I 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.
Enabling Expression Builder For Custom SSIS DataFlow Source Component
Mar 13, 2007Hi,
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.
Execute Package Utility / Set Values / How To Set Property Path And Values
Feb 4, 2008Hi
I have one problem in SSIS for passing Variable Values while executing Package. I'm giving in details as below:
I opened Microsoft SQL Server Management Studio
Made Connection to Integration Services
To Execute Package I Right Clicked and Click on Run Package
Then I Clicked on Execute and package was executed successfully.
Problem is that if I try to Set Values then Package through Error
DTExec: Could not set ProcessMode value to M.
Basically I could not understand in which format I should pass the Variables.
What I tried is listed below:
ProcessMode;M
Package.Variables[User:rocessMode].Value;M
Package.Variables[ProcessMode].Value;M
But every time I got errors.
And then I tried from Command Line
DTEXEC /DTS "MSDBLoad_Order" /SERVER SERVERNAME /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V /SET Package.Variables[ProcessMode].Value;M
First time Process run successfully. And it has changed the ProcessMode to M also. But after that it was also not changing the ProcessMode Value to M.
Please help in regarding. I tried a lot from Site examples also, but could not get proper solution.
Thanks in advance
Bhudev
Script Component - DataType Property Inaccessible
Dec 19, 2007
Hi,
I have a trouble with script component. From time to time, I cannot see available data types of the column I´m just editing, in the Script component editor. The cell in the section Data Type properties, where normally combobox with data types is, is completelly black. This happens both with "old" script components I'm only viewing and new script component I'm creating.
This problem occured for the first time immediately after I migrated from Windows XP to Vista Bussiness 64bit and then several times without any obvious similarity among terms under which this appeared.
I have SP2 for SQL Server and SP1 for VisualStudio 2005 Professional edition installed.
Please, can you help me?
Thanks
Jana
Validation Error On Custom Component Property
May 28, 2008I have set a validation error when changing a property in the advanced editor. Unfortunately this validation error is only displayed when you change tabs or press the ok button in the editor.
I have tried calling the validate method within the setcomponentproperty method but the error only show up when ok button is pressed or tab in editor.
I don't want to throw an exception, is there any way you can show a validation error immediately once the user has clicked away / entered / clicked to another property field after entering a property value in the advanced editor that is incorrect.
Thanks
List Hidden Property
Nov 20, 2007I have a list control in my report body.I want to set my visibility based on expression.I am doing this fine now but my problem is that my grouping is ignored.Before i am grouping my list based on my emp. name with page breaks after the list.But now it seems to ignore the page break and only come out in out page.What could be the work around for this?THanks guys!!
View 3 Replies View RelatedSQL 2012 :: List All Different Values That Go With Single CAS ID To Appear As Comma Separate List
Jun 15, 2015So at the moment, I don't have a function by the name CONCATENATE. What I like to do is to list all those different values that go with a single CASE_ID to appear as a a comma separate list. You might have a better way of doing without even writing a function
So the output would look like :
CASE_ID VARIABLE
=====================
1 [ABC],[HDR],[GHHHHH]
2 [ABCSS],[CCHDR],[XXGHHVVVHHH],[KKKJU],[KLK]
SELECT
preop.Case_ID,
dbo.Concatenate( '[' + CAST(preop.value_text AS VARCHAR) + ']' ) as variable
FROM
dbo.TBL_Preop preop
WHERE
preop.Deleted_CD = 0
GROUP BY
preop.Case_ID
How Make Script Component Output 2 Asynchronous?
Jul 6, 2007I am working with the Data Flow Task Script Component for the first time. I have created a second Output. In my script I add rows to this output.
I have found that Ssis does not release those rows to the second Output until it has processed all of the incomine pipeline records. This will not work for me as there are going to be a few million records coming down the pipe, so I need the Script Component to as soon as possible release these records downstream for insert into the destination component Ole Db component.
Any help would be greatly appreciated?
How To Store Values During Dataflow
Aug 2, 2007The point is, i want to calculate the max id of a table using Aggregate Transformation, then insert some rows with a OLEDB Command and finally , with another OLEDB Command select those rows with id >(max_id) calculated before.
How can i get a value that was calculated before? Can i store it in a variable?
Many Thanks!
Would Like To Make A "repeat List Menu"
Apr 14, 2001Instead of explaining my question here I've made a seperate
html page with a full explanation of my problem with pictures
and examples.
I've posted the question to this URL
http://miona.com/listmenus/
The Version Of Component **** (11773) Is Not Compatible With This Version Of The DataFlow.
Dec 12, 2007
I started this thread as the last attempt to sort the issue out.
I have an SSIS package that loads data from a .csv file into my database. It works fine on my developer machine. I start it programmatically or from Management Studio or from Visual Studio, and it works. Then I deploy it to the MSDB database on the computer on which it will have its final place. There again I can start it from Management Studio or from Visual Studio (using the source file), and it works. But when I start it programmatically, it just fails telling me:
Package Warnings:
Package Errors:
The version of component "****" (11773) is not compatible with this version of the DataFlow.
Component "component "Derived Column" (13627)" could not be created and returned error code 0x80070005. Make sure that the component is registered correctly.
The component is missing, not registered, not upgradeable, or missing required interfaces. The contact information for this component is "Flat File Source;Microsoft Corporation;Microsoft SqlServer v9; (C) 2005 Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;1".
component "Agreement File" (11773) failed validation and returned error code 0xC0048021.
One or more component failed validation.
There were errors during task validation.
Maybe the source of the problem is that this machine is a 64-bit one, while the developer machine is 32-bit one. But why does the package run fine when I start it from Management Studio?
Any help would be appreciated.
FMikee
Make Items Bold In Parameter Selection List
Mar 19, 2007Is it possible to make certain items in a parameter selection list appear bold?
View 3 Replies View RelatedGet The List Of Variables In A Package Inside A Custom Component
Oct 26, 2006Hi
I am developing custom dataflow component ,I need to get the of variables of the current package in the component , how can i get it?
Thanks
Mani
Access To The Property Values For My Expressions
Oct 2, 2006I may be looking too hard for this but I can't find a way around it.
I have an Expression and in that expression, I want to access a property on the same object (it would be great to get the properties of other objects as well).
Example: I have a flat file connection where I defined the name of the flat file in my ConnectionString. I also have a variable that I have it linked to my dtsConfig which points to the proper folder name at run time.
How can I create an expression similar to this:
@[User::strFolder] + @[Connectionstring]
where @[User::strFolder] is my variable and points to the correct folder for the given server the package is running on and @[Conenctionstring] is my made up name to access the VALUE of the Connectionstring that I have for this flat file.
So if I have the following:
in my connectionstring property: flatfile.txt
in my strFolder derived from dtsConfig at runtime: E:etl_data
I would like my final connectionstring to look as follow:
E:etl_dataflatfile.txt
So far I know I can do it with two variables but it would be great if I could reuse the property values of the current object for my expressions or any other object.
Perhaps this value is available thru the script where I can access "any" property in my dtsx and store it into another variable and then use it. This option at least allows me to reuse code instead of hardcoding table name (connectionstring) into my variables.
Did I make this too difficult and there is a simple way to access an object's property inside the expression builder?
Thanks
Anatole
Transact SQL :: Getting Primary Key Values As A List Of Values
Oct 14, 2015I am trying to audit data quality based on some defined data quality rules. The rules are stored in tables and processed using stored procedures. I am facing a problem while generating audits. Let's say I am trying to audit data in OrderDetail table. The table design is mentioned below...I inserted some sample data into the table using RedGate data generator.The audit table output I am expecting is as mentioned in the screenshot below
Its the PrimaryKeyAttributeValues column I am facing problems with. I am using STUFF function within a dynamic SQL query to get the primary key's as a list of comma separated values.
Expression Issue With Custom Data Flow Component And Custom Property
Apr 2, 2007Hi,
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 ?
Unable To Set Values In Array Custom Property
Apr 21, 2006I'd like to be able to hold onto lineageIDs in a list in the custom property of an output object. I found that I can really only easily use an array which is fine but now that I have coded it I cannot set any of the array values programatically. I can't even initialize the array the way I want it. I also cannot change the value in the debugger, it just pops back to zero.
Am I missing something? Here's how I'm trying to do it:
IDTSCustomProperty90 linIDsProperty = output.CustomPropertyCollection.New();
linIDsProperty.Name = "KeyColumnLineageIDs";
linIDsProperty.Value = new int[MAX_KEY_COLS];
for (int i = 0; i < ((int[])linIDsProperty.Value).Length; ++i)
((int[])linIDsProperty.Value)[i] = -1;
(please ignore the \ around the [i], the forum changes [i] into for some reason)
Thanks
Charlie.
Update Package To Save New Property Values
Jan 4, 2007Hi,
using custom tasks. My properties got saved properly the first time the task is added to the workspace. After that, changing values in the CustomUI updates the properties, but the package shows, that It doesn't need to get saved. So my properties don't get changed in the package XML. Moving my custom task on the workspace after changing properties, let the package go into "save me" state and my propertiy values get saved to XML.
So now, Im searching for a method or property I have to call or set to show the package it needs to get saved after closing my CustomUI.
Any hint?
Thanks
PS: When I change the property value in the task properties grid (lower right). The package gets notified about the need to get saved.
Property Values Cleared After Package Deployment
May 14, 2008I have a very odd problem. I have a package which uses some custom tasks that were written in C#. When the package is deployed to our production server, *some* of the property values for *some* of the tasks are cleared. For example, I have these five tasks:
FP Export File Logger
FP Import File Logger
FP Import Table Logger
FP Instance Logger
FP Job Reader
All of them inherit (of course) from Microsoft.SqlServer.Dts.Runtime.Task. All of them have custom members (some similar, some different), and of course, different implementation (though they are mostly the same). This test package has one instance of each of the different tasks.
As I said above, when we deploy to our production server, *some* of the property values for *some* of the tasks are cleared -- but when deployed to our dev server, everything remains intact.
Here is what is cleared:
- On 4 of the 5 tasks, the Description property (inherited by Task) is cleared, but the other one remained intact
- On 3 of the 5 tasks, the Connection property (custom property in all tasks) is cleared, but the other two remained intact
- 3 of the tasks have other string properties that were set, and all of these were cleared
We can reproduce this on two different production servers, and these two servers have some different configurations, suggesting these would not be the culprit:
- They have different service packs (one is build 2047, the other build 3042)
- One has the custom SSIS components installed (in the GAC), the other one does not
Our development server, where the package is deployed as expected, has build 2047 w/ the components installed.
Here are the packages, where you can compare and see the differences (using a text comparison tool):
Dev-GOOD.xml
Prod-BAD.dtsx
These were created after being deployed by importing within a Visual Studio SSIS project from the server.
Any suggestions would be *greatly* appreciated, as we are totally stumped as to why this is happening.
EDIT: Additional clues, this package is deployed to the MSDB. If it's deployed to the File System, it remains unmodified.
Thanks in advance.
Jerad