January 6, 2015 at 10:51 pm
Hi all,
I have a question on SSIS 2008 about Foreach Loop.
The following is the file location structure.
[System date: 20150107]
D:\Data\AC001\20150106\Target.dbf
D:\Data\AC001\20150105\Target.dbf
D:\Data\AC001\ ...
D:\Data\AC002\20150106\Target.dbf
D:\Data\AC002\20150105\Target.dbf
D:\Data\AC002\...
D:\Data\AC003\20150106\Target.dbf
D:\Data\AC003\20150105\Target.dbf
D:\Data\AC003\...
----------------------------------------------------
Every day, there was another job which create a new folder with folder name YYYYMMDD -1 and store the files in it.
There are around hundred folders with prefix AC000 and around 365 subfolders with name YYYYMMDD
I have learn how to import all Target.dbf files in all subfolders. But now there is a request on only capture the latest Target.dbf files.
i.e. assume today is 20150107, I need to capture the Target.dbf files in all 20150106 subfolders.
I would like to know how to set the Foreach loop to import the Target.dbf file form the newest folder [YYYYMMDD -1].
January 7, 2015 at 12:57 am
Personally I'd do this with .NET in a script task.
Or you could just loop over everything and check if the file you need with a script task. If it is a recent file, you import this in a data flow.You can do this using an expression on the precedence constraint between the script task and the data flow.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 7, 2015 at 1:00 am
The best way to do this, IMO, is to move processed files/folders to an Archive folder somewhere.
Then your FEL can remain as it is, picking up everything when it runs and then moving to 'processed'.
Your proposed method can run into difficulties as soon as there is a problem.
Eg, what happens when the job fails for a couple of days? You've configured it to pick up only (today - 1), now you have to hack it to pick up (today - x) and then remember to 'unhack' it.
Keep it simple.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 7, 2015 at 6:04 pm
You could go the way of variable expressions here with a data flow within the for loop container. Unless I am missing something you could:
variable,
type,
expression
-----------------------------------
StaticFolderNum,
int32,
no expression
AC000,
string,
"AC"+ REPLICATE( "0",3- len( (DT_WSTR,3) @[User::StaticFolderNum] )) + (DT_WSTR,3) @[User::StaticFolderNum]
TargetDateFolder,
string,
(DT_WSTR,4)YEAR(dateadd("d",-1,getdate()))+
REPLICATE ("0", 2-len( (DT_WSTR,2)Month(dateadd("d",-1,getdate()))) ) +(DT_WSTR,2)MONTH(dateadd("d",-1,getdate()) )+
REPLICATE ("0", 2-len( (DT_WSTR,2)DAY(dateadd("d",-1,getdate()))) ) +(DT_WSTR,2)DAY(dateadd("d",-1,getdate()) )
FullPath,
string,
"D:\\Data\\"+ @[User::AC000]+" \\" +@[User::TargetDateFolder] +"\\Target.dbf"
------------------------------------------
You could add the full path to the connection string property of the file connection as a expression using the FullPath variable.
Then just use this connection in the flat file source in the df (inside the for loop container) .Each time the loop goes through set the variable StaticFolderNum to increase by one. (I know , not the best name for it). Each day you will gather the file from each of the corresponding 100 folders. But as mentioned , there are better solutions because this way is not forgiving when you miss the files for a month and no one knows about it until way later.
----------------------------------------------------
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply