Rafael Salas

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 Task and Property Paths

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

There are times when we may need to know the path to an specific property or task within a SSIS package. e.g.

When setting up a data tap – a new debugging feature in SSIS 2012 – one of the values we need to provide to the SSIS catalog stored procedure [catalog].[add_data_tap] is the path of the dataflow task. e.g.

\Package\Data Flow Task

When Overriding design time value of some properties at run time – via either DTexec or new in SSIS 2012, via [catalog].[set_execution_property_override_value] – we need to provide the path to the specific property been overridden.

\Package.Variables[User::MyPackageVariable].Properties[Value]

If you are already familiar with SSIS, the syntax of these paths look pretty readable and in some cases you could just guess it right if you know the package well enough. However, given that paths are case sensitive and that they can get quite lengthy depending on the package structure, it is sometime helpful to know there is an old trick you can use to get them.

An Old Trick...

The trick is to use the package configuration wizard (look for it in SSDT in the SSIS menu) to simulate adding a package configuration to the property or task you need to get the path from. The package configuration wizard shows the property path being configured in the last page of the wizard, so once you reach that page, just copy the path and hit cancel.
Note: The package configuration wizard will force you to go down to the task/object property, so when you need only the path to a task (e.g. for data taps ), just discard the .Properties[xxx] part as shown in the pictures below.

What is the path to this dataflow task?

Task and property path using the configuration wizard

To give credit where credit is due, I first learned about this trick in Jamie Thomson’s old blog here, and that was back in 2006 - btw, Jamie's now blogs here.

There have been multiple requests in connect asking for a more straightforward way to get this information, but it seems like it did not make the cut for 2012 version either. To me this is not a deal breaker, but is one of those things that makes SSIS look harder to learn than necessary.

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

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:

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!

SSIS and Netezza: OLE DB Connection Bug in Multi-Threaded Environments

2011-02-09T10:58:00.001-05:00

This is a long overdue post. Several months ago, while I was working in a SQL Server to Netezza data migration, I ran into some issues when trying to to load data into Netezza tables via SSIS. First, it was the OLE DB destination acting up and not keeping its settings. While that was a very annoying issue, there was a work around - you can read about it in my other post.
Later, I ran into what appeared to be a random connectivity issue using Netezza OLE DB provider. The packages would unit test just fine, but some of them would fail when ran concurrently from a ‘parent’ package.

Each time the parent package ran, the result was the same: The first x numbers of package succeeded while the remaining ones failed. Packages would fail randomly as shown in the 2 examples in the picture above. The error messages generated by SSIS and Netezza were not very helpful:

SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "OLE_DST NZ Table" (1776) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.

After a lot of troubleshooting I was able to make some observations:

The first set of packages, as given by MaxConcurrentExecutables property in parent package, completed successfully. If I ran it in my laptop, with 2 cores, 2 packages would succeed. If I ran it in the server, a 16 core machine, then the first 16 or so packages would succeeded.
Watching the activity in Netezza admin console revealed that the first x connections remained open for extra 1-3 minutes after the first set of packages completed.

At that point, I decided to open a ticket with Netezza support. Things were not easy as they did not have a lot of experience with SSIS and their first attempts to reproduce the issue failed. They tried to make the point that I should run one package at the time, by setting MaxConcurrentExecutables=1 in the master package. The thing is that a) I was not willing to take that as an acceptable solution – waste of resources in multi-processor machines. b) the issue was even worse when using that setting.
To make the story short, after 5 months and exchanging 122 emails with Netezza support, they finally were able to reproduce the issue and acknowledge there was an problem with their OLE DB driver. Their final email says:

The issue is in our OLEDB driver relating to error-handling in a multi-threaded environment.The fix will be in patch 5.0.10P8. The release date will be sometime in mid to late December

I have not been able to verify if the issue was actually fixed because our internal operations team does not have a date for applying that patch. And if you are wondering what did I do to get around the problem, I ended up using a script task to pause the execution of.packages for 3 minutes, long enough for the open connections to close, before the next set of packages got executed. This is not by any mean an approach I would put into production, but was good enough for me since this was a 1-time data migration.

Here are the details of my environment:
• Windows Server 2003 R2 SP2 Enterprise x64 edition
• 64 GB RAM - 16 cores
• SQL Server 2005 SP2 Enterprise edition (64 bit)
• Netezza OLE DB Diver 5.0.8 (but we got same error with 5.0.6 version)

