SQL Server On Linux – Integration Services – Installation

Background

Let us install SQL Server Integration Services on our Linux system.

os

Our os is centOS and so we will be using yum as our Installer.

Installation

Installer

Find Installer Installer

yum search

syntax

yum search [search-tag]

sample

yum search mssql

output

yum.search.20181202.0326AM.PNG

Explanation
  1. Our match is
    • mssql-server-is.x86_x64

 

Confirm Installer

yum info

syntax

yum info [tag]

sample

yum info mssql-server-is.x86_64

output

yum.info.20181202.1131AM.PNG

Explanation
  1. Package Info
    • Name :- mssql-server-is
    • Architecture :- x86_64
    • Version :- 14.0.3015.40
    • Size :- 815 MB
    • Server :- Microsoft SQL Server Integration Services

 

Install

yum install

syntax


yum install [search-tag]

sample


yum install mssql-server-is

output

output -01

yum.install.20181202.0328AM.PNG

output -02

yum.install.20181202.0334AM.PNG

Explanation

Package downloaded & installed.

 

Confirm Installation

ssis-conf

Configure ssis by invoking /opt/ssis/bin/ssis-conf.

Please pass along the setup argument.

syntax


sudo /opt/ssis/bin/ssis-conf setup 

sample


sudo /opt/ssis/bin/ssis-conf setup 

output

  1. We are prompted for the following :-
    • SQL Server Edition
    • Agreement to License Terms

output

output -01

configure.20181202.0337AM.PNG

 

Review Installation

Services

systemctl

systemctl – list

syntax

systemctl --all 

sample

systemctl --all | grep "Microsoft"

output

systemctl.20181202.1152AM.PNG

Explanation

The listed services are :-

  1. mssql-server.service
    • Database Engine
  2. ssis-telemetry.service
    • SQL Server Integration Services
      • Telemetry

We noticed that unlike the Windows Install, we do not have an actual Integration Services Engine; just the telemetry app.

 

References

  1. Microsoft
    • Docs / SQL / SQL Server on Linux
      • Install SQL Server Integration Services (SSIS) on Linux
        Link
      • Configuration SQL Server Integration Services on Linux with ssis-configure-ssis
        Link
      • Limitations and known issues for SSIS on Linux
        Link
      • Schedule SQL Server Integration Services package execution on Linux with cron
        Link

 

SQL Server – Integration Services Catalog – Standard Reports – Execution Logs – Permission

Background

One of the managers in our Development group opened up a Ticket last night.

Ticket

The ticket read “Database: sql_server – Issue with table refresh to dev SQL database“.

1 – Daniel Response

As a “smart aleck”, I replied :-

Can you please use SQL Server Management Studio to troubleshoot.
I think as a senior member we gave you ample permissions to troubleshoot.
You want to look at the following:

A) SQL Server Agent
B) SQL Server SSIS Catalog
C) File System Log Folder & Files

If you get permission issue, please let us know and I will create a task and grant you additional permissions.

2 – Manager Response

Daniel,

I have looked at some of this stuff and have not been able to determine what the problem is. It could be that I just don’t know what to look at.

I will reach out the developer to see if she can help me. Please keep this ticket open in case I need to ask for some guidance/assistance from you.

Thanks,
Manager

Issue

Report

Image

report_empty.201816.0102PM.png

Observation

Let us see why she all tallies are coming up zero.

And, all no detail records.

Trouble Shooting

Integration Services Catalogs

Outline

Let use review SSISDB folder permissions for our principal.

Catalog

Catalog – SSISDB

Folder Permissions
Image

folderPermissions.20181116.1107AM.PNG

Explanation

Principal has Read and Read Objects permissions.

Though she has read objects permissions it does not seem to be sufficient.

Remediation

Grant additional permission

In MS SQL Servers v2016 and above, we can grant ssis_logreader role membership to the principal.

Krishnakumar Rukmangathan
Link

The SSIS upgrade to the SSIS 2016 can be an option here. SSIS 2016 brought a new role in the SSISDB, This new ssis_logreader database-level role that you can be used to grant permissions to access the views that contain logging output to users who aren’t administrators.

Ref: Link

Change Code

Outline

  1. SSISDB Database
    • Create new database role
    • Add principals to created database role
    • Alter Code to check role membership

Processing

Create new role

SQL

USE [SSISDB]
GO

if not exists
    (

        select *

        from   sys.database_principals tblSDP

        where  tblSDP.[name] = 'ssis_logreader'
    )
begin

    print 'Create Role - ssis_logreader ...'

    CREATE ROLE [ssis_logreader]
        authorization [dbo];

    print 'Created Role - ssis_logreader '		

end
GO

Add Members to new role

SQL
Syntax

USE [SSISDB]
GO

ALTER ROLE [role]
    ADD MEMBER [principal]
GO

Sample

USE [SSISDB]
GO

ALTER ROLE [ssis_logreader]
    ADD MEMBER [LABDC\psmith]
