Tag: SQL Server Integration Services

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   …

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 (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…