SSIS – Sending HTML formatted mail

As the SSIS send mail component doesn’t have an option to send html formatted messages, I thought it would help to post some code that implements the system.web.mail namespace.

Considering that sending messages is quite a regular task for any organisation, it might even be worth creating this as a standalone package and calling it each time you need to send a mail. Or even better still, writing a custom component instead.

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

Continue reading “SSIS – Sending HTML formatted mail”

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

SSIS Parent Package Configuration (Gotcha!)

The order of the configurations used in the development studio is not the same as the order used by DTEXEC, specifically in relation to parent package variables.

Parent Package variables always have the lowest priority no matter what order you set in the configuration manager.

Microsoft have recently added a note to BOL on this subject here

Amazon Link: Microsoft SQL Server 2005 Integration Services

Continue reading “SSIS Parent Package Configuration (Gotcha!)”

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