.Net Code Camp Orlando 2013

This past Saturday March 16th, I had the opportunity to speak once again the .Net Orlando Code Camp.  I presented a session under the SQL Server track titled “Change Data Capture, a developer’s best unknown friend”.  I had better than expected turn-out with about 20 attendees and overall very good participation.

I was glad to hear from more than one attendee that they were planning to use Change Data Capture in their environment and best of all to get rid of triggers!

I had the opportunity to catch-up with some of my friends and fellow SQL-ers from PASS. Also, met some new folks from the App/Dev community. These type of events are always great to expand your network, share your knowledge and learn something new!

Thanks to the organizers, sponsors, volunteers and attendees to make this event a success and for having me once more.

You can download my PowerPoint presentation and demo files at: http://sdrv.ms/15UUX64

Codeplex: SSIS Community Tasks and Components

I recently bumped into this very exhaustive list of SSIS tasks, components and samples in CodePlex http://ssisctc.codeplex.com. The list is maintained by SQL Server MVP (Blog|Twitter) and  grouped in the following sections:

• Tools
• Connection Managers
•  Log Providers
• Tasks (for Control Flow)
• Foreach Enumerators
• Script Task
• Script Samples
• Components (for Data Flow)
• Script Component Samples
•  Sources
• Transforms
• Destinations

I hope this list keeps being updated and more people contribute. Very useful.

Todo lo que debes saber sobre SSIS en 1 hora!

Gracias a lo asistentes de mi charla Todo lo que debes saber sobre SSIS en 1 hora! en el evento 24HOP LATAM. La verdad me agrado mucho presentar en espanol. Aun cuando mi primera lengua es espanol, todas las presentaciones tecnicas en el pasado las he realizado en ingles.

Si deseas ver la presentacion PowerPoint puedes bajarla dando click en la imagen a continuacion.

Tambien puedes bajar el proyecto que utilize en mi presentacion dando click en la imagen a continuacion. Nota: Solo podras utilizar la base de datos y proyecto en SQL Server Denali, no servira en SQL Server 2008 o 2005.

Differences between Merge and Union All transformations in SSIS

 What are the differences between Merge and Union All transformations in SSIS ?

 The first and most obvious difference is that Merge can only accept two datasets while Union All can accept more than two datasets for input. The second difference is that Merge requires both datasets to be sorted while Union All does not require sorted datasets. Both transformations are considered to be partially-blocking.

As can be seen on the following image, Union All can accept more than two datasets as input while Merge is limited to only two input datasets. Also, notice that for the Merge transformation, the datasets need to be sorted before they can be accepted as valid input.

For simplification purposes,  a Sort transformation was used. Keep in mind that the Sort transformation is a full blocking operation because it needs to read all the rows in the data flow (buffer) in order to sort the data. No data will be sent downstream until all rows have been read first by the Sort transformation. A better approach would be to sort the datasets with an ORDER BY T-SQL clause at the data source component.

Presenting at inaugural meeting for Wheeling, West Virginia User Group

I will be presenting my “Learning SSIS under 1 hour” session over live meeting for the Wheeling, West Virginia User Group on Thursday 1/20/2011. This meeting will kick-off the first meeting for the local user group put together by John Sterrett (Blog | Twitter). More details can be found at:

http://johnsterrett.com/2011/01/06/new-sql-user-group-in-wheeling-west-virginias-first-meeting-is-january-20th/

Learning SSIS under 1 hour – SQL Lunch 12-14-2010

Learning SSIS under 1 hour presentation was a fun presentation to put together for SQL Lunch. Thanks to all who attended. I hope it was valuable.

The live demo was recorded and can be found at http://www.sqllunch.com. The slides used for this presentation can be downloaded by clicking the image below.

Tuning the Performance of Change Data Capture in SQL Server 2008

Great SQL Server Best Practices Article on performance considerations for Change Data Capture:

http://msdn.microsoft.com/en-us/library/dd266396.aspx

Change Data Capture as a tool for Business Intelligence, Disaster Recovery, Auditing, and more

This is a summary of the Change Data Capture presentation covered in my sessions at SQL Saturday #38 – Jacksonville and SQLSaturday #40 – South Florida.

Presentation Summary
Change Data Capture (CDC) is one of the new features available for SQL Server 2008 Enterprise and Developer Editions, which allows you to easily track and keep history of all DML (Data Manipulation Language) changes on user tables such as INSERTs, DELETEs, and UPDATEs with little or no overhead. Change Data Capture has been used primarily to track changes for incremental loads using SSIS for the ETL process in Data Warehousing and Business Intelligence because it allows you to identify new data and data that changed or was deleted since last incremental load.

Before Change Data Capture in SQL Server 2005 and prior versions, identifying new or changed data required some level of custom programming, triggers or third party tools. These solutions work well but introduce additional overhead to your production systems. This is the main reason why Change Data Capture was introduced in SQL Server 2008; to eliminate overhead while tracking new and changed data.

But Change Data Capture use is not limited as a tool for ETL Process. It can be used for many other purposes where overhead, simplicity and cost might impose a restriction.  Some of these scenarios include:

1) You are a developer adding or modifying existing code and you need to see the difference in the data output on a particular table.
2) You are a DBA and need to audit several tables in a database and need to report which data was changed, what were the new and previous values, who did the change and when was the change made.
3) You were tasked to delete several records on a highly transactional production database and unintentionally deleted records that you were not supposed to with no possibility of rolling back the changes (no explicit transaction) and no possibility of restoring a backup.
4) You are a DBA and need to know which tables and columns are being written to the most in your database.
5) You are a DBA and need to know how many new records are being entered into your database in a period of time and the percentage of change of existing records.

In most cases you would want to know what changed and which were the values before the change.

 One of the most important benefits of this feature is that it allows you to see the data before & after an UPDATE or DELETE statement, which in turn allows you to query and recover data overwritten or deleted quickly without the need of a database restore. It also captures each new row inserted.

CDC accomplishes this task by reading the committed operations from the log file and inserting the changed records in a tracking table that mirrors the source (tracked) table. The records inserted in this tracking table contain both the value before the change and the value after the change along with the metadata associated with the change. The metadata can be queried to identify the type of DML operation as follows:

1 = Delete
2 = Insert
3 = Update (record’s value before update)
4 = Update (record’s value after update)

More concepts are covered in my PowerPoint presentation which you can download here or by clicking slide below.