March 1, 2007 at 1:50 am
dear all,
I have a problem using SSIS since I was a beginner, the problem is I have to do some data transform from flat files into database. These flat files come from many branches of my office with the file structure like this D:\SSIS\branch_nm\file_nm.txt, in folder SSIS there are many branch_nm folder and each branch_nm folder contains many flat files.
My idea was grab the branch_nm folder into array variable using Script Task and then loop this array variable using ForEach Loop Container to get the file and using it for Flat File connection, but I don't know the way to do it.
May this idea work out for sure ?? How to use array variable, that we previously defined inside Script Task, in ForEach Loop Container ?
Thanks in advance
March 2, 2007 at 2:43 am
Hi,
Well, you could do this without using an Array Variable, because the ForEach Loop container gives you an enumerator for traversing through files.
You could try this out:
01. Create a 'Flat File Connection' manager.
(You can set it to use one of the files as a dummy move otherwise you won't be able to click on 'OK', but it doesn't matter)
02. Next, create a variable name "Filename" or something.
03. Then, drop a ForEach loop container onto the space.
04. Edit the ForEach loop container and go to the 'Collections' section.
05. Here, select 'Foreach File Enumerator' under the 'Enumerator' option.
06. Then, in the 'Enumeration configuration' you will find a text box where you can set the folder (ideally, this would be you "D:\SSIS" folder)
07. Under the "Retrieve Filename" section, select the 'Fully Qualified' radio button, and check the 'Traverse subfolders' checkbox.
08. Now, go to the 'Variable Mappings' section from the left panel. Select the variable you previously created under the variable heading. You would see that the 'Index' would show '0'
09. Now, that's done with the Loop Container mapping. So click on 'OK'
10. Next, drop a script task inside the Loop Container's space.
11. Edit the script task and go to the 'Script' section from the left panel.
12. In the 'ReadOnlyVariables' section type the variable name which you had created previously ("User::Filename)
13. Click on the 'Design Script' button at the bottom.
14. In the script section enter the code to set the 'Flat File Connection' manager's connection string to what's in the variable. Assuming that the 'Flat File Connection' Manager has been named as "Source" the code will be something like this:
Dts.Connections("Source").ConnectionString = Dts.Variables("Filename").Value.ToString()
15. Close and Exit the script, then click on 'OK'.
16. You now have a Loop container which will loop through all the file and set the 'Flat File Connection' manager with each file's name.
17. You could now have a 'Data Flow Task' or something inside the loop container's space to the transformation.
If you'd like it I could mail you a little sample of this...
Cheers,
Gogula
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply