Category: Microsoft

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”

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 FTP Task – Code to set the password

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); you are being caught out by clicking [OK] (finger trouble) in the FTP connection manager editor, or you are 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

Continue reading “SSIS FTP Task – Code to set the password”

SSIS Logging in dbo.sysdtslog90

I was recently asked if you can only use the msdb.dbo.sysdtslog90 table to log SSIS package execution…

It is possible to log your SSIS execution to a database other than msdb, simply by scripting the sysdtslog90 table from the msdb database, and creating it in a new db.

Then when you configure your SQL Server log provider, use a connection manager that points to the database on which you’ve just created your table.

Amazon Link: Microsoft SQL Server 2005 Integration Services Step-by-Step

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”