Quick Tip

Generate an Empty Raw File Without Running your SSIS Package

Generate an Empty Raw File Without Running your SSIS Package

A new feature was added to the Raw File Destination in SQL Server 2012 which allows you to output an empty raw file from the editor UI. Add a Raw File Destination to your data flow, and connect it to the previous transform Double click to open the Raw File Destination Editor Configure the file … Continued

Quick Tip: How can I tell when my DQS knowledge base has been updated?

Quick Tip: How can I tell when my DQS knowledge base has been updated?

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

Monitoring SSIS package executions

Monitoring SSIS package executions

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

Installing SSIS for Visual Studio 2012

Installing SSIS for Visual Studio 2012

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

Quick Tip: Resolving Oracle Destination Datatype conversion errors

Quick Tip: Resolving Oracle Destination Datatype conversion errors

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

Automatically Select Input Columns in a Custom Data Flow Component

Automatically Select Input Columns in a Custom Data Flow Component

The following code snippet can be used in a custom data flow component to automatically select all input columns when you attach a path.

This is the equivalent to clicking the Select All box in the Advanced Editor.  

Lookups with really big reference tables

Lookups with really big reference tables

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

Can I deploy a single SSIS package from my project to the SSIS Catalog?

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

Publish to SSIS Catalog using PowerShell

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

Should I use the SQL Server Destination?

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