July 28, 2009 at 7:40 am
Hello all...Can someone please give me some idea what in the world is going on...I will try to be as detailed as I can be...But here it goes...
I have an SSIS package that truncates two tables then takes two flat files, does some conversions on one of the files, then loads the two files into their respective tables...I have tested this on my local pc and it works GREAT...However when I put the package on the test server...it fails everytime saying it cannot find the path to those two files...I have checked with our system admins who run the servers and he has seconded that I have entered the correct file path to the two flat files...But here are the errors it keeps giving me:
Warning: 0x80070003 at Data Flow Task, Patient Data [1]: The system cannot find the path specified.
Error: 0xC020200E at Data Flow Task, Patient Data [1]: Cannot open the datafile "F:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\ImportRepository\wicgcdemo".
Error: 0xC004701A at Data Flow Task, DTS.Pipeline: component "Patient Data" (1) failed the pre-execute phase and returned error code 0xC020200E.
What is going on here? How come it works fine on my local but when I try running this on the test server and I even tried running on the production server but again I keep getting this error? When me and my colleague were discussing this matter, he mentioned that he had trouble setting up a DTS package - that he had created on his local and it worked fine - on a server and actually had to go to that actual server and set it up manually on the server? Could the same thing apply here? Any help on this will be greatly appreciated as I am stuck in this rut...The sys admin says he doesnt know whats going on now b/c the file path is correct and he doesnt know anything about SQL Server 05! Thanks in advance to those who reply!
July 28, 2009 at 7:54 am
2 things I would check, first make sure the job is running under an account with the proper security and second use a UNC path just to be sure. If you run this locally and specify a mapped drive, that same mapped drive may not be available to the service or proxy account.
July 28, 2009 at 2:21 pm
1. F:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\ImportRepository\wicgcdemo".
this path is on your local machine not your test servers.
2.1 copy the flat files to a drive on the test server and reconfigure your ssis to that drive path.
2.2 use an unc path on the test server and drop your flat files there but be sure your ssis pkg is looking at that location.
jope this helps.
🙂
MCSE SQL Server 2012\2014\2016
July 29, 2009 at 8:35 am
Yeah that path is the correct path that is on the test server...At first I was using the path on my local then realized it and changed to the path that flat files are on the test servers! We think we are going to have to just go to that test server physically and create the package on the server manually! We dont know what else to do!
July 29, 2009 at 9:58 am
did you verify the security that the job runs under?
July 29, 2009 at 10:03 am
That is one of our questions we are waiting to get answered! Because we know to be able to access that path that the files are under, you have to have a username/password to be able to reach those paths...So we are waiting to get that answer!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply