Search results matching tags 'ssis' and 'Design Pattern'

Presenting Loading Data Warehouse Partitions with SSIS 2012 at SQL Saturday DC!

andyleonard — Mon, 19 Nov 2012 18:37:00 GMT

Join Darryll Petrancuri and I as we present Loading Data Warehouse Partitions with SSIS 2012 Saturday 8 Dec 2012 at SQL Saturday 173 in DC!

SQL Server 2012 table partitions offer powerful Big Data solutions to the Data Warehouse ETL Developer. In this presentation, Darryll Petrancuri and Andy Leonard demonstrate one approach to loading partitioned tables and managing the partitions using SSIS 2012, and reporting partition metrics using SSRS 2012. Objectives

A practical solution for loading Big Data Fact tables (1B+ rows).

Learn more about SQL Server 2012 table partitioning.

Learn more about using SSIS Expression Language to generate dynamic SQL in SSIS 2012.

I hope to see you there!

:{>

About Darryll Petrancuri:

Darryll Petrancuri is a Data & Business Intelligence Architect for SPS Commerce. He has been in software development for over 30 years, working across a wide variety of industries and domains serving as a consultant, developer, architect, thought leader, visionary, mentor and instructor. His background in the Microsoft technology stack includes VB (from Classic 1.0 Beta & .Net), C#, SQL Server 6.5 - 2012 (Core, Reporting Services, Integration Services, Notification Services, Analysis Services), component, application, database, data warehouse and business intelligence architecture and development. He currently specializes in innovative data warehouse solutions architecture and development, leveraging custom metadata repositories and automation.

About Andy Leonard:

Andy Leonard is CSO of Linchpin People, an SSIS Trainer and Consultant, SQL Server database and Integration Services developer, SQL Server data warehouse developer, community mentor, blogger, and engineer. He is a co-author of SSIS Design Patterns. His background includes Visual Basic and web application architecture and development and SQL Server 2000-2012.

SSIS Design Pattern: Producing a Footer Row

andyleonard — Tue, 09 Oct 2012 11:00:00 GMT

The following is an excerpt from SSIS Design Patterns (now available in the UK!) Chapter 7, Flat File Source Patterns. The only planned appearance of all five authors presenting on SSIS Design Patterns is the SSIS Design Patterns day-long pre-conference session at the PASS Summit 2012. Register today.

Let’s look at producing a footer row and adding it to the data file. For this pattern, we will leverage project and package parameters. We will also leverage the Parent-Child pattern, which will be discussed in detail in another chapter. We are not going to build the package that creates a flat file containing data. We will start with the assumptions that an extract file exists and we know the number of rows and the extract date. We will use parameters to transmit metadata from the parent package to the child package. Let’s get started!

Create a new SSIS package and name it WriteFileFooter.dtsx. Click on the Parameters tab and add the following parameters:

Name	Data Type	Value	Required
AmountSum	Decimal	0	FALSE
DateFormat	String		TRUE
Debug	Boolean	TRUE	FALSE
Delimiter	String	,	TRUE
ExtractFilePath	String		TRUE
LastUpdateDateTime	DateTime	1/1/1900	TRUE
RecordCount	Int32	0	TRUE

The parameters, when entered, appear as shown in Figure 7-20:

Figure 7-20. Parameters for the WriteFileFooter.dtsx Package

The Sensitive property for each parameter is set to False. The Description is optional and available in the image.
We’re going to do the heavy lifting in a Script Task. Return to the Control Flow and drag a Script Task onto the canvas. Change the name to “scr Append File Footer” and open the editor. On the Script page, click the ellipsis in the ReadOnlyVariables property’s value textbox. When the Select Variables window displays, select the following variables:
•    System::PackageName
•    System::TaskName
•    $Package::AmountSum
•    $Package::DateFormat
•    $Package::Debug
•    $Package::Delimiter
•    $Package::ExtractFilePath
•    $Package::LastUpdateDateTime
•    $Package::RecordCount

The Select Variables window will not appear exactly as shown in Figure 7-21, but these are the variables you need to select for use inside the “scr Append File Footer” Script Task:

Figure 7-21.Selecing Variables for the Footer File

Click the OK button to close the Select Variables window. Set the ScriptLanguage property to Microsoft Visual Basic 2010. Click the Edit Script button to open the VstaProjects window. At the top of the ScriptMain.vb code window, you will find an “Import” region. Add the following lines to that region:

Imports System.IO
Imports System.Text

Just after the Partial Class declaration, add the variable declaration for the bDebug variable (the Dim statement below):

Partial Public Class ScriptMain
Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

Dim bDebug As Boolean

Replace the code in Public Sub Main with the following:

Public Sub Main()

' 1: detect Debug setting...
bDebug = Convert.ToBoolean(Dts.Variables("Debug").Value)

        ' 2: declare and initialize variables...
        ' 2a: generic variables...
        Dim sPackageName As String = Dts.Variables("PackageName").Value.ToString
        Dim sTaskName As String = Dts.Variables("TaskName").Value.ToString
        Dim sSubComponent As String = sPackageName & "." & sTaskName
        Dim sMsg As String
        ' 2b: task-specific variables...
        Dim sExtractFilePath As String = Dts.Variables("ExtractFilePath").Value.ToString
        Dim iRecordCount As Integer = Convert.ToInt32(Dts.Variables("RecordCount").Value)
        Dim sAmountSum As String = Dts.Variables("AmountSum").Value.ToString
        Dim sDateFormat As String = Dts.Variables("DateFormat").Value.ToString
        Dim sDelimiter As String = Dts.Variables("Delimiter").Value.ToString
        Dim sLastUpdateDateTime As String= _
Strings.Format(Dts.Variables("LastUpdateDateTime").Value, sDateFormat) _
'"yyyy/MM/dd hh:mm:ss.fff")
        Dim sFooterRow As String
        Dim s As Integer = 0

        ' 3: log values...
        sMsg = "Package Name.Task Name: " & sSubComponent & ControlChars.CrLf & _
ControlChars.CrLf & _
            "Extract File Path: " & sExtractFilePath & ControlChars.CrLf & _
ControlChars.CrLf & _
            "Record Count: " & iRecordCount.ToString & ControlChars.CrLf & _
ControlChars.CrLf & _
               "Amount Sum: " & sAmountSum & ControlChars.CrLf & ControlChars.CrLf & _
               "Date Format: " & sDateFormat & ControlChars.CrLf & ControlChars.CrLf & _
               "Delimiter: " & sDelimiter & ControlChars.CrLf & ControlChars.CrLf & _
            "LastUpdateDateTime: " & sLastUpdateDateTime & ControlChars.CrLf & _
ControlChars.CrLf & _
               "Debug: " & bDebug.ToString
        Dts.Events.FireInformation(0, sSubComponent, sMsg, "", 0, True)
        If bDebug Then MsgBox(sMsg)

        ' 4: create footer row...
        sFooterRow = iRecordCount.ToString & sDelimiter & sAmountSum & sDelimiter & _
sLastUpdateDateTime
        ' 5: log...
        sMsg = "Footer Row: " & sFooterRow
        Dts.Events.FireInformation(0, sSubComponent, sMsg, "", 0, True)
        If bDebug Then MsgBox(sMsg)

        ' 6: check if the file is in use...
        While FileInUse(sExtractFilePath)
            ' 6a: if file is in use, sleep for a second...
            System.Threading.Thread.Sleep(1000)
            ' 6b: incrementor...
            s += 1
            ' 6c: if incrementor reaches 10 (10 seconds),
            If s > 10 Then
                ' exit the loop...
                Exit While
            End If 's > 10
        End While 'FileInUse(sExtractFilePath)
        ' 7: log...
        If s = 1 Then
            sMsg = "File was in use " & s.ToString & " time."
        Else ' s = 1
            sMsg = "File was in use " & s.ToString & " times."
        End If ' s = 1
        Dts.Events.FireInformation(0, sSubComponent, sMsg, "", 0, True)
        If bDebug Then MsgBox(sMsg)

        ' 8: if the file exists...
        If File.Exists(sExtractFilePath) Then
            Try
                ' 8a: open it for append, encoded as built, using a streamwriter...
                Dim writer As StreamWriter = New StreamWriter(sExtractFilePath, True, _
Encoding.Default)
                ' 8b: add the footer row...
                writer.WriteLine(sFooterRow)
                ' 8c: clean up...
                writer.Flush()
                ' 8d: get out...
                writer.Close()
                ' 8e: log...
                sMsg = "File " & sExtractFilePath & " exists and the footer row has " & _
"been appended."
                Dts.Events.FireInformation(0, sSubComponent, sMsg, "", 0, True)
                If bDebug Then MsgBox(sMsg)
            Catch ex As Exception
                ' 8f: log...
                sMsg = "Issue with appending footer row to " & sExtractFilePath & _
" file: " & ControlChars.CrLf & ex.Message
                Dts.Events.FireInformation(0, sSubComponent, sMsg, "", 0, True)
                If bDebug Then MsgBox(sMsg)
            End Try
        Else
            ' 8g: log...
            sMsg = "Cannot find file: " & sExtractFilePath
            Dts.Events.FireInformation(0, sSubComponent, sMsg, "", 0, True)
            If bDebug Then MsgBox(sMsg)
        End If ' File.Exists(sExtractFilePath)

' 9: return success...
Dts.TaskResult = ScriptResults.Success

End Sub

Add the following function after Public Sub Main():

Function FileInUse(ByVal sFile As String) As Boolean

        If File.Exists(sFile) Then
            Try
                Dim f As Integer = FreeFile()
                FileOpen(f, sFile, OpenMode.Binary, OpenAccess.ReadWrite, _
OpenShare.LockReadWrite)
                FileClose(f)
            Catch ex As Exception
                Return True
            End Try
        End If
    End Function

This script builds the footer row and appends it to the Extract file. The first thing we do – at the comment labeled 1 – is assign a value to the Debug variable. I use the Debug variable to control message boxes displaying variable values and other pertinent information. I describe why in the chapter on Execution Patterns.

At comment 2, we declare and initialize variables. I break variables into two categories: generic and task-specific variables. At comment 3, we build a message in the variable sMsg. This message contains the values of each variable used in the Script thus far. If we are running in Debug mode (if bDebug is True), the code displays a message box (via the MsgBox function) containing the contents of sMsg. Whether we’re running in Debug Mode or not, I use the Dts.Events.FireInformation method to raise an OnInformation event, passing it the contents of sMsg. This means the information is always logged and is optionally displayed by a message box. I like options (a lot).

Comment 4 has us constructing the actual footer row and placing its text in the String variable sFooterRow. Note the delimiter is also dynamic. The String variable sDelimiter contains the value passed to the WriteFileFooter into the Package Parameter named $Package::Delimiter. At comment 5, we log the contents of the footer row.

At comment 6, we initiate a check to make sure the Extract File is not marked as “in use” by the operating system. There are many ways to detect the state of the file in the file system, so I created a Boolean function named FileInUse to encapsulate this test. If the function I created doesn’t work for you, you can construct your own. If the file is in use, the code initiates a While loop that sleeps the thread for one second. Each iteration through the loop causes the variable s (the incrementor in this example) to increment at comment 6b. If s exceeds ten, the loop exits. We will only wait 10 seconds for the file to be usable. Note that if the file remains in use at this juncture, we still move on. We'll deal with the file in use matter later, but we will not hang ourselves in a potentially endless loop waiting for the file’s availability. We will instead fail. Whether the file is in use or not in use, the script logs its state at comment 7.

At comment 8, we check for the existence of the file and begin a Try-Catch. If the file doesn’t exist, I opt to log a status message (via Dts.Events.FireInformation) and continue (see comment 8g). The Try-Catch enforces the final test of the file’s usability. If the file remains in use here, the Catch fires and logs the status message at comment 8f. At 8f and / or 8g, you may very well decide to raise an error using the Dts.Events.FireError method. Raising an error causes the Script Task to fail, and you may want this to happen. At comments 8a through 8d, we open the file, append the footer row, close the file, and clean up. At comment 8e, the code logs a status message. If anything fails when executing 8a through 8e, code execution jumps to the Catch block.

If all goes well, the code returns Success to the SSIS Control Flow via the Dts.TaskResult function (comment 9).
The Script Task does all the work in this pattern.

I created a test package called TestParent.dtsx to test this package. The package has variables that align with the parameters of the WriteFileFooter.dtsx package, as shown in Figure 7-22:

Figure 7-22. Variables in the TestParent.dtsx Package

If you’re playing along at home, you should adjust the path of the ExtractFooterFilePath variable.
I added a Sequence Container named “seq Test WriteFileFooter” and included an Execute Package Task named “ept Execute WriteFileFooter Package.” On the Package page of the Execute Package Task Editor, set the ReferenceType property to “Project Reference” and select WriteFileFooter.dtsx from the PackageNameFromProjectReference property dropdown. Map the TestParent package variables to the WriteFileFooter package parameters as shown in Figure 7-23:

Figure 7-23. Mapping Package Parameters

Execute TestParent.dtsx to test the functionality. The package executes successfully and the footer row is appended to the file as shown in Figure 7-24:

Figure 7-24. Mission Accomplished

Interesting stuff? I think so but I’m biased; I wrote it! You can get more in the SSIS Design Patterns book and by attending the SSIS Design Patterns day-long pre-conference session at the PASS Summit 2012.

:{>

Join the Authors of SSIS Design Patterns at the PASS Summit 2012!

andyleonard — Wed, 19 Sep 2012 09:00:00 GMT

My fellow authors and I will be presenting a day-long pre-conference session titled SSIS Design Patterns at the PASS Summit 2012 in Seattle Monday 5 Nov 2012!

Package execution
Package logging
Loading flat file sources
Loading XML sources
Loading the cloud
Dynamic package generation
SSIS Frameworks
Data warehouse ETL
Data flow performance

Presenting this session:

I hope to see you in Seattle! If you read this blog and attend our session or see me wandering around, please introduce yourself. I am the fat guy with a fu.

:{>

Book Review: SSIS Design Patterns

andyleonard — Mon, 17 Sep 2012 13:05:56 GMT

Samuel Vanga (Blog | @SamuelVanga) has posted a review of our new book SSIS Design Patterns at his blog. Several of Sam’s statements struck me, but none more than this:

Within a few hours of reading SQL Server 2012 Integration Services Design Patterns, it stood out that none of the authors were trying to impress by showing what they all know in SSIS. Instead, they focused on describing solutions and patterns in a great detail (exactly why I paid for).

Sam mentions he could not locate the source code, a concern mentioned by others who have emailed me. Links source code is located on the Apress Home Page for SSIS Design Patterns shown here:

I do not like this page design because it has a link at the top called “Source Code” and that link does not take you to the source code for this book, but rather to a page that describes Apress’ source code supplied with books.

The link to the source code for SSIS Design Patterns is down the page a bit, shown here:

I appreciate Samuel’s review and hope his experience is shared by many!

:{>

Learn SSIS from the Authors of SSIS Design Patterns at the PASS Summit 2012!

andyleonard — Wed, 22 Aug 2012 11:00:00 GMT

Jessica Moss (blog | @jessicammoss), Michelle Ufford (blog | @sqlfool), Tim Mitchell (blog | @tim_mitchell), Matt Masson (blog | @mattmasson), and me – we are all presenting the SSIS Design Patterns pre-conference session at the PASS Summit 2012! We will be covering material from, and based upon, the book.

We will describe and demonstrate patterns for package execution, package logging, loading flat file and XML sources, loading the cloud, dynamic package generation, SSIS Frameworks, data warehouse ETL, data flow performance, and more!

Join us – register today!

:{>

Presenting at Ohio North SQL Server User Group 1 May 2012!

andyleonard — Fri, 20 Apr 2012 15:01:00 GMT

I am honored to present Using BIML as an SSIS Design Patterns Engine to the Ohio North SQL Server User Group 1 May 2012! This presentation is based on one of my favorite chapters in the upcoming book: SSIS Design Patterns scheduled for release this summer.

If you find yourself in the Cleveland area on the evening of 1 May, please drop by and introduce yourself. I am the fat guy with a fu.

:{>

Updated SSIS Framework, Now Includes Reports

andyleonard — Wed, 07 Mar 2012 12:00:00 GMT

I have built a beta version of Linchpin People’s SSIS Framework, now including much-requested reports. You can grab the updated copy here.

I appreciate any feedback you care to share.

:{>

Designing an SSIS Framework

andyleonard — Fri, 16 Sep 2011 11:00:00 GMT

In preparation for SQL Saturday #84 – Kalamazoo, SQLBits 9, and the PASS Summit 2011; I’ve been honing my presentation called Designing an SSIS Framework. I delivered this presentation a few times over the past couple months. Each time, I promised the attendees I’d send them the code if they emailed me… and a couple things got in the way:

First, a lot of people requested the presentation. Second, I got really busy! Both of those beat their respective alternatives.

I finished building and barely testing the code this evening. You can grab an updated copy here. The zip file contains the SSIS 2008 R2 solution and two other folders: Config and Sql. Config holds a configuration file that points to a database named SSISConfig on the default instance of your local workstation or server (edit SSISConfigParent.dtsConfig if you want to change this location).

To make it all work dynamically, you need to create a System Environment Variable named SSISConfig (case-sensitive) and set the value to the full path of SSISConfigParent.dtsConfig on your workstation or server. Next, run the “Create SSISConfig.sql” script in the Sql folder, followed by the “Add An SSIS Applications.sql” script (edited to reflect the folder that contains the SSIS packages on your workstation or server). This should get everything ready for the SSISConfig SSIS solution. When you execute Parent.dtsx, it should call Template1.dtsx, then Template2.dtsx, and finally ErrorTest.dtsx.

Let me know how it goes!

:{>

Presenting at SQL Saturday #84–Kalamazoo 17 Sep 2011!

andyleonard — Wed, 07 Sep 2011 21:00:00 GMT

Introduction

I am honored to present Designing an SSIS Framework and “I See a Control Flow. Now What?” at SQL Saturday #84 in Kalamazoo Michigan 17 Sep 2011!

I See a Control Flow. Now What?

This highly-interactive, demo-intense presentation is for beginners and developers just getting started with SSIS. Attend and learn how to build SSIS packages from the ground up.

Designing an SSIS Framework

In this “demo-tastic” presentation, SSIS trainer, author, and consultant Andy Leonard explains the what, why, and how of an SSIS framework that delivers metadata-driven package execution, connections management, and centralizes logging. Key takeaways: 1) Developers can migrate packages from Development, through their lifecycle, to Production without editing SSIS Connection Managers properties. 2) A metadata-driven approach to SSIS package execution. 3) Demonstration of a centralized logging reporting application.

Conclusion

If you will be attending SQL Saturday #84 and read this blog, please introduce yourself! I’m the fat guy with a fu…

:{>

Presenting at Hampton Roads SQL Server and SharePoint Users Group Tomorrow!

andyleonard — Tue, 19 Jul 2011 16:58:14 GMT

I am honored to present Designing an SSIS Framework to Hampton Roads SQL Server and SharePoint Users Group tomorrow, 20 Jul 2011! You can register here.

Details:

Infotec
100 Constitution Dr
Virginia Beach, VA 23462

Wednesday, July 20, 2011 at 6:30 PM (ET)

If you read this blog and will be there, introduce yourself! I’m the fat guy with a fu.

:{>