SQL Server – Integration Services – Error – “Could not load package because of error 0x80070057. The path is not valid.”

 

Background

Our Business Intelligence person came to me with what turned out to be another good error last night.

Error

Error Image

jobHistory-20170125-0721AM

 

Error Textual

Could not load package because of error 0x80070057.  The path is not valid.

The path could not be loaded.

 

TroubleShooting

SQL Server Profiler

Launched the pre-eminent SQL Server troubleshooting tool for lazy Admins, SQL Server Profiler.

Configuration

Configured it to include:

  1. Events
    • User Error Message
  2. Attributes
    • DatabaseName
    • Error

 

Events

 

SSP-20170124-1105PM (cleaned-up)

 

Findings

  1. Execute permission was denied on the object ‘sp_ssis_getpackage’

 

Remediation

As the job’s step is using a proxy account, we added the proxy account to the db_ssisoperator role ( in the msdb database ).

 
set nocount on;
go
 
use [msdb]
go
 
declare @rolename sysname
declare @username sysname
 
set @rolename = 'db_ssisoperator'
set @username = 'domain\SSISProxy'
 
/*
    Create user, if it does not exist
*/
if suser_id(@username) is null
begin
 
    create user [domain\SSISProxy]
        from login [domain\SSISProxy];
 
end
 
/*
 
    IS_ROLEMEMBER
        <a href="https://msdn.microsoft.com/en-us/library/ee677633.aspx">https://msdn.microsoft.com/en-us/library/ee677633.aspx</a>
 
    sp_addrolemember
        <a href="https://msdn.microsoft.com/en-us/library/ms187750.aspx">https://msdn.microsoft.com/en-us/library/ms187750.aspx</a>
 
*/
if ( IS_ROLEMEMBER ( @rolename, @username)  = 0)
begin
 
    print 'Grant ' + @username + ' access to role ' + @rolename
 
    exec sp_addrolemember
              @rolename   = @rolename
            , @membername = @username
            ;
 
end
 
go

 

 

SQL Server – Integration Services – Network Flow and Rules

Background

Wanted to cover the Network Ports that are used by Microsoft’s Integration Services.

Network Trace

Wireshark

Port Mapper ( Port 135 )

Network Flow

rpc-20160106-1111pm-cleanedup

 

Explanation

  1. From Ephemeral Port ( 57916) connect to Server Port 135
  2. Request from client to server to issue RemoteCreateInstance
  3. Authenticate User
    • via NTLMSSP_AUTH
    • Pass in username

 

Integration Services

Network Flow

ssis-20170106-1122pm-brushed-up

 

Explanation

  1. From Ephemeral Port ( 57917) connect to SQL Server Integration Services Component
  2. This is important has it depends on how thru Component Services the Integration Services Component’s endpoint is configured

 

Component – Microsoft SQL Server Integration Services [NN.MM]

Using Component Services, let us review the Component’s endpoint configuration

 

Configuration

Here are our choices:

  1. Disable Protocol sequence
  2. Use default endpoints
  3. Use static endpoint
  4. Use intranet range of dynamic endpoints
  5. Use internet range of dynamic endpoints

 

Digging Deeper
  1. Disable Protocol sequence
    • Disable Network
  2. Use default endpoints
    • Use ephemeral ports
  3. Use static endpoint
    • Use static endpoint
  4. Use intranet range of dynamic endpoints
    • Use endpoint’s defined for Intranet
  5. Use internet range of dynamic endpoints
    • Use endpoint’s defined for Internet

 

Our Choice

To streamline our conversation with the Firewall team, we chose to use a static endpoint

dcomendpointconfigurations-usestaticport

 

 

NetLogonSAMAccount

Network Flow

rpcnetlogon_20170107_1206am-brushed-up

 

Explanation

This area covers the Network Authentication.

We did not have to make special care in our environment and so I can not cover in details.

But, please keep it mind when connecting between hosts that are not in the same Active Directory Domain, etc.

 

Component – Windows Management & Instrumentation ( WMI )

Network Flow

wmi-20170106-1144pm-brushed-up

 

Explanation

  1. From Ephemeral Port ( 57919) we connect to the port we dedicated to WMI
  2. This is important has it depends on how thru Component Services the WMI Component’s endpoint is configured
Configuration

Using Component Services, we will configure Windows Management and Instrumentation to listen on a specific port

wmi-configure-staticport

 

Network Listening Ports

Resource Monitor

On newer MS Windows Oses, you will be well served to remote connect to the Integration Services host, and run Resource Monitor

MsDtsSrvr.exe

msdtssrvr-20170107-0617am

Explanation

We can see that MsDtsSrvr.exe is:

  1. listening on Network Port 50000
  2. We have a record each for IPv4 and IPv6
  3. The internal MS Windows Firewall is allowing access to the Port

