Three Best Practices with SSIS About Us Nate

Three Best Practices with SSIS

About Us � Nate Locklin ◦ Database Analyst, PPG Industries ◦ nate@locklin. org � Steve Tirone ◦ Data Warehouse Analyst, Amerinet ◦ stephen. tirone@gmail. com

SSIS Experience? � Survey: ◦ ◦ What is SSIS? Use it occasionally Use it every day I wrote the book on advanced SSIS � Given that we didn’t know, we wanted to present something generally useful, hence ◦ “Three best practices to keep in mind when developing and deploying SSIS packages”

First, a Baseline Intro � Open BIDS (Business Intelligence Development Studio) � Available in VS 2005, VS 2008, but not VS 2010 (yet) � Incidentally, the Import/Export wizard in Management Studio runs SSIS under the hood

Constructing your Package � Several ◦ ◦ tabs at the top Control Flow: your top level workflow workspace Data Flow: details of data transfer operations Event Handlers: where you can handle raised events Package Explorer: like a site map of your package � Start by dragging Tasks to your Control tab � Most commonly used tasks are probably: ◦ Data Flow ◦ Execute SQL

Quick Example � Let’s import the DJIA stock prices ◦ downloaded from Yahoo Finance into an Excel spreadsheet

Demo: Quick Package

Best Practice #1: Logging � Two often used log providers ◦ Text Files ◦ SQL Server (sysssislog in 2008, sysdtslog 90 in 2005) – created when first used � Many events possible to log ◦ On. Error and On. Task. Fail events most common ◦ Can reduce detail to Computer, Source, and Message ◦ Here you will find all the error messages: �http: //msdn. microsoft. com/enus/library/ms 345164. aspx

Demo: Logging

Best Practice #2: Configurations � Properties of objects can be changed at run- time � No need to change the package itself to promote from test to production ◦ Connection string is all that’s needed to change � Several formats: ◦ XML configuration file most commonly used

Demo: Configurations

Best Practice #3: Checkpoints � Checkpoints let a package start where it left off previously, such as when an error occurs in the middle of your package ◦ Lets you fix the error, then restart the package, without it re-running the parts already done

Setting up for Checkpoints � Set the following properties of the Package � Set the following properties on all the tasks: ◦ Checkpoint. File. Name = <some name> ◦ Checkpoint. Usage = If. Exists ◦ Save. Checkpoints = True ◦ Fail. Package. On. Failure = True

Demo: Checkpoints

Checkpoint Caveats � Most importantly: ◦ “A package can be restarted only at the control flow level. You cannot restart a package in the middle of a data flow. ” � Also: ◦ “When a package is restarted the Foreach Loop containers and its child containers are run again. If a child container in the loop runs successfully, it is not recorded in the checkpoint file, instead it is rerun. ”

Other Design Considerations � Retain. Same. Connection ◦ Using lots of connections? Use this, please! ◦ Can be found as a property of the Connection Mgr � Packet size ◦ Consider increasing (32, 767) for larger data � Row Per Batch Size ◦ Sometimes speed is enhanced when setting the “Rows per batch” to something other than blank, like 1000, 5000 or 10, 000 (particularly when run through BIDS over the network)

Retain Same Connection � Property of the Connection Manager

Packet Size � On All page of the Connection Manager

Deployment Considerations � Package � Default protection level is Encrypt with User Key, but you want Encrypt with Password (all or sensitive)

Deployment Considerations � Know your environment (64 bit vs 32 bit) ◦ Might get �[Connection manager "Excel Connection Manager 1"] Error: SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64 -bit version of SSIS, as no OLE DB provider is available. ◦ Or �Class not registered � The 64 bit issue may be addressed by telling Integration Services to use 32 bit runtime

64/32 bit Options � In the SQL Agent job step:

64/32 bit Options (BIDS) � Property of the PROJECT:

Error Handling � Great flexibility in error handling if you “program” the On. Error handler

Summary � Implement Logging � Use Configuration Files for flexibility � Checkpoints can be useful � Know your environment � Keep small design considerations in mind

Questions? � Nate Locklin ◦ Database Analyst, PPG Industries ◦ nate@locklin. org � Steve Tirone ◦ Data Warehouse Analyst, Amerinet ◦ stephen. tirone@gmail. com � Slides are at http: //tinyurl. com/SSISBest. Practices

Another Survey! Yeah! � Future BI Topics? ◦ Integration Services (SSIS) ◦ Reporting Services (SSRS) ◦ Analysis Services (SSAS) � SQL Saturday? ◦ BI Edition? ◦ Volunteers to help coordinate? �Email Gina or Steve