UPDATE: it appears to work fine when the physical path to the file is replaced with a file share path (i.e. "\\machine\files\xxx.txt")
I recently posted the following question to the Microsoft Newsgroup. If you have any ideas please post a reply to the Newsgroup. THANKS
http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.dts&mid=61f557cb-37c7-4be1-91e5-960262653fdd
When executing a SSIS package I get the following error
The file name "D:\xxx.TXT" specified in the connection was not valid.
I am executing my package from a .NET Assembly using "Microsoft.SqlServer.Dts.Runtime"
C# excerpt
Application a = new Application();
Package p = a.LoadPackage("e:\xxx.dtsx", null);
DTSExecResult result = p.Execute();
My package does the following:
1) Executes a TSQL Statement to delete all the data from the table
2) Reads in records from a flat file on the disk (flat file source)
3) Uses Derived columns to add some information to the result set
4) Uses a OLE DB Destination to load that data into a table into SQL Server
The package is created on one computer and executed on another.
It uses integrated authentication to access the SQL Server, the nt account the application is running under has SA permissions in the sql server and is an admin on the windows server. The nt account that the SSIS service is running under is an admin on the windows server. Everyone has full control of the file. (I understand this is not the best thing from a security perspective, but I was trying to eliminate security as a cause of the package not being able to see the flat file on the disk.)