RPCC – svchost (winmgmt)

svchost-rpcss-20170107-0640am

 

Explanation

We can see that svchost.exe ( RPCSS ) is:

  1. listening on Network Port 135
  2. We have a record each for IPv4 and IPv6
  3. The internal MS Windows Firewall is allowing access to the Port

Unlike Integration Service which has its own process, RPCSS is being processed by a svchost.exe process.

 

Windows Management & Instrumentation – svchost (winmgmt)

 

svchost-exe-winmgmt-20170107-0630am

 

Explanation

We can see that svchost.exe ( winmgmt ) is:

  1. listening on Network Port 50090
  2. We have a record each for IPv4 and IPv6
  3. The internal MS Windows Firewall is allowing access to the Port

Unlike Integration Service which has its own process, winmgmt is being processed by a svchost.exe process.

 

Tabulated View

Objective Filter
 RPC Port Mapper Port 135
 Microsoft Integration Services Ephemeral Ports
Static Port
Intranet range of dynamic endpoints
Internet range of dynamic endpoints
 Network Authentication Ephemeral Ports
 Windows Management and Instrumentation ( WMI ) Ephemeral Ports
Static Ports
Intranet range of dynamic endpoints
Internet range of dynamic endpoints

SQL Server – Integration Services – Connectivity – Issue – “Class Not Registered” / Address by installing ssms /w same version as SSIS Server

Background

This is another post along our series on how to address an inability to connect to SQL Server Integration Services ( SSIS ) from a remote client.

Errors

Here is the error message.

Error – “Class Not registered”

Error Message

Image

ClassNotRegistered-cutomized-_thumb2

 

Textual

 
Failed to retrieve data for this request.
 
(Microsoft.SqlServer.Management.Sdk.Sfc)
 
For help, click: <a href="http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&amp;LinkId=20476">http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&amp;LinkId=20476</a>
 
------------------------------
 
Connecting to the Integration Services service on the computer "LABDB" failed with the following error: "Class not registered".
 
This error can occur when you try to connect to a SQL Server 2005
 
Integration Services service from the current version of the SQL Server
 
tools. Instead, add folders to the service configuration file to let
 
the local Integration Services service manage packages on the SQL
 
Server 2005 instance.
 

 

Diagnostic

In our environment, we thankfully observed that users running SSMS from a SQL Server 2014 install can connect, but not so with users running the v2016 version.

Things are starting to make sense as DCOM is version specific.

Remediation

If Server is running v2016 or later, Download & Install SQL Server Management Studio [version]

Starting from v2016, SSMS can be downloaded and installed as a standalone product.

 

If Server is running v2014 or earlier and No current client install, download & Install SQL Server Management Studio [version]

If Server is on v2014 or earlier and you do not have a current SQL Server install, you should be able to download SSMS versioned for the server and install it.

 

Download

We want v2014 and so we go here.

File Details

download-details

Choice

From the list above, as we want the full SQL Server Express Advanced Services, we choose “MgmtStudio 64BIT\SQLManagementStudio_x64_ENU.exe“.

The file name translates to:

  • MgmtStudio
    • Management Studio
  • 64Bit
    • Our OS is 64-bit
  • ENU
    • English

If Server is running v2014 or earlier and Current client install that you will like kept, download & Install SQL Server Express Advanced Services

 

Download

We want v2014 and so we go here.

File Details

download-details

 

Choice

From the list above, as we want the full SQL Server Express Advanced Services, we choose “ExpressAndTools 64BIT\SQLEXPRWT_x64_ENU.exe“.

The file name translates to:

  • Express
    • Express Engine
  • Tools
    • The SQL Server Tools are inclusive
  • 64Bit
    • Our OS is 64-bit
  • ENU
    • English

 

Install

ScreenShots

Installation Type

Choices:

  1. Choose to “Perform a new installation of SQL Server

 

SQLServer-InstallationType

 

 

License Terms

Choices:

  1. Choose to “Accept the License Terms

 

SQLServer-LicenseType

 

Feature Selection -01

Choices:

  1. Checked “Management Tools – Basic” & “Management Tools – Complete

 

FeatureSelection

 

Feature Selection –02

Choices:

  1. Checked
    • Instance Feature
      • Database Engine Services
    • Shared Features
      • Management Tools – Basic & Management Tools – Complete

 

SQLServer-FeatureSelection-20170105-1057AM

 

Instance Configuration

Choices:

  1. Named Instance
    • Give the instance a name
      • In our case, we name it v2014
        • This signifies to us that it is a SQL Server Version 2014

SQLServer-InstanceConfiguration

 

Explanation:

  1. Notice that the targeted folders will reflect the name given to the Instance

 

 

Server Configuration

