August 19, 2009 at 11:50 am
Having an issue using a TEXT file as my data source in an SSIS pkg… I've coded 40+ SSIS pkg's (SQL 2008) using Excel, Oracle, and Sybase as a data source with no problem whatsoever.
My Error:
Error: 2009-08-19 12:44:07.79
Code: 0xC0202070
Source: MY_SSIS_Pkg Connection manager "TXTFile"
Description: The file name property is not valid. The file name is a device or contains invalid characters.
End Error
The sample file name I want to use as my data source is: \\MyServer\MySubDir\MyFile20090820.TXT
My TXTFile Connection is dynamically generated as an Expression using 4 variables concatenated together:
@[User::BaseFolder] + @[User::BaseTypeFolder] + @[User::BaseFile] + @[User::pActiveDate] + ".TXT"
Where:
@[User::BaseFolder] = \\MyServer\
@[User::BaseTypeFolder] = MySubDir\
@[User::BaseFile] = MyFile
@[User::pActiveDate] = 20090820
+ ".TXT" = .TXT
This process works fine when sourcing Excel files, Oracle, SQL, and Sybase tables. NOTE: My Excel string also includes the variable to accommodate DataSource=xxxx, Provider=xxxx etc.. The flat file connection does not. (Do I need this for a flat file connection?)
To debug, I added a SQL task to capture the full string just prior to the Data Flow and write to a JUNK SQL table.. The String looks perfect.. When I copy the string into Windows Explorer I get to the file - no problem.
When I run the pkg from Visual Studio or directly from the SSIS File System, it runs fun and loads table (the pkg consumes the Variable value defined w/ the pkg). When I run the pkg from DTEXEC, passing the variables in, it fails (yet the rendered string from the passed variables looks fine).. My other 40 pkg's run fine from DTEXEC passing in the variables.
I have local admin on the SQL Server. I have full rights to the file system path. (so does the SQL Server Service account)
Do I need an escape character in the path name somewhere? This one is bewildering.
August 19, 2009 at 12:16 pm
Here is an additional error
Error: 2009-08-19 13:11:04.09
Code: 0xC001401E
Source: MY_SSIS_Pkg Connection manager "TXTFile"
Description: The file name "\\MyServer\MySubDir\MyFile20090820.TXT" specified in the connection was not valid.
End Error
August 20, 2009 at 11:49 pm
Hi BT
Did you get this one sorted? The only thing I can think of is that the DTEXEC command line syntax might be incorrect, could you post it here?
----------------------------------------------------------------------------------------------
Struggling to find the question?
http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
August 21, 2009 at 5:15 am
Actually - I'm using a Stored Proc to invoke DTEXEC for upwards of 40+ SSIS pkg's.. and it's running fine. The only difference is that for the first time, this failing pkg is trying to reference a flat file (CSV) .. all the other pkg's reference inputs such as SQL Server tbles, Oracle tables, Sybase tables, and Excel spreadsheets and they all seem to execute fine..
August 21, 2009 at 7:25 am
When I run the pkg from Visual Studio or directly from the SSIS File System, it runs fun and loads table (the pkg consumes the Variable value defined w/ the pkg). When I run the pkg from DTEXEC, passing the variables in, it fails (yet the rendered string from the passed variables looks fine)..
This does sound like a permissions issue. If it runs fine under your logon but not with an xp_cmdshell call to DTEXEC, then the SQL Service account is the logon that is causing the problem.
Have you tried passing in a filepath that you know the service account has permissions to. Somewhere like local C:\TestFolder\TestFileName.csv
Also, are you writing to a share or the physical path? Does the service account have full permissions on the directory? You could test service account permissions using xp_cmdshell too.
-- Copy a file
master..xp_cmdshell 'XCOPY "\\MySourceServer\MySubDir\MyFileName.txt" "\\MyTargetServer\MySubDir\MyNewFileName.txt"'
This should tell you if the service account does indeed have the correct permissions.
Failing that, could there be another process locking the file at the time?
Are you using mapped drives that don't exist for the service account?
You could also check if its the configuration that's causing the problem... How is package being configured? What if you save the variable values for the filepath at design time and don't pass in anything at run time. Does it run correctly then? Perhaps it's the way that your stored proc is passing in the variables?
Good luck
Kindest Regards,
Frank Bazan
August 21, 2009 at 9:15 am
thanks Frank.. You led me down the right path. My problem was a permissions issue.
-- When I ran the pkg from Visual Studio IDE, I was running under my personal Windows login - and it worked.
- When I logged onto the SQL Server w/ my personal Windows account and ran the SSIS pkg (stored in the file system) - it worked.
-- When I logged on to the SQL Server as the SSIS Service Account owner it failed. SQL Server rendered a very BOGUS error message saying "Description: The file name \\MyServer\Myfile…" specified in the connection was not valid."
The file name was perfectly valid.. BUT the Windows account I was using (owner of the SSIS Service) did NOT have permissions to the Directory!!!
thanks again Frank -
bt
August 26, 2009 at 2:24 am
No problem bt... thanks for posting the resolution too. It's always helpful to all to know the outcome.
Edit: Apologies for the double post, my browser crashed when I hit the post button and I can't find a delete post button.
Kindest Regards,
Frank Bazan
August 26, 2009 at 2:27 am
No problem bt... thanks for posting the resolution too. It's always helpful to all to know the outcome.
Cheers
Kindest Regards,
Frank Bazan
March 23, 2012 at 12:07 am
This was removed by the editor as SPAM
July 29, 2013 at 6:32 am
It's definitely folder permission issue.
Please give proper rights for everyone.
Alpesh Dhori
Software Professional
April 21, 2024 at 5:22 pm
This was removed by the editor as SPAM
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply