Rafael Salas

SQL Saturday Charlotte - BI Edition is Next Week!

2014-09-26T17:24:00.000-04:00

SQLSaturday Charlotte is the largest event serving Business Intelligence and SQL Server professionals in the Charlotte region

SQLSaturday Charlotte – BI Edition is only a week away and I would like to share some information about it:

· We have more than 40 sessions across different technical and professional development tracks.

· There is content for all skill levels

· We have a great lineup of regional and national speakers.

· There are many opportunities to network and connect with other professionals and companies in the area.

Space is limited, so if you are planning to attend I urge you to sign up soon and join the 400 professionals that have already registered.

I hope to see you next Saturday!

Power Query: Consuming SSIS Upgrade Wizard Output File

2014-02-19T23:20:00.000-05:00

Have you ever had the need to create summary and detailed reports to understand the results of a SSIS upgrade exercise such as packages by type of errors and warnings; the number of packages that upgraded successfully/unsuccessfully?

Upgrading SSIS packages is in most cases a straightforward exercise using the SSIS upgrade wizard. However, the output file produced by the wizard with the upgrade results (warning, errors, upgrade status, etc.) is very verbose and formatted in a way that makes it very hard to consume when upgrading a large number of packages.

I recently experienced this pain when working in a project that required to upgrade more than 1,800 SSIS packages that produced an upgrade output file with more than 35,000 lines worth of messages! See snippet of the output file below.

Since I typically use the output file to get a better sense of the effort involved to complete the upgrade of all packages, I quickly realized that for this project I would need to come up with a way to consume such a large output file. Fortunately, I was able to create an excel report like the one below using Power Query in a very simple way. As you can see below, it is very easy to get a sense of how difficult the upgrade is going to be. In the example below, I can quickly see there are only 14 packages that had errors ad 599 with warnings. Additionally, I can easily get a break down of the type of errors and the get the list of packages by message code/type.

If you are interested in creating a similar report, you can download a copy of a report template I created: SSISUpgradeResultsReport Template.xlsx

Note: Make sure you have Power Query for Excel installed and you edit pqSSISUpgradeReport Power Query query to point to the location where your SSIS upgrade wizard output file is stored ( See query below)

