I am new to SSIS but this does not seem like it should be a hard thing to do. Here is the background:
Windows Server 2003
SQL Server 2005
SSIS
Logged in to server as Admin
I've created a SSIS package that I want to run as a Job. In it there is a Ole DB Connection to a VFP Database on a Novell Server. Client for Netware Services is started when server is but NOT logged into. When I created the package everything runs fine. Note that I had to login when I created the Data Link. I can even run it from the SQL Server Package Store. As soon as I restart the server and try to run it I get an error that the file path is invalid in both SSIS and the Package Store. I check the connection string and it looks good.
The connection is availible in My Network Places
If I open the connection and login it runs fine again.
How do I intiate the network connection and then close it again?
Cory Bonallo
Retail Services
Developer
Aren't there some NET USE commands (or NetWare equivalents) that you could use to create the connection without having to log in?|||
I've never used anything like that. Should there be a setting or something in the SSIS connection manager? Do you have any examples? Thanks for your help!
Cory Bonallo
Retail Services
Developer
|||
Cory Bonallo wrote:
I've never used anything like that. Should there be a setting or something in the SSIS connection manager? Do you have any examples? Thanks for your help!
No, this is more in the Windows arena than SSIS. Taking the Novell client out of the picture for the moment, when you're not logged into the machine, none of your drive mappings are active since those are user-specific. Your package may work fine when you're logged it, but when it tries to run unattended with nobody logged in, it can no longer access mapped drives and fails.
This comes up from time to time here on the forum, but the search is failing me at the moment for good threads. This one applies, but isn't a stellar example.
With the Windows client, the NET USE command (try "net use /?" from the Windows command prompt) can be used to make these connections. If you're running the SSIS package from SQL Agent, you can use CmdExec steps before your package to create a connection with NET USE and one after the package to delete it.
I have no idea how having the NetWare client in the picture might change things. I don't know if NET USE will work or if there is a different command you have to use to accomplish this.
|||
Well I thought that did it. But now I can't get it to run in a job step. I cant seem to figure out the syntax or something for cmdExec.
Here is what I did:
in dos window typed "net use \\jds1\vol3 /user:admin" works great!
Created a job step as Operating System (cmd Exec) and typed in:
"net use" \\jds1\vol3 /user:admin
Got this error:
So I tried several other changes and still same error...
What am I missing?
Cory
|||Try:"C:\WINDOWS\system32\net.exe" use \\server\share /user:admin
"net use" is not a command. "use" is an argument to net.exe.|||
Thank you Phil. The more I use this tool the dumber I feel! So now I don't get an error but it just sits on that step. I'm assuming it has something to do with a return success code. Try as I might I just can't find any examples or instructions on how to code for this. Is there a way to reture a success code. I left it a zero because the articles I found said that was the default success code. Agail thanks for your help!
Cory
|||
Thank you Phil. The more I use this tool the dumber I feel! So now I don't get an error but it just sits on that step. I'm assuming it has something to do with a return success code. Try as I might I just can't find any examples or instructions on how to code for this. Is there a way to reture a success code. I left it a zero because the articles I found said that was the default success code. Agail thanks for your help!
Cory
I don't think its related to the return code. I think it probably isn't returning. I see you're specifying a user name but no password. The Net.exe may be prompting and waiting for a response. Try passing the password too.
"C:\WINDOWS\system32\net.exe" use \\server\share /user:admin mypassword
|||
You were right. It was the password. Now can you tell me why the connection is not persisting to the next job step? So here is what I want to do:
Step 1: open network connection
Step 2: run dtsx package that uses that connection
Step 3: Close network connection
Seems simple enough?
Cory
|||Look at either mapping a drive to that share, or using the /PERSISTENT:YES flag.|||
Look at either mapping a drive to that share, or using the /PERSISTENT:YES flag.
Or combining all three into a single step? Perhaps by putting them in a batch file?
|||
Or combining all three into a single step? Perhaps by putting them in a batch file?
Yep.|||
Thabk you both very much. What I ended up doing is adding an Excute Process Task to Open the connection, run the rest of the steps of the package and then close the connection. Thanks again to all!!!
Cory
|||I did get the net use working in the Job as a part of an SSIS package. Now I am trying to use the same process to open a connection to Novell in the Query editor and setting up a stored procedure that a Web service can call to get a connection to an access.mdb linked server on the Novell drive. When I try to execute via xp_cmdshell the command succeeds but does not make the conncetion availible to any other session. Then when I ran it in a batch to do a simple file copy the net use command succeeded but the copy failed. When I used impersonate it did not change the user from NT AUTHORITY/? I can't a channel opened.
Cory
|||I did get the net use working in the Job as a part of an SSIS package. Now I am trying to use the same process to open a connection to Novell in the Query editor and setting up a stored procedure that a Web service can call to get a connection to an access.mdb linked server on the Novell drive. When I try to execute via xp_cmdshell the command succeeds but does not make the conncetion availible to any other session. Then when I ran it in a batch to do a simple file copy the net use command succeeded but the copy failed. When I used impersonate it did not change the user from NT AUTHORITY/? I can't a channel opened.
Cory
No comments:
Post a Comment