This post shows you how to download files from a web site whilst really making the most of the SSIS objects that are available. There is no task to do this, so we have to use the Script Task and some simple VB.NET or C# (if you have SQL Server 2008) code. Very often I see suggestions about how to use the .NET class System.Net.WebClient and of course this works, you can code pretty much anything you like in .NET. Here I’d just like to raise the profile of an alternative. This approach uses the HTTP Connection Manager, one of the stock connection managers, so you can use configurations and prop... [More]

If you some of the dynamic features of SSIS such as package configurations or property expressions then sometimes trying to work out were your connections are pointing can be a bit confusing. You will work out in the end but it can be useful to explicitly log this information so that when things go wrong you can just review the logs. You may wish to develop this idea further and encapsulate such logging into a custom task, but for now lets keep it simple and use the Script Task. The Script Task code below will raise an Information event showing the name and connection string for a connection... [More]

Download the slides and samples from the Preview of SSIS in SQL Server 2008 presentation, first presented at the SQLBits 2007 Community Conference. In this session we looked at some of the new and improved features of SSIS coming in SQL Server 2008. Highlights include: Looking at the new pipeline limiter The new threading engine The arrival of C# as a scripting language option Change Data Capture(CDC) Presentation & Samples (360KB) Preview of SSIS in SQL Server 2008.zip

One of the things coming from our look at SQL Server 2008 Integration Services is that you are not going to see a lot of change visually. What you are going to see is things going on underneath the covers that makes SSIS perform better, and improved informational messages from the components. Just such a change is seen in the Script task. In SQL Server 2005 we drop onto the designer two Script tasks and join them with workflow. We can then go into the Scripts of the tasks and set breakpoints in the code of each. The Control Flow should now look something like this.   &... [More]

(By Jamie Thomson) Introduction Surrogate keys are generally considered fundamental building blocks of a data warehouse. They are used as identifiers for dimensional members and enable us to manage slowly changing dimensions. SSIS does not contain a built in component for generating surrogate keys but there is still a mechanism for doing it – the Script Component. The Script Component allows us to modify the data in a data flow path using managed code and we can use it to generate surrogate keys. The Row Number Transformation can be used to help generate surrogate keys... [More]

In this article we are going to assume a few things about the reader as we want to concentrate as much as possible on the Script Component itself and not the peripheral stuff The things we will assume are: You are happy with adding a Data Flow task to the designer in Workflow You are happy to configure an OLE-DB Source adapter You are comfortable with configuring mappings in the Destination adapter As usual we will show you a graphic of the Data Flow once it is completed so we know that if yours resembles ours at the end then chances are we are at the same place. ... [More]