let
    Source = Table.FromColumns({Lines.FromBinary(File.Contents("D:\SSIS\SSIS Upgrade report all packages 2013 10 09.txt"))}),
    FilteredRowsBlanks = Table.SelectRows(Source, each ([Column1] <> "" and [Column1] <> "#(tab)" and [Column1] <> "#(tab)Messages" and [Column1] <> "The Upgrade is Complete")),
    InsertedCustom = Table.AddColumn(FilteredRowsBlanks, "Custom", each if Text.Range([Column1],0,19)="- Upgrading package" then [Column1] else null),
    FillDown = Table.FillDown( InsertedCustom,"Custom"),
    FilteredRows1 = Table.SelectRows(FillDown, each not Text.Contains([Column1], "- Upgrading package")),
    SplitColumnDelimiter = Table.SplitColumn(FilteredRows1,"Custom",Splitter.SplitTextByEachDelimiter({"("}, null, true),2),
    ReplacedValue = Table.ReplaceValue(SplitColumnDelimiter,")","",Replacer.ReplaceText,{"Custom.2"}),
    ReplacedValue1 = Table.ReplaceValue(ReplacedValue,"- Upgrading package","",Replacer.ReplaceText,{"Custom.1"}),
    SplitColumnDelimiter1 = Table.SplitColumn(ReplacedValue1,"Custom.1",Splitter.SplitTextByEachDelimiter({"\"}, null, true),2),
    RenamedColumns = Table.RenameColumns(SplitColumnDelimiter1,{{"Column1", "Mesage"}, {"Custom.1.1", "Folder"}, {"Custom.1.2", "PackageName"}, {"Custom.2", "UpgradeResult"}}),
    ReorderedColumns = Table.ReorderColumns(RenamedColumns,{"Folder", "PackageName", "UpgradeResult", "Mesage"}),
    SplitColumnDelimiter2 = Table.SplitColumn(ReorderedColumns,"Mesage",Splitter.SplitTextByEachDelimiter({":"}, null, false),2),
    RenamedColumns1 = Table.RenameColumns(SplitColumnDelimiter2,{{"Mesage.1", "Message Code"}, {"Mesage.2", "Message"}}),
    TrimmedMessageCode = Table.TransformColumns(RenamedColumns1,{{"Message Code", Text.Trim}}),
    Lowercase = Table.TransformColumns(TrimmedMessageCode,{{"Message Code", Text.Trim}, {"PackageName", Text.Lower}})
in
    Lowercase

PASS Summit: First Timer Questions

2013-09-27T22:45:00.000-04:00

Besides speaking at PASS Summit 2013, I am also volunteering as PASS buddy to help a small group of people that is attending the conference for the first time and make sure they have a good Summit experience .

I have gotten very good questions from my group and I thought I would share them along with my recommendations - in case they are relevant to you as well.

Q. I see an interesting session and I was wondering how I could get further information about it beforehand so I am confident it covers what I want.

A. Few ways come to mind:

· Read the abstract of the session: http://www.sqlpass.org/summit/2013/Sessions.aspx

· Connect with the speaker and ask them directly. You can read speakers Bios and most of them list their blog, website and twitter. This method gets extra points as you network at the same time.

Q. There are so many sessions, which one would you recommend?

A. Ah! a tricky question. I would recommend to start by looking at the schedule and the session abstracts and make sure you consider levels (beginner, intermediate, advanced, etc.) when making your selection. I recommend you use the schedule builder to mark the sessions you want to attend: http://www.sqlpass.org/summit/2013/UserLogin/tabid/9758/Default.aspx?returnurl=%2fsummit%2f2013%2fSessions%2fBuildSchedule.aspx and carry the PDF format of the schedule: http://www.sqlpass.org/LinkClick.aspx?fileticket=eNh5qYO0Qkc%3d&tabid=9664 with you at all times. Keep in mind that you can buy the recording of ALL session for $125; which means you can always watch that session that you couldn’t catch. http://www.sqlpass.org/summit/2013/Sessions/SessionRecordings.aspx

Q. I would like to connect and network with some speakers. What is the best way to approach them?

A. ah! This is another tricky one as we all have different styles and level of comfort when approaching people we don’t know. Most speakers will arrive 10-15 minutes before their sessions and will hang out and take questions at the end. You can approach them at those times and introduce yourself. You can follow up with a LinkedIn invite, Twitter, etc. Note: the minutes before the presentation may be stressful for the speaker, so be mindful of their time.

Q. This is my first time coming to town. What are good places to hang out/eat, etc.?

A. Downtown Charlotte, or Uptown as we call it, is very walkable. There is also a free shuttle service provided by the city that runs during the day: http://charmeck.org/city/charlotte/cats/Bus/ridingcats/Pages/GoldRush.aspx

Our friends from SQLSentry are also providing a Shuttle services at night time with stops in interesting places: http://greg.blogs.sqlsentry.net/2013/09/pass-summiteers-guide-to-charlotte-part.html

As always, there is an app for that. I use Yelp! To checkout places when I visit a new city. This link shows things around the convention center: http://www.yelp.com/search?find_desc=&find_loc=210+E+Trade+St&ns=1&ls=1a811fb8b865826d#find_loc=210+E+Trade+St,+charlotte+NC

Q. Will WI-FI be available at the convention center?
A. Yes, but keep in mind there will be a few thousand people trying to use it as well.

Q. Should I carry my laptop or tablet?

A. I would recommend to go around as light-weighted as possible as days are long and you want to save as much energy as you can. I would carry the lightest device I can live with. Please leave the server at home.

Q. I heard there other activities and after hour events. How do I know about them?

A. PASS Summit website has a “Connect at PASS Summit” page: http://www.sqlpass.org/summit/2013/Connect.aspx

Events range from Luncheons, Karaoke parties to 5k walk/runs so there is something for every taste. If you have a Twitter account, then you may want to follow @SQLPass and watch #SQLPASS and #Summit13 hash tags. If you don’t have an account, then just get one!

Q. What about Airport- Convention center transportation?

A. Taxis: flat rate from CLT airport to center city $25

Bus: Route 5, departs from Airport every 20 minutes, and it is a 25 minutes ride to the Charlotte Transportation Center (CTC) which is 2 blocks from convention center. Cost: $2 http://charmeck.org/city/charlotte/cats/Bus/routes/Pages/default.aspx

PASS Summit and SQL Saturday Combo!

2013-09-16T11:00:00.000-04:00

There are a lot of things that are better when enjoyed together and we all have our favorites, such as eggs & bacon, beach sand & bare feet, cookies & milk and of course PASS Summit & SQL Saturday.

This year, Charlotte, NC will be hosting PASS Summit and a SQL Saturday. This means you get the opportunity to attend 2 days of pre-conferences and 3 days loaded with more than 190 sessions that PASS Summit offers, and a whole free extra day of learning and sharing offered by SQL Saturday Charlotte all in the same week and city!

Have you register yet?

The registrations to both events are still open, and if you are still wondering if wondering if they are worth it, you should know that more than 5,000 people from more than 50 countries think they are.
PASS Summit: http://www.sqlpass.org/RegisterforSQLPASS.aspx – $200 off with SUM223CH code!
SQL Saturday Charlotte: http://www.sqlsaturday.com/237/register.aspx

Power Query: Stand Alone Vs. Corporate version

2013-09-04T11:20:00.000-04:00

One thing that caught me off guard as I was trying to use Power Query against Power BI for Office 365 was the fact that there are 2 versions of Power Query. This actually seem to have confused at least one other person already, so here is some information in case you face the same situation:
Microsoft Power Query for Excel - version 1.5.3296.2082
http://www.microsoft.com/en-us/download/details.aspx?id=39379
Microsoft Power Query Preview - version 2.6.3387.121
http://www.microsoft.com/en-us/download/details.aspx?id=39933

How are they different?

Version 1.x is the first release of Power Query the one that reached General availability on July 8 2013. This means, among other things, it is safe to run it in production environments. This is also the “standalone” version meaning it does not have the options to connect and share Queries via Power BI for Office 365 Version 2.x is the the so called “corporate” which means has the options to interact with Office 365 Power BI sites. It is important to note that as of this date, this version is in the PREVIEW stage which is some sort of beta period where features and functionality are still being finalized and its use in production environments is not recommended. So, if you are trying to use Power Query for Excel against Power BI for Office 365 make sure you have the 2.x version!

Power BI for Office 365: Installing Power BI App from SharePoint Store

2013-09-03T23:18:00.001-04:00

I got my invitation email to the preview of Power BI for Office 365 few days ago and while the provisioning process was pretty straight forward, I run into a minor issue that needed few extra steps that were not covered in the Power BI Provisioning Video. In my case, I did not have Power BI as one of the available apps of the team site I wanted to use as shown in the video (~3:45 in video). Fortunately, there is a provisioning guide document that provide the steps to manually install Power BI app from the SharePoint store.

Thanks to Gilad Elyashar for pointing me in the right direction to get this issue solved by replying to my post in the power BI forum. If you run into this or any other issue, I encourage you to make use of the Power BI forums to get answer to your questions

Power Query: Creating a Function to Correct and and Verify Addresses via External API

2013-08-12T09:15:00.004-04:00

One of the features I found very helpful in Power Query for Excel (former codename Data Explorer)is the ability to interact with external APIs as this opens the door to many interesting use cases. In this post, I want to show how we can use Melissa Data Address Check - Verify, Correct, Geocode US and Canadian Addresses service available in Windows Azure Marketplace to create a Power Query parameterized function that can be later referenced and re-used by other queries. This address cleansing service offers up to 1000 records per month for free, but you would have to pay a monthly fee beyond that point. Although the the terms functions and APIs may intimidate some of us, you will find that the Power Query makes the process rather simple, at least in this case.

Before you start
In order to complete the steps in this post you will need:

A Windows Azure Marketplace account (this is free)
An Active subscription to Melissa Data Address Check - Verify, Correct, Geocode US and Canadian Addresses – The free 1,000 records per month subscription works fine.
Understand and configure Power Query privacy levels so data sources can be combined. In this example I enable Fast Combined option

Getting familiar with the API

We will start by spending some time on getting familiar with the service we want to use. In this case, the details page of Melissa Data service we want to use has the basic information we need to get started: the Service root URL and Input Parameters

Using the API within Power Query

Power Query for Excel has many options hen it comes to source data (e.g. From Web, From Database, From Other sources, etc.) so it took me a little of guessing as to which option to use in order to get send and receive data to this API. It turns out that using From Odata Feed and entering the Service root URL does the trick.

Upon clicking OK, we notice that Power Query detects the SuggestedAddresses function exposed by the API, and provide us with an Invoke option within the Query editor.

Let’s go ahead and use the Invoke button. You will get a new dialog prompting you for the input parameters. As we saw earlier, the services requires an address value, the maximum numbers of suggested address – this is in case the service has multiple suggested address – and the minimal level of confidence – a value from 0 to 1, representing the level of confidence of the service that the cleansing results are accurate. Let’s go ahead and enter some values to test it.

As you can see, the results returned by the API are nicely displayed in a tabular format inside of the preview pane of the query editor, ad if you click done, the results are place in an Excel table, where they can be easily refreshed and further combined with other data sets. Quite easy!

But, do you see a caveat here? so far, we just were able to call the Service API for a single address value that we have to type in the invoke function dialog, which won't work if we have to clean 100’s or perhaps thousands of addresses at the time. So, how could we invoke this function for each record in let’s say, another data set?

Invoking a function for each record in a table

First, let’s revisit the function query we just created to remove the InvokeSuggestedAddresses step and rename the query so we give it a more friendlier name.This should leave our function ready to be invoked from other queries.

Now, let’s suppose we have an Excel table with multiple address records that we want to send to Melissa Data service for cleansing and validation. In this case, we will create a new Power Query using the From Table option as shown below.

When the query editor opens, select the column containing the address to be cleaned, in our case the address column, and right-click. Then select the Insert Column --> Custom option. Once the Insert Custom Column dialog opens, enter the following expression to invoke the function we just created. Click Ok.

fCleanAddress([Address],1,0)

As you can see, all we are doing is invoking fCleanAddress function and passing the 3 required parameters. The address parameter will be given by the Address column available in the data set – That’s the trick! MaximumSuggestions and MinimumConfidence are hardcoded to 1 and 0 respectively - You can change that if you want, or read those values from your datset.

You should see now a new column called custom (feel free to rename it) and the work Table as it values. This means the results in the new column need to be further expanded in order to be able to see the detailed results from each call to Melissa Data API.

You can expand the custom column fields by clicking in the double arrow icon next to the column name. At this point you are presented with all available fields and have the ability to select/unselect the desired fields.

Once you are happy with the Selection of fields, click OK and then Done to get the query results in an excel table

Taking it further

The example above, uses an static Excel table as the starting point, but you can use this same approach to invoke the function and get API results for records coming from other types of sources that are more dynamic in nature such as queries From Web or From Database. by the way, I would not expect this type of solution to have split of a second response times as in this case data goes to the internet and back and we have no control over the API layer.

Preview version warning – the information in this post is current for July 2013 Power Query preview (Version: 1.5.3296.1161) and it may no be subject to change for RTM version.

Why I am attending PASS Business Analytics Conference in Chicago

2013-03-25T12:09:00.001-04:00

PASS BA conference is less than 3 weeks away and if you are still pondering whether to attend, let me share with you few good reasons – my reasons of course– to attend.

Catching up. The last couple of years Analytics and BI landscape has experimented dramatic changes as new technologies make their way in and the heavy buzzword marketing machinery from vendors makes harder to separate the wheat from the shaft. You can use this conference to see if Big Data is really for you, or get your head around Advanced data analytics and perhaps to find out if Data Science is real or just marketing fiction.
Agenda & Speakers. The lineup of speakers is great and topics are well balanced across 5 different tracks that promises to deliver first-hand real world experiences: Big data, Advanced Analytics, Data Visualization, Information delivery and collaboration & strategy and Architecture.
Networking. Yes, the usual but important part we tend to miss when we go to conferences. Being around 1000s of professionals with similar challenges and interests is a unique opportunity to get connected and learn how others are doing it.

I will be one of PASS networking ambassadors on Thursday evening event and be hanging out at either Experience Lounge or the Microsoft Kiosk, so if you decide to attend, please make sure to stop by and say hi.

This is a 2 days conference plus a pre-conference day and registrations are still open and you can get $200 off the registration fee by using code BAC945MVP.

Honor to be part of SQL Saturday Richmond and SQL Saturday Jacksonville 2013

2013-03-03T09:56:00.001-05:00

Next Friday, I will be heading out to Richmond,VA to attend and present at SLQ Saturday #187. This is the second time SQLSaturday is coming to Richmond and my first time going there so I am very excited about joining the party. With a 2 full tracks packed with BI content, there is plenty of good sessions and speakers to choose from. You can register at the event page (note: the event seems sold out now, but you can still get in the wait list) Then in April 26th, I will be in Jacksonville, FL for SQLSaturday #215. Jacksonville has hosted a SQL Saturday from the early days SQL Saturday – They actually hosted SQLSaturday #3, so there is a great of experience among the organizing team I am sure! There are 3 full tracks of BI at this event, so if you are trying to get started on BI or get up to speed with the latest tools be sure to attend. It will be fun I promise. Register for this event at http://www.sqlsaturday.com/215/register.aspx In both events, I will talk about SQL Server Data Quality services, a new set of tools in SQL Server 2012 that helps you addressing data cleansing and data duplication issues at an enterprise-level. I delivered this same presentation at the PASS Summit last year and got great feedback – so , catch it if you can. About SQLSaturday: PASS SQLSaturday’s are free 1-day training events for SQL Server professionals that focus on local speakers, providing a variety of high-quality technical sessions, and making it all happen through the efforts of volunteers. Whether you're attending a SQLSaturday or thinking about hosting your own, we think you'll find it's a great way to spend a Saturday – or any day.

Power Query for Excel: The Next Piece Of The Self-Service Puzzle

2013-03-01T00:48:00.003-05:00

Microsoft released the preview of Power Query ~~Data Explorer~~ for Excel, a new add-in for Excel 2010 and 2013 that promises to bring powerful capabilities for searching, transforming, shaping and merging data to excel users. A clear sign that Microsoft’ strategy to democratize BI is still in full swing and perhaps aligned with industry demands for Business Intelligence self-service platforms.

From Data Explorer preview download page:
Microsoft “Data Explorer” Preview for Excel is a new add-in that provides a seamless experience for data discovery,
data transformation and enrichment for Information Workers, BI professionals and other users.

Do you mean Self –Service ETL?

Maybe, but in any case Data Explorer sounds a very exciting proposal given that data integration is the biggest bottle neck most BI initiatives experience, and where more often users must rely on technologies and skillset accessible only via IT departments.

Data Explorer First Glance

Once it is installed, Data Explorer manifest as new tab in the Excel ribbon.

From there you can do:

Online Search

Search for data sources on the web – at the moment all results seemed constraint to Wikipedia pages

Get External Data

These options provides the ability to connect and import data from a number of sources. A query is the unit of work when getting external data – a query contains 1 or more steps

From Web: Once you provide a URL, it allows you to import data from a webpage in tabular format. I was able to easily obtain data from quite a few pages (e.g Zip Codes with the Highest Median Household Income in the United States), although I imagine there are constraints as how the data is layout in the page for data explorer to work.
From File: quite a few options to load data from files in different formats, including XML and Excel.
From Database: a predefined list of major relational database types. I found some major players missing from the list such as Sybase and Netezza – I would imagine support for additional providers will be added later on.
From Other Sources: several interesting options here such as SharePoint lists, Odata and HDFS

Notice that each time you use these options a new query is created and then you are given the opportunity to transform, manipulate and shape the data. What is even more powerful is that each action is recorded in the form of a ‘step’ that get replayed each time the query is refresh. Query steps are expressed via formulas that are based new set of functions. See “Data Explorer” formula reference for more details.

Combine Data

Once data has been imported you can combine it.

Merge: Allows you to bring columns from 2 different queries together – similar to a JOIN in the SQL relational world.
Append: Creates a new query with all records of a first query followed by the rows of a second one – similar to what a UNION ALL does in the SQL relational world does.

I created a quick sample workbook that gets data from Northwind Odata feed and another external web page (top Zip Codes with the Highest Median Household Income in the United States). It is not the most meaningful scenario but will let you get the general idea of how this stuff works. You can see a copy this sample here:

What’s next?

You can download Data Explorer and start learning about it:
Microsoft "Data Explorer" Preview for Excel
Data Explorer Help
Microsoft "Data Explorer" Preview for Excel blog

Summary

Data Explorer could play a key role in Microsoft self-service BI platform as it reduces the friction typical BI users experience when searching, collecting and connecting source data. However, as a component of a major system it will be interesting to see what steps Microsoft will take. Few things come to mind

What would the “sharing” story be?
What would the progression/graduation from self-service to IT-managed solution looks like? Perhaps a Data Explorer to SSIS path – I am sure many of us will like that
Would we get a tighter integration with the other Excel “Powers” (PowerPivot & PowerView)
Will it integrate with Data Quality and Master data Services?
Will IT be given tools that allows it to have the some sort of oversight ?

SQL Saturday 174 Charlotte BI Edition Countdown

2012-10-10T07:58:00.001-04:00

SQL Saturday is coming to town on Oct. 27th loaded with BI goodness for everybody taste and needs. As you, know 2012 is new version year for SQL Server and this is a great opportunity to come and learn the new and the so-no-much new in Microsoft BI from top notch speakers (event schedule).

If you are new to SQL Saturdays, you should know this is 1 full day conference bringing high quality training and networking opportunities; and the price is about right: free.

Registrations are still open, and I encourage to act now as we anticipate to go into waiting list in the next week or so (register here)

I hope to see you there!

SSIS 2012 and My Old ETL Framework

2012-08-13T09:00:00.000-04:00

Few weeks ago, I was working on adapting certain pieces of an existing SSIS framework created with an earlier version of SSIS to make it work with SSIS 2012.
In this post, I want to share some of my findings and design notes around auditing-logging-re-cover ability of that ETL framework and how I got them to play along with SSIS 2012 and the project deployment model.

An old good practice: ETL execution logging

It is a general good practice to generate and capture an ‘adequate’ level of execution logging information in your ETL applications or systems. The idea is that ETL developers and admins can use that information to determine if and when a processes has started, if and when it fails, and when it completes. From there, one can start doing more interesting things such as creating reports to monitor the execution of processes, develop auditing capabilities – e.g. what instance of a process touched what rows –, and even use this information to create a custom processes/package restart and recovery capabilities.
Almost every ETL framework I have seen and worked have functionality embedded, and it is typically implemented inside of the control flow of the packages using a pattern like this:

Log begging of process and generate ID>> process logic >> Log end of process

Typically, this is done at the master package level and in many cases something similar is done inside of each child package. In every case, an unique ID is generated as soon as the main (or master) package starts. This ID is the logical unit of ETL process execution. Under my design, no ID is generated until the previous one has been marked as completed – even if the process is executed multiple times due to failures.
People use different names for this ID or unit of work: run_id, application_instance_id, ETL_control_id, etc. In this reincarnation of ETL framework I am working on these days, I call it ETLbatchID, and I use a table like the one in the picture below to store them and keep track of them

As you see, This table not only gives me the ID, but also provides other important information such as start time, end time and the number of time the process was re-started.

But wait, do we still need this technique in SSIS 2012? I heard logging is automatic…

In my case, the short answer is yes. See, SSIS 2012 brings a great deal of manageability and auditing improvements that automatically tracks and logs execution of packages. Each time we run a package in the SSIS server, an execution_id is generated and logged in SSISDB – the SSIS catalog. It even goes as far as associating that execution_id to all executable – such as tasks, children packages - that ran under that package.Though this is great, it has its caveats. One of the problems is that SSIS 2012 server issues a new execution_id each time you execute a package, so if the process fails and I restart the master package , I would get a second execution_id, hence having to track 2 different ID values without having an straightforward way to group them. If you are curious, just run queries against the executions view in SSIDB as you run packages: [catalog].[executions] view in SISDB database.

The way most ETL frameworks work, there needs to be control over the unit of auditing of the ETL processes. For instance, all sub process and records affected by an execution of the ETL process can be identified using an unique ID, even if the process has to be run multiple times (e.g failures).

Bringing the 2 together

Since I wanted to keep the same level of control over the ETL auditing unit of my old framework, but still take advantage of the goodies SSIDB offers me, I ended up creating a bridge table that will associate the custom ETLBatchIDs with the SSIDB execution_ids and extending the framework’s logging process to populate it via execute sql tasks.

Notice that I did not add a FK constraint between the custom tables and the SSISDB (dotted line) as I wanted to minimize the impact this could have had over SSIDB cleanup processes.
Whit this setup, I now have the ability to keep the unit of work/auditing the user was used to – ETL Batch ID – but still be able to join to the built-in SSIDB execution tables and views, which open the door to a richer ETL auditing and reporting experience. For instance, I am able to see how many SSISDB executions_IDs were required before a given ETLBatchID was completed and access the execution details of each one of them, like in the case of ETLBatchID=1007 below

Notice that I still have a lot of testing to do, so if you decide to use this approach, use it at your own risk. On the other, I would love to hear your feedback if you know of a different way of doing this, or if you catch any flaws in my approach.

-rafa

Honored To Be Speaking At PASS Summit 2012

2012-06-19T08:23:00.001-04:00

PASS Summit is the largest and perhaps the most influential SQL Server event in the world with more than 170 technical sessions and drawing 3 to 4 thousand people every year. Why I am telling you all this? Because, I was greatly surprised when I learned 2 of my submissions were selected, one of them as alternate, for this year PASS Summit in Seattle.This is my first time presenting at such great event and I fell both excited and honored about making the list of speakers.
A big thanks goes to ,my employer Intellinet for sponsoring my trip there and to my team and coworkers for helping me refining the abstracts. Thanks Melissa, Leo and Laura!

TRACK	Session	Tags
BI Platform Architecture, Development & Administration	Waiter, There's a Fly In My Data	Accepted
BI Platform Architecture, Development & Administration	SSIS 2012: What is in that project?	Alternate*

*Alternate means that it has been accepted as an alternate session in the event that an accepted session is declined or canceled.

Browse the full schedule in PASS website. I hope you can attend.
I will see you in Seattle!

Presenting at Triangle SQL Server User Group April 16th

2012-04-16T23:29:00.001-04:00

I am honored to be presenting at Raleigh’s TriPass group tomorrow Tuesday at 5:30PM. In this session, I will be presenting the new server features in SSIS 2012 and how to create, deployed and managed solutions using the new deployment model. You can check out the slides for this session in my earlier post.

I hope you can attend.

Presentation: SSIS 2012 - New Server and Project Deployment Model

2012-04-14T07:13:00.001-04:00

I will be delivering this presentation in a number of events in the upcoming weeks and want to shares slides to give you a better idea what the session is about. Enjoy it!

SSIS 2012: New Server and Project Deployment Model

View more presentations from rfsalas.

Got a New Team!

2012-03-16T09:00:00.000-04:00

A really fun team!

A few weeks back, I took a big step and decided to change jobs. I joined one of the most talented and fun teams I have ever seen, where I get to hang out with Geoff Hiten (@SQLCraftman | blog), Melissa Coates (@SQLChick | blog) and Michael Oryszak(@next_connect | blog), all of them well known for their expertise and contributions to the technical community. I also get to work for a company that thinks and breaths community support, that delivers solution using Microsoft technologies, and to top it off, takes client engagement and satisfaction to the next level.

So, where did I go, and what will I be doing? Thanks for asking! I joined Intellinet, as a Principal Consultant in the BI practice. This position puts me back in a consulting role, and as a bonus I get to work from home – when not at client sites – which I am already loving!
Cheers,
Rafa

SQL Server 2012 General Availability

2012-03-13T09:00:00.000-04:00

Did you hear it already?

SQL Server 2012 is here

Last week, SQL Server 2012 was released to manufacturing (RTM) and general availability is expected on April first – That’s right, on April Fool’s Day. Now, if you don’t want to wait until then, you can download a 6 months evaluation copy here, or from your MSDN subscription – if you have one of course.
With that out f the way, here are few links that are worthy your attention:

What’s new in SQL Server 2012. An overview of what’s is new or changing in each of the SQL Server products. You may find that not all changes are listed there, specially small ones.

SQL Server 2012 Books Online. Product online help.

SQL Server Database Tools. Do you remember Data dude projects and BIDS? well, now they are coming under a single roof called SQL Server Database Tools (SSTD), but still using Visual Studio shell – No! you don’t need Visual Studio licenses for it, it is free for SQL Server users. You can read SSTD Frequently Asked Questions, follow the SSTD’s blog or read Books Online to get up to speed. This is probably the most exciting change for us database developers as it brings declarative development experience to the every SQL Server developer – believe me, something you want use if you take database development seriously.

SQL Server Data Quality Services (DQS). This tool is the new kid on the block and one of the componets the Entirprise Information Management (EIM) plataform. DQS functionality comes handy when dealing with data quality and cleasing scenarios where identity resolution and matching are required.

The licensing model and numbers of editions being offered are also changing. Although, I am excited about a new BI edition being offered, you may find out that some features that are arguably BI hardcore features are included only in the Enterprise edition.

SQL Saturday #111 Atlanta: Taking Your SSIS Skills To The Next Level

2012-02-15T08:51:00.001-05:00

With the SQL Saturday in Atlanta approaching quickly, I want to point out one more reason, and my personal favorite, to attend it: SSIS. This particular SQL Saturday comes loaded with great lineup of speakers and session in SSIS. First, there is a full day of SSIS offered as pre-conference ($99-$109) where Andy Leonard and Matt Masson take you from how to spell SSIS to building robust ETL solutions. The next day, you will have 5 more sessions on different topics and version of SSIS (including one by me):
Hope Foley
Super-size Your SSIS Breakfast Sandwich: Performance
Rafael Salas
SSIS 2012: Project Deployment Model and Catalog
Andy Leonard
Designing an SSIS Framework
Matt Masson
SSIS in SQL Server 2012: Deployment and Management
Patrick LeBlanc
High Availability for SSIS in SQL Server 2012
Remember that attending Saturday sessions is free, so make sure you register and come. I hope you see you there.

SSIS 2012: Better Execute Package Task

2012-01-31T09:32:00.002-05:00

Execute Package Task had some rough edges in previous versions of SSIS. to name a few, you had to create a connection manager to be used by each task/package, and there was not a simply way to parameterize the connection managers all at once. Passing values from parent to children packages was something that did not have a good story either.

A plethora of connection managers in the old days...

What has changed in SSIS 2012?

With SSIS 2012 when you use the new project deployment model, the Execute package task is now easier to setup and configure:
For one, we just need to select the name of the package – any package within the current project – from a drop down list, as we will save quite a few clicks a we don’t need to create a configuration manager each time.

Point and click

Second, we have the ability to bind the parameters of the child package to variable or parameters in the parent package, and once again, just by selecting the appropriate values from the dropdown lists.

Nice: The execute package task 'sees' the parameters defined in the child package

Third, there are no connection managers to maintain and configure.

Now, remember these benefits are only available when using the new project deployment model.

Two Tricks When Adding Packages To A SSIS Project

2012-01-26T12:14:00.002-05:00

If you need to add multiple packages to a project you may have noticed that the ‘Add existing package’ option is rather irritating as you have to do it one at the time. So, here is a trick to add several package all at once:

Right click in the project name in the solution explorer
Select add existing item
Navigate to the folder where the packages reside, and use CTRL+left click to select packages.
Click the Add button

Wait, you said there were two tricks!

Indeed. Matt Masson shows a different way of doing this in his blog. Actually, was his post the one that prompted me to write mine
As with most things, TMTOWTDI

SSIS 2012: A Peek to Data Taps

2012-01-10T09:00:00.000-05:00

Note: The example in post is based on SSIS 2012 RCO
Data taps are one of the new features in SSIS 2012 in the data and performance troubleshooting

category. In a nutshell, they allow us, at runtime, to choose a given path from a data flow and capture a copy of the data at that specific point of the data flow in a .csv file on a given execution instance of the package.

Let's break it down some more:
Run-time. Data taps are a runtime artifact, so as developer, you don't define them as you design and develop packages in SSDT. Rather, you do it after the packages are deployed to the server by way of running some stored procedures in SSMS.
Data Flow Path: Data taps are defined at the data flow level, and within a data flow, at the path level. Paths are the blue or red arrows in the data flow that connect inputs and outputs of data flow components. You can read the books online definition of a path here.
Execution Instance: A data tap captures data in a given data flow path and stores it in a .csv file. This happens within a single execution instance of the package. That means we need to add the data tap each time we run the package during our troubleshooting exercise.

You could think of data taps in a similar way you think about data viewers in ~~BIDS~~ SSDT, except you don’t have to edit the package to add them.

Setting up a Data Tap

Now, let’s see an example and some pictures to show how you can add a data tap to a data flow.
First, we create a package with a data flow in it and deploy it to the SSIS server. For this example, I have a pretty straight forward data flow that gets a list of 4 products via OLE DB Source, does some string manipulation and then loads the data into an OLE DB destination. As you can see in the picture, this data flow has 2 paths (blue arrows linking data flow components). Note the value of IdenificationString property of the data flow path as we will use it later.
.

We will need some pieces of information about the package we want to tap data from before we can setup the data tap:

Folder Name: The folder within SSIS server where the package is deployed.
Project Name: The name of the SSIS project that holds the package
Package Name: well, just the package name.
Data Flow Path ID: The value of the IdentificationString property of the data flow path where we want to add the data tap (highlighted in yellow in the picture above).
Data flow task path: The path of the data flow task within the package. As opposed to getting the Dataflow path ID, getting the task path is tricky as it is not readily displayed in SSDT, an you typically have to resort to some sort of trick to get it. I will share a trick you can use to get value of the property path in another post. In this example, let’s assume we do know that the path to the data flow is \Package\Load Product Update: The path to the data flow task is readily available in the packagePath property of the task. Thanks to David Joubert for pointing that out!

Once we have this information, it is time to add the data tap(s) and execute the package. We do all this in SQL Server Management Studio by running 3 stored procedures that are built-in the SSIS catalog:

Create a execution instance for the package by running [catalog].[create_execution] stored procedure
Add the data tap(s) by running [catalog].[add_data_tap]
Run the package by running [catalog].[start_execution] package

Here is the script I used while running my sample package:

https://raw.github.com/gist/1558450/f0a254d68a047f6199e6f53566c195cfcf06e192/gistfile1.sql

If everything goes well, you will have the .csv files in the :\Program Files\Microsoft SQL Server\110\DTS\DataDumps folder of the machine you are running the stored procedures. You can always use the built-in package execution reports to validate the execution of the package was successful or to look for error messages generated during the execution of the package.

Folder with data tap files

Is That it?

Almost. The example in the post is quite simplistic and the bare minimum I could come up with to get a data tap working. There is additional information in books online about them that you may want to review:

catalog.add_data_tap
catalog.remove_data_tap
catalog.execution_data_taps
catalog.execution_data_statistics

Conclusion

Troubleshooting data issues can get hairy at times and data taps are an extra tool in our belt that can help in cases where logging (which has also improve a lot in SSIS 2012) and dumps may not give us the required level of information. In the other hand, I would have appreciated a more seamless user experience, specially when getting the parameters needed by the stored procedures, and perhaps having more control over the file format and its content. As it stands, we may always need to open a copy of the package in SSDT to get the required metadata and the jump back to SSMS to complete the work. It would be great to have a point and click interface within SSMS that allows to navigate the structure of the package/dataflow and let us add a data fow with few clicks. I would like to see an interface like the 'package explorer' in BIDS for this, but unfortunately that suggestion did not get too much traction last time around and it was closed as 'won't fix'. may be in SSIS 2014?

SSIS 2012: Project Deployment Model and Build Configurations

2012-01-03T10:30:00.000-05:00

With the introduction of the new project deployment model in SSIS 2012, we now have the ability to deploy a project to a SSIS server and use parameters to packages and projects. With this, there may be instances where we need to change the name of the SSIS server and the value of parameters as we develop, debug and test packages within SQL Server Data Tools. Depending on the number of servers and parameters your project and package may have, making those changes may be rather tedious and prone to error. I am thinking in the many times people have mistakenly used the wrong connection strings when debugging a package in the old days - I have done it many time.
The good news is that projects using the new project deployment model in SSIS 2012 leverages build configurations from Visual Studio in a more meaningful way than in earlier versions, making most of these changes a lot simpler. How? I am glad you ask. For one, both, project and package parameters can get their values from build configurations. For two, some project level properties can also be set via build configurations (things like Deployment Server name and project path); which makes debugging and deploying packages in SSDT (formerly known as BIDS)against different SSIS servers much simpler.

Binding Parameter values to Build Configurations

For this, you just need to go to the parameters tab (project or package) and click button with the weird icon. The tooltip actually says “Add Parameter to Configurations'”.

Then you have the opportunity to choose the parameters you want to associate with the multiple build configurations you may have created. In the picture below, you can see how my package has 5 parameters and I had created 2 build configurations, one called “Test” and one called ‘Development”. Now you can enter a value for each parameter and configuration combination.

So, how is this helpful? Well, Assuming that I need to change the value of the 5 parameters at design time as I debug the package in 2 different machines, I just need to switch between build configurations and to have all parameter values changed at once. Switching between build configurations is as simple as selecting a value from a dropdown list.

Binding Project Properties to Build Configurations

Similarly, there are some Build, Deploy and debugging properties of the project that can be assigned using build configurations. For that go properties of the project and select the the properties and the configuration and assigned the desired value.

Lastly, keep in mind that build configurations are not new to SSIS 2012 projects, but I honestly did not find them helpful until now.

SSIS 2012: Parameters and Variables, what is the difference?

2011-12-28T14:02:00.000-05:00

Note: The information on this post is based SSIS 2012 RC0

This is a question I ran across recently while browsing the SSIS 2012 pre-lease forum. While there may be multiple ways to look at the differences and similarities among them, I thought I would give it a shot and share some of my observations.
If you are familiar with earlier versions of SSIS, it is worth noting that the concept and usability of variables have not changed much in the newer version. on the other hand, it is also important to point that parameters are one of the new features introduced in SSIS 2012 – available when using the new project deployment model - that promises to address some of the shortcomings of package configurations – which are still available.

Scope

Parameters can be defined at 2 different levels:

Project. These type of parameters are available to all packages within the SSIS project. Think about them as global parameters. They come handy as it makes really easy to share a given value, such as the path to a file share or the name of a server, across all packages in a project.

Package. These type of parameters are meant to affect only the package on which they were defined.

Variables are richer in scope as they can be scoped at the package, container, task or event handler level.

Execution Time behavior

The value of a parameter can’t change within the execution instance of a package. That means, its value remains the same for the entire execution of the package. Honestly, I don’t envision this being an issue in most cases.
On the other hand, the value of variables can change during the execution of the package, which make them suitable for scenarios where, for example, looping, conditional or any other type of logic where the values of the variable must change during the execution of the package.

Consumption and Usage

Parameters are applied via expressions on the properties which are intended to be parameterized. The value of a parameter can be set at development time, after the package is deployed to the SSIS server (via SSMS) or at run time (SSMS or SS agent job) and seem the way to go to affect package execution without having to modify the package. At design time, you can quickly get access to the “parameterize” window by right clicking on a task, container or connection manager, which saves few click when compared to using the expression property of the object. Additionally, parameter values can be set via execute package task, when a package is called from another package.

The value of a variable can be set in many ways within a package and its value can be further referenced by other expressions, containers, tasks or components.
Note: Expressions are not new to this version of SSIS and something you should put in your short list of things to learn if you are serious about SSIS.

Data types

When compared to variables, parameters do not support the following data types: Char, DBNull, Object.

Visual Studio Configuration Manager

One of the nicest improvements I have seen in the RC0 build is the ability to bind parameters to the design time configurations offered by SQL Server Database Tools (formerly known as BIDS), which comes very handy when designing and and debugging packages in ~~Visual Studio~~ in SQL Server Data Tools.

Conclusion

Parameters are the new kids on the block, and it is clear they are being treated as 1st class citizen within the new project deployment model. Although variables and parameters have some similarities, trying to find out which one is better may not be the right thing to do. In my opinion, it is more a matter of understanding their capabilities and choosing the right tool for the job.
I am pretty sure there may be more aspects to parameters and variables than the ones I listed here, so feel free to leave your feedback in the comments below with anything I may missed.

SQL Rally 2011 is this week!

2011-05-10T09:55:00.001-04:00

SQL Rally inaugural edition is in Orlando, FL this week and I am delighted to be attending as speaker. This will be a great opportunity for me to get good technical content and to catch up with old friends and meet new people. This conference comes with 2 special bonuses for me: I will not have to travel to the other side of the country – big savings there! - and will get to enjoy of Orlando's warmer weather.

What is my session about? I am glad you asked. I will be talking about one of my favorite topics: SSIS and ETL architecture, this time from a sinister angle:

Helping Your Data Warehouse Succeed: 10 Mistakes to Avoid in Data Integration

If you happen to be attending and are interested in learning from my mistakes, please feel free to attend this session.

Are you attending SQL Saturday #70 in Columbia, SC ?

2011-03-16T08:54:00.001-04:00

This is a quick reminder: SQL Saturday #70 is this coming Saturday March 19th in Columbia, SC. The schedule promises to be a day full of exciting sessions and good speakers.

BI seems to get increasing tractions at this type of events, and this year there are 3 tracks dedicated to BI topics. I have been given the honor of delivering a session:

DW-ETL: 10 Mistakes to Avoid

With data being the focal point of a data warehouse, its data integration component becomes a critical point of failure of the whole system, and to make matters worse, we, as developers, face increasingly complex scenarios and requirements such as larger number of systems and volumes of data, data quality problems and complex business logic. In this session Rafael will share a compilation of pitfalls and mistakes he has seen –and made!– across many implementations, and offers recommendations to avoid them. We will look at problems related to metadata, data quality, monitoring, system reliability and some SSIS specific issues among other common problems.

Ah! and if you haven’t registered yet, I encourage you to do it right away (registration link). Last thing, I heard is that space is running out. The cost for attending is about right: Free!