SSIS–Conditional Split based on Date
Below is a quick blog where I am using a conditional split based on your Date Column, so that you can then output your data to different tables or destination data flow tasks. I also found that there are bits and pieces of information as to how to use the Expressions within the Conditional Split. So this might help…
SSRS–Creating reports using Custom MDX or DAX to query SQL Server Analysis Services (SSAS) Cube both OLAP and Tabular (Not using the Wizard)
By default in SSRS you can query an SSAS Cube, but this is by using the drag and drop functionality. The issue with this is that it has quite a few limitations. One of the biggest limitations that I found is that you cannot use your own Custom MDX to create your data set. NOTE: You can use the same…
SSIS – Stopping an Error from Propagating to its Parent (Enabling an SSIS Package to continue when an error happens)
Due to our environment and packages we create, we have to enable the packages to continue even if it fails for some reason (Cannot connect to a Server for example) so that the rest of the data will be captured. In the past we configured the MaximumError count to be 100, but this is not ideal, because if another component…
SSIS – Fastest data flow task item for transferring data over the network
My goal was to find out which combination of data flow Source and Destinations would enable me to transfer data over a network as quickly and as efficiently as possible. · Below is what the outcome of the tests are · And then finally I have only detailed, based on the fastest data flow items how to configure the data…
SSIS – Automating SQL Server Backups, copying files and checking that they were copied
I had an issue where I lost all my backups. So what I have now created is an automated SQL Server backup process, which will back up all the databases, verify the backups, copy them to the required location. And then finally email the backup files copied to ensure that they are all there. …
SSIS & TSQL – Geo- location from IP Address to Country
Below are the details firstly from getting your Geo – location information from a table which has your IP Address to country information. And then getting your IP Address information from your source systems and quickly getting this to a Country so that you can see where the people are coming from. Geo – Location information There are a…
SQL Server 2012 Integration Services Error Creating Catalog Database
This is the error that I got when trying to create the SSIS Catalog Database in SQL Server 2012. The certificate, asymmetric key, or private key data is invalid. Changed database context to ‘SSISDB’. (Microsoft SQL Server, Error: 15297) Just a quick background to how I got to the error when creating the SSIS Catalog database in SQL Server 2012….
DAX Function error – An argument of function ‘LEFT’ has the wrong data type or has an invalid value
Today I was working in SSAS Tabular Mode for SQL Server 2012. What my goal was, was to remove version numbers from Products. This was so that instead of having the following below in a report: Microsoft .Net Framework 1.x Microsoft .Net Framework 2.x Microsoft .Net Framework 3.x Microsoft .Net Framework 4.x I would have one name instead: Microsoft .Net…
Date Filtering in Excel from your Analysis Services Dimension
I recently had to set up another Date Dimension and went through the process of setting up all the properties in the date dimension so that when it is used in Excel, the end users would then be able to use the Date Filters built into Excel. The Excel Date Filters are very handy when creating Excel documents. As an…
OLE DB error: OLE DB or ODBC error: Invalid column name ‘xxx’.; 42S2
Yesterday I thought it would be a quick addition to add a new column to my Fact table. Then add the dimension to Analysis Services. And finally add the dimension to the cube. but I ran into the error below and after struggling for quite a while I found the issue. NOTE: This is possibly one of many solutions. I…