I am wondering how many people is using SSIS to load data into Netezza and running into similar issues?

My Geeky Goals for 2011 (T-SQL Tuesday #14)

2011-01-11T21:14:00.001-05:00

This month’s T-SQL Tuesday party is being hosted by Jen McCown from MidnigthDBA and the topic she picked was:

What techie resolutions have you been pondering, and why?

…and what a better time to talk about goals and resolutions than the beginning of the year, right?

A few nights ago, I went trough the exercise of reviewing my career/community goals for the new year and I came up with a rather long list of things:

Speaking. 5 presentations (or maybe more?). Last year was when I got serious about it and ended up delivering about a dozen of them in 7 different cities. It was a lot of fun and I felt it helped me a lot to get to the first stop: feeling confortable in front of an audience. This year, I would like to polish my presentation skills, but I doubt I will be able to commit to present as many times as last year, mainly because I need to reduce time away from family and travel expenses.
Writing. I am considering 2 different formats this year: Technical articles and blogging. I am thinking I should be able to write 3 articles and to blog an average of 2 or 3 time per month. Again, trying to be conservative to make sure I get enough time for the remaining goals. A stretch goal here is to write some of them in Spanish.
Helping: I want to increase my contribution to technical forums – perhaps sticking to MSDN SSIS forum for now. This is how I first started contributing to the technical community and something I really enjoy.
Learning: The wish list here is long, so in order to make it doable I may need to limit myself to 2 new areas/technologies. So, right now, I think any 2 from the list below should make a good goal:

Data visualization
Data Quality
Analytics
PowerShell (yes, me too!)
SSIS Denali

I am choosing to work some in several areas rather than doing a lot in just a couple of them. Not sure if this will be the most effective way to invest my time, but it looks like is going to be fun. At the end of the year, I expect to look not only at the number of goals I have accomplished, but also how fun the ride was.
Happy new year!

Un-SQL Friday 001: Personal Branding by Accident

2010-11-19T23:58:00.002-05:00

This post is my way to pay to the new Un-SQL blog party created by MidnightDBAs.

You hear it a lot: Personal branding and marketing are powerful, importat, etc., etc., etc. – ok, but where do you start with it? While I may not have the right answer, I found out that branding, name recognition, or whatever you want to call it could be achieved as a side effect, or like in my case, almost accidentally.

Here is the branding part…

Little I knew, about personal branding back in 2005 – and little I know today for that matter! - when I started answering questions in one of the MSDN forums. One day, I happened to be looking for help with some SSIS work I was stuck with, and decided to answer a couple of questions thinking on just returning the favor. The next thing I knew, I was addicted to answering questions on that forum, to the point I started this blog as a way to add sample code – and pretty pictures – to my forum posts. Months later, good things started to happen. First, I was promoted as forum moderator – that was like advancing to the next level in your favorite video game –. Then, I was awarded as a Microsoft MVP, followed by a promotion at work. Flash-forward to the present, earlier this year, I also managed to change jobs. How did I found the new job? My new company happened to be looking for someone with BI and SSIS skills and my blog caught their eye, and in addition to that, some of my new co-workers knew my name from either my blog or presentations I gave at community events. While it may not be reasonable to say I achieved all these via blogging and forums activities, I know they were a differentiating factor that help me standing out from the competition.

So, where is the accidental part?

First, when I started spending that much time on replying to someone else's problems 5 years ago, I wasn't doing it as part of an elaborated branding plan. I was just doing it because I found it enriching and entertaining - yes, geek entertainment –. I just liked it. Second, my lack of artistic creativity worked on my favor when I picked my blog domain, online avatars and handles. I ended up using my own name and picture which happens to align with some pieces of advise offered by branding savvies like Brent Ozar. I wish I could have come up with something as cool as the MidnightDBAs or funny as SQLChicken, but that was just plain beyond my abilities.

My 2 final cents

If you are not ready to tackle the personal branding thing just yet, don’t stress yourself over it. But at least pick things you like – and that are legal – that can be easily aligned with your professional interests. Also, make sure you enjoy the time you invest on it, if it is not fun, then it may not be worth it.

Denali CTP 1: SSIS Parameters – Bring Them On!

2010-11-17T12:25:00.001-05:00

The new version of SQL Server code-named Denali brings the concept of Parameters to SSIS. At first glance, I can say they close some of the existing gaps in the package configuration model form earlier versions and it seems a big step in the right direction for the product usability.
If you are interested in learning about the Developer Experience and Usability enchantments, you can read mi earlier post.

In a nutshell,you can think of SSIS parameters as the input parameters you would use in a function or stored procedure. See the link at the end of the post to learn how they work.

The Good

Flexibility. They can be declared at the project and/or package level and can take 3 types of values: Design default value, Server Default value, and Execution value. The last 2 type of values become relevant after the SSIS project has been deployed to the server.
Simplicity. SSIS parameters seems easier to understand and work with. You add them to the packages or projects and reference them via expressions. This is especially true when you compare them to package configurations in the previous version of the product.

Parameter –> Object Property (via expression)

Security. You can mark parameters as sensitive and have their values stored encrypted when the package is deployed. This is helpful when you have parameter values containing sensitive information like IDs or password. Notice that parameter values are not encrypted at design time (BIDS) even if you marked them as sensitive, so you are still responsible of protecting that information in developer’s machine, perhaps leveraging the ProtectionLevel property of the package.

Caveats

Parameters are available only in SSIS projects using the new Project Deployment Model. There are 2 models in the new version of SSIS: Project Deployment Model and Legacy Deployment Model. I will try to cover the differences about this model in a later post, but for now think about them as present and past. The legacy model promises to let you work on the way you were used to in the previous version of the product, but you will be forbidden from using parameters and the new server based SSIS catalog along with its deployment model. Any new project uses the new model by default and there is a migration path from and to the Legacy model.
Learning curve. Are you of the type that don’t read the instructions and try to figure things out at your own? That did not work for me this time. I started playing around with the parameters and the deployment process, but could not get them to work quite well. It was not until I went trough some of the Wiki articles and videos some of the Microsoft folks put together that things starting to click in my head. You will need to understand how you could use 3 different type of values and how one could override the other. In the server side, you would need to get familiar with the concepts of Catalogs, Folder and Environments and how they play along with parameters. This is not rocket science, and certainly easier than package configurations to explain.

Final Comments and References

Parameters, in my opinion, are in overall better than the old package configuration model that was hard to explain, understand, and manage - especially for new users -, and they also seem a good fit for all the ETL scenarios I come across in a regular basis. However, I think the server part of the story around parameters may need some tweaking as there are some aspects that look little confusing and that could impact negatively the user experience. More on that later.

To learn more about parameters:
SQL Server “Denali” CTP1 - Integration Services (SSIS) Parameters

Denali CTP 1: SSIS Developer Experience & Usability Enhancements

2010-11-16T09:06:00.004-05:00

This is the second post of a series about SSIS Enhancements in Denali CTP 1

Denali CTP 1 has been around for almost a week now, and I would like to take the opportunity to highlight some of the improvements and changes being introduced in usability area. This review is accurate to the best of my knowledge and is the results of few hours playing with the tool, watching some demos and reviewing some of the documentation available – that means use with caution.

New design surface. A slicker look and feel with a friendlier zoom functionality that is always visible. While zooming in and out is quicker now, one thing I am missing is the old functionality of the little pane in the bottom right corner (Does anyone know how it was called?) that allowed scrolling across the entire design surface with a single click– it came very handy to navigate control and data flows that have lots of objects.

New SSIS toolbox. This is a new toolbox pane used only by SSIS and its items have a flashy look and are organized into a number of pre-defined groups similarly to earlier versions. However, something that seems to be missing from the previous version is the ability to create additional custom groups of items or Tabs – Not a big deal if you asked me, but something you may miss if your way of organizing toolbox items do not align with the one provided.

Flexible Authoring (data flow). Do you remember the annoying ‘Restore Invalid Column References’ Editor? In case you don’t, it used to get in your way when trying to edit data flow components after breaking downstream changes were detected. I hated that editor as none of its 3 options did really help you, and it would get in your way again if you picked the wrong option. As far as I can tell, now you can:

Edit transformations and components with invalid references. Now, the invalid references are marked in the linkage between components (yes, the green arrows) while you can still access the downstream transformations. When you double click on invalid linkages you get a nice ‘resolve reference’ editor that allows you to re-map input and output columns. You can also export/import the mappings to/from excel – something handy when you have too many columns – or just delete the invalid references.
Add destination components without having any input connected to it and being able to add the connection settings and target object information only. Why would you want to do that? I am not sure either, but we are talking about being flexible. Right?

Destination and Source Assistants (data flow). Just a convenience feature that allows saving few clicks when adding destinations and sources to your data flows. It also allows you to see the connection types for which you have drivers install if you wish.

Grouping of control and data flow items. Now, you can select multiple items and group them. This is a design time only feature that may come handy to keep packages with lots of tasks and components visually clean and organized. It has no run time effect.

Undo and Redo. This long overdue functionality finally made it into SSIS. Now you can use CTRL+Z or CTRL+Y to undo and redo latest changes – I don’t know how deep it goes, but I can say it goes beyond the last save operation.

Multiline Annotations. Another minor but annoying issue from earlier versions. No more jumping to text editors or CTRL+ENTER in order to get annotations in multiple lines of text. Just hit enter

Overall

While this new version has some notable improvements in this area, the most promising enhancements are in server components side, the overhaul of the parameterization and deployment processes, and the yet to be released data linage and impact analysis. Stay tuned for more details on those areas.

Denali CTP 1: What’s new in SSIS?

2010-11-15T00:12:00.004-05:00

In short: A lot.

The first Community Technology Preview (CTP) of the next SQL Server version was made available to the general public last week during PASS Summit. I am very excited about some of the changes coming from the BI side of the product, especially from the SSIS side. After seeing some of the demos at the PASS Summit, and playing around with the tool, I tend to group the changes in 3 major areas:

Developer Experience – Usability
SSIS Parameters
Deployment – A new model
SSIS Server – A new Server

I will cover changes in these areas in more detail in subsequent posts. But in the mean time you can use the following links if you want to read more - or even play with it:

Denali Resource center (download, videos, sample DB, etc.):
http://msdn.microsoft.com/en-us/sqlserver/denali_resource_center.aspx
Microsoft TechNet Wiki:
http://social.technet.microsoft.com/wiki/contents/articles/tags/Integration+Services/default.aspx
Denali Books on Line
http://msdn.microsoft.com/en-us/library/ms141026(v=SQL.110).aspx

Happy CTP!

SQL Saturday 49 Orlando: 1,040 Miles Later

2010-10-20T13:43:00.001-04:00

That’s right, this was a 1,040 miles driving journey to and from Orlando, and if you want to know if it was worth it, just keep reading.

I had originally planed to carpool with John Welch (blog), but he faced some last minute plan changes that left me with 2 options: either canceling the trip or tackling the long drive by myself. Fortunately, the power of ‘la familia’ kicked in, and Isabel - my wife - and my 3 kids: Carlos, Isabella and Diego decided to come along, so I didn’t have to do the long drive alone. I love you guys - You all rock!

We headed out on Thursday night and spent the night in Savannah, GA, which is roughly mid point between Charlotte and Orlando. The next morning, we resumed the driving, and decided to make a few hours stop in St. Augustine to do some emergency shopping – we had forgotten/lost Diego’s shoes :) . Finally, we arrived to our hotel just in time for the speakers dinner. At the restaurant, I had the opportunity to say hello to Eric Wisdahl (blog), Andy Warren (blog), Louis Davison (blog), Aaron Nelson (blog), Ed Wilson and his wife Teresa, Tom LaRock (blog), Jose Chinchilla (blog) and Patrick Leblanc (blog). I also had the pleasure to meet Jack Corbett (blog) and Mike Walsh (blog) and Pam Shaw.
The next morning, I was at the event site by 8:30, and just in time to attend Eric’s presentation on SSIS Data flows, which was great and well attended. Then, I stayed in the same room for Jose’s introductory session about SSIS which was packed with ~50 attendees. The third session was my talk “ETL architecture and SSIS”. Although the room wasn’t to its capacity, I had a great and very engaged audience. It looks like tying up the evaluations forms to the prize drawing was a good way to get feedback from them. I ended up with 23 evaluations averaging a +4 out of 5. After my session, I had the opportunity to meet the SQL Chapter lead of Pensacola, Jamie Beck, Ivan Gonzales, and Jorge Segarra (blog) and Noel McKinney (blog) among other great people.