Choices:

  1. Services
    • Please change Startup Type from Automatic to Manual
      • In our case, we do not intend to use the local services, just the client.
      • And, so it is likely OK to change the “Startup Type” from Automatic to Manual or Disabled

sqlserver-serverconfiguration

 

 

Database Engine Configuration

Choices:

  1. We will ignore as we do not really care about the Database Engine features.

 

databaseengineconfiguration-brushed-up

 

 

Installation Progress

 

We just have to wait for the Install to progress & complete.

 

Validate

Validate by running the Version specific Management Studio

 

Microsoft Connect Items

  1. SSMS 2016 error connecting to Integration Services on a SQL Server 2008 R2 instance – by G Britton
    • ID :- 2779968
    • Opened By :- G Britton
    • Date Published :- 2016-Jun-3rd
    • Link

 

 

SQL Server – Integration Services – Permissions on Client Computer – Day 1

Background

This is the second in a series of post in which we will discuss troubleshooting techniques where users are able to connect to an Integration Service when attempting to do so over a Remote Desktop Connection.  But, stumble badly when attempting to do so on their local desktops.

 

Series

  1. SQL Server – Integration Services – Connectivity–Issue–”Class Not Registered”
    Link

 

Error Message

Here is the error message

===================================
Cannot connect to LABDB.

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

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

——————————
Program Location:

at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.Process(Object connectionInfo, Request request)
at Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService.ValidateConnection(UIConnectionInfo ci, IServerType server)
at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

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

Connecting to the Integration Services service on the computer “LABDB” failed with the following error: “Class not registered”.

This error can occur when you try to connect to a SQL Server 2005 Integration Services service from the current version of the SQL Server tools. Instead, add folders to the service configuration file to let the local Integration Services service manage packages on the SQL Server 2005 instance.

——————————
Program Location:

at Microsoft.SqlServer.Dts.Runtime.Application.GetServerInfo(String server, String& serverVersion)
at Microsoft.SqlServer.Dts.SmoEnum.DTSEnum.GetData(EnumResult erParent)
at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData()
at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData(Request req, Object ci)
at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.GetData(Object connectionInfo, Request request)
at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.Process(Object connectionInfo, Request request)

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

Connecting to the Integration Services service on the computer “LABDB” failed with the following error: “Class not registered”.

This error can occur when you try to connect to a SQL Server 2005 Integration Services service from the current version of the SQL Server tools. Instead, add folders to the service configuration file to let the local Integration Services service manage packages on the SQL Server 2005 instance.
——————————
Program Location:

at Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.GetServerInfo(String bstrMachineName, String& serverVersion)
at Microsoft.SqlServer.Dts.Runtime.Application.GetServerInfo(String server, String& serverVersion)

TroubleShooting

One area that is very easy to overlook is the permission requirements that are required on the connecting client.

SysInternals

Process Monitor

MustBeRunFromAnAdministratorAccount

 

Unfortunately for now our troubleshooting pathway is blocked.

 

Remediation

Administrator Mode

As always, we took the big hammer approach.

And, that was to start command shell in Administrator mode and launch ssms.exe from there.

Command Shell in Administrator Mode

Here is what command shell looks like in Administrator Mode

commandShellInAdministratorMode

 

In Command Shell Administrative Mode, run ssms.exe

Access the Command Shell and initiate ssms.exe

Please be sure to specify the full path to ssms.exe if you have multiple versions of ssms installed and it is material.

 

Is UAC Issue

The problem appears to be User Access Control ( UAC ) related.

But, not quite sure how to detect whether UAC is enabled.

SQL Server – Integration Services – Connectivity–Issue–”Class Not Registered”

Background

Using SQL Server Management Studio (v14), trying to connect to a remote SQL Server Integration Services running on SQL Sever v2012.

But, running into problems.

I have been at the same junction since before the Christmas break; and so I am saddened and perplexed by it.

 

Errors

Error – “Class Not registered”

Error Message

Image

ClassNotRegistered-cutomized

 

Textual


Failed to retrieve data for this request.

(Microsoft.SqlServer.Management.Sdk.Sfc)

For help, click: <a href="http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&amp;LinkId=20476">http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&amp;LinkId=20476</a>

------------------------------

Connecting to the Integration Services service on the computer "LABDB" failed with the following error: "Class not registered".

This error can occur when you try to connect to a SQL Server 2005

Integration Services service from the current version of the SQL Server

tools. Instead, add folders to the service configuration file to let

the local Integration Services service manage packages on the SQL

Server 2005 instance.

Remediation

Extend Integration Services Configuration – Add new repository

Everybody and their mama, especially on the Q/A sites, says that the problem is due to version mismatch.

And, that to fix it, one should look for the SSIS Service configuration file and add a new XML Tag to it.

