Protean

The script task in SSIS was a fantastic improvement on DTS Active X script. The reasons for this in my view are 2 fold:-

1. Firstly, by allowing the user to code in Visual Studio it is far easier to develop and debug. Because the developer can make use of intellisense & object explorer, it means even the casual programmer can read & write code.
2. Secondly, because we can use it to access other external .net assemblies, it provides functionality for logic that can’t be achieved using the standard SSIS components.

These are both reasons in themselves that make it a compelling choice when deciding how to develop your package logic.

However, I have noticed at both client sites and on the forums, that there are those (the script task junkies!) that use the script task in almost any scenario when it is just not necessary. It makes maintenance and support tricky at best and sometimes impossible…

(more…)

Here’s the scenario… You have a RAW file which contains data from many files.

In the subsequent dataflow you need to perform a lookup against a large reference table, however you want just a subset that reflects the period contained within your RAW file.

Question 1, how do you find out the earliest date used within your RAW file data? And question 2, how do you write it to a variable so that you can use it in the subsequent data flow?

(more…)

In an earlier post, I demonstrated how to send HTML formatted mail using the script task. Another frequently requested use of the mail task is to be able to send query results within the email message body. Here’s how…

(more…)

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?

(more…)

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.

(more…)

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…

(more…)

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

(more…)