March 8, 2011 at 2:15 pm
If you want to simplify having an SSIS package move data between domains (local server vs different Windows domain), it's sometimes easiest to set up FTP on the target server/domain, and just upload to that.
If you set up a network drive, that will hold onto the credentials used to create it, and then you can access that using a UNC path. I've done that with robocopy between domains, where I had to use local accounts on both ends of the transfer.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 9, 2011 at 12:19 pm
How do I do either of those?
Thanks!
March 9, 2011 at 12:21 pm
Talk to your server / network admin. They can help you set those options up.
March 9, 2011 at 1:02 pm
Is there a way to simply test whether this is truly a permission issues?
I just think I have tried so many things and changed by package so much can I go back to the basics and test if this is the issue?
Thanks!
March 10, 2011 at 12:01 am
smac (3/9/2011)
Is there a way to simply test whether this is truly a permission issues?I just think I have tried so many things and changed by package so much can I go back to the basics and test if this is the issue?
Thanks!
Well, if the package works in BIDS with the exact same configuration (so the same remote directory, the same CSV et cetera) and it doesn't work in SQL Server Agent, then you need the find the differences. In 99% of the cases this is a permission issue, as the SQL Server Agent has usually a different set of permissions as yourself.
Other options could be (actually I'm just guessing here :-)):
* the server doesn't have a network connection to the remote path
* the server firewall is blocking the request
* Murphy just really hates you
Maybe, as GSquared mentioned, it is easier to ftp the file to a local location and do the processing from there.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 10, 2011 at 5:05 am
From what I read on the initial email, it sounded to me like the file is local to the server. Koen, however, keeps mentioning the words "remote path". Is the file on the actual non-domain box or is it on a UNC path that is not attached to the server at all?
March 10, 2011 at 5:09 am
Brandie Tarvin (3/10/2011)
From what I read on the initial email, it sounded to me like the file is local to the server. Koen, however, keeps mentioning the words "remote path". Is the file on the actual non-domain box or is it on a UNC path that is not attached to the server at all?
Ah, you are correct. I suspect however that the SSIS package doesn't run on that non-domain server.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 10, 2011 at 5:15 am
I think your right, its permissions. I thought about this last night and how early on I read the file off the non-domain server but created the new file local to SQL and uploaded and it worked fine.
The reason we don't have the file saved loaclly to SQL is the non-domain server looses connection to SQL a lot. So I guess our network guys need to figure out how to keep a constant connection and save the file to SQL server or put the server on the domain, ugh.
I really appreciate everyone's constant vigilance to this issue, if I ever resolve it I will post.
THANKS!
March 10, 2011 at 5:27 am
Koen Verbeeck (3/10/2011)
Brandie Tarvin (3/10/2011)
From what I read on the initial email, it sounded to me like the file is local to the server. Koen, however, keeps mentioning the words "remote path". Is the file on the actual non-domain box or is it on a UNC path that is not attached to the server at all?Ah, you are correct. I suspect however that the SSIS package doesn't run on that non-domain server.
The SSIS package does not run on the non-domain server.
The non-domain server contains the file that I need to read, create a new file and then move it also.
March 10, 2011 at 5:31 am
smac (3/10/2011)
Koen Verbeeck (3/10/2011)
Brandie Tarvin (3/10/2011)
From what I read on the initial email, it sounded to me like the file is local to the server. Koen, however, keeps mentioning the words "remote path". Is the file on the actual non-domain box or is it on a UNC path that is not attached to the server at all?Ah, you are correct. I suspect however that the SSIS package doesn't run on that non-domain server.
The SSIS package does not run on the non-domain server.
The non-domain server contains the file that I need to read, create a new file and then move it also.
Then, yes, your network admin either needs to attach the non-network server to the domain or create a trusted connection to it (edit) or setup the FTP process mentioned earlier in the thread.
Or you need to move the file-making process to the domain.
Which one is easier?
March 15, 2011 at 6:38 am
My resolution:
When building the job the
Step 1 was using "Type: Operating System (CmdExec)" then in the command line net use Z: \\10.239.130.170\export password /user:administrator
Step 2 was "Type: SQL Server Integration Services Package" run my package that contains my script task of reading the file and creating a new file on the non-domain server
Step 3 was again "Type: Operating System (CmdExec)" then in the command line net use Z: /delete
Runs now on a schedule too.
How exhausting 🙂
But you guys were great, thanks again!
March 15, 2011 at 6:43 am
Great! Glad that you finally resolved your problem. And thanks for posting back.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 15, 2011 at 7:31 am
Also glad you got it working and thanks for posting the solution. That will help others with the same problems.
Viewing 13 posts - 31 through 42 (of 42 total)
You must be logged in to reply to this topic. Login to reply