3 Things I really liked:

The Speaker’s gift. A really nice hand-made coffee mug. Time to say bye to my old ‘geek’ coffee mug.
Picnic style lunch. Weather was great to be outside.
The opportunity to hang out with people that enjoy this type of events as much as I do.

All in one, the event was well organized from end to end. If I had to pick something to be improved, I would say to make sure all rooms are well suited fro this type of presentations. I am not sure if the SSIS track was the only one with that problem, but in this room the screen was not at the centered of the room centered and sat very low, making impossible for attendees to read the content in the bottom part of the slides. An item to add to every organizer to-do list: Validate every room is well equipped and suited for this kind of presentations – that would be fair to all speakers and attendees.
BTW, thanks to Jack Corbett for the ride to the hotel and to Confio for sponsoring such a great dinner. Also thanks to Ed and Teresa for the PowerShell t-shirt for my eldest son – he wore it right away (see picture). Finally a big thanks to the organizers and all volunteers for making all this possible and giving me the opportunity to be there.

The drive back home was little rough for the little ones as we decided to do it in on shot, and it does not look like we will be jumping into any other long drive for such few days remaining on the year. …Wait, it looks like DC SQL Saturdays was just announced.

You can download the slide deck I used in my session from here:

Presenting at 24 Hours of PASS LATAM - En Español

