February 28, 2008 at 3:41 pm
First off, I'm new to SQL Server 2005 and Integration Services. I'd like to take advantage of the BULK INSERT function and set up a nightly data transfer so it seems that SSIS is the tool to use.
I'm interested in transferring part of the information in a set of .asc files within a folder into a database. I want to take a section of each file, do some manipulation which will add a couple of columns and then write the result to the database. To add to the fun there are several of these folders and I want to process each folder in turn. The disparate folders all have a common parent folder several levels up. More folders will be added in the future. So my questions are: how would I go about specifying which folders I want to go through? and Would it make sense to use Script to write the manipulation or are there other tools?
F
March 1, 2008 at 4:03 am
You can use a Foreach Loop Container for this task. On the Collection tab set the enumerator to Foreach File enumerator. Set Folder to your common parent folder, Files to *.asc and check the Traverse subfolders. On the Variable Mappings tab create a new variable FileName to hold the filename during iteration over the files. Inside the Foreach Look Container use a Data Flow Task which starts with a Flat File Source. Set the ConnectionString to the FileName variable using Expressions.
Peter
March 1, 2008 at 4:13 am
BTW, try using other Data Flow Transformation before using the Script transformations. You can use the Conditional Split Transformation to filter out unwanted data, use Derived Column Transformation to compute new values from you're input, etc. You can do a lot with the transformations which comes in the box. I'am using SSIS for more than a year now and I had to use the Script transformation only once (lucky me).
Peter
March 3, 2008 at 4:19 pm
Peter,
Thanks!! I'll give your ideas a try.
Frank
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply