SQL Server – Business Intelligence Development (BIDS) – Data Flow Task – Lookup Transformation – Introduction

What is Lookup Transformation?

Lookup Transformation is exactly what it sounds like.

Using columns that you designate from your Source Data Source, it draws a line to the Secondary Data Source and project columns.

Here is Microsoft’s own definition:

Link
“The Lookup transformation performs lookups by joining data in input columns with columns in a reference dataset. You use the lookup to access additional information in a related table that is based on values in common columns”.

Back End Database Platforms Supported

There are some restrictions, such as:

  1. OLE DB Provider
    • The Only 3 providers that are supported are
      • SQL Server
      • Oracle
      • DB2

 

Join Type

  • It is an Equijoin, an inner join so to speak
    • Only Source Records that have corresponding entries in the Secondary Table are returned
    • On each Source Record that have more than one Secondary Record, only one matching record is returned
  • Both singleton ( Single Column ) and Composite ( Multiple Columns ) joins  are supported
  • Comparison is usually case-sensitive, except if a Cache Manager is used to cache the dataset
    • To guide against mismatches due to case and space differences, please use
      • Conversion Functions
        • upper, lower
      • Trimming Functions
        • ltrim, rtrim, trim
    • To facilitate the adjustments to data mentioned above, please use
      • Character Map Transform
      • Derived Column Transform
  • When matching records are not found in the Lookup Table, an error is triggered.
    • This error is considered to be  a Row Level Error
    • Error can be handled using a few choices
      • Failure ( Fail Component )
      • Ignored ( Ignore failure )
      • Captured ( Redirect Row )
  • When records are found, we choose the columns to merge into the Source Table
    • This error is Column Level Error
    • Errors are handleable based on the same choices available to Row Level Error Handling

 

Business Case

OLTP tables are usually built to be very slim.

OLAP tables on the other hand are built to be fat.

 

Data Model

Drawn below are three tables.

DatabaseDiagram

 

The OLTP table is LTEDRKV.callLog, the lookup table is dbo.internationalCallingCode, and the Data Warehouse Table is LTEDRKV.callLogDW.

Our need is very preliminary and it is also track the country being called in the LTEDRKV.callLogDW.

 

Data

Data Definition Language

Function

