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

I don’t know about you, but one of the things that drives me to distraction when writing SSIS packages is being forced to use excel as a data source. I’d like to qualify that statement by adding that it’s the use of excel in the face of all reason, to do the job that other software would be better for. Unfortunately though, corporations and people being what they are, they are a fact of life for an ETL developer.

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

Many of you will have heard the mantra that loops are bad, set based is good. But how do you get around them?

The fact of the matter is that there are very few circumstances where a loop is the only way to achieve your objective. A look through the SQL forums will show you many a technique to turn iterative row by row processing into single transaction set based processing. I’m refering to hierarchical query techniques (see nested set theory) and of course the subject of this post, the numbers table.

(more…)

I talked in a previous post about the possibility of using the format command and bulk insert task inside of a foreach loop in order to load all your tables using a single package… I don’t much like the method as it means you have to run each table load in series, and you’re not taking advantage of SSIS (high speed dataflow task, parallelism and eliminating staging with a single pass transformation).

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

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

(more…)

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

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

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

(more…)