Another SSIS Framework – Beginner’s Guide

Another SSIS Framework (for n00bs)

A dummies book. Yep, I'm really scraping the bottom of the barrel for puns now.

A dummies book. Yep, I’m really scraping the bottom of the barrel for puns now.

While there is some good high level documentation written by SqlArchitect, I hope for this guide to compliment his efforts by providing some step by step step instructions for those new to SSIS against a clean SQL 2016 environment using SSISDB.

Before you get started, check out the overview written by SqlArchitect on the front page of his codeplex site.

Download the latest (SQL2014) version of the framework from codeplex.

Installation

RTFM

Extract the zip to your file system, then have a read of the HOWTO, located in SSIS2014-SSIDB-Framework\Templates\SQL2014-HOWTO.txt

Setting Environmental variables in Powershell

  1. Connect to your SQL Server in Remote Desktop
  2. Open the script from the zip file located at ‘SSIS2014-SSIDB-Framework\Templates\SetDWConfigEnvVariable.ps1’ in notepad, and copy it to the clipboard.
  3. Find ‘Powershell ISE’ in your programs menu, right-click it, and ‘Run as Administator’
  4. Click File, New
  5. Paste the contents of the script into the Untitled.ps1 document (note I had to go this way as the ps1 file wouldn’t run, apparently it needs to be digitally signed??)
  6. Press F5 to run
  7. Enter a name of your SQL Instance
  8. Enter a name for your config database

psa

SQL Server Object Creation

  1. Open SQL Server Management Studio
  2. There are 20 .sql files, which need to be run in order. I have specific notes for 1.06 and 1.21 below. For each file:
    1. Open the file in SSMS
    2. On the Query menu, click ‘Specify Values for Template Parameters’
    3. Fill in the details for each script, then run it

Note: ‘1.06.0-GrantDBPermissionsToETLService’

I ran this script four times, with the following changes between iterations

  1. First line of script ‘USE DataWarehouse’
  2. First line of script ‘USE DWH_Stage’
  3. First line of script ‘USE DWH_Config’
  4. First line of script ‘USE SSISDB’, removed db_ddladmin and db_datawriter lines

Note: ‘1.21.0-CreateErrorTableTemplate’

I had a syntax error in SQL 2016, so removed the FOR clause on the end of the constrained column definitions. When I look at the table definition a FOR clause is mentioned in the constraint so I figure this worked. Here is the edited SQL:

CREATE TABLE stg.DummyFactError(
RowNumber int NULL,
ErrorNumber int NULL,
ErrorDescription varchar(256) NULL,
ColumnId int NULL,
LineageTMST datetime NULL,
LineageString varchar(128) NULL,
InsertTMST datetime NOT NULL CONSTRAINT DF_stg_DummyFactError_InsertTMST DEFAULT (getdate()),
InsertedBy sysname NOT NULL CONSTRAINT DF_stg_DummyFactError_InsertedBy DEFAULT (suser_sname())
) ON "default"

1.22.0-CreateFrameworkEnvironment.sql

Prior to being able to run this script, you need to Deploy the ‘Production’ package to your SSISDB. (It has to be this package, as it includes parameters required for 1.23.) The way I deployed this was:

  1. In SQL Server Data Tools, Create a new solution
  2. Right-click on the solution in the ‘Solution Explorer’, Add, Existing Project
  3. Find and select SSIS2014-SSIDB-Framework\ProductionPackages\ProductionPackages.dtproj
  4. Right-click the Project, then ‘Deploy’
  5. Enter your SQL details, and Browse to a location in the SSIDB for Path

To Visual Studio… and Beyond

  1. Import the template project into your solution: SSIS2014-SSIDB-Framework\Templates\Templates.dtproj

As I’m using SQL2016 R2, I needed to upgrade SqlArchitect’s templates prior to using them:

  1. Right-Click on the ‘Templates’ project, then select ‘Properties’
  2. Navigate to  ‘Configuration Properties’, ‘General’
  3. Change ‘TargetServerVersion’ from ‘SQL Server 2014’ to ‘SQL Server 2016’
  4. Click Yes to the warning. You should now be able to copy/paste the template packages between the Template Project and your own projects.

Using the Templates

To use the templates, you need to:

  1. Locate the template in the ‘Templates’ project, Right-click on the name, Copy
  2. Right-click on ‘SSIS Packages’ in your Production project, Paste
  3. Double-click the pasted package to open it
  4. Click the variables icon in the top right

variables

5. Update the appropriate variables to your build

6. Run the package and trouble-shoot any errors.

 

2 Comments

  1. rich says:

    Hi Andrew,
    I’m using this framework myself but cannot see where the LineageTMST variable is updated. Wherever it is, it can’t be running.
    As a result I have a date of 26/02/2014 07:41 in every table!
    Thanks
    Rich

  2. Andrew Mosey says:

    Hi Rich,

    I’ve since made considerable changes to the framework, and believe I replaced the LineageTMST with a field I call etl_create_date. This field is populated by getdate() on each row of every table, and seems to be working well for me.

    I am still using LineageID, which is populated by a SQL query ‘SELECT ? = SCOPE_IDENTITY();’ and mapped through an Output parameter.

    Sorry I can’t see the original package any more so can’t be of more help.

Leave a Comment