• Post Categories

  • Browse Blogs

  • Blog Stats

    • 467,132 hits
  • Syndications

    SQLServerPedia Contributor

My PASS Summit 2013 Presentations

PASS_2013_SpeakingButton_180x180-1I had the honor to speak for the first time at this past PASS Summit 2013 in Charlotte, NC. It was a great opportunity for me as this is one of the most prestigious nationwide conferences on SQL Server. I delivered 3 different sessions on Business Intelligence related topics. Attendance to my sessions exceeded my expectations and my overall experience as a first-time speaker was very positive. I also need to add that the God’s of the live demos were on my side.

My session titled PowerPivot + Power View = Self-service BI Revolution was full and had about 5 to 8 people standing in the back. Everybody got excited when I did a demo of the nice interactivity in Power View and 3D geographical capabilities of PowerMap.

The Lightning Talk room was packed and people had to be turned away. This room should be bigger next time as these Lightning Talks are very popular. Everybody accepted and agreed with my postulation on the need for Conformed Stored Procedures in addition to Conformed Dimensions and Facts. After the Lightning Talks were over, several attendees came forward to discuss further how Conformed Stored Procedures should be implemented. One attendee in particular, mentioned that he had been thinking about proposing a similar approach to his team and this talk helped him get re-assurance of this approach.

My last session on SSAS Hierarhcies Tips & Tricks was a half-day session on the last day of the summit between 2:00pm and 5:00pm. It was half-full for the first half of the session, and ended up about 1/4 full.  This drop-off is expected towards the end of the Summit as many people fly back home on Friday and usually leave around 4:00pm.

I’m very pleased with the turnout and feedback received. The audience in all of my sessions was very engaged and had a lot of great questions. I definitely look forward to speak at PASS Summit 2014 in Seattle, WA and at PASS BA Conference 2014 in San Jose, CA.

To download my presentations slides click on the links below:

1. Regular Session (75 minutes): 4798 PowerPivot + Power View = Self-Service BI Revolution
2. Lightning Talk Session (10 minutes): Slides: 5511 Conformed Stored Procedures Script: ConformedStoredProcs_SQLScript
3. Half-Day Session (3 hours): 4803 Fast Performing SSAS Hierarchies Tips and Tricks

Thanks to all the attendees and PASS team for this great opportunity. I hope I met or exceeded everyone’s expectations.

If you attended one of my presentations, please leave your comments and feedback!

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.

SSIS errors: Bulk Load failed. Cannot obtain the required interface (“IID_IColumnsInfo”) from OLE DB provider “BULK” for linked server “(null)

When working with the Bulk Insert Task in SSIS 2008 you may get the following error:

 The complete error message is:

[Bulk Insert Task] Error: An error occurred with the following error message: “Cannot obtain the required interface (“IID_IColumnsInfo”) from OLE DB provider “BULK” for linked server “(null)”.The bulk load failed. The column is too long in the data file for row 1, column 1. Verify that the field terminator and row terminator are specified correctly.”

This error description can make you jump through hoops trying to figure out why is it detecting an error with the OLE DB provider or why does SSIS thinks you are trying to execute this operation on a linked server?

The real issue here has nothing to do with the first  sentence in the error description. Sentence 4 gives you the actual error:

Verify that the field terminator and row terminator are specified correctly.

You may be experiencing this error due to one or more of the following 3 reasons:

1) You may be specifying a wrong CommonDelimiter for your source file.
For example, You may be trying to do a Bulk Insert operation from a Comma Separated Value (CSV) file but did not change the CommonDelimiter property to Comma {,}. When you drag in the Bulk Insert Task the CommonDelimiter property default value is Tab.

2) You may be specifying a wrong RowDelimiter for your source file.
For example, you may be trying to do a Bulk Insert operation from a Comma Separated Value (CSV) file whose row delimiter character is different than the RowDelimiter property default value of {CR}{LF}. In some cases, you may receive a file with a very long stream of text with no Carriage Return (\r)  & Line Feed (\n) characters, commonly denoted as {CR}{LF} in between rows. These hidden {CR}{LF} row delimiter characters are placed on a text file each time you hit the ENTER key on your keyboard denoting the end of a row and beginning of the next row. You may read a little more about the Carriage Return and Line Feed characters in Pinal Dave’s blog: http://blog.sqlauthority.com/2009/07/01/sql-server-difference-between-line-feed-n-and-carriage-return-r-t-sql-new-line-char/

As seen on the image below, there are two properties,  CommonDelimiter and RowDelimiter, that you need to make sure  you specify the correct values for depending on your input or source file format:

3) You may be using a format file with an incorrect or invalid format defined.
Format files can be non-xml, commonly with an *.fmt extension or for SQL Server 2005 and later only you can also use xml format files. For more information about format files read MSDN Books on Line http://msdn.microsoft.com/en-us/library/ms191516.aspx

If you are using a format file make sure you are pointing to the right format file or that the format defined in your format file is correct. (Notice that the Format property value changes from Specify to Use File):

Phoenix User Group Presentation: 0 to SSIS

Thanks to the Phoenix User Group for hosting me tonight! The presentation went smooth and had good questions at the end. I will present anytime you need me and hopefully make it to Phoenix sometime soon.

Cheers my fellow Phoenix friends!

Phoenix User Group 2/9/2011 meeting attendees

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:


%d bloggers like this: