SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Archives: September 2011

Creating your first Data Mining Structure and Model

Data mining is a great way to help your company make decisions and predict future values. The Data Mining Algorithms built into SQL Server Analysis Services gives you this power.
The Adventure works data base comes with views that are already set up to perform data mining. Let’s take a… Read more

0 comments, 7,680 reads

Posted in Mike Davis SQL on 30 September 2011

SSIS For Each Column in a Data flow

Previously I wrote a blog on how to do a for each loop to look through each col in an SSIS data flow here. Well things have changed since I wrote that blog, in fact I believe that old  code only works in SSIS 2005. So I thought I… Read more

0 comments, 1,177 reads

Posted in Mike Davis SQL on 29 September 2011

Getting Previous Row in SSIS Data Flow

There is no native function built in to SSIS to get the previous row in the data flow. But with a little work you can do this and make it perform much better than a SQL cursor and you don’t have to use the dreadfully slow OLEDB Command transform.
In… Read more

0 comments, 1,372 reads

Posted in Mike Davis SQL on 29 September 2011

SSIS Merge with Duplicate Rows

The Merge component in SSIS will take two sorted sources and union them while maintaining the original sort order. The question arises, what about duplicate records. These duplicates do not get eliminated.

Here are two tables with the ID 5 and the name Brian repeated on each.

Here is… Read more

0 comments, 1,131 reads

Posted in Mike Davis SQL on 28 September 2011

SSIS Child Errors in Parent Package

If you have a parent package that is looping through a set of files and calling child packages and want the parent package to continue even if the child package fails, there is a little work involved in making this happen.
In this example I have a parent package that… Read more

0 comments, 2,102 reads

Posted in Mike Davis SQL on 27 September 2011

SSIS Lookup Cache Connection Manager with Excel

The lookup transform in SSIS 2008 gives you the ability to join data and eliminate non matching rows. One of the limitations of the lookup is the fact it requires an OLEDB connection. However, with the cache connection manager you can use just about any other data source as your… Read more

0 comments, 1,497 reads

Posted in Mike Davis SQL on 26 September 2011

Partial Cache Lookup with a Date Range

When setting up a lookup in SSIS it is usually just a basic comparison between two key fields like an ID field. What if you want to compare and id and also find the specific date range. Let’s say the id is in the lookup reference table multiple times and… Read more

0 comments, 712 reads

Posted in Mike Davis SQL on 23 September 2011

Replacing a SQL Cursor with SSIS

On a forum post recently the questions was asked how to replace a cursor with an SSIS package. This can be done several ways depending on the situation. In this situation there is a number on each row that determines the number of times a row needs to be written… Read more

0 comments, 1,281 reads

Posted in Mike Davis SQL on 22 September 2011

Handling Flat File Headers and Errors in SSIS

Using SSIS to load a flat file into a database is a common use of the tool. This works great in SSIS and is very fast due to the dataflow doing batch updates. This is easy to set up when the flat file has no headers and footers. I am… Read more

0 comments, 2,585 reads

Posted in Mike Davis SQL on 12 September 2011

Using the SSIS Merge Join

The Merge Join Transform in SSIS is a great way to load a Data warehouse quickly and an easy way to join two data sources together. There are a few requirements to join these two data sources. The data sources must be sorted and there must be a key that… Read more

1 comments, 8,696 reads

Posted in Mike Davis SQL on 6 September 2011

Pragmatic Works Foundation Class Oct 2011

I am super excited to announce that there is a strong demand for the Foundation Training to be held more frequently.  Our last one was held in August and several people were hired for Jr. DBA and Report Writing opportunities.  We also have more companies interested in looking at the…

Read more

0 comments, 518 reads

Posted in Mike Davis SQL on 2 September 2011

Variables and Expressions with Connections in SSIS

Variables and expressions help make any SSIS package dynamic and flexible. It is always a best practice to use variables in a package, especially when the information is used more than once in the package. In any package you will most likely have Connections, Tasks, Containers, Data Flows and Event… Read more

0 comments, 1,295 reads

Posted in Mike Davis SQL on 2 September 2011