Monthly Archives: March 2013

Refresh an Excel Workbook with a Script Task

Refresh an Excel Workbook with a Script Task

Are you loading data from an Excel workbook with external queries using SSIS? Do you want to make sure those queries are refreshed before the load takes place? Have I got the post for you! I recently had to do this myself, and after some googling with Bing, I found an MSDN forum post that … Continued

Consuming CANSIM Datasets using Data Explorer

Consuming CANSIM Datasets using Data Explorer

I recently stumbled upon CANSIM – Canada’s equivalent of Data.Gov. The site has a lot of really interesting datasets that come in handy if you’re someone who frequently creates data focused demos. I wanted to play around a bit more with Data Visualizations in Excel (specifically, the Scatter Chart over Time feature in Power View), … Continued

Using OUTPUT Parameters with the OLE DB Source

Using OUTPUT Parameters with the OLE DB Source

Did you know that support for OUTPUT parameters was added to the OLE DB Source in SSIS 2012? So many features were added in 2012, this one is often overlooked. (I had completely forgotten about it until someone asked the other day…). This post has a quick walkthrough of how to use them. For this … Continued

Slides from SSIS Performance Design Patterns | TechDays Hong Kong 2013

Slides from SSIS Performance Design Patterns | TechDays Hong Kong 2013

The slides from my 3rd presentation at the TechDays Hong Kong event are now available. As I mentioned during the talk, design patterns are one of my favorite topics, and something I really enjoyed writing about. This presentation had a subset of the SSIS Performance Design patterns in the full day training sessions I’ve done … Continued

Slides from Bringing Together SSIS, DQS and MDS | TechDays Hong Kong 2013

Slides from Bringing Together SSIS, DQS and MDS | TechDays Hong Kong 2013

A big thank you to everyone who attended my 2nd session at the TechDays Hong Kong event, Enterprise Information Management (EIM): Bringing Together SQL Server Integration Services (SSIS), Data Quality Services (DQS) and Master Data Services (MDS) (wow that’s a long title). The slides are available below. The files for my integrated demo are available … Continued

Slides from SSIS with Oracle talk | TechDays Hong Kong 2013

Slides from SSIS with Oracle talk | TechDays Hong Kong 2013

The slides from the Using SQL Server Integration Services with Oracle talk from TechDays Hong Kong 2013 can be found below (use the embedded control to download the powerpoint presentation directly from SkyDrive). Thank you to everyone who attended!  

Using Data Explorer with Power View

Using Data Explorer with Power View

In my first Data Explorer post, I pulled a list of event data from the SQL Saturday website. I then showed how to do dynamic lookups against an address verification service to pull back location data about the event’s venue. In this post I’ll complete my experiment and plot out each location on a map … Continued

Generate Data with a Script Component

Generate Data with a Script Component

I use the following code to generate data with a script component. I’ve found this useful in the past to demonstrate how buffers are handled in the data flow, as well as perf/load testing a destination component. This script will automatically generate data based on the columns you’ve defined for the component – no additional … 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

Dynamic Lookups With Data Explorer

Dynamic Lookups With Data Explorer

In my previous post, I used Data Explorer to grab the list of upcoming events from the SQL Saturday website. Excel has had the ability to grab data from HTML pages for awhile, but using Data Explorer provided a richer set of options, and allowed me to apply a series of transforms to get the … Continued