dbi services Blog
Welcome to the dbi services Blog! This blog focuses on IT infrastructure - featuring news, troubleshooting, and tips & tricks. It covers database, middleware, and OS technologies such as Oracle, Microsoft SQL Server & SharePoint, Documentum, MySQL, PostgreSQL, Sybase, Unix/Linux, etc. The dbi services blog represents the view of our consultants, not necessarily that of dbi services. Feel free to comment on the postings!
SQL Server 2012: new features for Integration Services (SSIS)
Like other SQL server 2012 products, Integration Services has a lot of new features and enhancements. I am presenting a summary of these new "goodies" (from CTP1 to RC0), which can be powerful for DBAs and developers.
- Deployment and Administration Enhancements
You have a new deployment model and ways to administer your data integration workloads in the company. The project deployment model provides the ability to define parameters for packages and projects and to modify the parameter values at runtime. More information here.
- Object Impact and Data Lineage Analysis
You can now view information about impact and data lineage between supported Integration Services objects that are deployed to the server and supported SQL Server SQL Server objects. More information here.
- Usability Enhancements
SQL Server Data Tools (SSDT) has been updated to help new and experienced users develop packages more easily. A new Integration Services toolbox, designer refinements, and other usability enhancements help new users become productive quickly.
- Reduced Memory Usage by the Merge and Merge Join Transformations
Microsoft has made the Integration Services Merge and Merge Join transformations more robust and reliable. This is achieved by reducing the risk that these components will consume excessive memory when the multiple inputs produce data at uneven rates. More information here.
- New Reports for Troubleshooting Package Operations
In this release, two standard reports are available in SQL Server Management Studio to help you troubleshoot Integration Services packages that have been deployed to the Integration Services catalog. More information here.
- Viewing Dependencies for Objects
You can now easily view dependencies for SQL Server objects and packages stored on different servers, using the Object Dependencies dialog box in SQL Server Management Studio.
- Comparing and Merging Packages
The structure of .dtsx package files has been modified to make it easier for you to compare packages. You can also more reliably merge packages that don’t contain conflicting changes or changes stored in binary format.
- Easier Access to Samples and Tutorials
The Getting Started window in the SSIS Designer provides links to samples, tutorials and videos. You can customize the window by adding links to additional content. For more information, see Integration Services User Interface. More information here.
The SSIS Toolbox in SQL Server Data Tools (SSDT) provides links to samples and Help content for Control Flow and Data Flow items. You can add links for custom components. More information here.
This build provides additional elements like views, stored procedures and functions to help you troubleshoot performance and data issues.
|Troubleshooting Capability||Views, Procedures, and Functions|
|Get performance statistics and other information for an execution||catalog.executions (SSISDB Database)
dm_execution_performance_counters (SSISDB Database)
|Add, remove, and query data taps in a package data flow||catalog.add_data_tap
|Create a dump for a running package||catalog.create_execution_dump|
|Set a parameter value in an instance of an execution||catalog.set_execution_parameter_value (SSISDB Database)|
- Shared Connection Managers
This release allows you to create connection managers at the project level that can be shared by multiple packages in the project. The connection manager you create at the project level is automatically visible in the Connection Managers tab of the SSIS Designer window for all packages.
Integration Services (SSIS) parameters allow you to assign values to properties within packages at the time of package execution. You can create project parameters at the project level and package parameters at the package level. Project parameters are used to supply any external input the project receives to one or more packages in the project. Package parameters allow you to modify package execution without having to edit and redeploy the package. More information here.
- Undo/Redo in SSIS Designer
You can undo and redo up to 20 actions in the SSIS Designer. For a package, undo/redo is available in the Control Flow, Data Flow, Event Handlers, and Parameters tabs, and in the Variables window. More information here.
You can find all information about the new Integration Services here on the Microsoft TechNet. At the moment, I have not tested all new features but I will and you will see it soon.
My SQL Server 2012 series is to be continued...