Creating Inferred Dimension Members with SSIS

  • Comments posted to this topic are about the item Creating Inferred Dimension Members with SSIS

  • A great article. Very detailed and easy to follow.

  • Looking for this for a while..thanks!

  • A good example, though I am new to SSIS and was unable to find the SourceName when building the Script Component. From where is that input coming ?


  • That's a good catch. The SourceName field does not exist in this solution and it should not be included in the screenshot (Figure 14).

    It can be beneficial to record in your dimension table the source of the dimension row. In an earlier iteration of this solution, I added the SourceName column to the data flow and assigned it a value. While the solution no longer uses that column, a SourceName value can be added to the InsertColumnList / InsertColumnValues columns so the source of the record is included in the dimension table.

  • Good article, needed to know a little about SSIS while following example.



    Thomas LeBlanc, MVP Data Platform Consultant

  • hello ,

    I have gone through this blog post and i followed the same things as described in this article but i could not get the solution and its just sending some error also i am new to SSIS and i dont no how the SSIS tools are used.mainly i dont no how lookup transformation works and other tool too..

    so can any one help me out order to know and perform as this obtain the solution also i could not get the idea where we put the VB script code in visual studio ?..which project have to make to execute a same in this article.

    so help me out to know this article with more details..i am also just working in the field of BI mainly SSRS/SSAS.


    Anil Maharjan

  • hello,

    I also try to implement as similar to this blog but couldnot get the solution excatly some error occurrs.

    So,i am also wondering where we can download the package you built,can't we use the package that you already built.Hope you will help us out..!!!

    Also i am new to SSIS so could some one suggest me some blog\links from where we can learn SSIS basic details.

    hope some one help me out... 🙂


    Anil Maharjan

  • Anil,

    Welcome to SSIS! This article admittedly covers a more advanced topic and is not geared for SSIS beginners. Here is some reference material you may find helpful. I also encourage you to search and participate in forums on SQL Server Central. There is a lot of great information for SSIS beginners.

    - Tutorial: Creating a Simple ETL Package: (Lesson 1, Step 6 covers configuration of a Lookup Transformation)

    - Lookup Transformation:

    - Extending the Data Flow with the Script Component:

  • hi - thanks so much for such a timely article! Just trying to do exactly what you have outlined in this article, - which I feel was extremely well laid out and clear. Thanks for it!

  • Hi Robert, this is a great article and elegant way to add inferred dimension.

    Just wanted to know how was BKColumnList planned to be used?

  • Hello,

    BKColumnList is a comma separated list of the fields that make up the business key or natural key of your dimension. The sample uses a single field (Product_Nbr) as the business key. However, dimensions may use a composite business key. For example, the business key of the sample dimension could have been the combination of Product_Nbr and Product_Name, in which case the value of BKColumnList would be "Product_Nbr, Product_Name".

  • Nice article!

    Plain, Simple, Clear, and To the Point!

    Thanks for sharing 🙂

    Dattatrey Sindol
    Blog: Datta's Ramblings on Business Intelligence 'N' Life[/url]

    This information is provided "AS IS" with no warranties, and confers no rights.

  • Very good and well written article. Would it be possible to get the SSIS solution files uploaded to the site ?

  • Hello,

    thank you for article. Could someone please upload the Project Files of this solution? I am not unable to make this work as the scrip task returns some error.

    "[Generate Product_Key for no match [28]] Error: System.Data.OleDb.OleDbException (0x80040E14): There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

    Invalid column name 'EE505'.

    at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e)

    at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)

    at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper100 wrapper, Int32 inputID, IDTSBuffer100 pDTSBuffer, IntPtr bufferWirePacket)"



Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply