Excel – Pro·te·an

Excel VBA to prevent user generated errors in SSIS

I don’t know about you, but one of the things that drives me to distraction when writing SSIS packages is being forced to use excel as a data source. I’d like to qualify that statement by adding that it’s the use of excel in the face of all reason, to do the job that other software would be better for. Unfortunately though, corporations and people being what they are, they are a fact of life for an ETL developer.

Amazon Link: Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

Continue reading “Excel VBA to prevent user generated errors in SSIS”

Excel – a read-only database?

If you are trying to execute some Jet SQL DDL like CREATE TABLE or DROP TABLE against an Excel workbook and you get this message:

Executing the query “DROP TABLE [Sheet1];” failed with the following error: “Cannot modify the design of table ‘Sheet1’. It is in a read-only database.”.

Continue reading “Excel – a read-only database?”

Code to query Excel metadata in SSIS script component

Given the problems inherent in loading excel files with SSIS, I thought it might be useful to share with you some code to get you started on interrogating it’s schema prior to running your Execute SQL control tasks or your dataflows.

This is especially useful for me as it allows me to test column names, worksheet names and datatypes etc prior to attempting to load the data (I have found that users have a tendency to format and rename sheets without realising the impact it has on the import). There are other uses too…

$OLE DB Consumer Templates: A Programmer\'s Guide$

Continue reading “Code to query Excel metadata in SSIS script component”

Dynamic table loading in SSIS (Part 1)

The quick answer to this is that you can’t… at least not in a dataflow. Whilst you can dynamically change environments and databases, the column metadata has to remain the same. It’s therefore fairly limited as to what you can load “on the fly”.

There is a work around however for those of you who wish to be able to create a looping mechanism in a single package with a single task to load the data.

Amazon Link: SQL Server 2000 Fast Answers for DBAs and Developers, Signature Edition

Continue reading “Dynamic table loading in SSIS (Part 1)”

SSIS Pivot Component Example

It may be me, but as yet I haven’t seen too much written about the SSIS pivot and unpivot components.

Whilst they are not the easiest to configure, when faced with the option of importing to SQL Server and using a UNION or the new PIVOT clauses, I actually prefer the SSIS component.

Rather than actually explain the component, I thought it might be useful to go through a real example, so here goes…

Amazon Link:Professional ASP.NET 3.5: In C# and VB (Programmer to Programmer)

Continue reading “SSIS Pivot Component Example”