2010-10-19T10:00:00.002-04:00

I am thrilled of being selected as one of the 24 presenters for the first edition of the Spanish version of 24 hours of PASS. My session “Database development using Visual Studio 2010” is this coming Wednesday at 5:00PM EST. I am very excited about this event as this is a double first time for me. This is my first webcast, and also my first time presenting in my native language. You can read more about the event and register at the PASS LATAM website:http://www.sqlpass-latam.org/24horas.aspx
Now, to read the remaining of the post in English, please press 2
Apreciados amigos, los organizadores de las 24 horas de PASS LATAM me han concedido el honor de dirigir una de las 24 charlas técnicas en esta primera edición del evento. Este evento consiste en de 24 charlas impartidas en 2 días (19 y 20 de Octubre). Mi Charla esta titulada” Desarrollo de base de datos con Visual Studio 2010” y es una introducción al uso de esta poderosa herramienta. Si quieres aprender mas acerca de este tema u otros interesantes tópicos relacionados con SQL Server, te invito a que te registres en la página de PASS LATAM :http://www.sqlpass-latam.org/24horas.aspx

SQL Saturday#48 Recap and Slides

2010-10-14T01:08:00.000-04:00

With SQL Saturday #48 in the rear mirror now, I don’t want to miss the opportunity to provide some feedback and post the slide decks I used during my sessions. For this event, I ended up carpooling with a co-worker and blogger Dave Turpin (blog). We got at the event location just on time but have little trouble finding the right building once we arrived at Midlands Technical College campus. I was ready to register and grab my first donut when we realized that we were at the registration table of a nuclear medicine event.Clearly, not the right place for a couple of SQL Server geeks. After few minutes walking in circles, we finally found the SQL Saturday signs. More signs the next time around would certainly help. Registration was smooth and the bag and speaker shirt was promptly provided. I missed the first 2 sessions of the day since I had to make few changes to my slides. I regret not being able attend Matthew Campbell's session (Tweeter | Blog) on spatial data. Matthew is a good friend and ex-coworker that has been doing really cool stuff combining spatial data and data visualization. I heard he had an awesome session. This event was also a good opportunity to catch up with some of my friends at Mariner: Wayne Snyder, Melissa Coates and Bill Pearson. I also have a eye-opening conversation with Andy Warren (Blog) about the amount of work and challenges of putting together a brand new SQL event – SQL Rally – and the upcoming SQL Saturday in Orlando.This time around, I decided to shorter my time away from family and decided to skip the speakers dinner and and the post-event party.This was a well organized event and I am looking forward to attend again the next time around.

