I am writing a custom task that has some custom properties. I would like to parameterize these properties i.e. read from a varaible, so I can change these variables from a config file during runtime.
I read the documentation and it says if we set the ExpressionType to CPET_NOTIFY, it should work, but it does not seem to work. Not sure if I am missing anything. Can someone please help me?
In the Editor of my custom task, under custom properties section, I expected a button with 3 dots, to click & pop-up so we can specify the expression or at least so it evaluates the variables if we give @[User::VaraibleName]
I developed a simple custom control flow component which has several read/write properties and one readonly property (lets call it ROP) whichs Get method simple returns the value of a private variable (VAR as string). In the Execute method the VAR has a value assigened. When I put the value of ROP or VAR into MsgBox I can see the correct value. However when I execute the component I can not see the value of the ROP in the property window. I see the property but its value is empty string. For example when I put a breakpoint to postexecute or check the property before click OK in a MsgBox I would expect that the property value would be updated in SSIS as well. Is there a way how to display correct values of custom tasks properties in property window?
I'm wondering if anyone's accomplished this before - I've been unable to find a whiff of info on how to do this so far.
I'm creating a custom component that I'd like to give a "Derived Column" type of ability to. By that, I mean I'd like to populate a property of my component with an expression (including references to input columns, package variables and functions) and be able to evaluate it at runtime - per row processed by the component.
I would also appreciate any information as to how to provide the interface to allow the user to build such an expression as well - is there a UI function in SSIS I can call to pop up the "expression builder"?
I wrote a custom task following the outline on MSDN. I signed it and installed it into the Tasks folder and in the GAC.
When I go to an SSIS project and add my task, the properties window shows "Could not get value for property 'd61935d9-430b-4c93-9f3e-a29f720d8659'. Specified cast is not valid." (where the guid is different obviously) for many of the properties.
What have I done wrong?
Update: I know this isn't my code because I tried a simple task that just returns success and doesn't do anything. I get the exact same errors, so I must be installing it incorrectly.
I have a task I wrote which does not always update the property value (as seen in the properties pane) Basically, change something on the form, then update the task host property with: this.taskHostValue.Properties["Duration"].SetValue(this.taskHostValue, Convert.ToInt32(spnDuration.Value));
Stepping through this, it does exactly what it is supposed to. Having a look at the property value, it confirms it has changed. Reopening the UI and resetting all the controls returns the expected results.
The package however does not realise it has changed. There is no * next to the package name in the top tabs. As long as the package thinks it is unchanged, SaveXML does not get called either so the tasks do not persist.
Changing the value on the properties pane works fine though.
The frustrating thing is this is slightly random. Slight in the sense that sometimes it works but most of the time it does not.
The sample code I used was the MS download IncrementTask (Which works BTW) so I can't see it as being a VS / SSIS bug but rather something I am / am not doing. 3 tasks I have written all behave the same. I have to "nudge" them before savign the package.
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.
We have a built a custom control flow task that we use frequently. It has a simple user interface form where we can set certain properties. Problem is, after I configure it once and then copy and paste it all of the internal property settings seem to be lost on the newly pasted task. When I open up the editor for the task all of the properties are displayed visually in the editor form, but the properties pane is empty.
I created a Custom Task which it has a Property called ConfigFilePath. I'm overriding the Validate() method from Task. I want to throw an error if my property ConfigFilePath is empty and if the expression for this property is empty. So far, I can check if the property is empty but I don't see how I can access the Expressions Collection of my Custom Task.
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.
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'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)
I 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.
Im working through the MS example of "removeDuplicates". I cant seem to figure out how to add custom property for input column.
I added the helper method: private static void AddIsKeyCustomPropertyToInput(IDTSInput90 input, object value) { IDTSCustomProperty90 isKey = input.CustomPropertyCollection.New(); isKey.Name = "IsKey"; isKey.Value = value; } I call it from: public override void ProvideComponentProperties() { //... AddIsKeyCustomPropertyToInput(input, false); //... } public override void ReinitializeMetaData() { IDTSInput90 input = ComponentMetaData.InputCollection[0]; if (input.CustomPropertyCollection.Count == 0) { AddIsKeyCustomPropertyToInput(input, false); } // ... }
However when I deployed it and added the component to SSIS package - I cant see the Custom Column "IsKey" in the input column properties window. What am I missing - please help
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.
Hi, I have a report that needs sorting on a column that is calculated. however, the calculation is so complex (iterative calculations) i've had to do that within my custom code. I am getting the results back well and good but i cannot perform a sort based on that. My problem is: how do i write a custom sort expression function that will direct the column to sort my table based on say the value(s) returned from that function. I want for example:
Code Snippet Public Function mySortExpression(byVal myIDorWhatever as Something) as CustomSortableExpression
' Perform some calculation ' Return some value(s)/array or anything that the column can appreciate and sort the table accordingly with End Function
then in say the Interactive Sort Sort Expression, i'll have the code:
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.
How to Protect From SQL Injection in ASP.NET and SQL 2005 for custom query expression?In my project, I allow user to custom query expression through UI, such asstring queryCondition=' sale>20 and sale <100'string queryCondition=' createDate>"10/10/2005"'string queryCondition='Fullname like "%Paul%" '...I construct SQL based the queryCondition string, such as string mysql='select * from mytable where '+queryConditionI know it's very dangerous because of SQL Injection, but it's very convenient for user to custom query expressionCould you tell me how to do? many 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.
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.
I've created a custom task in VB6 and compiled the DLL. wHen I run the task in DTS designer as a step it runs ok, however when I try to exectue the whole package nothing happens??
Hi there, I'm still pretty new to SSIS, what I am really battling with is getting a custom task to work. I have developed a custom task, digitally signed it added it to the Global Assembly Cache, written the UI for it - Pretty much as it is done here: http://technet.microsoft.com/en-us/library/ms136080.aspx but my problem is that when I drag it from the tool box into the control flow pane, I get this message: "The task user interface specified by type name 'XXXXXXXXXXXXXXXXXXX' could not be loaded. (Microsoft.DatatransformationServices.Design).
Does anybody have an idea of what I am doing wrong here?
We have developed some custom tasks which we currently deploy the standard way. That is, we install the custom task assemblies in the DTSTasks folder and we also install the assemblies in the GAC. Due to some deployment practices we are trying to implement we would like to be able to remove the assemblies from the GAC and install them in some other location. I have tried installing the assemblies in the DTSBinn folder, but this does not appear to be working. When I drop the custom control flow tasks onto the package's control flow tab I get the "Cannot create a task with the name ..." error.
Is it possible to not have the custom task assemblies in the GAC? Are there any tricks to getting it to work?
FYI, our assemblies are signed. I'm not sure if that has anything to do with our troubles.
Had anyone code a custom task UI and used the propertygrid like most of the SSIS components do?
I am having an issue while trying to get the connections from my UI FORM.
They way i did was to get the parent of the form (The package) and then get the connections but that will only work for the FORM if i use the property from the properties panel i throws an error because obviously i dont have the FORM neither the parent...
I now i should use the service provider and get the IDTSconnection... but somehow it always comes = "Nothing"....
I've gone through all the steps, and when I finally drag the custom task I've made into the control flow, I get the error
"Failed to create the task. ... Cannot create a task with the name "SSISExportToExcel.ExcelExport.ExcelExport, SSISExcelExport, ... Verify the name is correct"
Here's the beginning of my class... any help would be appreciated...
Namespace ExcelExport <DtsTask(DisplayName:="Excel Export Task", _ Description:="Exports a SQL query results to an Excel Document")> _ Public Class ExcelExport Inherits Task ....
Is there a way you can reference a prebuilt CLR function in a custom dataflow task?
The CLR and the custom dataflow task are one in the same. The reason that I'm designing the custom task is that I find that it's time consuming to make a call to the database to retrieve a function to work on something that's already in memory. Also, another reason is that if the code needs to be updated, it doesn't have to be done in 2 places allowing for less confusion.
I tried adding a reference to the CLR function in my SSIS dataflow task, but when I try running it in SSIS, I get the error saying it cannot use a SQL CLR defined task in the package which I believe is due to this reference in the CLR code:
[Microsoft.SqlServer.Server.SqlFunction] public static string toCase(string text, string caseType)
Is there a way to circumvent the [...SqlFunction] line of code when the DLL is referenced by an SSIS dataflow task, or do I just have to deal with having 2 copies of the same code?
I'm having a nightmare trying to test my custom task.
I get the following error message when trying to add the task from the toolbox...
Cannot create a task with the name "myTask, Version=1.0.0.0, Culture=neutral, PublicKeyToken=b8e53511af163eb7". Verify that the name is correct. (Package)
------------------------------ Program Location:
at Microsoft.SqlServer.Dts.Runtime.Executables.Add(String moniker) at Microsoft.DataTransformationServices.Design.DtsBasePackageDesigner.CreateExecutable(String moniker, IDTSSequence container, String name)
I am having a hard time setting the executable path for an Execute Process Task in SSIS. I have a variable that is initialized at package statup which holds the path to an executable in Windows. When I set the property "Executable" Path in an expression, I get a warning that the path for the executable is not set. One workaround was to try and initialize the variable with a bogus path with the hopes that the "correct" value will be written on run-time. NO LUCK. I still get the error and I cannot run the package until I put a static path.
I need to find a better way to handle DTS Job Failure issue. Currently, we have about 50 jobs which executed through DTS packages. Everytime when sources were not there or came in late, the DTS sent out an email to my page which I carried every day. Some those came in during the holidays even though I know the source party won’t generate the source files at that day.
Trying to avoid to get beep everytime when job was failed. Someone suggested that it is possible to add kind of executable file within the Custom task and let it trigger the DTS packages. If, for example, a holiday then don’t run the package so I won’t get the page.
Any hints suggestions would be greatly appreciated
I've created a custom task, and now I want to upgrade it. There's actually no change in the task itself, but I've changed the assembly version number, and now I need to upgrade all the tasks that reference the old version number.
So I rebuilt the task, and I created a policy assembly with a bindingRedirect that forwards to my new assembly. I removed the old version of the assembly from the GAC. All good so far - I've even run the task through the debugger, I open the package as "Reload with Upgrade", and the breakpoints in my CanUpdate, Update and Version overrides are being hit fine. This approach works fine in my custom pipeline components, and the PerformUpgrade override updates the UserComponentTypeName to reflect the new assembly version number (see Milen's post in http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=893641&SiteID=1).
But the task itself won't upgrade, or at least, it seems to require the old assembly version number. I get an error message:
Code SnippetThe task with the name "My Task" and the creation name "xx.xxx.MyTask, xxx.xxx, Version=0.1.0.0, Culture=neutral, PublicKeyToken=xxxx" is not registered for use on this computer.
Has anyone got any ideas what I'm doing wrong? I've got 40 or so packages with 2 tasks in each, and I don't really want to have to delete and add them to each one ...
