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

The syntax when using the OLE DB Command component against an Access database is:

UPDATE jetTableName SET jetColumnName1 = Input1 WHERE jetColumnName2 = Input2

Where Input1/2 can be any name that isn’t a column name in your Access table.

Cheers
Frank

Leave a Reply

Your email address will not be published.