November 6, 2014 at 11:16 am
One of our BI devs is working on a parent-child package setup. We use a File System setup in SSIS instead of storing the packages in MSDB. So when we call the package from a job (using dtexec.exe in an Operating System job step), generally we use the path "\File System\My Folder\My Package.dtsx".
The Execute Package task has options File system and SQL Server. I'm pretty sure he should be using File system, but he can't seem to get the file connection manager working for this. Currently, he's using \\Server\File System\My Folder\My Package.dtsx" but is getting errors trying to reach the server. I thought it was his permissions, but I'm sysadmin and I'm getting the same errors.
How should we be setting up the connection manager to reach integration services File System?
Or are we using the wrong option? Should we be using SQL Server? (Which I also can't seem to get to work).
Advice is appreciated.
November 7, 2014 at 5:36 am
I am not asking a question about permissions. I am asking about how to set up the connection manager.
November 7, 2014 at 6:00 am
Stewart, I have been a DBA for over 10 years. Please give me a little credit for knowing the basics.
Can we please get to the question I am asking?
EDIT: And BTW, I already mentioned what permissions I have in the first post.
November 7, 2014 at 6:19 am
Does it already fail in BIDS?
And what are the exact errors/warnings that you encounter?
Is the package stored on the machine itself, or on a UNC path?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 7, 2014 at 6:35 am
Koen Verbeeck (11/7/2014)
Does it already fail in BIDS?
Yes. In fact, I haven't gotten past BIDS to running it from the GUI or a job. What I'm doing is running the parent in BIDS and trying to connect to the child on the APP server.
And what are the exact errors/warnings that you encounter?
When I edit the connection manager I get "File specified does not exist". When I try to run the step anyway (despite this error), I get the generic "Make sure the file exists and you have permission to access it."
Because I know (without doubt) that I have all the necessary permissions, plus the fact that the connection manager says the file doesn't exist, I am sure that the connection manager / Execute Package task are set up wrong. I've never used this task before, so I'm also sure I gave bad info to the Dev when I grabbed what we use out of a SQL Job and gave him that info to use in his connection manager.
Is the package stored on the machine itself, or on a UNC path?
The package is stored on the machine itself under D:\SQLServer\MSSQL10\100\DTS\Packages\My Folder\. In Integration Services (the GUI), this translates as Stored Packages\File System\My Folder\.
So would this be the issue? I'm using the wrong path naming convention? It never occurred to me to use the literal server path. I've been trying what I see in the GUI and what I see in our OS Command job steps as the path.
November 7, 2014 at 6:39 am
Brandie Tarvin (11/7/2014)
Koen Verbeeck (11/7/2014)
Is the package stored on the machine itself, or on a UNC path?
The package is stored on the machine itself under D:\SQLServer\MSSQL10\100\DTS\Packages\My Folder\. In Integration Services (the GUI), this translates as Stored Packages\File System\My Folder\.
So would this be the issue? I'm using the wrong path naming convention? It never occurred to me to use the literal server path. I've been trying what I see in the GUI and what I see in our OS Command job steps as the path.
GAH!!!! That was exactly the issue!
Using the wrong damn path. I have to be literal about it and use the D$ share info.
<headdesk>
EDIT: Sigh. Sometimes one just needs to explain it out to see what one is doing wrong. Thanks.
November 7, 2014 at 6:46 am
Ah, so not a permission issue after all 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 7, 2014 at 7:02 am
Koen Verbeeck (11/7/2014)
Ah, so not a permission issue after all 😀
I knew it wasn't. I just couldn't figure out how to create the correct connection manager. Now I'm kicking myself for wasting a day researching this task. Nobody is clear on any of their articles when they say "relative paths can't be used". Seriously, I was about to scream because that was the only comment (with no explanation of what they meant by "literal") that I could find out of 15 different articles.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply