SQL Server 2012 introduces Sensitive Parameters. These parameter values are masked in the UI, protected according to the ProtectionLevel of your package/project when saved to disk, and encrypted when deployed to the SSIS Catalog. When using SSIS Expressions, sensitive parameters can only be assigned to sensitive properties (such as a connection manager’s Password property). You … Continued
SSIS Quick Tip: Copy & Paste Packages into a Visual Studio Project
The Add Existing Package option for SSIS projects is very helpful, but has one major limitation. The dialog only allows you to add a single package at a time. When working with a lot of packages, this can be a time consuming process. Thankfully, there is a shortcut. You can add multiple packages to a … Continued
SQL Bits X Schedule has been Posted
The SQL Bits X schedule has been posted and I am very impressed with the lineup! Most conferences will have 3-4 tracks running in parallel – Bits will have 8 sessions to choose from in most timeslots, offering you a wide variety of options. Friday schedule Saturday (Community Day) schedule More details about the pre-con … Continued
Speaking at SQL Saturday #105 in Dublin, Ireland
I’m honored to be presenting at SQL Saturday #105 in Dublin, Ireland on March 24th. They recently added a 4th session track to keep up with demand, and I was more than happy to fill in one of the timeslots with a session about the developer experience enhancements in SSIS 2012. I’ll also be covering … Continued
SSIS Quick Tip: Required Parameters
SQL Server 2012 introduces the new Parameter model. Setting a parameter’s Required property to True means that a value must be supplied at runtime by the caller of the package (dtexec, SSMS, SQL Agent, the Execute Package Task, etc). This means that although the parameter has a default value at design time, it will never … Continued
How Can I Improve the Performance of my Destination Component
Question: I’ve created a custom destination component, but I’m not happy with the performance. I tried using multiple threads, but the pipeline buffer doesn’t allow free traversal. What can I do? I’d start by profiling the component to make sure you identify the correct bottlenecks. The most common source of problems is accessing the … Continued
Processing Modes for the CDC Source
The CDC Source offers five different ways to retrieve change data. The format you receive the data in is determined by the “CDC processing mode” you select in the CDC Source Editor UI. The two primary modes are All Changes, or Net Changes. Net is the best mode to use for most ETL processes, although it … Continued
SSIS Training at SQL Bits X in London
I am really excited to be invited back to SQL Bits to do a full day of SSIS training. Once again, I’ll be covering my favorite topic – SSIS Performance Design Patterns. The content will be similar to the talk I gave at SQL Bits 9, with a couple of big changes. First, I’ll be … Continued
Too Many Sources in a Data Flow
This was a recent customer issue that I wanted to share. The customer was transferring a large amount of data between two tables, without performing any transformations on it. They wanted to run multiple transfer streams in parallel, and created a way to split up their source data into six partitions. In their original design, they had … Continued