My sessions had an attendance of around 18 people. I had some technical difficulties with the projector in my first sessions, but the problem was quickly fixed thanks to the intervention of some of the attendees and volunteers. The second talk one went smoother and I got good feedback including some spelling corrections made by Dave. If you are still interested on those slides, you can download them from the links below.

Managing Database Schemas With Visual Studio 2010

Planning your ETL Architecture with SSIS

Speaking at SQL Saturday #48 next Saturday – Columbia, SC

2010-09-27T15:57:00.000-04:00

I have been given the honor to present at SQL Saturday #48 in Columbia, SC. next Saturday, and would like to extend an invitation for you to join me. I will be delivering 2 sessions: Planning your ETL Architecture with SSIS and Managing Database Schemas with Visual Studio 2010 projects (data dude).

As I said few weeks ago, this type of events are becoming extremely popular and have high demand, and if you asked why one should consider attending, I would give you few reasons:

High quality content: There is a good mix of sessions ranging from beginner to advanced for DBAs, BI developers and DB developers alike. See the event schedule and judge it by yourself (schedule page).
Professional development and networking: You can attend sessions like Andy’s Building a Professional Development Plan or meet as many of the attendees and speakers as you can. Remember, your career is not just about technology!
Landing your next job. Although, this is not officially part of this event, I see very often that attendees and speakers use this type of events to spread the word about openings at their companies. I will actually be sharing some of the openings my current company, Quaero a CSG solution, has and will encourage you to approach me if you are interested on learning more about them.
Learn more about Project Phoneix and how it could help you getting free software and training while giving back to the community.
You will be supporting your local SQL Sever Community.
It's free!