GO

Amend Code

Outline
  1. Amend the following views
    • Database :- SSISDB
      • Views
        • catalog.event_messages
        • catalog.executions
      • Code original
        • Database Role Membership
          • ssis_admin
            • IS_MEMBER(‘ssis_admin’)
        •  SQL Server Role Member
          • sysadmin
            • IS_SRVROLEMEMBER(‘sysadmin’)
[catalog].[event_messages]
SQL
USE [SSISDB]
GO

ALTER VIEW [catalog].[event_messages]
AS
SELECT     opmsg.[operation_message_id] as [event_message_id],
           opmsg.[operation_id],
           opmsg.[message_time],
           opmsg.[message_type],
           opmsg.[message_source_type],
           opmsg.[message],
           opmsg.[extended_info_id],
           eventmsg.[package_name],
           eventmsg.[event_name],

           message_source_name =
                      CASE
                        WHEN (opmsg.message_source_type = 10) THEN 'ISServerExec'
                        WHEN (opmsg.message_source_type = 20) THEN 'Transact-SQL stored procedure'
                        ELSE eventmsg.message_source_name
                    END,
           eventmsg.[message_source_id],
           eventmsg.[subcomponent_name],
           eventmsg.[package_path],
           eventmsg.[execution_path],
           eventmsg.[threadID],
           eventmsg.[message_code]
FROM       [internal].[operation_messages] opmsg LEFT JOIN [internal].[event_messages] eventmsg
           ON opmsg.[operation_message_id] = eventmsg.[event_message_id]
WHERE     (

                opmsg.[operation_id] in
                (
                    SELECT [id] FROM [internal].[current_user_readable_operations]
                )

                OR (IS_MEMBER('ssis_admin') = 1)

                OR (IS_SRVROLEMEMBER('sysadmin') = 1)

               -- Added on 2018-11-16 11:47 AM
               OR (IS_MEMBER('ssis_logreader') = 1)

           )
GO

[catalog].[event_messages]
SQL

USE [SSISDB]
GO

ALTER VIEW [catalog].[executions]
AS
SELECT     execs.[execution_id],
           execs.[folder_name],
           execs.[project_name],
           execs.[package_name],
           execs.[reference_id],
           execs.[reference_type],
           execs.[environment_folder_name],
           execs.[environment_name],
           execs.[project_lsn],
           execs.[executed_as_sid],
           execs.[executed_as_name],
           execs.[use32bitruntime],
           opers.[operation_type],
           opers.[created_time],
           opers.[object_type],
           opers.[object_id],
           opers.[status],
           opers.[start_time],
           opers.[end_time],
           opers.[caller_sid],
           opers.[caller_name],
           opers.[process_id],
           opers.[stopped_by_sid],
           opers.[stopped_by_name],
           opers.[operation_guid] as [dump_id],
           opers.[server_name],
           opers.[machine_name],
           ossysinfos.[total_physical_memory_kb],
           ossysinfos.[available_physical_memory_kb],
           ossysinfos.[total_page_file_kb],
           ossysinfos.[available_page_file_kb],
           ossysinfos.[cpu_count]
FROM       [internal].[executions] execs INNER JOIN [internal].[operations] opers
           ON execs.[execution_id]= opers.[operation_id]
           LEFT JOIN [internal].[operation_os_sys_info] ossysinfos
           ON ossysinfos.[operation_id]= execs.[execution_id]
WHERE      (
                opers.[operation_id] in
                        (
                           SELECT id
                           FROM [internal].[current_user_readable_operations]
                        )

                OR (IS_MEMBER('ssis_admin') = 1)

                OR (IS_SRVROLEMEMBER('sysadmin') = 1)

               -- Added on 2018-11-16 11:51 AM
                OR (IS_MEMBER('ssis_logreader') = 1)

            )

GO

Validation

Once the changes outlined above are done, one can validate by taken upon the user of the user and try to access the view.

SQL


EXECUTE AS LOGIN = 'LAB\dog';

     select top 100 *
     from [catalog].[executions]
     order by
          1 desc

revert

Output

Output – No Access

executeAs.noAccess.20181116.1247PM.PNG

Output – Sufficient Access

executeAs.sufficientAccess.20181116.1250PM

 

Summary

Revising Microsoft’s code should not be not taken lightly.

But, I think in this case there is a bit of justification.

Those are :-

  1. There is no error message when the user tries to access the reports.
    • The report skeleton is still present
    • It is just that records are filtered out
  2. Other remediation choices such as granting membership in ssis-admin ( database role ) and sysadmin ( Sql Instance Role) are more high bar

References

  1. Microsoft
    • Microsoft Developer
      • SQL BI / Data Access Technologies
        ( SSIS, SSRS, SSAS, Data Access, POWER BI, PBI RS, Azure AS, SSMA, LINQ, System.Data … )

        • How a non-Admin users of SSIS 2012/2014 can view SSIS Execution Reports
          Link
  2. SQL Server Central
    • Home»SQL Server 2012»SQL 2012 – General»SSIS Execution Reports Permission
      • SSIS Execution Reports Permission
        Link

