SQL Server 2014 Observations
Today’s post is not going to be about explaining something but more along the lines of my experience on SQL Server 2014. When I heard about all the new features in SQL Server 2014, the In Memory Tables, new cardinality estimator, new changes to how TempDB works, SSIS and Delayed Durability, I was excited to see how I could use…
SQL Server Integration Services (SSIS) – Using Send Mail Task for data validation checks
What I wanted to do was to be notified in my data if there were not enough rows of data, or if the financials were too low, indicating that there was a potential issue with the data coming in. And if it was too low to fire off an email. If it was correct then to not send an email….
SQL Server Integration Services (SSIS) – Re-run a specific Control Flow Item if it has an error
We had an issue where we only wanted to re-run a particular control flow item when it failed or had an error. We did not want to do this for our Entire SSIS Package but just for a specific Control Flow Item. Example: Inserting Data into a Staging Table and if it fails to then re-try the insert 3…
SSIS – Package duration time
Just a quick blog post today. I wanted to see how long all my packages were taking to run in SSIS on SQL Server 2012, due to there being some storage issues, which related to the jobs taking longer to run. So below is the query that I used to pass my parent package, so that I could then get…
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…
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…
Excel – Using a Custom MDX Query in Excel Table
I had a requirement where I wanted to use my own Custom MDX Query within an Excel Table. And from the output of the table I then wanted to Pivot this data. I do know that you can do this in Power Pivot, but the requirement was to not use Power Pivot. Below are the steps and it is a…
SSAS – Getting a negative value for measure that has been used in a SUM
When you are browsing data from a cube, you see the grand total or a value in a column it has a negative value. This is while you know that it does not have any negative values so this must be wrong. The reason for the value being negative is due to the fact that data type in your cube…
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…