Make sure you register for this event (registration still open) before is sold out.

SQL Saturday#46 – Slide deck

2010-09-18T10:20:00.001-04:00

I had really good time during my presentation at SQL Saturday#46 today.My session had 18 attendees and I got great questions coming from them. Thank you to all of you attended my session, and if you are interested, you can find the slide deck:

SQL Saturday #46 Raleigh, NC is just 3 days away!

2010-09-15T12:38:00.000-04:00

And I am delighted to be part of the speakers roaster of this great event.
Why is it so great? I am glad you ask. SQL Saturdays are becoming very popular event among SQL Server professionals as they are whole days worth SQL Server and professional development training delivered by exceptional speakers – and occasionally me :) – and also represent a great opportunity to connect with people sharing similar interests and expand your professional network. And the best of all is that it won’t cost you anything to attend. That’s right, it is free of charge for attendees. You can check the line-up of sessions and speakers (link) and register (link) in the event site.

I will be presenting: “Dimensional Modeling: Why should you care?”, which is a intro level session about Kimball’s Data Warehouse Lifecycle and Dimensional Modeling. If you happen to attend this event, I will be glad to have you in my session.

SQL Saturday #51 write-up and Slide deck

2010-09-14T21:30:00.002-04:00

This post in long overdue.
I had the honor to present at SQL Saturday #51 in Nashville few weeks ago, and would like to take the opportunity to give you my review of the event and post the slide deck I used during my session. This was my third SQL Saturday as a presenter, and once again, I had great time.Let me start with a big thank you to all the people that made this great event possible: The Nashville SQL Server Group, its leadership and its army of volunteers.
For me, the fun started the night before at the speakers cookout/dinner at Kevin Kline’s house. Kevin was very kind on hosting the dinner for speakers and volunteers at his home. Food and drinks were great and plenty, and it was a great opportunity to hang out with the PASS board of directors and other friends. The organizers set the bar way too high on the speakers dinner category.
The next morning I was at the event location by 7:15 AM as my presentation was scheduled for 8:30 am. That meant I had the increased responsibility of making sure attendees had a good start – and that that nobody would fall asleep! - The room was packed with 50+ attendees and the audience was very engaged through the talk. I was Half-way through my presentation when I noticed that I had some well-known SQL server experts and speakers in the audience: Wayne Snyder, Rushabh Metha, Douglas McDowell and William E. Pearson among others. The presentation was well received and I got very positive feedback from the attendees. I received 36 evaluation forms with same number of 4s and 5s in 34 of them and 3s in the other 2. 5=‘great’ and 1=‘very poor’.
Here is a transcript of the 4 comments I got in the evaluations form:

“Great comfort level and pace, good explanation/justification”

“Examples were very helpful”

“Need more time”

“Great Job! Great presentation Skills, Thanks Rafael”

This kind of feedback is certainly encouraging and I am looking forward to speaking at upcoming events.
In case you are interested here is the slide deck:

Presenting at SQL Saturday#51 Nashville, TN

2010-08-19T23:59:00.002-04:00

