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):...
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...
MDX Functions: BottomCount
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...
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...
Date Dimension Script with Fiscal Year
One dimension you can be positive will always make it’s way into your data warehouse is the Date dimension. Over the past couple of years I have taken a look at a lot of Date dimension scripts and found quite a few good ones. Well recently I needed to add fiscal date to the dimension and the script I had didn’t let me do that properly. Rarely does the fiscal year start and end on...
Introduction to SharePoint BI Webinar Tomorrow
Tomorrow at 11AM Eastern time I will be presenting a FREE webinar for Pragmatic Works (website) on Introduction to SharePoint BI. Get the details below along with the link to view the FREE event. Title: Introduction to SharePoint BI When: Tuesday March 6th, 2012 at 11AM Eastern Description: In this session Brad will provide you an overview of SharePoint 2010 and how it can be integrated with...
The 'Microsoft.Ace.OLEDB.12.0' Provider Is Not Registered on the Local Machine
If you have used SSIS to move data from something like Access or more commonly Excel, it is a good possibility you have seen this error at some point: Error: The ‘Microsoft.Ace.OLEDB.12.0‘ provider is not registered on the local machine. The reason this error shows up is because the proper components are not present that are required to move data from Office files (I’m looking...
SSIS Variable and Expressions Followup
Over the last week I have done several sessions either for the Pragmatic Works Free Training online or at SQL Saturday. It has been a lot of fun, so thank you to everyone who has come out and hopefully learned something from one of the sessions I have done. So, I mentioned a couple things I was going to do, one of which is post a quick recap and sample package from the Expressions and Variables...
SSIS Variables and Expressions Webinar Recording
Thank you to everyone who attended my free webinar hosted by Pragmatic Works yesterday (September 20, 2011) on SSIS Variables and Expressions. The webinar was recorded, so if you want to reference it again or if you were unable to attend and wanted to see it you can view the recording from this link here the clicking view on the right side on the Pragmatic Works website (Here is the direct link...
SSIS Variables and Expressions Webinar
Looks like it is that time again… for me to present a FREE webinar for good ol’ Pragmatic Works today at 11AM eastern standard time. The topic today will be SSIS variables and expressions. I previously did a webinar on a similar topic that also included configurations. If you were in that webinar then you are well aware that we really only got to go into detail on configurations and...