December 31, 2009 at 10:13 am
I am creating a simple Flat File to Table package.
No special activity.
Just a Flat file connection to a sql table connection.
I have no problem running the package from anywhere using the mapped drive letter.
But when I use the UNC it fails period. It does not matter where I run it from.
When I am in SSIS editing the package. I go into the Flat File Connection Manager
and I type in the UNC it says I must specify a valid file.
IT IS A VALID FILE.
WHY DO I HAVE TO USE THE MAPPED DRIVE? WHY CAN I NOT USE THE UNC?
I use the exact same UNC with excel files and it works just fine.
Why NOT THE FLAT FILE? WHAT AM I MISSING?
Ahhrrggg!!!
Execution messages follow:
Information: 0x402090DC at Load Raw Data, Flat File Source [1294]: The processing of file "\\hal\QlikViewDocs$\Analytix\Circulation\SubscriberWriteOffs\SubscriberWriteOffs.txt" has started.
Warning: 0x80070002 at Load Raw Data, Flat File Source [1294]: The system cannot find the file specified.
Error: 0xC020200E at Load Raw Data, Flat File Source [1294]: Cannot open the datafile "\\hal\QlikViewDocs$\Analytix\Circulation\SubscriberWriteOffs\SubscriberWriteOffs.txt".
Error: 0xC004701A at Load Raw Data, DTS.Pipeline: component "Flat File Source" (1294) failed the pre-execute phase and returned error code 0xC020200E.
Information: 0x402090DD at Load Raw Data, Flat File Source [1294]: The processing of file "\\hal\QlikViewDocs$\Analytix\Circulation\SubscriberWriteOffs\SubscriberWriteOffs.txt" has ended.
Information: 0x40043009 at Load Raw Data, DTS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300B at Load Raw Data, DTS.Pipeline: "component "OLE DB Destination" (1363)" wrote 0 rows.
Task failed: Load Raw Data
Warning: 0x80019002 at SubscriberWriteOffsLoad: 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 "SubscriberWriteOffsLoad.dtsx" finished: Failure.
December 31, 2009 at 11:01 am
I'm just taking a shot in the dark with this but does this link help you?
http://blogs.perkinsconsulting.com/?tag=/cannot-open-the-datafile
December 31, 2009 at 11:23 am
Nope.
Any other thoughts folks?
Why can I not use a UNC in a Flat File Connection Manager?
The
UNC I am using is --> \\hal\QlikViewDocs$\Analytix\Circulation\SubscriberWriteOffs\SubscriberWriteOffs.txt
I've used an expression to set the connection property and I've hardcoded it.
I've changed it at runtime too. Perhaps it's the '$' in the path? How do I "escape" that character?
I get the following error message when running from file system OR in debug mode.
Error: 0xC001401E at SubscriberWriteOffsLoad, Connection manager "SubscriberWriteOffs Text File": The file name "\\hal\QlikViewDocs$\Analytix\Circulation\SubscriberWriteOffs\SubscriberWriteOffs.txt" specified in the connection was not valid.
Error: 0xC0202070 at SubscriberWriteOffsLoad, Connection manager "SubscriberWriteOffs Text File": The file name property is not valid. The file name is a device or contains invalid characters.
December 31, 2009 at 11:40 am
By the way.
I have no problem using File System tasks in the same package to move the file and rename the file using the UNC in variables.
It is only the Flat File Connection Manager that has a problem.
I used an Excel File Connection manager and it has no problem with the UNC either.
So what's the deal with the Flat File Connection Manager?
HELP!!!!!
December 31, 2009 at 12:16 pm
dugsmith (12/31/2009)
I've used an expression to set the connection property and I've hardcoded it.
I've changed it at runtime too. Perhaps it's the '$' in the path? How do I "escape" that character?
I was thinking the same thing about the '$' in the path. Couldn't you temporarily share the Analytix or Circulation directory to skip the part with the '$' as a test?...so that the path could be
\\hal\Analytix\Circulation\SubscriberWriteOffs\SubscriberWriteOffs.txt
...or...
\\hal\Circulation\SubscriberWriteOffs\SubscriberWriteOffs.txt
Seems like that would tell you if the '$' was the culprit.
December 31, 2009 at 1:22 pm
I will have to give that a try.
Unfortunately I am just contracting my services here and I do not have that level of permissions on the systems and network. So I am at someone elses mercy on it.
I'll let you know what happens.
February 2, 2010 at 2:51 pm
Hi all:
I was facing the same trouble with the Flat File Connection when there is a '$' in the UNC path (\\XYZ\DEVDATA$\CMR\QAC\Input\Data\)
So after a while searching for something a decide to map a network drive using command line to create it:
NET USE Z: \\XYZ\DEVDATA$\CMR\QAC\Input\Data\ /USER:MyDomain\testuser /PERSISTENT:YES
The use of the USER option is because the mapping must be done using the security context of the SQL Server Agent process
After the work is done is highly recommended to delete the mapping. So I use:
NET USE Z: /DELETE
This pair of command line sentences must be included inside the SSIS package.
It works perfectly for me, so I hope it work for you.
Regards,
Carlos David Araque G
MCP, MCTS
BI Solution Consultant
----------------------------------------------------------------------------------------------------------------------
"Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere!!!"
February 2, 2010 at 3:25 pm
That is a great way to handle it.
Although I must say that I recently was able to test with the client network admin and indeed it was a permissions issue for both my username through BIDS and the sql server agent credentials also did not have correct permissions to the share.
November 19, 2010 at 2:28 pm
1. make sure sql agent is running under a domain account
2. create a share - and assign permissions accordingly for the sql agent account
point your package to the UNC path with the share:
\\myserver\myshare
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply