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.”.

How to Do Everything with Microsoft Office Access 2003 (How to Do Everything)

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

Bulk Import of DTS 2000 packages to SQL2005

I recently saw some forum posts on SSC.com asking if there was a way to bulk import DTS 2000 packages into a SQL 2005 database.

The answer is that you can use the DTSBackup utility available for download at www.sqldts.com. Its a lightweight and extremely useful tool that can save hours of mouseclicking, and it works on both SQL 2000 and 2005.

Amazon Link: SQL Server 2000 Developer Edition

OLE DB Command for Jet 4.0 in SSIS

Normally in SSIS when we want to issue a SQL command against each row in a dataflow, we use the OLE DB Command component. When executed against SQL Server the command normally takes the form:-

UPDATE dbo.TableName SET ColumnName1 = ? WHERE ColumnName2 = ?

Where “?” can be mapped to the incoming columns in the order they are written.

However you will find that when you are using this component against an Access database, that this syntax will not work. You will get an error saying:

There is more than one data source column with the name “?”. The data source column names must be unique.

Amazon Link: Microsoft® Office Access™ 2007 Inside Out (Inside Out (Microsoft))

Continue reading “OLE DB Command for Jet 4.0 in SSIS”

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”

SSIS ETL Control (Part 2)

After some prompting, I decided it was about time I posted the solution to my initial post on the subject of metadata driven SSIS.

If you have read part one of this series (or other posts related to replication and sql agent), it will come as no surprise to you to know that I am a big proponent of metadata driven control in SQL Server. Whilst more complex to code, it is infinately easier to manage across environments.

I will add further explanations to this package at a later date, but for now, here is the solution and a very brief summary of what it does.

Amazon Link: Business Metadata: Capturing Enterprise Knowledge

Continue reading “SSIS ETL Control (Part 2)”

SSIS ETL Control (Part 1)

I can’t stress enough the importance of designing the framework for your ETL prior to getting stuck in with individual packages.

I bring this up, because I have seen that look of dread on a developers face (including my own) when they are about to deploy a package to the live environment and aren’t too sure what gremlins are hiding in the live environment that don’t exist in development. Not only this, with the proliferation of SSIS packages across the corporation, it can become extremely difficult to figure out and manage the process flow across all your servers.

It is for this reason, that I am a big fan of metadata controlled ETL. And I’m not just referring to a configuration table set up as part of an SSIS package, I’m talking about modelling a set of tables within a control database that can hold all the data that a package might need to run. I’m also talking about a generic parent package that can use this meta data to control the execution of any packages defined within the tables.

Amazon Link: The Data Warehouse ETL Toolkit: Practical Techniques for Extracting, Cleaning, Conforming, and Delivering Data

Continue reading “SSIS ETL Control (Part 1)”

Indexing for Dummies

From time to time these days I have to put my DBA hat back on and review database schema designed by those whose focus is on application development. What I often find is that attempts to improve performance through indexing have been done in vain as the query optimizer either doesn’t use them enough to merit their creation or they have been created on the wrong columns.

Frequently, optimisation is referred to as a dark art, but here I am going to attempt to give some basic pointers to help enighten the novice database designer…

Amazon Link: High Performance SQL Server DBA: Tuning & Optimization Secrets: Tuning and Optimization Secrets (IT In-Focus)

Continue reading “Indexing for Dummies”

Tokenize a String

We try to avoid SQL Server user-defined functions unless we can be certain that there is no other way to achieve what it is we’re asking of them. They aren’t very performant as they calculate on a row by row basis rather than the set based operations that our esteemed RDMS is renowned for.

Having said this I am going to post a table function I created recently for use as part of an SSIS import package that transforms an incoming single row CSV into roughly 100 records each time it runs.

The content team in our organisation faces the soul-destroying task of entering records as well as comma delimited strings on to an Excel spreadsheet which in turn is imported into 2 SQL tables.

There is no straight forward way to split the csv string into a SELECT list, but here is what I have done…

Amazon Link: Studio 8 Student & Teacher Edition (Includes: Flash Professional 8, Dreamweaver 8, Fireworks 8, Contribute 3, and FlashPaper 2)

Continue reading “Tokenize a String”

A useful script to analyse current queries on your box…

Ever been stuck trying to figure out what people are doing on your server, but unable to tell from DBCC InputBuffer what statements the users are running? This is one of the most useful scripts I’ve come accross and helps enormously when trying to analyse what is happening on your SQL Server.

It is often the case that users will query your database with tools like MS Access or Business Objects which are quite difficult to track. Well this useful tool helps to solve that problem…

Amazon Link: SQL Server 2005 Administrator’s Pocket Consultant (Pro-Administrator\’s Pocket Consultant)

Continue reading “A useful script to analyse current queries on your box…”