BI-NSIGHT – Power BI (Q&A for SSAS Tabular, Weekly Service Update, Lithium Content Pack, Mobile App for Windows 10 Mobile) – Azure (SQL Server Virtual Machines) – Power BI and Leveraging the Cloud
Even though I think a lot of people took time off over the Christmas period it seems that the Power BI team at Microsoft was full steam ahead. Which is a great thing to come back to work and have all these updates and new things to look into. Power BI – Q&A for SSAS Tabular The reason for me…
SSIS – Using SSIS Variable within a SQL Script with Temp Tables – Error at Data Flow Task : No Column information was returned by the SQL Command
Below details what I was trying to do within SSIS and how I got it to work. Overview I was using a SQL Script which contained some temp tables which was used in the result set. When I then put this into an OLE DB Source I would get the error shown below: I then tried to see…
SSIS – Using Date and Time Cache Lookups for faster inserts into Fact Tables
What happened is that I found that when doing inserts on larger data sets, it would take a long time to complete the date and time joins. If I excluded this the data would be returned a lot quicker. So my solution to the problem was to use the Cache Transform and then the Loopkup Transform in the SSIS…
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…