SQL Server Integration Service
Partitions improve the query response of SSAS cube by narrowing the MDX query to a pre-defined subset of data. Usually cubes are developed with few basic partitions to begin with and new partitions are added over time. Partitions are created using XMLA commands. The rest of this post explains the steps to create partitions using metadata and SSIS package.
Continue ReadingSSAS Partitions are primarily geared towards better query performance by narrowing the MDX query to a pre-defined subset of data. Each partition must be in processed state for the cube to be available for browsing. However in a delta ETL run, it is more than likely that only some partitions would be refreshed with data while the rest remain unchanged. For example in a hospital admissions cube partitioned by month, data movements are frequent for admissions made during the current/last month compared to earlier months. This means if the partitions updated by the ETL run can be identified, then we can tailor SSIS package to dynamically process only those partitions that require data refresh while leaving the remaining partitions untouched.
Continue ReadingThe execution plan of SSAS processing engine triggers a sequence of processing tasks based on the object’s current processed state and its dependencies with other objects. This is what happens when the SSAS cube is processed from SQL Server Management Studio (SSMS). It is possible to replicate this execution plan close enough in an SSIS package so that the cube is always processed and refreshed with latest data.
Continue ReadingBulk Insert option for SSIS ADO.NET Destination which is available since SQL Server 2008 R2 improves data load speeds significantly. This option is enabled on the ADO.NET Destination component by selecting the “Use Bulk Insert when possible” check-box Continue Reading
Since SQL Server 2014, Tables with Columnstore Index are no longer read only and are updatable. This post is only applicable to SQL Server 2012.
Columnstore Index on fact tables provide significant improvements in response time especially for aggregated queries. One of the first things I did after migrating my databases to SQL Server 2012 was to create Columnstore Index on fact tables. Now I can run aggregated queries that fetch results in reasonable response a time which is great for development, unit testing and helps testers to run their test scripts faster.
One caveat to be aware of is that the table on which a Columnstore Index is defined cannot be inserted, updated or deleted (at the time of writing this post). Continue Reading
An easy way to completely enable/disable SSIS Logging would be to set the LoggingMode property of the package using a Package Variable or Parent Package variable. LoggingMode takes the following enumerated values:
The default SSIS log provider for SQL Server captures package trace in sysssislog table. It does capture some useful but limited trace information about the package execution. This trace is of little use without user defined variable values like record count, status flags, control metadata, selection from and to dates etc.
Of course SSIS provides a framework to write custom log providers to handle specific situations like these. At the time of writing this post, the custom log providers are unable to access the package variables. The good news is significant improvements are anticipated in SSIS logging capabilities in next version of SQL Server.
For those of you on SQL Server 2008 R2 and below, you could still customize SSIS log by creating your own execution log table and using it in tandem with sysssislog. Your execution log table could be structured something as shown below. Note that the execution log table captures record count, status flags, control metadata, selection from and to dates etc.
Continue Reading
Late Arriving Dimension/Early Arriving Fact as the name implies is a dimension record which is not available when the ETL loads the related fact record but becomes available at a later time.
This scenario could happen for a variety of reasons;
Dataflow Discoverer (DFLD) is a command-line discovery utility that detects and writes the metadata of SSIS dataflow columns to a SQL Server lookup table. DFLD detects Dataflows nested within containers up to any level of nesting. For information on how to install and use DFLD utility,visit http://dfld.codeplex.com
DFLD works on one package at a time. It’s a typical ops requirement to keep the DFLMetadata of all SSIS packages up to date. This can be automated by creating a SSIS package similar to the one shown below Continue Reading
SSIS automatically creates two columns on the Error Output from dataflow components – ErrorColumn and ErrorCode. ErrorColumn contains the column ID of the erroneous column. Column ID is a number that is unique to a dataflow within the package. Similarly ErrorCode contains the error number that caused the row to be rejected. At the moment, SSIS by default does not provide ErrorColumn name and ErrorCode description. These can be derived using the methods described below. Continue Reading
Benny Austin 