Based on Beta 2
SQL Server 2005 has made it a lot easier for us to loop over a collection and with each iteration do something with the item retrieved. In this article we are going to show you how to iterate over a folder looking at the files within and doing something with those files. In this instance we will be entering the filename into a SQL Server table and we will then load the actual files we have just found into another SQL Server table. You will note here that there is still the need to load the file names into a table as an intermediate step just as we need to do in SQL Server 2000.
Here are the steps we will follow
- Setup of the ForEachLoop container
- Using the ExecuteSQL task with paramaters and variables
- Setup the FileInserter transform
We have a lot of new concepts to get through so let's get started
To make it easier to see what the Workflow section will resemble when we have finished here let's take a look before we start. It will also help us identify things being spoken about in the following early sections.
The image shows the ForEachLoop container with an ExecuteSQL task inside joined to the DataFlow task by an "On Success" Constraint
We will need to create the following tables for our code to work as well.
CREATE TABLE HoldSourceName(colFilename varchar(1000))
CREATE TABLE DestinationImageHolder(colFileName varchar(1000), colImage image)