September 14, 2009 at 11:59 am
We're using variables to house our connection strings.
For XLS, my string works fine and looks like this:
---> Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\myserver\myFile.XLS;Extended Properties="EXCEL 8.0;HDR=YES";
Does anyone have a sample connect string to accomdate a FLAT FILE (text file) connection?
For my FLAT FILE connection, I tried this but received an error msg from SSIS at runtime:
---> Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\myserver\myFile.TXT;Extended Properties="Text;HDR=YES;FMT=Delimited";
Notes: the file defintely exists and the context which I'm running w/ has full permissions on the DIR..
and ironically, the Pkg runs fine locally in the VS 2008 IDE AND runs fine on the SQL Server SSIS File System if I right-mouse click and execute. It does not run when I run it via DTEXEC. (though I'm running dozens of ther pkg's successfully using DTEXEC)
September 15, 2009 at 6:52 am
Bewildering.. this 2008 SSIS package, executes successfully on 64-bit by remote desk topping onto the SQL Server and right mouse clicking/executing the pkg from the SSIS file system.
When I run it thru the DTEXEC, I get:
Source: My_FLAT_FILE Connection manager "TXTFile"
Description: The file name property is not valid. The file name is a device or contains invalid characters.
Here is my DTExec statement...
dtexec /f \\MySQLserver\PkgDir\MySSISpkg.dtsx /SET \package.variables[pActiveDate].Value;"\"20090910\""
IN the pkg, pActiveDate is a variable value concatenate onto the file name - rendering a valid file name.
I don't believe the ESCAPE character is causing an issue.. (we need this to make other pkg's operate)
September 15, 2009 at 7:38 am
It's a permissions issue. Verify that the SQL Service account has access to that directory.
September 18, 2009 at 6:58 am
yes, this was a permissions issue. But a buried one. The SQL Server service account had R/W access to the file the SSIS pkg was looking to use as inout:
T:\MyDir1\SubDir2\SubDir3\and all files in here.txt
the SQL Server service Acct gets "Access Denied" on:
T:\MyDir1\SubDir2
Our Windows folks modified the permission here.. and it worked?? Hokey
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply