SSIS – download a file from a website

Some of the companies I’ve worked for have had reporting contracts with smaller 3rd party providers. Campaign or website analysis provided by them would have to be loaded to the datawarehouse

Security policy dissallowed downloading the files over FTP, because credentials are passed in clear text, and our 3rd party report providers didn’t have a sFTP facility, so in this scenario what other options are available?

Microsoft Office 2007 SharePoint Designer Edition (PC)

Continue reading “SSIS – download a file from a website”

SSIS system variable – ExecutionInstanceGUID

ExecutionInstanceGUID is a system variable of type string. If you are exporting this variable to a package management, custom logging or auditing table ensure that you have the correct mapping in your execute SQL task. It is a string variable and not a GUID (the same applies to other variables whose type may not be the same as the value they represent).

SQL Server 2005 Reporting Services Custom Controls

Continue reading “SSIS system variable – ExecutionInstanceGUID”

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

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”

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 – error code 0xC0010001

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.

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