Protean

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))

Read the rest of this entry »

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…

Read the rest of this entry »

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

Read the rest of this entry »

If you are having problems setting the password to the FTP task in SSIS, then the likelyhood is you are trying to use an expression to set it (can’t be done); being caught out by clicking [OK] (finger trouble) in the FTP connection manager editor, or you’re struggling with the encryption settings (a long subject) for your package.

I don’t want to enter into discussion here on the encryption level you use (I will discuss that in another post some other time), merely this post just contains some simple code that you can use to set the password with a script component placed before the FTP task.

Amazon Link: Hands-On Microsoft SQL Server 2005 Integration Services

Read the rest of this entry »

This script might be the best SQL insert generator I’ve seen yet. It’s flexible and deals quite comfortably with the majority of problems a dev can come across when attempting to write one of these. Why re-invent the wheel.

Take a look at the website, there are plenty of neat tips and tricks.

Cheers

Frank

Amazon Link: Microsoft SQL Server 2008 T-SQL Fundamentals (PRO-Developer)

I have a SQL generation package that I am using to create sql files that will later be executed against the production environment. It is necessary to do this for corporate IT policy reasons which I won’t go into, however, suffice to say it isn’t the most efficient way to get new data into the database!

The data is input into Excel by the users and then passed to me the run the SSIS package. This worked fine until recently when one of the Excel spreadsheet I recieved had cells in it that were in excess of 8000 characters wide (containing Legal T&Cs).

Amazon Link: The Rational Guide to Extending SSIS 2005 with Script (Rational Guides)

Read the rest of this entry »

A quick nugget… If you get this error:

[DTS.Pipeline] Error: component “OLE DB Source” (79) failed the pre-execute phase and returned error code 0xC0010001 

Check that your variables are mapped and that they can be read at run time. Similarly if you get the same error code with the description:

Configuration from a parent variable “XXXXX” did not occur because there was no parent variable. Error code: 0xC0010001.

Check that you have spelt and referenced the correct parent variable.

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.

Read the rest of this entry »

During a recent data migration from Oracle to SQL Server, I came accross the problem of trying to import Greek encoded strings into our SQL Server database. With the standard character set installed with my Oracle client, all I got was upside down question marks with the odd square and ampersand thrown in for good measure.

Read the rest of this entry »

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. 

Read the rest of this entry »