SSIS (SQL Server Integration Services) – Using the Lookup Transformation and cache and how to handle NULL Values
I had a situation where I was using the Lookup transformation and then loading this into the SSIS Cache, but I wanted all rows to be inserted using the SSIS Cache, even if there was a NULL Value. Below explains how I overcame this. Example: · In our Source data we have Products, but they might not be in…
SSIS – Running single SSIS packages in Parallel to get faster performance for multiple data loads
What we are going to show you below, is that by using ONE SSIS package, we can re-use this one SSIS package multiple times to get data from multiple sources, possibly from different databases hosted in different locations around the world. So in effect we are loading our data from one SSIS package in parallel. NOTE: By completing the…
SSIS – Getting Partition Names for the past 12 months
I had a situation where I was using a conditional split, so that when data was coming in from my source tables it would dynamically go into the correct partitioned table. Below is the script that I used, as well as for reference the conditional split — ================================================================================================================== — What we are doing here is to create a…
Setting up a Proxy Account to run SQL Server Integration Services (SSIS) 2012 packages
Below are the steps that we had to do in order for us to create and setup a Proxy Account to run our SSIS Project. As well as allowing the domain user to have access to the job so that they could create and edit the job which ran the SSIS Project. NOTE: When setting up all the SQL Settings…
SCOM (Systems Center Operations Manager) Cube and Data warehouse
What I have done is I have found that it is very handy having all our SCOM data in a data warehouse and then having it in a SSAS (SQL Server Analysis Services). Within the zip file which can be located here below it has all the details for the following: · SCOM Data Warehouse and OLAP Documentation.docx o This…
Data warehousing – Bringing down data twice but avoiding deleting on the data warehouse Fact Table
This is better explained with our example below. Example: · We had a situation where we were getting data down, but due to the nature of the data we were getting is we were missing data. · Also another note was the fact that we did not store the granular data, but aggregated the data, so due to this…
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…
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 2012 – Inserting data into a SQL Server Table from an MDX Query
The reason for this blog post, is I recently had a query if it was possible to get data returned from an MDX Query and then insert the data into a SQL Server Table. And the reason for putting it into SSIS was that we could schedule the job to run on a schedule. I have inserted pictures from my…