Item: SQL Server Integration Services
Rating: 8
Author: Verified User

Use Cases and Deployment Scope

We use SQL Server Integration Services (SSIS) as part of our on-premise Data Warehouse architecture. We have an ELT pattern and SSIS is used almost exclusively for the Extract and Load steps. Almost all packages are generated using BIML (Business Intelligence Markup Language) as a means of templating and then scripting them.

Pros and Cons

Easily connect with a wide variety of sources.
Provide control and flow to job execution order.
Able to extend functionality through scripting tasks.

Not flexible when source/target tables and file formats change.
Inflexible with regards to varying data types when Excel spreadsheets are used as a source or columns are added (as per previous item).
Occasional issues around mixed development and production x86/x64 run times can be frustrating.

Return on Investment

Rapid integration with source systems has provided a high ROI however this is based on using BIML in conjunction with SSIS.
Combination of workflow, data manipulation and scripting make it a very capable data integration tool.

Alternatives Considered

BusinessObjects Data Integrator, Oracle Data Integrator and IBM InfoSphere DataStage

Low-cost relative to other products - in fact, zero cost if one is considering the license cost as being for the database engine with Integration Services added on. It has a comparable range of functionality and performance and as such it's a 'no-brainer' to use SSIS over other data integration tools.

Key Insights

Do you think SQL Server Integration Services delivers good value for the price?

Yes

Are you happy with SQL Server Integration Services's feature set?

Yes

Did SQL Server Integration Services live up to sales and marketing promises?

Yes

Did implementation of SQL Server Integration Services go as expected?

Yes

Would you buy SQL Server Integration Services again?

Yes

Other Software Used

Microsoft SQL Server, Microsoft Power BI, Visual Studio IDE

Likelihood to Recommend

I do not think that SQL Server Integration Services (SSIS) is great for complex dependency management and scheduling for an entire DW load. However, it is great for smaller units of work and particularly where moving data between systems is required due to its extensive and extensible connectivity options. That said, it is obviously focused on traditional on-premise systems and cloud-based environments are likely to prefer using the next-generation version of SSIS being Azure Data Factory (ADF).

Rapid and low cost integration with various sources for a DW

Overall Satisfaction with SQL Server Integration Services