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 – Dropping Partitions in SQL Server Analysis Services (SSAS) with XMLA and Analysis Services DDL
Below are the steps that we have integrated into SSAS using SSIS so that we can then drop our old SSAS Partitions using SSIS and XMLA. Example: · We are going to drop our oldest partition from Measure Group called Fact InternetSales 1, which is in our Adventure Works cube. · The actual Cube partition name is called: o …
SSAS (SQL Server Analysis Services) – Getting all partition information from SSAS Database
I had a requirement where I wanted to find out and keep a constant record of exactly how my partitions were set up and created within SQL Server Analysis Services (SSAS). So below is a script that I found somewhere (If I find the source I will put it in hereJ) and how I inserted into a SQL Server Table…
Excel–Power Query–Automating SSIS job duration report
Following on from our query to get our SSIS Job duration information, I decided to start using Power Query and see if I could use it to facilitate the automation of my reporting requirements. Detailed below is how and what I archived. Example · We are going to use our SSISDB to see if there have been any changes in…
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…
SSRS – Viewing status of Data Driven Subscriptions
What I wanted to do, is to see the status of my data driven subscriptions which are emailed out. I wanted to ensure that there were no errors when being sent out, as well as ensuring that they were indeed sent out. NOTE: This follows on from the FRAMEWORK FOR AUTOMATING SQL SERVER REPORTING SERVICES (SSRS) DATA DRIVEN SUBSCRIPTIONS –…
Framework for Automating SQL Server Reporting Services (SSRS) data driven subscriptions – Part 2
Description: What the goal was to find a way to automate how an end user could subscribe to a SSRS Data Driven Subscription without any intervention from the Business Intelligence Developer. And also enable the end users to have their own selections. And finally if they did not want to receive the report any longer to then delete their subscription….
Framework for Automating SQL Server Reporting Services (SSRS) data driven subscriptions – Part 1
Description: What the goal was to find a way to automate how an end user could subscribe to a SSRS Data Driven Subscription without any intervention from the Business Intelligence Developer. And also enable the end users to have their own selections. And finally if they did not want to receive the report any longer to then delete their subscription….
SSIS – Creating new Partitions in SQL Server Analysis Services (SSAS) with XMLA and Analysis Services DDL
What I wanted to do is to create a process in SSIS that did not use code to create new SSAS Partitions using XMLA. NOTE: This is quite a long blog post, but it has all the details from start to finish to get this completed. Example: · We will be using the AdventureWorks2012DW data warehouse and Cube….
SSIS – Using SQL Server Analysis Services (SSAS) MDX query into Variable in Execute SQL Task
What I wanted to do was to get the output from my MDX query, and then use this in SSIS and take the output and put it into an SSIS Variable which could then be used later in your SSIS Package. Example: · We are going to get the Internet Sales Amounts for 2 days. o 01 June 2008…