I recently started working on a task that required me to dig into creating SQL Server Integration Services packages programmatically. There is not a lot of info out there on this, so thought it might be beneficial for me to blog a bit as I go in case other benefit from this.
Relatively early on in my investigation I found that the internal SSIS test team at Microsoft has created and released a second level API for SSIS called EzAPI that sits on top of the native SSIS API. Using EzAPI, you can create packages and package objects in only a couple lines of simple code, vs. trying to decipher the somewhat esoteric COM wrapper APIs for SSIS.
You can read about EzAPI on the SSIS Team Blog here.
An update was released in late 2010, which you can read about here. This update added a lot of additional components to the API.
You can download it here (including source code and samples).
The objects that are supported are as follows:
Tasks and Containers
- For Loop container
- DataFlow Task
- Execute Package Task
- ActiveX Script Task
DataFlow Components
- OLEDB Source
- OLEDB Destination
- FlatFile Source
- FlatFile Destination
- ADO.Net Source
- ADO.Net Destination
- Multicast Transform
- Derived Column Transform
- Sort Transform
- OLEDB command Transform
- Lookup Transform
- Cache Transform
- Data Convert Transform
- Aggregate Transform
Connection Managers
- OLEDB Connection manager (including specific versions for SQL Server, Oracle, DB2)
- FILE connection manager
- FLATFILE connection manager
- CACHE connection manager
- ADO.NET connection manager
ADDED IN DECEMBER 2010 UPDATE
- Sequence container
- For each loop container
- Transfer database task
- Execute process task
- File system task
- Execute SQL task
- Excel source adapter
- Excel destination adapter
- Union all transform
- Merge & Merge join transform
- Conditional split transform
- SCD transform
No comments:
Post a Comment