BDBI

Radio

B

aker’s

D

ozen

B

usiness

I

ntelligence Webcast Radio

13 Weekly tips on Microsoft SQL Server/BI/SharePoint Technologies http://www.BDBIRadio.com

Week of 01/27/2013: 13 great features in SQL Server Integration Services 2012

BDBI Radio

• Hosted by Kevin S. Goff, Microsoft SQL Server MVP and author of CoDe Magazine “Baker’s Dozen” Productivity Series • Weekly webcast - Sundays, 11 AM to 12:30 PM EST • Site: http://www.BDBIRadio.com

• http://kevin_s_goff.typepad.com/kevin_s_goff_weblog/bdbi-radio/ • Program format: – First 60 minutes - 13 quick tips on different SQL/BI/SharePoint topics • In any week, tips might cover one topic (13 features in PowerPivot or 13 SQL Interview topics), or 13 random tips on anything from data warehousing to MDX/DAX programming, to maybe even a few .NET tips for scenarios when the .NET and BI worlds collide – Second 30 minutes , open Q/A (email me at [email protected]

) – Webcast recorded, recordings will be available on website • Once in a while, a special guest interview 5/2/2020 BDBI Radio-13 features in SSIS 2012 2

BDBI Radio • Upcoming community events:

– I’ll be at SharePoint Saturday in Philadelphia on February 23 • http://sharepointsaturday.org/philly/default.aspx

– Possibly at SQL Saturday events in Boston (April 6) and Richmond (March 9) • http://sqlsaturday.com/187/eventhome.aspx

• http://sqlsaturday.com/187/eventhome.aspx

• Next week on BDBI radio:

– 13 great features in PerformancePoint Services (SharePoint 2010 and SharePoint 2013) 5/2/2020 BDBI Radio-13 features in SSIS 2012 3

BDBI Radio

• Today: 13 great features in SSIS 2012 • Some history on SSIS - a great ETL tool for getting information from point A to point B • Started as DTS (Data Transformation Services) 2000 – “OK”, but not great • SSIS 2005 – a major release • SSIS 2008 – some resource/performance enhancements under the hood, greatly improved lookup, a few new features (no changes in 2008R2) • SSIS 2012 –great new functionality and a UI face-lift • I wrote an article in CoDe Magazine in early 2012: – http://code-magazine.com/articleprint.aspx?quickid=1206021&printmode=true 5/2/2020 BDBI Radio-13 features in SSIS 2012 4

BDBI Radio

• Today: 13 great features in SSIS 2012 1.

SQL Server Data Tools (Visual Studio 2010, WPF-based user interface) 2.

3.

4.

5.

6.

Shared Connection managers SSIS Project/Package Parameters New Variable Expression Task to reduce need for scripts UNDO/REDO in editor New SSIS Expression Features (REPLACENULL) 7.

8.

New ODBC Data Flow Source and Destination Components Better handling of invalid metadata 9.

New SSIS tasks to support Change Data Capture 10. New/improved deployment features and SSIS Management Dashboard for deployed packages 11. New Data Taps to dynamically “tap” into pipeline 12. Debugging in Data Flow Script Components 13. A few miscellaneous features 5/2/2020 BDBI Radio-13 features in SSIS 2012 5

BDBI Radio 1) Now aligned with Visual Studio 2010/2012:

– SSIS 2012 now uses Visual Studio 2010/2012 environment – Now called SQL Server Data Tools (SSDT) instead of BIDS 5/2/2020 BDBI Radio-13 features in SSIS 2012 6

BDBI Radio • 2. Shared connection managers:

– In earlier versions of Integration Services, connection managers were scoped to a package, not the project – Could not directly share connection managers (email connections, FTP connections, database connections) across packages • Workaround was package templates (which still isn’t a bad idea) – Now in 2012 Integration Services, you can implement connection managers at the project level and use them across packages in the project—a much cleaner approach – One downside: doesn’t support expressions – A good resource for this: • http://blogs.msdn.com/b/mattm/archive/2011/07/19/project-connection-managers.aspx

5/2/2020 BDBI Radio-13 features in SSIS 2012 7

BDBI Radio • 3. Integration Services project/package parameters:

– Alternate approach, versus using existing Integration Services configurations – Can implement parameters at the project and package level and define parameter values at any time – Parameter values can be used in Integration Services expressions, just like Integration Services variables – Can use it to implement “global” variables across all packages in a project 5/2/2020 BDBI Radio-13 features in SSIS 2012 8

BDBI Radio • 4. New variable Expression Task (no need for scripts to increment/accumulate variables):

– In earlier versions of Integration Services, you needed a script just to increment or accumulate the values of variables that you wanted to use across the entire package – New Expression Task for variables—available at the control flow – More easily handles maintenance of variable values – No longer necessary to write short C# or VB scripts just to manipulate variables 5/2/2020 BDBI Radio-13 features in SSIS 2012 9

BDBI Radio • 5. UNDO/REDO in editor:

– Earlier versions of Integration Services did not have an undo/redo feature in the design editor – UNDO and REDO now, in the editor 5/2/2020 BDBI Radio-13 features in SSIS 2012 10

BDBI Radio • 6. New Integration Services expression features (REPLACENULL, TOKEN, TOKENCOUNT):