Function – [dbo].[ufn_getPhoneNumberCountryCode]

 
if object_id('[dbo].[ufn_getPhoneNumberCountryCode]') is null
begin
 
    exec('create function [dbo].[ufn_getPhoneNumberCountryCode]() 
          returns varchar(60)
          as
          begin
            return 1
          end
          ')
 
end
go
 
alter function [dbo].[ufn_getPhoneNumberCountryCode]
(
    @phoneNumber varchar(100)
) 
returns varchar(60)
with schemabinding
as
begin
 
    return
    (
 
        case
                when (@phoneNumber is null) then null
                when charindex('-', @phoneNumber) = 0 then null
                else left(
                              @phoneNumber
                            , charindex('-', @phoneNumber) -1
                         )
        end
 
    )
 
end    
 
go
 
grant execute [dbo].[ufn_getPhoneNumberCountryCode] on [public]
go
 

 

Table

Table – [dbo].[internationalCallingCode]

 

 
set noexec off
go
 
if object_id('[dbo].[internationalCallingCode]') is not null
begin
    set noexec on
end
go
 
create table [dbo].[internationalCallingCode]
(
 
      [country]                varchar(60) not null
    , [countryCallingCode]  varchar(6)  not null
 
    , [dateAdded]   datetime not null
                        default getdate()
 
    , constraint [PK_InternationalCallingCode]
            primary key
            (
                  [country]    
                , [countryCallingCode]
            )
 
)
go
 
set noexec off
go
 

 

 

 

Table – [LTEDRKV].[callLog]

 

 

 
set noexec off
go
 
if schema_id('LTEDRKV') is null
begin 
 
    exec('create schema [LTEDRKV] authorization [dbo]') 
 
end 
go
 
if object_id('[LTEDRKV].[callLog]') is not null
begin
    set noexec on
end
go
 
create table [LTEDRKV].[callLog]
(
 
      [callID] bigint not null
            identity(1,1)
 
    , [phoneNumber] varchar(60) not null
 
    , [dateAdded]   datetime not null
                        default getdate()
 
    , constraint [PK_CallLog]
            primary key
            (
                  [callID]    
            )
 
)
go
 
set noexec off
go
 
if not exists
    (
        select 1
        from   sys.columns tblSC
        where  tblSC.[object_id] = object_id('[LTEDRKV].[callLog]')
        and    tblSC.[name] = 'phoneNumberCountryCode'
    )
begin
 
    alter table [LTEDRKV].[callLog]
        add [phoneNumberCountryCode] 
            as [dbo].[ufn_getPhoneNumberCountryCode]
            (
                [phoneNumber]
            )
            persisted
end
go
 

 

Table – [LTEDRKV].[callLogDW]

 

 

 
use [DBLab]
go
 
/*
 
    drop table [LTEDRKV].[callLogDW]
 
*/
 
set noexec off
go
 
if schema_id('LTEDRKV') is null
begin 
 
    exec('create schema [LTEDRKV] authorization [dbo]') 
 
end 
go
 
if object_id('[LTEDRKV].[callLogDW]') is not null
begin
    set noexec on
end
go
 

 
create table [LTEDRKV].[callLogDW]
(
      [callID]                   bigint not null
            
    , [phoneNumber]               varchar(60) not null
 
    , [phoneNumberCountryCode] varchar(6) not null
    , [country]                   varchar(60) not null
 
    , [dateAdded]               datetime not null
 
    , constraint [PK_CallLogDW]
            primary key
            (
                  [callID]    
            )
 
                        
)
go
 
set noexec off
go

 

 

Business Intelligence Development (BIDS)

Designer

Lookup

OLE DB Source Editor

The OLE DB Source Editor has 3 tabs. And, those are the Connection Manager, Columns, and Error Output.

Here is a brief outline of the purpose of each tab.

  1. Connection Manager
    • Reference or shall we say a pointer to one of the Connection Managers defined for the Project
    • Data Access mode
      • Our choices are
        • Table or view
          • Name of the table or view
        • SQL
      • In our case it is LTEDRKV.callLog
  2. Columns
    • Available External Columns
      • All the Columns defined in the External Columns are listed
      • Optimize performance by unchecking uneeded columns
      • In our case we choose all the columns ( callID,  phoneNumber, dateAdded, phoneNumberCountryCode )
  3. Error Output
    • Excception Handling Block
      • Data read from Source / External into Internal Storage
        • If truncation error occurs, how should it be handled
          • Fail?
OLE DB Source Editor – Connection Manager

OLEDBSourceEditor-ConnectionManager

OLE DB Source Editor – Columns

OLEDBSourceEditor-Columns

 

OLE DB Source Editor – Error Output

OLEDBSourceEditor-ErrorOuput

 

 

Lookup Transformation Editor

In the Lookup Transformation Editor we perform an inner join against our Lookup Table ( dbo.InternationalCallingCode )

This Editor has 3 Tabs, as well.

The Tabs are named – Reference Table,  Columns, and Advanced.

Here is a brief outline of the purpose of each tab.

  1. Reference Table
    • Details on the Referenced Table
      • The Connection Manager that houses it
      • And, the Table\View or SQL Statement
      • In our case it is dbo.InternationalCallingCode
  2. Columns
    • Define the Joining Columns as well as the Columns to Project from the Referenced Table
      • Review Data Model and see if Foreign Key Relationships are defined
      • Again, optimize by unchecking columns from the Referenced Table
      • In our case we are joining on the columns below:
        • LTEDRKV.callLog.phoneNumberCountryCode <> dbo.InternationalCallingCode countryCallingCode
    • Projected Columns
      • dbo.InternationalCallingCode.country
  3. Advanced
    • Enable Memory Restriction

 

LookupTransformationEditor-ReferenceTable

 

Columns

LookupColumns

 

Advanced

LookupTransformationEditor-Advanced

 

 

Lookup Transformation Editor – Is Data Captured in Data WareHouse Table

In the Lookup Transformation Editor we perform an inner join against our Destination Table ( LTEDRKV.callLogDW )

In cases where corresponding records are not found in the Lookup Table, we redirect them.

This Editor has 3 Tabs, as well.

The Tabs are named – Reference Table,  Columns, and Advanced.

Here is a brief outline of the purpose of each tab.

  1. Reference Table
    • Details on the Referenced Table
      • The Connection Manager that houses it
      • And, the Table\View or SQL Statement
      • In our case it is LTEDRKV.callLogDW
  2. Columns
    • Define the Joining Columns as well as the Columns to Project from the Referenced Table
      • Review Data Model and see if Foreign Key Relationships are defined
      • Again, optimize by unchecking columns from the Referenced Table
      • In our case it is LTEDRKV.CallLog.CallID <->LTEDRKV.callLogDW.CallID
    • Projected Columns
      • Nothing Defined
  3. Advanced
    • Enable Memory Restriction
  4. Configure Error Output
    • Input Output :- Lookup Output
    • Column :- N/A
    • Error :-
      • Ignore Failure
      • Redirect row
      • Failure Component
      • In our case, we chose “Redirect Row
    • Truncation :- N/A

 

Reference Table

ReferenceTable

 

 

Columns

ReferenceTable-Columns

 

Advanced

ReferenceTable-Advanced

 

Configure Error Output

ConfigureErrorOutput-20161029-0805AM

 

 

Lookup Error Output

In the preceding we join LTEDRKV.callLog against LTEDRKV.callLogDW.

When matching data is not found, they are placed in an ERROR bucket.

 

 

 

Data Flow Path Editor

This Editor has 3 Tabs, as well.

The Tabs are named – Reference Table,  Columns, and Advanced.

Here is a brief outline of the purpose of each tab.

  1. General
    • SourceName :- In our case it is Lookup Error Output
    • DestinationName :- In our case it is OLE DB Destination Input
  2. Metadata
    • Summary for the columns
      • Column Name
      • Datatype
      • Length
      • Source Component

 

General

DataFlowPathEditor-CallLogDW-General

Metadata

DataFlowPathEditor-CallLogDW-Metadata

 

 

 

OLE DB Destination Editor

The OLE DB Destination Editor has 3 tabs. And, those are the Connection Manager, Columns, and Error Output.

Here is a brief outline of the purpose of each tab.

  1. Connection Manger
    • Reference or shall we say a pointer to one of the Connection Managers defined for the Project
    • Data Access mode
      • Our choices are
        • Table or view ( Fast Load )
        • Name of the table or view
        • SQL
      • In our case it is LTEDRKV.callLogDW
  2. Columns
    • Available External Columns
      • All the Columns defined in the External Columns are listed
      • Optimize performance by unchecking uneeded columns
      • In our case we choose all the columns ( callID,  phoneNumber, dateAdded, phoneNumberCountryCode )
  3. Error Output
    • Exception Handling Block
      • Data read from Source / External into Internal Storage
        • If truncation error occurs, how should it be handled
          • Fail?

 

 

Connection Manager

ConnectionManager

 

Mappings

Map each Source Column unto the Destination Column

Mappings

 

 

Data Flow Path Editor

dataflowtask-calllog

Summary

Our sample SSIS Project is pretty simple.

We retrieve transactional data from callLog table and join that table against our Lookup table.

We attempt a join against our Lookup table.

When data is not find, we insert the missing rows into the callLogDW.

 

Reference

  1. Lookup Transformation
    • Integration Services > Data Flow > Integration Services Transformations
      Link
  2. SSIS Team Blog
    • Matt Masson
      • Lookup Pattern: Case Insensitive
        Link
      • Lookup – Using the cache connection manager
        Link

SQL Server–Business Intelligence Development (BIDS)–Warnings-Truncation May Occur due to inserting data from Data Flow Column

 

Background

Experiencing SSIS Package execution errors and so sought out understanding looking at the dbo.sysdtslog90 table.

 

Errors And Warnings

Here is what [dbo].[sysdtslog90] contains

Event Source Datacode Message
OnWarning PaymentAccount Transformation -2145348953 Truncation may occur due to inserting data from data flow column “card_lastfour” with a length of 100 to database column “card_lastfour” with a length of 4.
OnWarning PaymentAccount Transformation -2145348953 Truncation may occur due to inserting data from data flow column “card_firstfour” with a length of 100 to database column “card_firstfour” with a length of 4.
OnWarning StudentDiscountHistory Transformation -2145348953 Truncation may occur due to inserting data from data flow column “discountCode” with a length of 600 to database column “discountCode” with a length of 50.
OnWarning DTSAnalytics90 -2145348953 Truncation may occur due to inserting data from data flow column “discountCode” with a length of 600 to database column “discountCode” with a length of 50.

 

Remediation Process

Access the Data Flow and review each step.

Component

OLE DB Source

Advanced Editor – Input and Output Columns

For the OLE DB Source, there are two sets of Columns, the “External Columns” and the “Output Columns.

The “External Columns” is exposed by the OLE-DB Provider when queried by BIDs.

And, the “Output Columnsis maintained in house by BIDs in response to what was received upon quering the provider.

External Columns

Here is how the discountCode column is defined for the column cited.

BTW, the column cited is discountCode.

AdvancedEditor-OLEDBSource-ExternalColumns-DiscountCode-0620PM

 

Output Columns
Output Columns – Original

Here is the original settings for the discountCode column.

AdvancedEditor-OLEDBSource-OutputColumns-DiscountCode-0621PM

 

Output Columns – Revised

Here is the revised setting

AdvancedEditor-OLEDBSource-OutputColumns-DiscountCode-0622PM

 

Explanation:

  1. We changed the Length to 50

 

After Effect

The change will reverberate across dependant components.

Non-Conforming Lineage ID

Here is a sample error message:

Input column dest_discountCode has lineage ID 3632 that was not previously used in the Data..

 

InvalidColumnDueToLienage

 

To fix, approach each component, edit it by double-clicking on it and “Apply” previous changes.

RestoreInvalidColumnReferenceEditor

 

Ensuring the following:

  1. Include downstream invalid column references
    • The checkbox should be checked

 

OLE DB Destination

 

Here is the warning message we receive on the OLE-DB Destination

Control Flow

Image

 

dbo-PaymentAccount-TruncationWarning-Silo

dbo-PaymentAccount-TruncationWarning-Silo

OLE-DB Destination Control

OLE-DB Destination Control – Tab – Mappings

Here is the Mappings setting.

 

OLEDBDestination-Mappings

 

There is nothing indicative of column sizes.

 

XML File

Very, Very thankfully SSIS files are saved in XML Files.  And, so we can look at the file, see what is in it, and search through it.

And, so brought by an XML Editor and searched for the column name, card_lastfour, cited.

XML File – Student Sort

Here is the code for Student Sort ..

Image

ComponentStudentSort

 

Explanation
  1. We see that both card_lastfour and card_firstfour have a size of 100
  2. And, our target is only expecting 4
SSIS

Back to BIDs, let us look for the component referenced, “Student Sort

StudentSort-BIDS

 

Explanation
  1. We see that both card_lastfour has a size of 100, as well
  2. We will like to change to 4, but the Length is readonly in the Sort component

 

XML File – Component – Student Reference Merge Join
Here is the code for “Student Reference Merge Join”.
Image

ReferenceMergeJoin

 

BIDS – Component – Student Reference Merge Join

Before

In the merge join, we see our two suspect columns, card_lastfour and card_firstfour.

MergeJoinTransformationEditor-Before-20161026-0115PM

 

In Progress – Delete Columns

In the merge join, we will remove the suspecting columns, card_lastfour and card_firstfour.

We do so by selecting each column, right clicking on it, and choosing “Delete” from the drop-down menu.

In Progress – Delete Columns – Initiate Delete

MergeJoinTransformationEditor-InProgess-20161026-0120PM

 

In Progress – Delete Columns – Initiate Delete

MergeJoinTransformationEditor-InProgess-20161026-0126PM

 

Click the OK button to apply the changes.

 

In Progress – Re-add Columns

Here we re-adding the columns we removed earlier.

MergeJoinTransformationEditor-InProgess-Adding-20161026-0134PM

 

Summary

Using the GUI development tools, BIDs, we are able to find unaligned column sizes.

Unfortunately in some cases the tool does not display and avail the column sizes and one has to use an XML Editor or Text Editor.

When doing so search out the identified column and pay attention to the column length attribute.

SQL Server Reporting Services [SSRS] – Error – Null is not declared. ‘Null’ constants is no longer supported; use ‘System.DBNull’ instead

Background

It is a little bit of work to use null-able arguments in Sql Server Reporting Services (SSRS).

 

In DataSet Properties, Tried using “=(Null)”

For the dataset, when we set the parameter value to =Null

DatasetProperties-Parameters-(Null)

 

 Textual:

Error - Null is not declared.  'Null' constants is no longer supported; use 'System.DBNull' instead
Image:

NullConstantIsNoLongerSupported

 

In Dataset Properties, tried using “System.DBNull”

In Dataset properties, When we set the parameter value to =System.DBNull

DatasetProperties-Parameters-System.DBNull

Text
Error: [BC30691] 'DBNull' is a type in 'System' and cannot be used as an expression.

Image:
DBNullIsATypeInSystemAndCannotBeUsedInAnExpression

In Dataset Properties, tried using “=Nothing”

Tried setting the parameter value to:

  • =Nothing
  • =System.Convert.DBNull

 

DatasetProperties-Parameters-Nothing

 

We get an error.

Error

In Designer, when trying to preview the report.

Textual:

One or more parameters required to run the report have not been specified

 

Image:

OneOrMoreParametersRequiredToRunTheReportHaveNotBeenSpecified


 

Report Parameter – Default Value – Setting Parameter Value

  • Access the parameter
  • Access the “Default Values” panel
  • In the “Default Values” panel
    • In “Select from one of the following options” radio group, choose the “Specify values” option
    • In “Value” set “(Null)

ReportParameter-DefaultValues-SpecifyValues-Value-(Null)

 

Does not help

Dataset Properties – Report Parameter

Let us remove the parameter

Original:

Here are the dual parameters.

DatasetProperties-Parameter-Null-Included

Revised:

We have removed the second parameter.

DatasetProperties-Parameter-Null-Excluded

Error Message:

OneOrMoreParametersRequiredToRunTheReportHaveNotBeenSpecified (2nd Parameter removed)

Report Parameter – Set Value to Empty

Let us go set the parameter’s value to “” or System.String.Empty.

DatasetProperties-Parameter-SetToEmptyString

Things are good.

SQL Server Profiler

Here is what we see in SQL Server Profiler:

SQLServerProfiler-ParameterValueToEmpty

Transact SQL

In Transact SQL, make sure of the following:

    • The arguments is nullable — (variable [datatype] = null )
    • That you have same code line for when the variable is null and empty

 Sample Code:


if object_id('[dbo].[usp_city]') is null
begin

    exec('create procedure [dbo].[usp_city] as select 1/0 as [shell] ')

end
go

alter procedure [dbo].[usp_city]
(
      -- make sure @countryCode is nullable
      @countryCode varchar(255) = null

     --make sure that @country is nullable
    , @country     varchar(255) = null
)
as

    select
              vc.country
            , vc.countryCode
            , vc.city

    from   [dbo].[vw_city] vc

    where  (

                (
                    vc.countryCode
                                = case
                                        when (@countryCode is null) then vc.countryCode
                                        when (@countryCode = '') then vc.countryCode
                                        else @countryCode
                                  end
                )

                and
                (
                    vc.country
                                = case
                                        when (@country is null) then vc.country
                                        when (@country = '') then vc.country
                                        else @country
                                  end
                )

           ) 

    order by
                  vc.country asc
                , vc.city

go

 

Conclusion

It appears that SSRS does not really support the setting of parameters to Null, but it supports empty string.

 

References

SSRS

Microsoft – SQL Server – Integration Services (SSIS) – Business Intelligence Studio – Dealing with data integrity errors – Using Package Logging

Introduction

Wanted to see how useful SSIS Package logging is when run-time data inconsistency errors occur.

 

Enable Logging at SSIS Package Level

 

  1. Launch Business Intelligence Studio (BIDS)
  2. Load SSIS Project
  3. Access SSIS Package
  4. Access SSIS Design pane
  5. Access the “Control Flow” tab
  6. In the Control-Flow tab, right click in an empty spot, and from the drop-down menu, select the “Logging” menu item
  7. In the “Configure SSIS Logs”, make the changes detailed below
    1. In the Containers’s tab, enable logging at the package level
    2. In the Providers and Logs tab, add “SSIS log provider for Text files” as a provider type
    3. In the Details tab, enable “OnError” / “OnTaskFailed” /”OnWarning”

 

Before

ConfigureSSISLogs-Before

 

 

After – Providers and Logs

 

ConfigureSSISLogs-After-ProviderAndLogs

 

 

After – Providers and Logs

 

ConfigureSSISLogs-After-Details

 

 

Log File Contents

Here is what we captured in the log-file:



OnError,PF00N9ME20B7,DBLAB\dadeniji,Data Flow Task,{675AA3C7-8057-41FB-8F05-6929845F2746},{7B5D3817-390E-4B87-BAE6-3C1DD41D00D3},8/4/2014 10:10:44 AM,8/4/2014 10:10:44 AM,-1071636471,0x,SSIS Error Code DTS_E_OLEDBERROR.  
An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "Unspecified error".

OnError,PF00N9ME20B7,DBLAB\dadeniji,xferUsingLogging,{A51961CB-EB18-48C8-8A38-67EE876A7F9A},{7B5D3817-390E-4B87-BAE6-3C1DD41D00D3},8/4/2014 10:10:44 AM,8/4/2014 10:10:44 AM,-1071636471,0x,SSIS Error Code DTS_E_OLEDBERROR.  
An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "Unspecified error".

OnError,PF00N9ME20B7,DBLAB\dadeniji,Data Flow Task,{675AA3C7-8057-41FB-8F05-6929845F2746},{7B5D3817-390E-4B87-BAE6-3C1DD41D00D3},8/4/2014 10:10:44 AM,8/4/2014 10:10:44 AM,-1071607780,0x,
There was an error with 
OLE DB Destination.Inputs[OLE DB Destination Input].Columns[inceptionDate]
on OLE DB Destination.Inputs[OLE DB Destination Input]. 
The column status returned was: 
"The value violated the integrity constraints for the column.".

OnError,PF00N9ME20B7,DBLAB\dadeniji,xferUsingLogging,{A51961CB-EB18-48C8-8A38-67EE876A7F9A},{7B5D3817-390E-4B87-BAE6-3C1DD41D00D3},8/4/2014 10:10:44 AM,8/4/2014 10:10:44 AM,-1071607780,0x,
There was an error with 
OLE DB Destination.Inputs[OLE DB Destination Input].Columns[inceptionDate] 
on OLE DB Destination.Inputs[OLE DB Destination Input]. 
The column status returned was: 
"The value violated the integrity constraints for the column.".



 

 

 

SQL Server Profiler

 

Let us see how useful SQL Server Profiler is when we encounter incomplete data errors.

 

Events Selection

 

Here is the “Events Selection”

 

Events Selection

SQLServerProfiler-TraceProperties

 

 

 Tabulated

Events Event Detail Column
Errors and Warnings
ErrorLog Error
EventLog Error
Exception Error
User Error Message Error
Stored Procedures
 RPC:Completed  Error
TSQL
SQL:BatchStart
SQL:BatchCompleted  Error

 

 

 

 

Events

Here are the captured events :

 

SQLServerProfiler

 

Interpretation

There are a couple of points we noticed when we executed the SSIS package and reviewed the generated “SQL Server Profiler” session:

  • There are no occurrences of “Errors and Warnings”.  This means that our ETL tool internally and gracefully handles the errors
  • The SQL:BatchCompleted event exposes a value 1 in the Error Column

 

 

 

Summary

In summary, SSIS Logging can be useful to capture run time errors.

But, unfortunately the errors captured is not very precise.

Microsoft – SQL Server – Integration Services (SSIS) – Business Intelligence Studio – Dealing with data integrity errors

Background

During Data transfer operations, data compatibility mismatch will occasionally surface.

Microsoft Business Intelligence Studio has a built in tooling to address such problems.  Let us touch on one of them.

 

 

Set up Lab

Let us create our database objects.

We have a simple database structure with two tables; dbo.custList and dbo.custListDest.  The only difference between the two tables is that the “inceptionDate” can be null in the source table, but not in the destination table.

Here is what our tables look like in SQL Server Management Studio.

Design Table – dbo.custList ( Source Table )

 

dbo-custList

 

 

Design Table – dbo.custListDest ( Destination Table )

 

dbo-custListDest

 

 

Data – dbo.custList ( Source Table )

Here is what our data looks like.

Notice that using CTRL-0 we intentionally nulled out some rows’ inceptionDate column.

 

dbo-custList (data)

 

Business Intelligence Studio (BIDS)

 

Control

Here is what our BIDS Task Flowchart looks like:

 

Control

 

 

Task – Execute SQL Task

Our first task is to remove all records from the destination table, truncate table dbo.custListDest.

 

 

ExecuteSQLTask

 

Task – Data Flow Task

 

Data Flow Task

Here is our Data Flow Task

DataFlowTask

 

Thank goodness the flow is  straightforward.  It starts off with our OLE DB Source, into our OLE DB Destination, and errors are logged into the Flat File Destination.

 

OLE DB Destination – Connection Manager

 

OLEDBDestinationEditor--ConnectionManager

 

 

OLE DB Destination – Mappings

 

OLEDBDestinationEditor--Mappings

 

 

OLE DB Destination – Error Output

 

OLEDBDestinationEditor--ErrorOutput

 

OLE DB Destination Editor – Settings

 

Tab Element Value
Connection Manager
Data Access Mode Table or view fast load
Keep Identity  Yes
Keep nulls  Yes
Table lock  Off
Check Constraints Yes
 Rows per batch  Please use an optimal value based on what your environment can support
 Mappings
 Please map the columns based on your need
Error Output
 Error  Redirect Error

 

 

 

Data Flow Path Editor

Pasted below is us passing off errors unto an OLEDB Destination Output.

DataFlowPathEditor

 

Settings

DataFlowPathEditor

 

 

Flat File Destination

Flat File Destination Editor – Connection Manager

 

FlatFileDestinationEditor-ConnectionManager-Header

 

Flat File Destination Editor – Flat File Connection Manager Editor

Within the FlatFile Destination Editor, we clicked on the Edit button to customize our Error File.

FlatFileConnectionManagerEditor

 

Flat File Destination Editor – Mappings

Here are the columns that we want to capture in our error file.

FlatFileDestinationEditor-Mappings

 

Settings

 

Tab Element Value
Connection Manager
Overwrite data in the file Yes
File name Please choose an existing folder in your user folder
Unicode  Yes ( to support internationalization )
Format Delimited
Column names in the first data row Yes

 

 

 

 

 

 

Profiling

 

SQL Server Profiler

As good developers we ran SQL Server profiler and took a quick look at the SQL Traffic.  Seeing that “insert bulk” is in use, we feel comfortable about the throughput.

 

SQLServerProfiler-InsertBulk

 

 

 

Listening To

Listening to Yvonne DeVaughn sing “Healing Waters for the Soul”
https://www.youtube.com/watch?v=sgfBByJjfh8

Yvonne Devaughn is a director with AVA (Advocacy for victims of Abuse). And, AVA is an important component of the community outreach of Evangelical Covenant Church.

You can read more here – http://www.npcwm.org/what-we-do/ava/

 

 

 

 

Technical: Microsoft – SQL Server – v2008/R2 – Business Intelligence Studio – Cube Browsing – Error dragging measures\attributes to view pane

Background

When Browsing Cube, one of the errors one might encounter as you add measures and attributes to the pane is one that reads:


Retrieving the COM class factory for component with CLSID 
{C966837E-970C-4F4F-A91D-D81E21ABBBDB} failed due to the following error: 80040154. 
(Microsoft Visual Studio)

Full Error Message

Error Text


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

Retrieving the COM class factory for component with CLSID {C966837E-970C-4F4F-A91D-D81E21ABBBDB} failed due to the following error: 80040154. (Microsoft Visual Studio)

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

   at Microsoft.AnalysisServices.Controls.MiscUtilities.GetMixedDataObjectForFilterAndPivotTable(DataObject dataObjectForFilter, PivotTableDataObject pivotTableDataObject)
   at Microsoft.AnalysisServices.Controls.PivotTableBoundMetadataBrowser.GetDataObject(TreeNode node)
   at Microsoft.AnalysisServices.Controls.MetadataTreeView.OnItemDrag(ItemDragEventArgs e)
   at System.Windows.Forms.TreeView.TvnBeginDrag(MouseButtons buttons, NMTREEVIEW* nmtv)
   at System.Windows.Forms.TreeView.WmNotify(Message& m)
   at System.Windows.Forms.TreeView.WndProc(Message& m)
   at Microsoft.AnalysisServices.Controls.MetadataTreeView.WndProc(Message& msg)
   at Microsoft.AnalysisServices.Browse.CubeBrowser.CubeBrowserMetadataTreeView.WndProc(Message& msg)



Error Image

 

RetrievingTheComClassFactory

 

 

Resolution

There are correlating errors on the .Net and prominent fixes includes:

Repair / Install Microsoft Office 2003 Web Components

 

The OWC track did not work for us.

Inspect MS Windows registry

Searched the Windows registry for C966837E-970C-4F4F-A91D-D81E21ABBBDB and found the stub, but could not find CLASS (CLS) branch.

VSIntegrationNativeHelpers

 

 

Tried fiddling with MS Windows Registry

Tried fiddling with MS Windows Registry, but could not find good starting data on the Internet; note that data has to match SQL Server Version, etc.

Install latest Product patch

Downloaded and Installed MS SQL Server v2008/R2 SP2.

BTW, patch is available @ http://www.microsoft.com/en-us/download/details.aspx?id=30437

 

Possible Workaround

Registry Tweaking

BTW, if you want to try to tweak your registry and see if that might work and you avoid installing\re-installing a Service Pack, here is a working version:



Windows Registry Editor Version 5.00

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{C966837E-970C-4f4f-A91D-D81E21ABBBDB}]
@="MarshalledToIStreamDataObject Class"
"AppID"="{B2463DC8-B3FA-4BEC-945E-60219DCC6FD8}"

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{C966837E-970C-4f4f-A91D-D81E21ABBBDB}\InprocServer32]
@="c:\\Program Files (x86)\\Microsoft SQL Server\\100\\Tools\\Bin\\Microsoft.DataWarehouse.VsIntegration.Helpers.dll"
"ThreadingModel"="Apartment"

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{C966837E-970C-4f4f-A91D-D81E21ABBBDB}\ProgID]
@="VsIntergrationNativeHelpers.Marshalle.2"

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{C966837E-970C-4f4f-A91D-D81E21ABBBDB}\TypeLib]
@="{84F2933D-1F4E-43D8-9006-372E64998B36}"



Please keep in mind that the registry entry pasted above is for MS Windows 2008/R2 SP2. Other versions of SQL Server will invariable have version specific entries.

 

Listening To

Listening to Brett Eldredge – Raymond (Video) [http://www.youtube.com/watch?v=txCUwSKo1kg]