May 30, 2016 at 4:37 am
Attached is a screenshot of my SSIS package.
I can create directories based on number of servers as of now.I need further assistance as described underneath.
Under Execute SQL Task Editor-->
Under General-->
Rsult Set:Full Result Set
Connection Type : OL DB
SQLSourceType:Direct InputSQL Statement:select * from Table1 (Gives a list of server names :lets says 10 server names)
Under Result Set:
Result name 0 : Variable Name:serverlist
Under For Each Loop Container:
Under Collection : Enumerator : For eachADO Enumerator
ADO objec source variable:User:serverlist
Rows in first table option is checked
Under Variable mappings:
Variable:User::currentserver
Index:0
I have File systen task under inside the for each loop container.
UNder File system Task editor:
Under General:
Operation : Create directory
Issourcepathvariable:True
SourceVariable:User::VarFolderFullPath
I need to update same table colA (UpdateTableA)( same database name on each server).
Eg:ServerA to ServerJ
The current package creates as many directories under (C:\User\ABCD\ServerA).....C:\User\ABCD\ServerJ)as the number of servers in Table1.
My update table has these columns:
ServerA
ServerName DirectoryPath
A D:\myservers\
A D:\myservers\
ServerB
B D:\myse\
B D:\myse\
The new updated path should be this way:
On server A
ServerName DirecotoryPath
A D:\User\A
On server B
ServerName DirecotoryPath
B D:\User\B
On server C
ServerName DirecotoryPath
B D:\User\C
So for each server the path should be update accordingly as per the server name.
So every time the for each loop carries a new servername in its variable @[User::currentserver].This should be
added to the update statement
update tableA
set Directory = 'D:\User\C\@[User::currentserver]'
update tableA
set Directory = 'D:\User\C\@[User::currentserver]'
update tableA
set Directory = 'D:\User\C\@[User::currentserver]'
This variable @[User::currentserver] carries current servername and change each time it loops through.
After update the tables across server should look like:
On server A
ServerName DirecotoryPath
A D:\User\A
On server B
ServerName DirecotoryPath
B D:\User\B
On server C
ServerName DirecotoryPath
B D:\User\C
Appreciate any assistance.
Thanks
May 31, 2016 at 9:51 am
You need an execute SQL task inside of your for each loop container. Your UPDATE statement should be something like this:
UPDATEtableA
SETDirectory = Directory + ?
Then, on the Parmeter Mapping tab, use your server name variable from the for each loop as input. For the parameter name, use zero.
May 31, 2016 at 10:10 am
To pass a variable in SSIS in the Execute SQL Task node, you use a ?, and then add map your parameters on the Parameter Mapping Pane.
So, for example. Say you have a query that selects all data from a TableA, where ID = @[User::EntryID] and Username = @[User::Username] You would do the following:
Your SQLStatement would be:
Select *
from TableA A
where A.ID = ?
and A.Username = ?
Then, on your parameter Mapping pane you would add a new parameter. Select User::EntryID for the VariableName, Input for the Direction, DataType LONG, Parameter Name 0 (this is actually more like ID, where the first parameter ID is 0). leave the Parameter Size as -1.
Then add a second parameter. Select User::Username for the Variable Name, Input for the Direction, VARCHAR for the Datatype and enter 1 for the Parameter name.
If, we then say that User::EntryID has a value of 22 and Username is "Thom A", SSIS would then effectively execute the SQL:
Select *
from TableA A
where A.ID = 22
and A.Username = 'Thom A'
(to be precise it uses sp_executesql so does:
Exec sp_executesql N'Select * from TableA A where A.ID = @P1 and A.UserName = @P2', N'@P1 int, @P2 varchar(50)', 22, 'Thom A'
But I'm sure you get the idea)
Hope that helps!
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 31, 2016 at 10:09 pm
This is my update statement:
I have tried these 2 statements as underneath.
update dbo.BackupDeviceInfo
set filepath = 'C:\ABCDEF\' +'?'
update SMSPHcdb.dbo.BackupDeviceInfo
set filepath = 'C:\ABCDEF\' +?
The update statement works on the first server it loops through and then the package fails.
My parameter mapping under Execute SQL editor has these values under variable mapping :
user::currentserver as in the for each loop container.
I get the following error.
I get the following error.
[Execute SQL Task] Error: Failed to acquire connection "Server.DB". Connection may not be configured correctly or you may not have the right permissions on this connection.
Please advise.
May 31, 2016 at 10:09 pm
This is my update statement:
I have tried these 2 statements as underneath.
update dbo.BackupDeviceInfo
set filepath = "C:\ABCDEF\" +"?"
update dbo.BackupDeviceInfo
set filepath = "C:\ABCDEF\" +?
My parameter mapping under Execute SQL editor has these values under variable mapping :
user::currentserver as in the for each loop container.
I get the following error.
[Execute SQL Task] Error: Executing the query "update dbo.Table1
set filepath = "\\abc..." failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Please advise.
June 1, 2016 at 1:09 am
This is my update statement:
I have tried these 2 statements as underneath.
update dbo.BackupDeviceInfo
set filepath = 'C:\ABCDEF\' +'?'
update SMSPHcdb.dbo.BackupDeviceInfo
set filepath = 'C:\ABCDEF\' +?
The update statement works on the first server it loops through and then the package fails.
My parameter mapping under Execute SQL editor has these values under variable mapping :
user::currentserver as in the for each loop container.
I get the following error.
I get the following error.
[Execute SQL Task] Error: Failed to acquire connection "Server.DB". Connection may not be configured correctly or you may not have the right permissions on this connection.
Please advise.
June 1, 2016 at 1:41 am
The latest post you've made states that you've got a connection error, which is separate to the above. SSIS is advising it can't connected to ServerDB. Check that your login credentials, etc, are correct.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 1, 2016 at 2:31 am
I am able to connect,buthe update works only the first time the update statement runs i.e only for the first server value in the User:serverlist variable.
June 1, 2016 at 2:46 am
sqlnewbie17 (6/1/2016)
I am able to connect
Yes, but only to the first server. Find out why the connection to the second server is failing, and your package will then run properly.
John
June 2, 2016 at 12:08 pm
I have checked it and cant seem to understand.Anysuggestions please.
June 2, 2016 at 12:16 pm
So you are able to log into the second server as the account that runs your package?
Another thing you can do is put a breakpoint on one of your tasks in your for each loop. When execution hits the breakpoint, check the Locals tab for your variable values to make sure everything is as you expect it to be.
June 2, 2016 at 11:26 pm
Yes,I do have access to the other server.Tested and tried.
June 3, 2016 at 1:05 am
Hi John & All,
I tested the following.
Had only 1 server along with this statement :
update tableA set filepath = ?
This statement ran successfully for only 1 server(ServerA).The related tableA in ServerA got updated with the server name under column filepath.
Then I included only the other server(ServerB) as the source and it immediately failed.
Error:
[Execute SQL Task] Error: Failed to acquire connection "ServerB.Database". Connection may not be configured correctly or you may not have the right permissions on this connection.
But I several other packages working with this server(ServerB) as datasource,so I dont think its an access issue.
I believe there is something wrong in my variable declarations.
Variables:
NameScopeDataType Value
currentserver package1 string serverA
serverlist package1 ObjectSystem.Object
VarfolderFullpathPackage1 StringC:\Myfolder\ServerA
VarFolderNamePackage1 StringServerA
VarFolderPathPackage1 StringC:\MyFolderA
Can you please suggest.
Thanks
June 3, 2016 at 2:04 am
How are you running the package - interactively in SSDT/Visual Studio, from a SQL Server Agent job, with dtexec, or something else? Can you ping Server B from the computer you're running the package from (let's call it ComputerP)? Can you open a connection using SSMS or sqlcmd from ComputerP to ServerB? 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 2:31 am
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.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply