As a follow-up to my blogs What product to use to transform my data? and Should I load structured data into my data lake?, I wanted to talk about where you should you clean your data when building a modern data warehouse … Continue reading →
If you are building a big data solution in the cloud, you will likely be landing most of the source data into a data lake. And much of this data will need to be transformed (i.e. cleaned and joined together … Continue reading →
Azure Data Factory v2 (ADF) has a new feature in public preview called Data Flow. I have usually described ADF as an orchestration tool instead of an Extract-Transform-Load (ETL) tool since it has the “E” and “L” in ETL but … Continue reading →
I see a lot of confusion when it comes to Azure Data Factory (ADF) and how it compares to SSIS. It is not simply “SSIS in the cloud”. See What is Azure Data Factory? for an overview of ADF, and I’ll assume … Continue reading →
When doing ETL, you have the choice of using T-SQL or SSIS (see When to use T-SQL or SSIS for ETL). If you decide T-SQL is the way to go and you just want to execute a bunch of T-SQL statements (individually … Continue reading →
Data cleansing can be done via SSIS as well as Data Quality Services (DQS) and Master Data Services (MDS). The lines are a bit blurred when talking about data cleansing using SSIS, DQS and MDS. In what product should data … Continue reading →
I had someone ask me the other day if they are using SSIS 2012 Project Versions, is there any benefit to using TFS? The answer is Yes! While Project Versions is a new feature that keeps prior versions of SSIS … Continue reading →
Using SQL Server views throughout a Business Intelligence (BI) solution can provide a tremendous amount of benefits. Here is a list of such benefits, taken in large part from the excellent video SQLBI Methodology by Marco Russo and Alberto Ferrari: Benefit … Continue reading →
My most popular blog has been Visual Studio 2012 does not support BI. In short, this means VS 2012 does not support SSAS, SSIS, or SSRS. Well, not anymore! Just released is Microsoft SQL Server Data Tools – Business Intelligence for … Continue reading →
This is an annoying problem I have had a few times: I open a SQL Server Integration Services (SSIS) project in SSDT, and see that the Toolbox pane is empty, displaying a “There are no usable controls in this group. … Continue reading →
The script task in SSIS 2012 is a great tool when you need to use C# or VB code to perform a function that is not possible with the other SSIS built-in tasks. It uses Visual Studio Tools for Applications … Continue reading →
In Master Data Services (MDS), I received an error when using C# code in the script task in SSIS 2012 to call the MDS web services API: To fix this, I had to edit the web.config in C:\Program Files\Microsoft SQL … Continue reading →
In SSIS there are two tasks than can be used to execute SQL statements: Execute T-SQL Statement and Execute SQL. What is the difference between the two? The Execute T-SQL Statement task tasks less memory, parse time, and CPU time … Continue reading →
Being a long-time SSIS 2008 developer, I have been working with SSIS 2012 for the past few months, and I wanted to jot down a quick guide for those of you also making the jump from SSIS 2008 to SSIS … Continue reading →
UPDATE 3/6/13: VS 2012 now supports BI! Check out Microsoft SQL Server Data Tools – Business Intelligence for Visual Studio 2012 In the same vein as my post Watch out, Visual Studio 2010 does not support BI, Visual Studio 2012 does … Continue reading →
BIDS helper is Visual Studio add-in with features that extend and enhance the functionality of the SQL Server 2005 and SQL Server 2008 BI Development Studio, and now supports SQL Server 2012 (including tabular mode) and Visual Studio 2010 (SSDT). Version 1.6 … Continue reading →
In previous SSIS versions as the developer you were responsible for configuring all of your logging options, such as what events you were going to log, which containers you were going to collect logs for, and where you were going … Continue reading →
Do you know there are hundreds of additional tasks, tools, and components for SSIS, many which are free? Why re-invent the wheel? Take a look at this exhaustive list created by Todd McDermid on CodePlex: SSIS Community Tasks and Components. It … Continue reading →
Behind the scenes in SSIS, the data flow engine uses a buffer-oriented architecture to efficiently load and manipulate datasets in memory. The benefit of this in-memory processing is that you do not need to physically copy and stage data at … Continue reading →
Parallel execution in SSIS improves performance on computers that have multiple physical or logical processors. To support parallel execution of different tasks in a package, SSIS uses two properties: MaxConcurrentExecutables and EngineThreads. If you are like me, you probably did not … Continue reading →