SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Batch ETL of Multiple Data Files Using an SSIS Foreach Loop Container


Batch ETL of Multiple Data Files Using an SSIS Foreach Loop Container

Author
Message
wholmes 69850
wholmes 69850
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 39
Super, thanks, Stan
wholmes 69850
wholmes 69850
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 39
Thanks, Stan. This looks very helpful.
Gerard Silveira
Gerard Silveira
SSC-Enthusiastic
SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)

Group: General Forum Members
Points: 136 Visits: 226
Thank you for this post, it helped me understand how the for each loop container writes into a variable , which is then subsequently used in the connection manager.
xxing29
xxing29
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 0
Welcome to http://www.likesurprise.com//

where is the most popular Panthers online shop.


((( http://www.likesurprise.com/ )))

lower price fast shippment with higher quality!!

WE ACCEPT CREDIT CARD /WESTERN UNION PAYMENT
YOU MUST NOT MISS IT!!!
RonKyle
RonKyle
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2706 Visits: 3498
@imz--I tried the task once again as I'm working on a project where I need to do that. Although the same variables that are used in the code are also used in the File Task, the error is part of path not found. This code executes without error:

    Public Sub Main()

Try
'Move File
File.Move(Dts.Variables("FullyQualifiedUnprocessedFileName").Value, Dts.Variables("FullyQualifiedProcessedFileName").Value)
Dts.TaskResult = ScriptResults.Success

Catch e As Exception

Dts.TaskResult = ScriptResults.Failure

End Try

End Sub


So I will stick with what works. The overwrite version of this would begin with:

    
If File.Exists(Dts.Variables("FullyQualifiedProcessedFileName").Value) Then File.Delete(Dts.Variables("FullyQualifiedProcessedFileName").Value)





msmithson
msmithson
Mr or Mrs. 500
Mr or Mrs. 500 (511 reputation)Mr or Mrs. 500 (511 reputation)Mr or Mrs. 500 (511 reputation)Mr or Mrs. 500 (511 reputation)Mr or Mrs. 500 (511 reputation)Mr or Mrs. 500 (511 reputation)Mr or Mrs. 500 (511 reputation)Mr or Mrs. 500 (511 reputation)

Group: General Forum Members
Points: 511 Visits: 578
imz (6/13/2013)
RonKyle (6/13/2013)
You can actually simplify it even more if you like, by using a File System Task, instead of a Script Task to move your processed files once done. That way you don't even need to write any code!


Having just spent the past several days developing imports for multiple csv and xls files, I would recommend that a script task would be best to do the move. The move file task seems to be buggy, at least with variables, and the suggested work around of using a copy followed by a delete affects file properties such as creation date.


I have used the File task with variable and never encountered a problem so I'm curious to know what issues have you had and in what scenario(s). Do you have some examples you could share?

Thanks in advance


File system task with a variable works just fine for me, not buggy at all.
femalemalls3
femalemalls3
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 0
Wonderful.

Share a website with you ,

( http://www.femalemalls.com/ )

Believe you will love it.

We accept any form of payment.


http://www.femalemalls.com/Michael-Kors-Handbags-n2447/
RonKyle
RonKyle
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2706 Visits: 3498
Would you be able to send a snapshot of the appropriate tabs on your file task system. I would be very interested to see it.

Thanks,



imz
imz
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 Visits: 62
Here are some screenshots of how I use the File Task.

First I have ForEach loop that will scan a folder looking for files matching a prefix. Here I'm using variables for folder path, file mask (ForEach1.jpg) and to capture the names of each file found in another variable (ForaEach2.jpg). Inside the ForEach loop I have the File Task that will move a file using the "Move File" operation (FileTask1.jpg). You could also use the "Rename File" to move a file if you need to rename the file as you move it (e.g. add a time stamp to the file name).
Attachments
ForEach1.JPG (11 views, 64.00 KB)
ForEach2.JPG (12 views, 28.00 KB)
FileTask.JPG (13 views, 61.00 KB)
RonKyle
RonKyle
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2706 Visits: 3498
I'm really puzzled by this as that's exactly how I had it set up. The first two steps are still that way since w/o that the code wouldn't work as well. It's very odd that the same variables that don't execute using the File Task work fine as code. The file is getting renamed, so maybe I will try it again using the rename even though the move function works in code. I'll post the results one way or the other.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search