EzAPI – Alternative package creation API

SSIS provides APIs to programmatically create dtsx packages. While we provide a managed wrapper layer for the runtime/control flow, you have to use the lower level COM wrappers (DTSPipelineWrap) to create your data flows – the usability of which could definitely be improved. Evgeny Koblov, one of the testers on the SSIS team, has put…


Lookup Pattern: Range Lookups

Performing range lookups (i.e. to find a key for a given range) is a common ETL operation in data warehousing scenarios. It’s especially for historical loads and late arriving fact situations, where you’re using type 2 dimensions and you need to locate the key which represents the dimension value for a given point in time….


Roll your own Transfer SQL Server Objects task

Awhile back we went through a lot of customer feedback logged through the Connect site concerning the Transfer SQL Server Objects task. Some of these turned out to be bugs, but a lot of the reported issues stemmed from usability problems with the task itself. There’s actually not that much to the task on the…


SQL Server Data Tools – Business Intelligence for Visual Studio 2012 released online

Today we are releasing SQL Server Data Tools – Business Intelligence for Visual Studio 2012 (SSDT BI) templates. With SSDT BI for Visual Studio 2012 you can develop and deploy SQL Server Business intelligence projects. Projects created in Visual Studio 2010 can be opened in Visual Studio 2012 and the other way around without upgrading…

22

Using XML Source

Providing a generic way to extract data from XML documents of any format is pretty complicated. The SSIS XML Source Adapter tries to do just that, to various degrees of success. XML Source is easiest to use when your input has a simple element/sub-element structure: <rootgoo> <goo> <subgoo>value</subgoo> <moregoo>1</moregoo> </goo> <goo> <subgoo>value</subgoo> <moregoo>2</moregoo> </goo> </rootgoo>…


SSIS with AlwaysOn

In SQL Server 2012, AlwaysOn Availability Groups maximizes the availability of a set of user databases for an enterprise. In addition, SSIS introduces new capabilities which allow customers with the ability to easily deploy to a centralized SSIS Catalog (i.e. SSISDB user database). In this article, we explore how you can make use of SSIS…


Connecting to MySQL from SSIS

Update:  See this follow-up post on writing data to MySQL. I’ve recently seen a bunch of questions about connecting to MySQL from SSIS, so I thought I’d give it a try. My overall findings was that while there were quirks, both the ODBC and ADO.Net drivers that I tried worked fine. Both drivers work with the…


What’s New in SSIS for SQL Server 2012 RC0

At the 2011 PASS Summit I presented a Top 10 list of features that had been added since CTP3, or hadn’t gotten much attention so far. Now that the SQL Server Release Candidate 0 (RC0) available, I can start blogging about them! I’ll continue to post more details over the next couple of weeks. Top…


SSIS and PowerShell in SQL Server 2012

This post is from Parth Shah, a Software Development Engineer in Test on the SSIS Team. Previously we have talked about SSIS Catalog Managed Object Model. For those of you don’t remember what MOM is or have not heard of it before, think about MOM as a set of APIs that allow you to automate…


CDC for Oracle in SQL Server 2012

One of the new features introduced in SQL Server 2012 is the ability to use SQL Server CDC for tracking changes on an Oracle table. In the following post you will understand how to setup such a service for tracking changes on your Oracle tables. Configuring your Oracle server Before creating the service, you must…