I will be heading out tomorrow morning to Music City as I will be presenting at the SQL Saturday #51. This time I will speaking about SSIS and ETL Architecture.
I am always looking forward to attend this type of community events as they are full of opportunities to learn and meet people that share similar interests.

If you don’t know what the SQL Saturdays are about, then you are missing a lot. If you want to participate in an upcoming one, then check their web site for more details: http://www.sqlsaturday.com/

I hope to see you there!

Help PASS Choosing the SQLRally Logo

2010-08-05T10:09:00.000-04:00

PASS is in the last stage of selecting a logo for SQLRally. Right now, they are down to the 3 finalists and will be selecting the one the highest number of votes. Here is my favorite one.

Vote for the new PASS SQLRally Logo! here

And if you are wondering what the SQLRally is, then you should know it is a regional SQL Server conference to be held every year. I think about it as the younger sibling of the annual PASS Summit.
I personally love the way they are allowing the community to drive the process. Kudos to the team.

Presenting to SQL Server Innovators Guild – Greenville, SC

2010-08-02T16:57:00.002-04:00

I am honored to present Visual Studio 2010 Database Projects: Top 7 features to make database development a breeze! to the SQL Server Innovators Guild tomorrow Tuesday, August 3rd 2010 at 6:00 PM.
In this session, I do my best attempt to convince database developers- that’s us – that database schema artifacts should be given the same level of importance and treatment of any other code artifact, and show how you could use Visual Studio projects to accomplish it. Here is the session’s abstract:
Visual Studio 2010 Database Projects: Top 7 features to make database development a breeze!
Most of us agree that the database is the most important component in a typical application, yet formal processes for versioning, testing and deployment of database objects as are often rudimentary or inexistent.
In this session Rafael explains how you can use Visual Studio database projects (aka data dude) to have better control over your database schema and reduce the risk and complexity related to the database development cycle. He will talk about the pros and cons he has experienced when using the tool in his projects and will highlight improvements made since the last version. This is a demo packed session where you will learn about schema versioning, testing, refactoring, deployment and more.
Most the content of this session remains relevant to the previous version of the tool- VSDB 2008 GDR2 - and, no, you don't need prior knowledge of Visual Studio to get the most out of this presentation!
This will be my second time speaking to this group - SQL Saturday #9 was my first one – and I am looking forward to having fun again. I hope to see you there!

And the Winner is…

2010-07-22T23:28:00.002-04:00

Last week I decided to run a contest to give away a Visual Studio 2010 Ultimate with MSDN subscription (retail value of $11,889), and today I am announcing the lucky winner:
<<<<<< Drum rolls >>>>>>
Nick Nillon (@nichelob )
After a long deliberation and selection process I decided to pick Nick from the large pool of ,oh well, only one contestant.

I am still trying to figure out how to give away the remaining 2 subscriptions, but I will announce it here and via twitter for sure.
Congratulations to Nick!

Win a Visual Studio 2010 Ultimate with MSDN Subscription - Yet Another Contest

2010-07-13T12:12:00.001-04:00

Few days ago, Microsoft sent me 3 Visual Studio 2010 Ultimate with MSDN Subscriptions –a value of $11,889 not for resale – and kindly asked me to give them away “to someone who has not yet experienced some of the fantastic benefits of Visual Studio 2010 Ultimate with MSDN”. Does that sound like you? Then, keep reading to learn how you can get the chance to win one of them.

What does it take to enter?

It is as easy as 1, 2:

1. Create and share a tip, trick, mini lesson, bug, favorite feature(s) or any other sort of helpful information that is relevant to SQL Server 2008 (or R2).

Be creative! You can create a short blog post, screen cast, video, etc, or just include it as comment in this post. In all cases, you must leave a comment in this post that contains or links your stuff. Plagiarizing someone else’s content is not a valid option – and it is wrong!

2. Send a tweet with the hash tag #FreeMSDN with a link to this post.

If you don't have a Twitter account, you can open one here: http://twitter.com/

What is Visual Studio 2010 Ultimate with MSDN anyway?

You can read the details here: Visual Studio 2010 Ultimate with MSDN Subscriptions

Deadline

I will announce the winner on July 22nd in a blog/Twitter post.

I will give away the other 2 subscriptions in the following weeks, and blog/tweet the details. Stay tuned!