June 15, 2011 at 5:49 am
Hello all,
What I'm trying to do is:
-Grab info from a flatfile that is dumped by an external program with the naming convention "carrtrack[date][timestamp].txt"
---I only want to grab data from the file with yesterday's date
-copy this data to a sql table
What I've done:
-Created a For Each Loop container with a Data Flow Task inside
-Created a variable and linked it to the FileSpec of the ForEach Loop
-In the Data Flow I have a Flat File Source pointing to an OLE DB Destination
At this point, it seems that I'm extremely close, but I get this error:
Error: 0xC020200E at Import Daily Delivery Tracking - Data Flow Task, Flat File Source [1]: Cannot open the datafile "carrtrack613201175235 .txt".
It seems to find the right file, but notice there's a space right before the .txt, and I'm assuming why it's saying that it cannot find the file.
Here's my fileSpec variable:
"carrtrack" + RIGHT((DT_WSTR,2)MONTH(DATEADD("DD",-1,GETDATE())),2) + RIGHT("0" + (DT_WSTR,2)DAY(DATEADD("d",-1,GETDATE())),2) +
(DT_WSTR,4)YEAR(GETDATE())+"*.txt"
June 15, 2011 at 6:05 am
You could set up the SSIS task to use a for-each loop take data from a set location (wherever the file is dumped to) on a regular basis.
After processing is complete, the file(s) in that location could be moved to say an Archive folder.
That way, the SSIS takes care of cleaning up the live file each time it is processed, by moving it elsewhere, so that next time the SSIS routine is run, it only picks up the file(s) in the set location.
HTH.
---------------------------------------------
If data can screw you, it will; never assume the data are correct.
June 15, 2011 at 6:30 am
Thanks for the reply, Kevin. I'll consider that option. Although at this point I'd like to finish in the direction that I started instead of starting a new route.
I'm very new to SSIS and even the most basic tasks take some time for me to grasp them.
June 15, 2011 at 2:02 pm
I'm still working through this and haven't figured it out yet. I wonder if because I'm using a wildcard(*) that it somehow adds a space where the wildcard sits in the Expression. If that is the case, can I somehow trim my expression to remove the space?
June 15, 2011 at 3:06 pm
Your mask will find files with that name. Did you check to see if the file on disk actually has a space in it?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 16, 2011 at 1:48 pm
Ah, you're right. My file DOES have a space before the .txt. I feel pretty stupid after missing that.
When I run the task, this is what I get:
SSIS package "MalvernShipmentAndDelivery.dtsx" starting.
Information: 0x4004300A at Import Daily Delivery Tracking - Data Flow Task, SSIS.Pipeline: Validation phase is beginning.
Information: 0x40043006 at Import Daily Delivery Tracking - Data Flow Task, SSIS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Import Daily Delivery Tracking - Data Flow Task, SSIS.Pipeline: Pre-Execute phase is beginning.
Information: 0x402090DC at Import Daily Delivery Tracking - Data Flow Task, Flat File Source [1]: The processing of file "carrtrack615201174242 .txt" has started.
Warning: 0x80070002 at Import Daily Delivery Tracking - Data Flow Task, Flat File Source [1]: The system cannot find the file specified.
Error: 0xC020200E at Import Daily Delivery Tracking - Data Flow Task, Flat File Source [1]: Cannot open the datafile "carrtrack615201174242 .txt".
Error: 0xC004701A at Import Daily Delivery Tracking - Data Flow Task, SSIS.Pipeline: component "Flat File Source" (1) failed the pre-execute phase and returned error code 0xC020200E.
Information: 0x402090DD at Import Daily Delivery Tracking - Data Flow Task, Flat File Source [1]: The processing of file "carrtrack615201174242 .txt" has ended.
Information: 0x4004300B at Import Daily Delivery Tracking - Data Flow Task, SSIS.Pipeline: "component "OLE DB Destination" (92)" wrote 0 rows.
Information: 0x40043009 at Import Daily Delivery Tracking - Data Flow Task, SSIS.Pipeline: Cleanup phase is beginning.
Task failed: Import Daily Delivery Tracking - Data Flow Task
Warning: 0x80019002 at Foreach Loop Container: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "MalvernShipmentAndDelivery.dtsx" finished: Failure.
Regarding the first warning, I verified the file path and the file does exist. It's a strange warning anyway, since the next line shows the exact name of the file.
I verified the file name and it is exactly as displayed in the error above.
June 16, 2011 at 2:12 pm
No sweat...besides I don't think that's the root issue, I just wanted to confirm the mask was working, which it sounds like it is. SSIS can handle files with spaces in it just fine. I don't see the file path in your error message...in your Foreach Loop Editor what do you have for the "Retrieve file name" setting?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 16, 2011 at 2:52 pm
Under Collections Tab:
Expressions:
-Directory: @[User::TRACKLOCATION] (variable that holds the folder location, but you probably already knew that)
-FileSpec: "carrtrack" +
RIGHT((DT_WSTR,2)MONTH(DATEADD("DD",-1,GETDATE())),2) + RIGHT("0" + (DT_WSTR,2)DAY(DATEADD("d",-1,GETDATE())),2) +
(DT_WSTR,4)YEAR(GETDATE())+"*.txt"
Under Enumerator Configuration:
Folder: \\asmserver01\ClientApps\MalvernData\mlscripts\POD_ArchivesFiles: carrtrack6152011*.txt
Under Variable Mappings:
Variable: FileFound
June 16, 2011 at 2:59 pm
Under Collection what do you have for the "Retrieve file name" setting?
> Fully qualified (you want to select this one)
> Name only
> Name and extension
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 17, 2011 at 1:04 pm
That's it!
I chose Fully Qualified and got it working. I previously had Name and Extension checked.
Thanks a million.
June 17, 2011 at 1:17 pm
jason.spamalicious (6/17/2011)
Thanks a million.
Awesome 😎
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply