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

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics