loop through the following files using For Each Loop
File Names
05032012SSHIPHDR12.IMP
05032012SSHIPHDR11.IMP
05072012SSHIPHDR12.IMP
05102012SSHIPDETAIL12.IMP
05072012SSHIPDETAIL11.IMP
05102012SSHIPDETAIL12.IMP
05102012SSHIPHDR12.IMP
How to pick just "SSHIPHDR" files from above files and combine them to one file, i used For each loop to accomplish this and in Data Flow -- FLat File Source ---> table.
my question is how to give Wild card search for above files.(I dont want to use Script Task).
i am accessing above files thgrough table and putting them in resulting set in a variable, but i am stuck how to give wild card search for above files.
June 12th, 2012 2:17pm
To pickup only SSHIPHDR use the mask *SSHIPHDR*.imp in the the "Files:" text boxArthur My Blog
Free Windows Admin Tool Kit Click here and download it now
June 12th, 2012 2:26pm
You can use a foreach loop container with a filemask of *SSHIPHDR*.IMP
Same way you would do it from a DOS promptChuck Pedretti | Magenic North Region | magenic.com
June 12th, 2012 2:27pm
i didnt get u.... on this part "Files:" text box
Free Windows Admin Tool Kit Click here and download it now
June 12th, 2012 2:43pm
i used for each ADO enumerator and storing into variable
in table i updated as follows:
F:\Veeva\Shipments\StagingSubfolder\PG\*SSHIPHDR*.IMP
F:\Veeva\Shipments\StagingSubfolder\SA\*SSHIPHDR*.IMP
F:\Veeva\Shipments\StagingSubfolder\ST\*SSHIPHDR*.IMP
F:\Veeva\Shipments\StagingSubfolder\PC\*SSHIPHDR*.IMP
I got the following error
[Flat File Source [31]] Error: Cannot open the datafile "F:\Veeva\Shipments\StagingSubfolder\PG\*SSHIPHDR*.IMP".
[DTS.Pipeline] Error: component "Flat File Source" (31) failed the pre-execute phase and returned error code 0xC020200E.
June 12th, 2012 2:47pm
Use a file enumerator
Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
June 12th, 2012 2:49pm
i cannot hard code...i have to use Variables and Expressions...
i have 4 vendor location where i need to pick the files...
June 12th, 2012 2:52pm
mask *SSHIPHDR*.imp and it becomes possible to pick anyhow named file as long as it has the SSH... string in it
The location sure can be parametrized - just go to variable mappings, create a new variable and assign it an espression, make sure you set its EvalueteAsExpression property to true.Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
June 12th, 2012 2:55pm
Then just use an expression to populate the Files propertyChuck Pedretti | Magenic North Region | magenic.com
June 12th, 2012 2:56pm
1st variable to capture result set
2nd variable for mapping
My question is under expression what property i need to select and assign the varible.
Free Windows Admin Tool Kit Click here and download it now
June 12th, 2012 3:13pm
I understood now you capture the paths from a table is that right?
Then you need to implement shredding a recordset: http://www.sqlis.com/post/Shredding-a-Recordset.aspxArthur My Blog
June 12th, 2012 3:15pm
To do what you are trying to do, you need a loop within a loop. The outer loop is the one you have defined. Then inside that loop you use a foreach File and set the Directory and FileSpec property on that. SO you would need to split up
your ADO field into the Folder portion and the filespec portion
Chuck Pedretti | Magenic North Region | magenic.com
Free Windows Admin Tool Kit Click here and download it now
June 12th, 2012 3:17pm
i did that part Arthur, but i am stuck i picking the files from the Locations...
June 12th, 2012 3:21pm
i will assign the @[User::Staging_Subfolder] variable to Directory
Expression
what about File Spec how should i use it.....
Free Windows Admin Tool Kit Click here and download it now
June 12th, 2012 3:35pm
i will assign the @[User::Staging_Subfolder] variable to Directory
Expression
what about File Spec how should i use it.....
Using the example data: F:\Veeva\Shipments\StagingSubfolder\PG\*SSHIPHDR*.IMP
Directory should contain F:\Veeva\Shipments\StagingSubfolder\PG\
Filespec should contain *SSHIPHDR*.IMPChuck Pedretti | Magenic North Region | magenic.com
June 12th, 2012 3:37pm
FileSpec is the mask, or whatever you get from the db a a file nameArthur My Blog
Free Windows Admin Tool Kit Click here and download it now
June 12th, 2012 3:38pm
i am getting following error mssg:
[Flat File Source [31]] Error: Cannot open the datafile "F:\Veeva\Shipments\StagingSubfolder\PG".
[DTS.Pipeline] Error: component "Flat File Source" (31) failed the pre-execute phase and returned error code 0xC020200E.
i have full access to files, this is showing up again and again as i am running it locally
June 12th, 2012 3:40pm
Did you set up your foreach File loop to populate a variable with the file it is looping and then use that variable for an expression for ConnectionString in your Flat File Connection manager?
Look here for a tutorial on how to set it up
http://www.sqlis.com/sqlis/post/Looping-over-files-with-the-Foreach-Loop.aspx
Chuck Pedretti | Magenic North Region | magenic.com
Free Windows Admin Tool Kit Click here and download it now
June 12th, 2012 3:44pm
i am getting following error mssg:
[Flat File Source [31]] Error: Cannot open the datafile "F:\Veeva\Shipments\StagingSubfolder\PG".
[DTS.Pipeline] Error: component "Flat File Source" (31) failed the pre-execute phase and returned error code 0xC020200E.
i have full access to files, this is showing up again and again as i am running it locally
This does not constitute a file name, only the path.
Review and make sure the expressions used work to construct the full path.Arthur My Blog
June 12th, 2012 3:45pm
i did exactly Same....is that error is due to Access issue or may be i am wrong....
but i did copy paste the path that is full qualified path.
Free Windows Admin Tool Kit Click here and download it now
June 12th, 2012 3:50pm
It is not the permission error.
The end result of the whole is not a valid path to file, no file portion at all.
I suggest you set a breakpoint to each component then inspect the variables for their values.
You will find probably a missing part to make the file name.Arthur My Blog
June 12th, 2012 3:59pm
i fixed it, but wild card entry which it is getting from table...it says could not find the file....
i think if i fix the file pattern or wild card pattern it will solve the problem...
i dont know whether the supplied pattern " *SSHIPHDR*.IMP"
is not correct
any thoughts....
Free Windows Admin Tool Kit Click here and download it now
June 12th, 2012 4:16pm
Only you know if the wildcard pattern is correct. Open up a command prompt in the directory you are using and do a DIR *SSHIPHDR*.IMP to see if you get any results. Or just look at the files and see if *SSHIPHDR*.IMP actually matches
the naming of the files in the directory.
Chuck Pedretti | Magenic North Region | magenic.com
June 12th, 2012 4:18pm
i tried on Command Prompt it is showing the Files
Free Windows Admin Tool Kit Click here and download it now
June 12th, 2012 4:35pm
Then you have something set up wrong in the package - follow through the tutorial I linked to and figure out what it is.Chuck Pedretti | Magenic North Region | magenic.com
June 12th, 2012 4:37pm
Thanks alot Chuck and Arthur....you guys are awesome....i fixed it....
Free Windows Admin Tool Kit Click here and download it now
June 13th, 2012 9:27am
Thanks alot Chuck and Arthur....you guys are awesome....i fixed it....
June 14th, 2012 9:26am