The XML tag is meant to have SQL Server expose other SQL Server Instances on same box, Clustered instances, etc.

&nbsp;

Configuration – File – Original

&nbsp;


<?xml version="1.0" encoding="utf-8"?>
<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>
  <TopLevelFolders>
    <Folder xsi:type="SqlServerFolder">
      <Name>MSDB</Name>
      <ServerName>DBLAB</ServerName>
    <Folder xsi:type="FileSystemFolder">
      <Name>File System</Name>
      <StorePath>..\Packages</StorePath>
    </Folder>

  </TopLevelFolders>
</DtsServiceConfiguration>

 

Configuration – File – Revised

<?xml version="1.0" encoding="utf-8"?>
<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>
  <TopLevelFolders>
    <Folder xsi:type="SqlServerFolder">
      <Name>MSDB</Name>
      <ServerName>DBLAB</ServerName>
    </Folder>
	 <Folder xsi:type="SqlServerFolder">
         <Name>DBLAB - Local MSDB ( SQL Server 2012)</Name>
         <ServerName>DBLAB</ServerName>
      </Folder>	
    <Folder xsi:type="FileSystemFolder">
      <Name>File System</Name>
      <StorePath>..\Packages</StorePath>
    </Folder>

  </TopLevelFolders>
</DtsServiceConfiguration>

 

made the changes, but still stuck

 

 

Network Monitoring

WireShark
ScreenShot

nwtrace20170104-0337pm-masked

Explanation
  1. Network Addresses
    • Our Desktop is x.x.x.189
    • Our SSIS Server is x.x.x.111
  2. Number 269 thru 277
    • From Desktop Connect to TCP Port 135
      • Port 135 is RPC Endpoint Mapper (EPM)
        • Protocol
          • DCERPC
  3. Number 278 thru 279
    • Request from Source To Component Services
      • Requesting RemoteCreateInstance

Nothing seems to be the matter!

 

Re-configure Integration Services DCom Component to use specific port

I have seen this problem before, but too much In & Out burger is making my brain mushy.

And, so took to the Net and found this wonderful gem:

Authored :- Mohammad Hoque
Title :- Integration Service is running on dynamic port can’t connect remotely
Link

Basically, Mohammad’s contention is that the problem is because RPC uses ephemeral ports and that we can either keep things that way and open up all of  TCP Block 49152 thru 65535.

Or hard code a  specific TCP Port.

 

Remediation Steps:
  1. Using Component Services, Determine the Application ID that corresponds to the version of your Integration Services
  2. In MS Windows Registry, search out HKEY_LOCAL_MACHINE\SOFTWARE\Classes\AppID\<Application ID>
    • Add a new registry key
  3. Restart Integration Services
  4. Retry Client Connection

 

Component Services

Launch Component Services and find SQL Server Version Specific Integration Services

DCOM Config

You want to look under Component Services \ Computers \ My Computer \ DCOM Config

dcomconfig

And, look for Microsoft SQL Server Integration Services <version#>

findintegrationservices

Microsoft SQL Server Integration Services 12

 

component-general-20170104-0531pm

The info we want is Application ID

 

Registry

Original

registry-20170104-0545pm

Revised

Here we add :

  1. Item :- Endpoints
  2. Name :- REG_MULTI_SZ
  3. Data
    • Syntax :- ncacn_ip_tcp,0,[portNumber]
    • Value :- ncacn_ip_tcp,0,50000

Adding Data …

registry-add-endpoints-20170104-1000pm

 

Post Data

registry-20170104-0546pm

 

 

Integration Services

Restart Integration Services.

 

Client Retry

Retry from Client ( SSMS )

 

Network Monitoring

WireShark
ScreenShot

nwtrace20170104-1020pm-masked

Explanation

With an hardcoded TCP Port for our DCOM Component and Firewall Changes for TCP 135 and our assigned DCOM Port, we have a fuller network conversation.

 

Credits

Crediting Mohammad Hoque

Integration Service is running on dynamic port can’t connect remotely
April 26, 2011
Link

Microsoft Connect Items

  1. SSMS 2016 error connecting to Integration Services on a SQL Server 2008 R2 instance – by G Britton
    • ID :- 2779968
    • Opened By :- G Britton
    • Date Published :- 2016-Jun-3rd
    • Link

 

Summary

For most people things will work.

In our case, it did not and we will cover edge cases in later posts.

 

Addendum

  1. SQL Server – Integration Services – Permissions on Client Computer – Day 1
    Published On: 2017-Jan-05
    Link
  2. SQL Server – Integration Services – Connectivity – Issue – “Class Not Registered” / Address by installing ssms /w same version as SSIS Server
    Published On: 2017-Jan-06
    Link
  3.  SQL Server – Integration Services – Network Flow and Rules
    Published On: 2017-Jan-07
    Link

 

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