Joshua Fennessy

Dealing with Excel data sources in SSIS

It’s well known knowledge that Excel can be used as a data source in SSIS.  Maybe not as well known is the fact that there are several issues with this.  Probably most importantly, is the fact that SSIS is going to assume ALL numeric values to be FLOAT datatype.  Since FLOATS are an approximate datatype, this can cause issues when a high level of precision is required.

There are several different approaches to getting around this, including using a script source to read the Excel document, changing columns to text and converting them in the data flow, but those have inherent problems of their own.

One solution that I’ve liked to use in the past, is to batch convert these Excel documents to CSV in the control flow, and then use the CSV as a data source in the data flow.  One BIG caveat to this approach is the fact that Excel MUST be installed on the server, as the script calls the Excel executable.

I’ve implemented this at numerous clients and it’s working without any problems reported.  It’s a great solution when you have a lot of Excel files to import and would like to work around limitations inherent with SSIS Excel Source adapters.

Below is a quick .vbs script that I’ve created which will convert an Excel file to a CSV.  It only converts the first worksheet in the workbook, but can easily be modified to handle multiple worksheets.  I use it in the control flow with a For Each Enumerator to handle multiple Excel files in a directory.

option explicit

dim a, arg, oArgs, ArgNum
a = 0

Set oArgs = WSCript.arguments
ArgNum = oArgs.Count
if ArgNum <> 1 then
 WSCript.echo "Syntax: cscript <Script> filename_root"
 WScript.quit(1)
end if

dim filename_root, oldfilename, pos,newname
oldfilename = oArgs(0)

rem Remove .xls file extension if it was provided
dim ext
ext = right( oldfilename, 4 )

if lcase( ext ) = ".xls" then
 oldfilename = left( oldfilename, len( oldfilename ) - 4 )
end if
filename_root = oldfilename

dim app
set app = createobject("Excel.Application")

dim wb
set wb = app.workbooks.open( filename_root & ".xls" )

const xlXMLSpreadsheet = 46
const xlCSV = 6

app.DisplayAlerts = false

wb.worksheets(1).activate
dim output_filename
output_filename = filename_root & ".csv"
wb.saveAs output_filename, xlCSV

app.DisplayAlerts = true
wb.close false

WScript.quit

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: