Change data capture was introduced in SQL Server 2008 and has been pretty popular ever since. The basic concept behind it is that all changes (inserts, updates, deletes) are captured and the details made available in a consumable manner. This means ultimately that all changes will be written to a table you can query to see everything that has been going on. In SSIS 2008 it was a little cumbersome...
July 10, 2012
SSIS 2012 Change Data Capture (CDC) Control Task
10July
June 26, 2012
Preach The Gospel Always, If Necessary Use Words
26June
I’ll be honest, I contemplated a lot about whether to write this blog or not for fear that it may come off the wrong way or just not convey the point I want, but here we are…in a hotel room typing away. In no particular order, just my thoughts as they come out. I’m not syndicating this particular blog just to get that out in the open, you will only find it here. I’m not...
June 25, 2012
Speaking at PASS Summit 2012
25June
It’s about that time again, PASS Summit 2012 is right around the corner in Seattle, WA. It will be held at the convention center from November 6-9. This year will feature more than 170 sessions across 6 tracks from 90 speakers in addition to 2 days of pre-cons; and that doesn’t even include the sessions from Microsoft! I have spoken at a lot of SQL Saturdays, code camps and done a...
April 5, 2012
SQL Server 2012 PowerPivot for Excel Prerequisites Error
05April
PowerPivot was a great new tool when it released several years ago. Now the newest version is out along side SQL Server 2012.You can still install the 2008 R2 version of PowerPivot or you can try out the new 2012 RTM version. There are some nifty improvements that Devin Knight has chronicled (Blog | Twitter). There are several requirements that are not part of the 2008 R2 requirements. The...
April 2, 2012
Unsupported Features in Excel Services SharePoint 2010
02April
Between SharePoint 2007 and SharePoint 2010 there were many new features that were added or enhanced. There still remains a list of items that are either unsupported actions, such as workbook authoring, or unsupported features, such as cell comments. In Excel Services 2007 many unsupported features would cause the workbook to not load at all and have since had the behavior changed in 2010. The...
April 2, 2012
Mirroring Support for SharePoint 2010 Databases
Like all systems built on top of SQL Server SharePoint 2010 needs to have a plan for availability. There are many methods for configuring high availability, one of which is database mirroring. Other options, such as clustering will be discussed separately. Database mirroring is a SQL Server technology used for providing database redundancy. It is configured on a database level, not the server...
March 23, 2012
Trim Functions in SSIS
23March
In T-SQL there are many functions there to help do a variety of different things. SSIS is no different. In fact, a good number of the commonly used functions from T-SQL are in SSIS as well. Here we will explore the different Trim functions found in SSIS. We will be using the test phrase ” Test ” to illustrate the three functions: LTRIM, RTRIM and TRIM. LTRIM (Left Trim):...
March 23, 2012
Excel Source The Output Column Failed Because Truncation Occurred
In an SSIS package you have the option to choose from a wide range of source from Flat Files to writing queries using OLEDB sources such as SQL Server. Often times, such as writing queries against SQL Server, the metadata will come from the table and you won’t need to worry about truncation in the source. Flat files are scanned and metadata is chosen based on the file so those are usually...
March 12, 2012
MDX Functions: BottomCount
12March
The MultiDimensional eXpressions, or MDX, language is used to query the data in a multidimensional in an OLAP database. In the case of examples here, SQL Server Analysis Services. This example uses the Contoso demo cube available for download here: Function Name: BottomCount Use Case: Brings back the lowest X number of values from a set MSDN Link: Notes: Always breaks a hierarchy...
March 12, 2012
MDX Functions: TopCount
The MultiDimensional eXpressions, or MDX, language is used to query the data in a multidimensional in an OLAP database. In the case of examples here, SQL Server Analysis Services. This example uses the Contoso demo cube available for download here: Function Name: TopCount Use Case: Brings back the highest X number of values from a set MSDN Link: Notes: Always breaks a hierarchy...
