Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Batch ETL of Multiple Data Files Using an SSIS Foreach Loop Container Expand / Collapse
Author
Message
Posted Thursday, June 13, 2013 11:25 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 4, 2014 9:37 AM
Points: 30, Visits: 39
Super, thanks, Stan
Post #1463218
Posted Thursday, June 13, 2013 1:51 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 4, 2014 9:37 AM
Points: 30, Visits: 39
Thanks, Stan. This looks very helpful.
Post #1463293
Posted Thursday, June 13, 2013 3:14 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, August 22, 2014 12:45 PM
Points: 77, Visits: 188
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.
Post #1463326
Posted Friday, June 14, 2013 7:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 14, 2013 7:16 AM
Points: 5, 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!!!
Post #1463552
Posted Friday, June 14, 2013 8:47 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 11:41 AM
Points: 814, Visits: 2,013
@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)




Post #1463621
Posted Friday, June 14, 2013 4:13 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 6:02 PM
Points: 499, Visits: 570
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.
Post #1463782
Posted Sunday, June 16, 2013 10:09 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, June 16, 2013 10:08 PM
Points: 4, 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/

Post #1463985
Posted Monday, June 17, 2013 6:40 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 11:41 AM
Points: 814, Visits: 2,013
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,



Post #1464116
Posted Monday, June 17, 2013 7:27 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, January 27, 2014 11:51 AM
Points: 30, Visits: 61
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).


  Post Attachments 
ForEach1.JPG (5 views, 64.46 KB)
ForEach2.JPG (5 views, 28.47 KB)
FileTask.JPG (5 views, 61.43 KB)
Post #1464138
Posted Monday, June 17, 2013 11:10 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 11:41 AM
Points: 814, Visits: 2,013
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.


Post #1464286
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse