Category Archives: SSIS

Azure Data Factory and SSIS compared

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

Posted in Azure Data Factory, SQLServerPedia Syndication, SSIS | 4 Comments

SQL Server Agent job steps vs SSIS

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

Posted in SQLServerPedia Syndication, SSIS | 2 Comments

Data cleansing in SSIS, DQS, and MDS

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

Posted in DQS, MDS/MDM, SQLServerPedia Syndication, SSIS | 2 Comments

TFS vs SSIS project versioning

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

Posted in SQLServerPedia Syndication, SSIS, Team Foundation Server | Comments Off on TFS vs SSIS project versioning

Benefits of using views in a BI solution

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

Posted in Business Intelligence, SQLServerPedia Syndication, SSAS, SSIS | Comments Off on Benefits of using views in a BI solution

Visual Studio 2012 now supports BI!

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

Posted in Business Intelligence, SQL Server, SQLServerPedia Syndication, SSAS, SSDT/Juneau, SSIS, SSRS, Visual Studio 2012 | 9 Comments

SSIS 2012: Empty toolbox?

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

Posted in SQLServerPedia Syndication, SSDT/Juneau, SSIS | 65 Comments

Solution to VSTA editor error in SSIS 2012

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

Posted in SQLServerPedia Syndication, SSIS | Comments Off on Solution to VSTA editor error in SSIS 2012

Master Data Services Web Services API Error

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

Posted in MDS/MDM, SQLServerPedia Syndication, SSIS | Comments Off on Master Data Services Web Services API Error

SSIS: Execute SQL task vs Execute T-SQL Statement task

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

Posted in SQLServerPedia Syndication, SSIS | 13 Comments