June 3, 2016 at 3:12 am
Yes I am working from Visual studio.
Yes I can connect to all servers using SSMS form the source server where my package is being run in Visual studio.
June 3, 2016 at 3:33 am
Good. In that case, sounds like you're using different credentials in the package from those you're using in SSMS, or the connection manager specifies a database that doesn't exist.
Is ServerB set up to capture failed logins in the errorlog? If so, are there any failed attempts reported when you run the package? Please post a screenshot of your connection manager.
John
June 3, 2016 at 3:33 am
Thom A (6/3/2016)
The fact that the error says that your connection failed, really doesn't suggest there's a problem with the variable mapping, and is the connection manager. Especially if you have the set up for the two nodes exactly the same. The error message is giving you that error for a reason.Like John said, it's worth checking the logs on ServerB, and seeing that it is receiving the connection attempt. if you can't see the refused connection in there, that would suggest that it's not trying to connect, and so you're next step would be to trouble shoot the Connection manager.
If you really want to check it's not the variable, you could write other the ? in your SQL statement with an actual value. If it still fails, then you have your answer.
What did the SQL logs show?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 3, 2016 at 8:29 am
At this point, I'd consider taking out the looping, variables, and expressions and just set up a task to hit that server. Remove the dynamic SSIS stuff and just try to connect with an execute SQL task and run a simple statement. Then you can know if you still get errors that it's a connection issue and not something with your loop/variables/expressions. Simplify the operation to narrow it down.
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply