SQL Tips! Set your default database in SSMS (Installment 1)

In my time as a Business Intelligence consultant and trainer I have picked up some pretty cool tricks and thought I would start blogging on these tips two or three times a week as I catch myself using them.

In this blog I am going to show you how you can set your default database when you connect to a server. I find this to be an extremely helpful little tip. Please see below.


    1. Open SSMS and from the object explorer click “Connect”.
    2. From the Connect to Server prompt: Enter your server and Authentication information.
    3. Click “Options”, located at the bottom right.
    4. Next click the “Connection Properties” tab found at the top.
    5. Locate “Connect to Database” and then click the drop down menu.
    6. From the dropdown menu select <Browse> and then select the database that you want to be your default. Click Connect..
    7. Voila! Now every time you connect to that server it will default to that database instead of master!


As always, thanks for looking!

SSIS Performance Tuning Webinar–Q & A

Thank you everyone for attending my webinar on SSIS Performance Tuning, if you missed that presentation you can watch it here: http://pragmaticworks.com/LearningCenter/FreeTrainingWebinars/PastWebinars.aspx?ResourceId=683

Below are some of the questions that I received from the Webinar:

Q: Can you give some performance tips in using script tasks too?

A: Yea, don’t use them! Script tasks can be really bad for performance and many times I have been able to replace someone else’s code with native SSIS components. For example, I had situation where the client was using some very complicated .net code to parse out columns based on logic. The package was taking over an hour to process 1 million records. I replaced this with some conditional splits and derived columns and it now runs in 3 minutes.

Q:  I am assuming that the file formats must be the same for all files when using the MultiFlatFile transform, correct?

A: You are absolutely correct. The metadata in each file must match.

Q: PW delivers a ‘TF Advanced Lookup Cache Transform” component.  What are the benefits of using this component over the Cache Transform covered earlier?  It seems that the TF components cannot make use of the same result set when the data set is role based.

A: For basic caching of data I would use the native SSIS cache transform. The major advantage you get from the Task Factory component is you can do very difficult range lookups with ease and  they will perform at a high level. Please see my blog post on this.

Q: What version of SQL Server is being used?

A: I was using SQL 2012, but everything in the presentation is applicable to 2005 and 2008.

Q: With the multi flatfile connection manager can you specify specific types?

A:  Yes, the wild card character can be anywhere in the connection string property. So you could do test*.txt to only pull in text files where the file name begins with test.

Q: Why would you ever not use table or view (fast load) option in the OLEDB Destination?

A: Well I personally would always use that option. However, with the fast load option all records are committed for the entire batch. So if there is a record that is bad and causes the failure you will not know which record caused the error. With table or view option each record is committed individually so you know exactly which record caused the failure.

Q: is the volume on?

A: Sorry can you ask that again, I couldn’t hear you….

Thanks again!

How to solve a a tricky SSIS Problem with the TF Surrogate Key transform!

Recently I was able to solve a really tricky problem in SSIS using the TF Surrogate key transform. Let’s discuss the business problem:

The client was using some complicated .net code to loop through each line in a file and look for a very specific string of code. Once the string was found they then wanted to return all the values found six rows below that row. This process continues through the file. A picture is worth a thousand words:


So now that we understand the business problem we can solve this with SSIS. The solution is actually quite simple. Essentially we need to get the string we are searching for (5909045743) and the row with the values on the same row so we can let SSIS work its’ magic. This is where the Surrogate Key transform comes in.

To solve this problem I used not one but two Flat File source components in my data flow and I brought the same file in twice. I then used the surrogate key transform to assign row numbers to the rows coming from each flat file source component.

  • For the first flat file source I started the row number at 6 and then incremented by one.
  • For the second flat file source I started the row number at 1 and incremented by one.
  • Setting up the surrogate key transform is extremely easy, 3 easy steps, see below:


Now I use the merge join component in SSIS and join the two data sets on the newly created SK_Columns. This will put our search string on the same row with the values we were looking for.

Below is a picture of the data flow thus far:


And here is a screenshot of the new result set after the Merge Join transform, notice how the two rows are now on the same row. From here we can do some basic SSIS operations to split out all the records we don’t need and then we can use a derived column transform to parse out the values in “Column 2”.


Thanks for looking!

SSIS Tips and Tricks, the Select * counter punch.

Working with SSIS and the BI Stack from Microsoft I have discovered a few tips and tricks that come in quite handy. To my surprise a lot of these are not very well know in the BI World. In this series I am going to try and post one or two SSIS Tips and Tricks on a weekly basis.

As you are well aware the SELECT * syntax in SQL Server should not be used inside of SSIS. This is because the metadata in SSIS is very rigid and if you add or remove columns to the table in your select statement it can affect other transforms in the pipeline that have been defined with very specific metadata properties.

So if you are like many people who use SSIS it’s likely you alt + tab over to SQL Server Management Studios and then view the table and write your select syntax there with all the applicable columns for your query. Then you copy that syntax and alt + tab back over to BIDS / SSDTs and paste.

It’s unfortunate that there isn’t some kind of tool inside SSIS that will assist us with our incredibly difficult predicament. Just kidding, of course there is, if there wasn’t I wouldn’t be blogging about it! Let’s demonstrate.

First – Open up your OLE DB Source component in your data flow and write your Select * from table statement. For this example I used SELECT * FROM SALES.CUSTOMER in the AdventureWorks2012 database.

Next click “Build Query…” found on the right side of the OLE DB Source Editor.



Click ok and you are done! Thanks for looking and check back for more time saving tips and tricks.

Outlook Email Tips on Searching Emails

I, like everyone, get an incredible amount of email on a daily basis. I recently learned some awesome tricks for searching my outlook that I wanted to share!

In the past when I wanted to search for something I would just type that item into the search box. For example ‘John Doe’. Unfortunately this returned a lot emails that John Doe not only might have sent me but also was cc’d or bcc’d in. It also appeared that if his name was in the subject that email would be returned. Well when you are working on a project in which multiple people are cc’d on every email this results in an overwhelming number of results.

So now to the good part!! Want to search for emails that you received from John Doe? Try this

From:John Doe (Note you don’t have to fully fill out his name.)

From:Joh – This will also return emails from John Doe, but of course anyone else with Joh as well.

To:Jane Doe

Subject:Analytics (This will search the full subject string for the word ‘Analytics’

These are just a couple of the options available. For a full list please check out the website below:

Learn to narrow your search!