– Earlier Integration Services expression language had an ISNULL function with only 1 parameter – Needed to use ?

and : operators (derived from C#) to test for a null value (e.g., to optionally concatenate a value that might be null) – Now a REPLACENULL value, simpler to use – Also new TOKEN and TOKENCOUNT to parse a string for a specific token or to return the number of tokens from a string 5/2/2020 BDBI Radio-13 features in SSIS 2012 11

BDBI Radio • 7. New ODBC data flow source and destination components:

– Consistent with Microsoft support for ODBC, we now have regular ODBC data flow sources and ODBC data flow destinations – Much easier for those who rely on ODBC – Microsoft strategically aligning with ODBC in SQL Server/Azure, moving away from OLE DB long-term 5/2/2020 BDBI Radio-13 features in SSIS 2012 12

BDBI Radio • 8. Better handling of invalid metadata:

– In earlier versions of Integration Services, the data flow design editor was “temperamental” when data flow components received a different set of columns from the pipeline – Data flow components operated on lineage IDs that would change, even if the component subsequently received the same set of columns from a different component – Pipeline structure has been modified to account for discrepancies and metadata changes more gracefully – New pipeline editor to resolve invalid/changed column references 5/2/2020 BDBI Radio-13 features in SSIS 2012 13

BDBI Radio • 9. New Integration Services tasks to support change data capture (CDC):

– New Control Flow tab to manage CDC process – Three phases: Mark Load Start, Get Processing Range, and Mark Load End – Must first use CDC Control Flow task to mark load start and end – Then use CDC Control Flow task to get processing range, and then use CDC source/splitter to get new/changed/deleted rows – New data flow source to read a CDC change tracking table – New data flow CDC “splitter” transformation: a specialized conditional split to break the pipeline into three separate flows for historical insertions, updates, and deletions – Tasks will be especially helpful for those who are new to CDC 5/2/2020 BDBI Radio-13 features in SSIS 2012 14

BDBI Radio

• 10. New/improved deployment features and SSIS management dashboard for deployed packages: – New SSISDB catalog in SQL Server (must enable CLR integration) – Much easier deployment process when deploying to SQL Server – Once packages are deployed, can manage settings, parameters, logging, connection manager settings, etc. in the catalog database – Can also generate reports against historical package executions using new Management dashboard – Many will view this as a significant improvement over package deployments to SQL Server in earlier versions of Integration Services – For additional reporting capabilities: • http://ssisreportingpack.codeplex.com

5/2/2020 BDBI Radio-13 features in SSIS 2012 15

BDBI Radio • 11. Integration Services data taps:

– Suppose you suddenly wanted to “tap into” a specific pipeline in the data flow, for a package that’s already been deployed?

– Previously, you would implement an extra pipeline in the data flow and send the output to a flat file connection manager (but you’d have to modify the package and redeploy) – In 2012 Integration Services, we can “tap into” the identification string of the pipeline and call a stored procedure in the SSISDB catalog. This will set up a “hook” to capture the pipeline and write it out to a file, without ever modifying the package – Data taps stored in \Program Files\Microsoft SQL Server\110\DTS\DataDumps – A bit of a learning curve on the use of the stored procedures, but worth it 5/2/2020 BDBI Radio-13 features in SSIS 2012 16

BDBI Radio • 12. Debug Data Flow Script Components

– Can now set breakpoints and debug script components in the Data Flow – Can examine variables, step through script code – Scripting engine upgrades to VSTA 3.0, providing a VS2010 shell and support for .NET 4.0

• http://blogs.msdn.com/b/mattm/archive/2012/01/13/script component-debugging-in-ssis-2012.aspx

17 5/2/2020 BDBI Radio-13 features in SSIS 2012

BDBI Radio • 13. Miscellaneous enhancements

– In SSIS 2005, there were many areas in the User Interface where you had to type out a variable (as opposed to selecting from a list). SSIS 2008 took care of most of those areas, but left a small number unaddressed. SSIS 2012 has finally covered all the areas where a variable needs to be referenced. – SSIS 2012 makes it easier to create a data viewer (fewer keystrokes).

– You can now populate a data flow row count very quickly – Expression Result Length > 4000.

– The Merge and Merge Join Transformations now use less memory than before. As a result, developers no longer need to set the MaxBuffersPerInput property (which was necessary to avoid consuming excess memory). 5/2/2020 BDBI Radio-13 features in SSIS 2012 18

BDBI Radio • Just some general performance/usage tips

–

Be careful when using the following with LARGE input rows:

• Any blocking transformation (sort/aggregate) • OLE DB commands, which work “row by row” • Be careful about using LOOKUP when input or destination is extremely large • In any OLE DB Source, don’t use the column picker to select columns – use a query or stored procedure instead • – Avoid embedding business enumerated values in a package

Query enumerated values/lists into an Object Array, and then iterate through the array using a ForEach ADO Enumerator

• • As much as possible, use stored procedures!

Use MERGE and MERGE…OUTPUT INTO for 5/2/2020 BDBI Radio-13 features in SSIS 2012 19

Recommended reading

• Some outstanding new books on SSIS 2012

5/2/2020 BDBI Radio-13 features in SSIS 2012 20