Showing Edge Shaped Event Duration in StreamInsight using Debugger

by Allan Mitchell 29 Dec 2010 15:54
Whilst writing some courseware I wanted to be able to see the start and end times of Edge shaped events from within the debugger.  A quick recap on Edge events At the start of the event you do not know the end time and most probably cannot work it out or you should be using one of the other shapes. You enqueue an event (Start Edge) with the start time and payload of the event.  The end time of the event is set to infinity When you see the end edge come through, you enqueue another event (End Edge) with the previous start time and payload and restate the event’s end time.&... [More]

Tags

    RecentComments

    Comment RSS
    SQLIS | Row Number Transformation

    Row Number Transformation

    by Darren Green 6 Sep 2014 15:56

    The Row Number Transformation calculates a row number for each row, and adds this as a new output column to the data flow. The column number is a sequential number, based on a seed value. Each row receives the next number in the sequence, based on the defined increment value.

    The final row number can be stored in a variable for later analysis, and can be used as part of a process to validate the integrity of the data movement.

    The Row Number transform has a variety of uses, such as generating surrogate keys, or as the basis for a data partitioning scheme when combined with the Conditional Split transformation.

    Properties

    Property Data Type Description
    Seed Int32 The first row number or seed value.
    Increment Int32 The value added to the previous row number to make the next row number.
    OutputVariable String The name of the variable into which the final row number is written post execution. (Optional).

    The three properties have been configured to support expressions, or they can set directly in the normal manner. Expressions on components are only visible on the hosting Data Flow task, not at the individual component level. Sometimes the data type of the property is incorrectly set when the properties are created, see the Troubleshooting section below for details on how to fix this.

    Installation

    The component is provided as an MSI file which you can download and run to install it. This simply places the files on disk in the correct locations and also installs the assemblies in the Global Assembly Cache as per Microsoft’s recommendations.

    You may need to restart the SQL Server Integration Services service, as this caches information about what components are installed, as well as restarting any open instances of Business Intelligence Development Studio (BIDS) / Visual Studio that you may be using to build your SSIS packages.

    For 2005/2008 Only - Finally you will have to add the transformation to the Visual Studio toolbox manually. Right-click the toolbox, and select Choose Items.... Select the SSIS Data Flow Items tab, and then check the Row Number transformation in the Choose Toolbox Items window. This process has been described in detail in the related FAQ entry for How do I install a task or transform component?

    We recommend you follow best practice and apply the current Microsoft SQL Server Service pack to your SQL Server servers and workstations, and this component requires a minimum of SQL Server 2005 Service Pack 1.

    Downloads

    The Row Number Transformation  is available for SQL Server 2005, SQL Server 2008 (includes R2) and SQL Server 2012. Please choose the version to match your SQL Server version, or you can install multiple versions and use them side by side if you have more than one version of SQL Server installed.

    Row Number Transformation for SQL Server 2005

    Row Number Transformation for SQL Server 2008

    Row Number Transformation for SQL Server 2012

    Row Number Transformation for SQL Server 2014

    Version History

    SQL Server 2014

    Version 4.0.0.6 - SQL Server 2014 release. Includes upgrade support for both 2005, 2008 and 2012 packages to 2014.
    (6 Sep 2014)

    SQL Server 2012

    Version 3.0.0.6 - SQL Server 2012 release. Includes upgrade support for both 2005 and 2008 packages to 2012.
    (5 Jun 2012)

    SQL Server 2008

    Version 2.0.0.5 - SQL Server 2008 release.
    (15 Oct 2008)

    SQL Server 2005

    Version 1.2.0.34 – Updated installer.
    (25 Jun 2008)

    Version 1.2.0.7 - SQL Server 2005 RTM Refresh. SP1 Compatibility Testing. Added the ability to reuse an existing column to hold the generated row number, as an alternative to the default of adding a new column to the output.
    (18 Jun 2006)

    Version 1.2.0.7 - SQL Server 2005 RTM Refresh. SP1 Compatibility Testing. Added the ability to reuse an existing column to hold the generated row number, as an alternative to the default of adding a new column to the output.
    (18 Jun 2006)

    Version 1.0.0.0 - Public Release for SQL Server 2005 IDW 15 June CTP
    (29 Aug 2005)

    Screenshot

    Row Number Transformation Editor dialog

    Code Sample

    The following code sample demonstrates using the Data Generator Source and Row Number Transformation programmatically in a very simple package.

    Package package = new Package();
    package.Name = "Data Generator & Row Number";
    
    // Add the Data Flow Task 
    Executable taskExecutable = package.Executables.Add("STOCK:PipelineTask");
    
    // Get the task host wrapper, and the Data Flow task
    TaskHost taskHost = taskExecutable as TaskHost;
    MainPipe dataFlowTask = (MainPipe)taskHost.InnerObject;
    
    
    // Add Data Generator Source
    IDTSComponentMetaData100 componentSource = dataFlowTask.ComponentMetaDataCollection.New();
    componentSource.Name = "Data Generator";
    componentSource.ComponentClassID = 
        "Konesans.Dts.Pipeline.DataGenerator.DataGenerator, Konesans.Dts.Pipeline.DataGenerator, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b2ab4a111192992b";
    CManagedComponentWrapper instanceSource = componentSource.Instantiate();
    instanceSource.ProvideComponentProperties();
    instanceSource.SetComponentProperty("RowCount", 10000);
    
    // Add Row Number Tx
    IDTSComponentMetaData100 componentRowNumber = dataFlowTask.ComponentMetaDataCollection.New();
    componentRowNumber.Name = "FlatFileDestination";
    componentRowNumber.ComponentClassID = 
        "Konesans.Dts.Pipeline.RowNumberTransform.RowNumberTransform, Konesans.Dts.Pipeline.RowNumberTransform, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b2ab4a111192992b";
    CManagedComponentWrapper instanceRowNumber = componentRowNumber.Instantiate();
    instanceRowNumber.ProvideComponentProperties();
    instanceRowNumber.SetComponentProperty("Increment", 10);
    
    // Connect the two components together
    IDTSPath100 path = dataFlowTask.PathCollection.New();
    path.AttachPathAndPropagateNotifications(componentSource.OutputCollection[0], componentRowNumber.InputCollection[0]);
    
    
    #if DEBUG
    // Save package to disk, DEBUG only
    new Application().SaveToXml(String.Format(@"C:\Temp\{0}.dtsx", package.Name), package, null);
    #endif
    
    package.Execute();
    
    foreach (DtsError error in package.Errors)
    {
        Console.WriteLine("ErrorCode       : {0}", error.ErrorCode);
        Console.WriteLine("  SubComponent  : {0}", error.SubComponent);
        Console.WriteLine("  Description   : {0}", error.Description);
    }
    
    package.Dispose();

    Troubleshooting

    Make sure you have downloaded the version that matches your version of SQL Server. We offer separate downloads for SQL Server 2005, SQL Server 2008 and SQL Server 2012.

    If you get an error when you try and use the component along the lines of The component could not be added to the Data Flow task. Please verify that this component is properly installed.  ... The data flow object "Konesans ..." is not installed correctly on this computer, this usually indicates that the internal cache of SSIS components needs to be updated. This is held by the SSIS service, so you need restart the the SQL Server Integration Services service. You can do this from the Services applet in Control Panel or Administrative Tools in Windows. You can also restart the computer if you prefer. You may also need to restart any current instances of Business Intelligence Development Studio (BIDS) / Visual Studio that you may be using to build your SSIS packages.

    Once installation is complete you need to manually add the task to the toolbox before you will see it and to be able add it to packages - How do I install a task or transform component?

    Please also make sure you have installed a minimum of SP1 for SQL 2005. The IDtsPipelineEnvironmentService was added in SQL Server 2005 Service Pack 1 (SP1) (See  http://support.microsoft.com/kb/916940). If you get an error Could not load type 'Microsoft.SqlServer.Dts.Design.IDtsPipelineEnvironmentService' from assembly 'Microsoft.SqlServer.Dts.Design, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'. when trying to open the user interface, it implies that your development machine has not had SP1 applied.

    Very occasionally we get a problem to do with the properties not being created with the correct data type. Since there is no way to programmatically to define the data type of a pipeline component property, it can only infer it. Whilst we set an integer value as we create the property, sometimes SSIS decides to define it is a decimal. This is often highlighted when you use a property expression against the property and get an error similar to Cannot convert System.Int32 to System.Decimal. Unfortunately this is beyond our control and there appears to be no pattern as to when this happens. If you do have more information we would be happy to hear it. To fix this issue you can manually edit the package file.

    In Visual Studio right click the package file from the Solution Explorer and select View Code, which will open the package as raw XML. You can now search for the properties by name or the component name. You can then change the incorrect property data types highlighted below from Decimal to Int32.

    <component id="37" name="Row Number Transformation" componentClassID="{BF01D463-7089-41EE-8F05-0A6DC17CE633}" … >
        <properties>
            <property id="38" name="UserComponentTypeName" …>
            <property id="41" name="Seed" dataType="System.Int32" ...>10</property>
            <property id="42" name="Increment" dataType="System.Decimal" ...>10</property>
            ...

    If you are still having issues then contact us, but please provide as much detail as possible about error, as well as which version of the the task you are using and details of the SSIS tools installed.

    Comments (28) -

    12/1/2008 6:41:39 PM #

    Bryant

    This is a superb component! Is there any chance that you might be considering any enhancements? I have a situation where I am loading historical employee records for over 7,000 employees averaging about 9 records each. I am using the Row Number Transformation to generate a sequence number for each historical record but I have to process each employee separately in a Foreach loop to get the sequence number to start over for each employee. It would be nice if I could just get the sequence number to start over when the value of a specified field changes. How hard would this be to implement?

    Feel free to email me if you have any questions or if my comments aren't entirely clear.

    Thank you!
    Bryant

    Bryant United States

    1/7/2009 9:46:44 PM #

    Geoff B

    Note that if the data is coming from a query, and the query is running against SQL Server 2005 or later, you can also get a row number natively.
    For example:

    select
      name,
      cast(row_number() over(order by name) as int) as row_num
    from
      sys.tables
    order by
      name

    Geoff B United States

    2/11/2009 8:12:46 PM #

    Arjan Fraaij

    Hello Darren,

    I think this is a great component but do have some questions:
    -- You say that the Seed value is a INT32, I use a variable intDimSK defined as INT32, when I use the expression for the SEED = @[User::intDimSK] I get error message: Cannot convert System.int32 to system.decimal??? The Seed is an INT32 so why should it be converted?

    -- When I use (DT_DECIMAL,2)@[User::intDimSK] I get error message: Specified OLE variant is invalid. (Microsoft.DataTransformationServices.Controls)

    What am I doing wrong?

    Regards,
    Arjan Fraaij

    Arjan Fraaij Netherlands

    2/11/2009 8:17:37 PM #

    Arjan Fraaij

    Darren,

    Sorry found a solution, a bit strange to do but it works.

    (DT_DECIMAL,0) (DT_WSTR,10)@[User::intDimSK]

    So first convert INT32 Variable to WSTR and then to DT_Decimal.
    Or simply define the variable as STRING instead of INT32...

    Arjan Fraaij Netherlands

    3/18/2009 9:32:58 PM #

    Rick Fitch

    Has anyone found a solution for this problem?
    ===================================

    Unexpected exception for RowNumberTransformUI.Edit

    ===================================

    Could not load type 'Microsoft.SqlServer.Dts.Design.IDtsPipelineEnvironmentService' from assembly 'Microsoft.SqlServer.Dts.Design, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'. (Konesans.Dts.Pipeline.RowNumberTransformUI)

    ------------------------------
    Program Location:

       at Konesans.Dts.Pipeline.RowNumberTransform.RowNumberTransformUIForm..ctor(IDTSComponentMetaData90 dtsComponentMetaData, Variables variables, Connections connections, IServiceProvider serviceProvider)
       at Konesans.Dts.Pipeline.RowNumberTransform.RowNumberTransformUI.Edit(IWin32Window parentWindow, Variables variables, Connections connections)

    Rick Fitch United States

    7/14/2009 10:02:53 PM #

    Chris Evans

    This is a note on the error

    Could not load type 'Microsoft.SqlServer.Dts.Design.IDtsPipelineEnvironmentService' from assembly 'Microsoft.SqlServer.Dts.Design, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'. (Konesans.Dts.Pipeline.RowNumberTransformUI)

    I use the Row Number, Trash and Checksum Transforms and have loaded them several times with no problems.  Then all of a sudden during a different install, I started getting this error.  What seems to have finally worked for me was to load one at a time and restart SSIS service after each install.  Didn't seem to be any rhyme or reason, but since no one had posted any solutions, I thought I would share what I did.


    Chris Evans United States

    7/21/2009 9:45:19 AM #

    Darren Green

    Chris, the IDtsPipelineEnvironmentService was added in SQL Server 2005 Service Pack 1 (SP1) (See  http://support.microsoft.com/kb/916940). The error Could not load type 'Microsoft.SqlServer.Dts.Design.IDtsPipelineEnvironmentService' from assembly 'Microsoft.SqlServer.Dts.Design, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'. implies that your development machine has not had SP1 applied. Thanks for raising it, I'll update the body of included this requirement.


    I have updated the post with more details in the troubleshooting section about how to fix corrupt properties, Decimal vs Int32, and will have a clear out of some of the comments shortly.

    Darren Green United Kingdom

    8/5/2009 9:04:15 PM #

    Jennifer

    The 2005 download link is name version 1.2.0.34, yet the update notes state they're up to version 1.2.0.7.

    Jennifer United States

    8/12/2009 4:33:44 PM #

    Madhava

    I am also getting same error and I am not able to fix it...Please give anybody valuable assistance on this issue.

    Unexpected exception for RowNumberTransformUI.Edit
    Could not load type 'Microsoft.SqlServer.Dts.Design.IDtsPipelineEnvironmentService' from assembly 'Microsoft.SqlServer.Dts.Design, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'. (Konesans.Dts.Pipeline.RowNumberTransformUI)

    Madhava India

    8/16/2009 9:39:30 AM #

    Darren Green

    Madhava, as posted above, you need to install a minimum of SP1.

    Darren Green United Kingdom

    9/26/2009 4:11:58 PM #

    Ellen Russell

    Regarding the Int32 error issue:  It appears the the test for data type matching occurs the first time the property is set via the Property Expresssion Editor and is invoked when exiting the Editor.   I use this component to assign surrogate key values in the data pipeline, so I need to assign the Seed property to the value of a user variable that holds the next key value.  To get around the type mismatch, when I create the expression for the property the first time in the Editor, I assign the value to 0.  This allows me to successfully exit the Editor and loads the expression into the Properties pane for the data flow task.  After it's presented there, I can change the value from 0 to the name of the key value variable.  Once the expression is instantiated this way, you can alter it in the Properties Expression Editor without encountering the type mismatch error again.

    Ellen Russell United States

    1/12/2010 1:31:41 PM #

    Dino

    Hi,

    I'm trying to set the SEED value using a variable and guess what I'm facing the same problems as mentioned above, only a different message "@[User::MAX_IDC_PERSON] is not a valid value for Decimal.". No matter what I try (type casting, changing the datatype of the variable, using the 0 option mentioned by Ellen) can't get it to work.

    We are using SQL Server std ed. 64 bit on win2008

    Please advise?
    thx,

    Dino Netherlands

    1/20/2010 11:05:46 AM #

    Darren Green

    Dino, have you read the core article above? It suggests editing the package XML to change the type to the expected Int32, instead of Decimal. Whilst this is not ideal, it will solve the problem (this assumes of course that your variable is an Int32 as well).

    Darren Green United Kingdom

    3/1/2010 7:39:17 AM #

    Shailesh

    Works like a charm!
    Thanks for this great transformation.

    Shailesh India

    4/8/2010 5:30:00 PM #

    Josh Grant

    We're having a problem using the "Final Number Output Variable".

    Good: When the component processes exactly one row, it returns the seed value in this variable.
    Bad: When the component processes NO rows, it also returns the seed value in this variable.

    This makes the "Final Number Output Variable" unsafe to use for scenarios where the source data set could sometimes be empty.  We're coding around it by using the built-in Row Count component, but I think the component should be changed so that it only updates the "Final Number Output Variable" when at least one row is processed.

    Josh Grant Canada

    8/12/2010 11:08:50 AM #

    Ravi

    HI

    I have a question?

    I have already 100 or n records in my table. Now using seunce generator i need to inter records starting from n+1 . How to achieve it?

    Ravi India

    8/12/2010 3:44:58 PM #

    Darren

    Ravi, I would add an Execute SQL Task before the Data Flow Task. You can then query to get the max value, (SELECT MAX(MyId) AS MaxId FROM MyTable) save it to a variable in the SQL task (SQL Task ResultSet) and then use a property expression on the data flow task to set the Row Number Tx's Seed property.

    Darren United Kingdom

    10/3/2010 5:36:06 PM #

    rick

    Wonderful component, just what we needed...thank you!

    rick United States

    2/7/2011 8:19:52 PM #

    Sri

    Hi,
    We are already using the Row number transformation in SSIS 2005. we just migrated to 2008 R2. Since the row number transformation is different from 2005 to 2008 we are facing compatibility error. Do you have any patch to fix this? I can send you more details if you need.

    Thanks
    Sri

    Sri United States

    2/21/2011 9:57:55 AM #

    Darren Green

    Sri, please post more details or email us direct. There are 2005 and 2008 versions of this transformation available. You will need to install the correct version to match your SSIS install. The 2008 version is fully compatible with 2008 R2. Our 2008 components use the standard out of the box upgrade mapping, which means the references are upgraded for our components as well when a package is upgraded. You will of course need to have installed the 2008 version of our component for this to work.

    Darren Green United Kingdom

    3/2/2011 5:15:32 PM #

    Pradeep

    Though I restarted the SSIS Services, the Service Pack updated to SP4, i still get the following error, when i use the rowcount tranformation:

    TITLE:
    ------------------------------

    Unexpected exception for RowNumberTransformUI.Edit

    ------------------------------
    ADDITIONAL INFORMATION:

    Could not load type 'Microsoft.SqlServer.Dts.Design.IDtsPipelineEnvironmentService' from assembly 'Microsoft.SqlServer.Dts.Design, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'. (Konesans.Dts.Pipeline.RowNumberTransformUI)

    ------------------------------
    BUTTONS:

    OK
    ------------------------------

    Pradeep India

    3/10/2011 7:43:58 PM #

    Darren

    Pradeep, can you confirm you have applied the SQL 2005 SP4 to the PC that you are using to develop the package?

    Darren United Kingdom

    7/27/2011 1:34:17 AM #

    Steve

    Has anyone experienced problems assigning the Final Number Output to a variable? I get 0 returned every time despite the transformation working correctly.

    Steve Australia

    5/23/2012 9:43:38 PM #

    John Pal

    This is reaally cool and works great. Could you please post the code, if is cin c#.

    John Pal United States

    5/24/2012 4:07:40 PM #

    Jyoti

    Can we use this Transformation programatically ?? How do we set the properties of this in C#.

    Jyoti United States

    6/5/2012 5:26:30 PM #

    Daren Green

    Jyoti, please see the sample code now added to the post.

    Daren Green United Kingdom

    6/8/2012 2:37:44 PM #

    Leo Viloria

    I was able to implement the row number with one exception, the Final Number Output Variable output field is not updated at all. What's missing in my setup?

    Thank you,

    Leo

    Leo Viloria United States

    6/19/2012 9:39:15 AM #

    Darren Green

    Leo, I've just tested the output variable feature again and can't find any issues with it. I used a Script Task and a MessageBox call to display the variable at run-time, but I could have used one of the other mechanisms such as breakpoints and the Watch window to access the runtime value of the variable.

    Darren Green United Kingdom

    Add comment

      Country flag

    biuquote
    • Comment
    • Preview
    Loading

    RecentComments

    Comment RSS