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 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.
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?
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.
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]
How to count the number of values that exist in a row based on the values from an array of numbers. Basically the the array of numbers I want to look for are in row 1 of table [test 1] and I want to search for them and count the "out of" in table [test 2]. Excuse me for not using the easiest way to convey my question below. I guess in short I have 10 numbers and like to find how many of those numbers exist in each row. short example:
I created a custom function that accepts an array of strings as a input. I need to pass several fields from the dataset to this function as an array. How can I do this?
Hi 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.
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'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 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
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.
Hi there...Is it possible to write an UPDATE or INSERT query, where the new value comesfrom an array? For example:UPDATE table_a SET column_x = [@array1]WHERE column_y = [@array2];It's a query (for argument's sake called query1) in an Access database,which I'm accessing as a stored procedure through PHP, so I would run somephp code that looked possibly like this:$array1 = array("a","b","c","d");$array2 = array(1,2,3,4);$str_sql = "exec query1 $array1, $array2";my_run_query_function($str_sql);But is it possible? The information I can find about it seems to say that ithas to be an array created inside the query, but if it's a stored procedure,this isn't possible, is it?Hope someone can help...Plankmeister.
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 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?
Hi, Is it possible to format any kind of XML file with the FOR XML EXPLICIT mode in SQL Server 2005? I am asking because I am trying to format my file in a way that I could get 2 or 3 elements on my parent or child level and put some attributes (icon path for example) in these 2nd element or 3rd element. I am unable to format it that way. Let me show you the way that I would like to have my XML file formated :
I want to have all my parent, children, sub-children to have these attribut but I am having difficulty putting one in the “Cell” tags. And I really think that my problem resides in the specifying of my column and their directives but I do not know how to fix it. See my icon path is commented here. I have to put the [row!2!cell!xmltext], but it is not working.
Here is the way that I am building my Select query.
SELECT 1 as tag, NULL as parent, NULL AS 'rows!1!', ----Root rows tag ID_metric as [row!2!id], Description_english as [row!2!cell!Element], ----Icon as [row!2!cell!xmltext], Sort as [row!2!sort], ID_metric as [row!3!id], Description_english as [row!3!cell!Element], --Icon as [row!3!cell!xmltext], sort as [row!3!sort], ID_metric as [row!4!id], Description_english as [row!4!cell!Element], --Icon as [row!4!cell!xmltext], sort as [row!4!sort], ID_metric as [row!5!id], Description_english as [row!5!cell!Element], --Icon as [row!5!cell!xmltext], sort as [row!5!sort], FROM #buildData WHERE HLevel = 0 UNION ALL SELECT 2 as tag, 1 as parent, NULL AS 'rows!1!', ID_metric as [row!2!id], Description_english as [row!2!cell!Element], --Icon as [row!2!cell!xmltext], sort as [row!2!sort], ID_metric as [row!3!id], Description_english as [row!3!cell!Element], --Icon as [row!3!cell!xmltext], sort as [row!3!sort], ID_metric as [row!4!id], Description_english as [row!4!cell!Element], --Icon as [row!4!cell!xmltext], sort as [row!4!sort], ID_metric as [row!5!id], Description_english as [row!5!cell!Element], --Icon as [row!5!cell!xmltext], sort as [row!5!sort], FROM #buildData WHERE HLevel = 1 UNION ALL SELECT 3 as tag, 2 as parent, NULL AS 'rows!1!', ID_metric as [row!2!id], Description_english as [row!2!cell!Element], --Icon as [row!2!cell!xmltext], sort as [row!2!sort], ID_metric as [row!3!id], Description_english as [row!3!cell!Element], --Icon as [row!3!cell!xmltext], sort as [row!3!sort], ID_metric as [row!4!id], Description_english as [row!4!cell!Element], --Icon as [row!4!cell!xmltext], sort as [row!4!sort], ID_metric as [row!5!id], Description_english as [row!5!cell!Element], --Icon as [row!5!cell!xmltext], sort as [row!5!sort], FROM #buildData WHERE HLevel = 2 UNION ALL SELECT 4 as tag, 3 as parent, NULL AS 'rows!1!', ID_metric as [row!2!id], Description_english as [row!2!cell!Element], --Icon as [row!2!cell!xmltext], sort as [row!2!sort], ID_metric as [row!3!id], Description_english as [row!3!cell!Element], --Icon as [row!3!cell!xmltext], sort as [row!3!sort], ID_metric as [row!4!id], Description_english as [row!4!cell!Element], --Icon as [row!4!cell!xmltext], sort as [row!4!sort], ID_metric as [row!5!id], Description_english as [row!5!cell!Element], --Icon as [row!5!cell!xml], sort as [row!5!sort], FROM #buildData WHERE HLevel = 3 ORDER BY [row!2!sort], [row!3!sort], [row!4!sort], [row!5!sort] for XML explicit
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.
I 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:
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.
I developed a report with some values in textboxes. I want the output not to wrap around to the next line but to be truncated if it is more that the size of the textbox. Is there any setting that i can do b/c values are going to the second line when their size is more like printing name and last name will cause last name to go to the second line ?
We are setting up a job for the SQL Server Agent via SSMS. The Job Step Type is SSIS.
In the Job Step Properties window, on the Set values tab, you can enter Values to override your package variables - normally all well and good.
However in this particular case, the variable Value contains semicolons ( - it is a Connection String for an ODBC driver. Eg: Driver={Client Access ODBC Driver (32-bit)};system=MYSERVER;...
The behaviour for this Value is weird:
If the Value is not surrounded with double quotes ("), the job fails with "The command line parameters are invalid." If the Value is surrounded with double quotes ("), the job will run as intended. The catch is: that entry and any subsequent "Set Values" entries disappear next time the Job Step Properties window is opened.
This looks like a bug with the parsing of those strings by the Job Step Properties window?
We have a 10 digit primary key value in this format: M000123456. Theorder for this key is first determined by positions 3 and 4 in thisexample, then positions 1 and 2. So a brief sample of correct orderingwould look like this:M000001501M000011501M000021501M000001601M000011601M000021601Now my question: how can I use a BETWEEN (or > and <) in my WHEREclause to get a range of values for this column? I use the followingORDER BY clause to control how the results are sorted, but I can't getthe same logic to work with BETWEEN in a WHERE clause.ORDER BY SUBSTRING(<fieldname>, 7, 2), SUBSTRING (<fieldname>, 5, 2)How do I return values between M000011501 and M000011601 for example?
I have a stored procedure that has a paramter that accepts a string of values. At the user interface, I use a StringBuilder to concatenate the values (2,4,34,35,etc.) I would send these value to the stored procedure. The problem is that the stored procedure doesn't allow it to be query with the parameter because the Fieldname, "Officer_UID" is an integer data type, which can't be query against parameter string type. What would I need to do to convert it to an Integer array? @OfficerIDs as varchar(200) Select Officer_UID From Officers Where Officer_UID in (@OfficerIDs) Thanks
I've created a custom Data Processing Extension and I've implemented the IDBCommandAnalysis interface so that my reports can enter parameters and pass them to my Data Processing Extension.
My question is, how do I extract the value from the Parameters coming from the report? Where do the parameters get passed off from the report? I can query the Parameters collection and my report gets prompted in Preview mode to enter something for the parameter but I can't find the spot where it gets passed for processing.
I am having problems exporting data into a flat file using specific code page. My application has a variable "User::CodePage" that stores code page value (936, 950, 1252, etc) based on the data source. This variable is assigned to the CodePage property of desitnation file connection using Property expression.
But, when I execute the package, the CodePage property of the Destination file connection defaults to the initial value that was set for "User:CodePage" variable in design mode. I checked the value within the variable during runtime and it changes correctly for each data source. But, the property of the destinatin file connection doesn't change and results in an error.
[Flat File Destination [473]] Error: Data conversion failed. The data conversion for column "Column01" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
[DTS.Pipeline] Error: The ProcessInput method on component "Flat File Destination" (473) failed with error code 0xC02020A0. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
If I manually update the variable with correct code page and re-run the ETL, everything works fine. Just that it doesn't work during run-time mode.