SQL Server – SSIS – Copying Packages Stored in the msdb database

Background

As part of a project that we are working on, we need to move SSIS packages between environments.

Storage Options

SSIS packages can be stored in few places:

  1. File System
  2. MSDB
  3. Integration Services Catalog

 

Storage – MSDB

Here is how to move them if they are stored in the MSDB Database.

 

SQL Server Management Studio

Because of DCOM, SSIS Management is very dependant on the SSIS Engine version.

And, so save yourself the headache and make sure that you are using Management Studio ( SSMS ) that matches your version.

 

Outline

  1. Launch SSMS
  2. Source
    • Connect To Source and export SSIS Package
    • Exported package should be save into File System
  3. Destination
    • Connect to Destination and import SSIS package
    • Import file saved earlier
  4. Validation

Steps

Launch SSMS

Once again launch SSMS that matches the version  of your  Database Engine

 

Connect To Source & Export Package

Steps

  1. Connect to “Integration Services” on your Source Server
  2. In the Explorer Pane on the left side of the window, navigate the Stored Packages node
    • Review the list of packages
    • Select the package
    • Right click on your selection and access the drop down menu
    • From the options availed through down menu, choose to “Export Package
    • In the “Export Package” window
      • Package Location
        • Accept the default choice of “File System”
      • Package Path
        • Please click on the button (  ) to the right of package path
        • A File Dialog appears
        • Navigate the dialog and settle on a target folder
        • Click the Save button to confirm the targeted folder
      • Review your choices
      • Press the OK button to export the package

 

Screen Shot

Connect to Server

Stored Packages

Export Package

 

Save package to path

 

Export Package

 

 

Import package to Destination

Steps

  1. Connect to “Integration Services” on your Destination Server
  2. In the Explorer Pane on the left side of the window, navigate the Stored Packages node
    • Access the msdb node
    • Review the list of packages
    • Delete or rename existing packages that share the name of the package that you will be importing
    • Right click the MSDB Node or the parent folder if you have chosen to use folders
    • From the drop-down menu, choose the “Import Package” menu item
    • In the “Import Package” window
      • Package Location
        • Accept the default choice of “File System”
      • Package Path
        • Please click on the button (  ) to the right of package path
        • A File Dialog appears
        • Navigate the dialog and access the target folder chosen earlier
        • Click the Save button to confirm the targeted folder
      • Review your choices
      • Press the OK button to import the package

 

Screen Shot

Connect to Server

Import Package – Initial

Load package

 

Import Package – Configured

Validation

Olap Helper

Olap Helper has many small SQL Scripts out there.

Olap’s blogs here.

We are going to use one of them to list our SSIS Packages

Coverage

  • Script Center > Repository > Databases > List all SSIS packages stored in msdb database
    List all SSIS packages stored in msdb database
    Link

Code


/*

	List all SSIS packages stored in msdb database. 
	sysssispackages (Transact-SQL)
	https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/sysssispackages-transact-sql
	
	List all SSIS packages stored in msdb database
	Script Center > Repository > Databases > List all SSIS packages stored in msdb database
	https://gallery.technet.microsoft.com/scriptcenter/List-all-SSIS-packages-3b247394
	

*/
SELECT 

		[package]
			= PCK.[name]

	  , [description]
			= PCK.[description]
 
	  , [folderName]
		= FLD.foldername 

	  , [packageType]
		= CASE PCK.packagetype 
			WHEN 0 THEN 'Default client' 
			WHEN 1 THEN 'SQL Server Import and Export Wizard' 
			WHEN 2 THEN 'DTS Designer' 
			WHEN 3 THEN 'SQL Server Replication' 
			WHEN 5 THEN 'SSIS Designer' 
			WHEN 6 THEN 'Maintenance Plan Designer or Wizard' 
			ELSE 'Unknown' 
		END
		 
	  , PCK.ownersid

	  ,[owner]
		= tblSSP.[name]

	  , [isEncrypted]
		= PCK.isencrypted 

	  , [createDate]
		= PCK.createdate 

	  , [version]
			= CONVERT(varchar(10), vermajor) 
				+ '.' + CONVERT(varchar(10), verminor) 
				+ '.' + CONVERT(varchar(10), verbuild) --AS version 

	  , versionComment 
		= PCK.vercomments

	  , [packageSize]
		= DATALENGTH
			(
				PCK.packagedata
			) 

FROM msdb.dbo.sysssispackages AS PCK 

INNER JOIN msdb.dbo.sysssispackagefolders AS FLD 
	ON PCK.folderid = FLD.folderid 


LEFT OUTER JOIN sys.server_principals tblSSP

	ON PCK.ownersid = [tblSSP].[sid]

ORDER BY 
		PCK.[name]


Ouput

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