You can improve data quality in your SSIS data flows by using the DQS Cleansing transform (new in SQL 2012). When you’re using DQS, the data is cleansed according to the rules you’ve built up in the DQS knowledge base. As you update the knowledge base with better rules, your overall data quality goes up. … Continued
Question: When we run SSIS in development (from SSDT/BIDS), we can see the progress of the job, with all of the steps highlighted in yellow, red or green. Is there a way to display this same status information when the packages run in production? You have a number of options when it comes to monitoring … Continued
Did you hear that SQL Server Data Tools – Business Intelligence (formerly known as BIDS) has been releases for Visual Studio 2012? It was announced last month, and we’ve seen a lot of downloads so far. The download adds support for the SQL Server 2012 BI projects (Reporting Services, Analysis Services, and Integration Services) to … Continued
When working with the Oracle Destination (the one from Microsoft/Attunity (2008 | 2012)), you might encounter generic data type conversion errors after mapping your columns. Validation error. Data Flow Task: Data Flow Task: Datatype conversion of Oracle Destination.Inputs[Oracle Destination Input].Columns[L_QUANTITY] is not supported. The error appears because there is not a 1:1 mapping of SSIS … Continued
The following code snippet can be used in a custom data flow component to automatically select all input columns when you attach a path.
public override void OnInputPathAttached(int inputID)
for (int i = 0; i < ComponentMetaData.InputCollection.Count; i++)
IDTSVirtualInput100 input = ComponentMetaData.InputCollection[i].GetVirtualInput();
foreach (IDTSVirtualInputColumn100 vcol in input.VirtualInputColumnCollection)
This is the equivalent to clicking the Select All box in the Advanced Editor.
Recent customer issue: We are using the Lookup Transform to do joins in our Data Flows. Most Data Flows have more than one Lookup. The process worked fine in our development environment, but fails when we run in production because the source of the lookups have between 4-6 billion records each. How can we resolve … Continued
No. No you cannot. In SQL Server 2012, you will always deploy the entire .ispac file to the Catalog. I’ve received this question a number of times in the past couple of weeks. It’s actually a feature we considered, and one that I (initially) argued for, as I know it is a common practice with … Continued
Here is a PowerShell script that I use to provision and setup SSIS Catalogs when I’m demoing SQL Server 2012. It does the following: Connects to localhost (default instance) Drops the existing SSIS Catalog Creates a new SSIS Catalog Creates a Folder Deploys a Project Creates an Environment Creates three server varaibles Creates an environment … Continued
Short answer: No. Long answer: Noooooooooooooooooooooooooooooooooooooooooooooooooo. UPDATE: I was told that some people would like an explanation instead of blindly trusting my advice (weird), so here goes: In SQL 2005 RTM, the SQL Server Destination was the fastest way to load data into SQL Server with SSIS. Somewhere along the way (in the 2005 SP2 … Continued
The DQS client will automatically sort the list of Domain Values, which can make adding new values to a big list tricky. Once you enter the new value at the bottom of the list, it gets automatically sorted, and you need to scroll up the list to find it again. However, there is a better … Continued