Hery Susanto WR
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
Re: Using Array Variable in ForEach Loop Container
Ravi G
This functionality is provided in the Foreach loop container already. You dont have to write any scripts.
One caveat is all the flat files should have the same format. Assuming thats the case, here's what you need to do:
1. Create a variable to store filename
2. Create a foreach loop and use these settings:
4. Add a data flow task to the loop and set it up to load one flat file
5. On the flat file connection in the connection manager use expressions to set the filename property to use the filename variable
That should do it.
Alternatively, you can use a multi-flat file approach, which I ended up using in a similar situation. Instead of the data flow task, you would have a script task to construct a connection string, and then use the connection string on a multi-flat file connection.
Hope that helps.
This functionality is provided in the Foreach loop container already. You dont have to write any scripts.
One caveat is all the flat files should have the same format. Assuming thats the case, here's what you need to do:
1. Create a variable to store filename
2. Create a foreach loop and use these settings:
- In the collections tab, select "Foreach File Enumerator" for Enumerator
- For folder, select the root folder, i.e., D:\SSIS\ in your case
- For Files, enter *.txt
- Select Fully qualified for retrieve filename; and
- Check "Traverse subfolders"
4. Add a data flow task to the loop and set it up to load one flat file
5. On the flat file connection in the connection manager use expressions to set the filename property to use the filename variable
That should do it.
Alternatively, you can use a multi-flat file approach, which I ended up using in a similar situation. Instead of the data flow task, you would have a script task to construct a connection string, and then use the connection string on a multi-flat file connection.
Hope that helps.
Re: Using Array Variable in ForEach Loop Container
Hery Susanto WR
Thanks Ravi, it helped me so much, now I know what the "Traverse subfolders" is for :)
One more question Ravi, when I put the value into variable, can I get that value inside Script Component my plan is establish the flat file connection inside Script Component and get the data row by row using Synchronous method and push it into database. Is that a problem
Thanks in advance.
Thanks Ravi, it helped me so much, now I know what the "Traverse subfolders" is for :)
One more question Ravi, when I put the value into variable, can I get that value inside Script Component my plan is establish the flat file connection inside Script Component and get the data row by row using Synchronous method and push it into database. Is that a problem
Thanks in advance.
Re: Using Array Variable in ForEach Loop Container
Rafael Salas
Hery Susanto WR wrote:Thanks Ravi, it helped me so much, now I know what the "Traverse subfolders" is for :)
Is to get to the subfolders; in your case that is required as SSIS needs to go all branch_nm subfolders.
Hery Susanto WR wrote:
One more question Ravi, when I put the value into variable, can I get that value inside Script Component my plan is establish the flat file connection inside Script Component and get the data row by row using Synchronous method and push it into database. Is that a problem
You could; but the dataflow has already all that functionality built-in. Inside of the ForEach loop container, place a dataflow. Then in the dataflow, use a flat file source component and the required destination component. The trick is to use an expression in I have an example that shows how to do it for excel files. See an example here:
http://blogs.conchango.com/jamiethomson/archive/2005/05/30/1489.aspx
Re: Using Array Variable in ForEach Loop Container
Hery Susanto WR
Hi Rafael,
I've already used the method that you've explained but I can't control the datatype for each column, so I want to explore using Script Component. How can I get the sample for it, since Script Component have two method when we using it (Synchronous and Asynchronous).
Thanks in advance.
Hi Rafael,
I've already used the method that you've explained but I can't control the datatype for each column, so I want to explore using Script Component. How can I get the sample for it, since Script Component have two method when we using it (Synchronous and Asynchronous).
Thanks in advance.
Re: Using Array Variable in ForEach Loop Container
Ravi G
One more question Ravi, when I put the value into variable, can I get that value inside Script Component my plan is establish the flat file connection inside Script Component and get the data row by row using Synchronous method and push it into database. Is that a problem
Thanks in advance.
Yes you can. You can pass the variable to the script component.
Re: Using Array Variable in ForEach Loop Container
Ravi G
Hi Rafael,
I've already used the method that you've explained but I can't control the datatype for each column, so I want to explore using Script Component. How can I get the sample for it, since Script Component have two method when we using it (Synchronous and Asynchronous).
Thanks in advance.
Are you saying that each flat file has a different table structure
I agree with Rafael, the DFT has a lot of functionality built in it. Its better to use it rather than writing your own. See if you can do that.
Re: Using Array Variable in ForEach Loop Container
Hery Susanto WR
Hi Ravi,
Yup each flat file has a different structure.
I'm sure the built-in functionality within DFT has a great help but when we have to do a customization can we just depend on it
Hi Ravi,
Yup each flat file has a different structure.
I'm sure the built-in functionality within DFT has a great help but when we have to do a customization can we just depend on it
Re: Using Array Variable in ForEach Loop Container
Threko
A related, but "right on track" question.
I don't know what the folder of the files I want to work with runtime. Is it possible to make the Folder option dynamic through expressions If so, how
Regards,
Thomas
Re: Using Array Variable in ForEach Loop Container
Ravi G
Hi Ravi,
Yup each flat file has a different structure.
I'm sure the built-in functionality within DFT has a great help but when we have to do a customization can we just depend on it
If each file has a different structure, I guess you are forced to do something like what you are doing.
But see if you can group the flat files which have similar structure. You can use the DFT for each group if you have a small number of them.
Re: Using Array Variable in ForEach Loop Container
Ravi G
A related, but "right on track" question.
I don't know what the folder of the files I want to work with runtime. Is it possible to make the Folder option dynamic through expressions If so, how
Yes, you can. Its a little tricky though.
- Create a variable to store directory name
- Open the Foreach loop editor
- goto the "Collection" tab (do not go to the expressions tab...this is the tricky part)
- Select Foreach File Enumerator for Enumerator
- Expand Expressions and set the Directory property to the variable created.
That should do it. Let me know if it works.