June 7, 2016 at 2:48 am
Hi SSCommited,
Appreciate the detailed reply.
This is what I have and need to fix.
I have an execute SQL task editor inside a for each loop which has this SQL statement
update DB.dbo.Table set col1 = '\\abcd\dfrg\'+ ?
This ? is the variable value which is the servername as part of the for each loop.
Also attached is the screenshot of parameter mapping.
User::currentserver is the variable name.
Finally my col1 should look something like this where ServerA/B/C/D/E/F are the servernames which come from the for each loop.
\\abcd\dfrg\ServerA
\\abcd\dfrg\ServerB
\\abcd\dfrg\ServerC
\\abcd\dfrg\ServerD
\\abcd\dfrg\ServerE
\\abcd\dfrg\ServerF
Thanks
June 7, 2016 at 2:59 am
OK, you've shown what results you need, but you haven't said in what way what you currently have isn't working. I would guess that your col1 looks more like this when all is done:
[font="Courier New"]\\abcd\dfrg\ServerF
\\abcd\dfrg\ServerF
\\abcd\dfrg\ServerF
\\abcd\dfrg\ServerF
\\abcd\dfrg\ServerF
\\abcd\dfrg\ServerF[/font]
Could it be as simple as adding a WHERE clause to your UPDATE statement?
John
June 7, 2016 at 3:29 am
As of now my SSIS package only update the first server it comes across in the for each loop , updates the relevant column and then fails stating that the second server in the for each loop is not found.But that is false as I can access all the servers in the loop.Already tested this.
June 7, 2016 at 3:40 am
Please post the exact error message. Is the Execute SQL task the only task in your ForEach Loop container? Is the table it updates on a central server, or are you connecting to each server to update the table? Please post the connection string from your connection manager. When you say you can access all servers in the loop, how do you do that?
John
June 7, 2016 at 3:43 am
May be credentials issue..Plz chcek which account you have used ?
Add one more Script task or executeprocess task (Batch file) to test whether the server is able to access or not?
June 7, 2016 at 4:25 am
Hi John,
Attached are screenshots of the variables and the package and also the parameter mappings.
The first task inside the for loop creates as many directories as the number of servers in the for each loop.
That task works fine.
But the second task inside the for each loop fails.
This is the update statement:
update DB.dbo.Table
set col1 = '\\path\' + ?
? should be the User::currentserver
Thanks
June 7, 2016 at 4:58 am
Thanks. You've answered the question in bold - please will you now answer the others?
Please post the exact error message. Is the Execute SQL task the only task in your ForEach Loop container? Is the table it updates on a central server, or are you connecting to each server to update the table? Please post the connection string from your connection manager. When you say you can access all servers in the loop, how do you do that?
John
June 7, 2016 at 8:48 am
The for each loop container has Execute SQL Task and also a File system task
Based on the number of input servers (select in the 1st execute task outside the for each loop) the file
system task creates as many folders with same name as the servers under a common directory.
This part works fine.
So the directories will be as follows \\path\serverA;\\path\serverB;etc...
I am connecting to each server to update a table which goes by a common name across all the servers/db's.
I can access each of the servers using the for each loop container.
Please see the screenshots attached 1.jpg and 2.jpg
I do not have any data flow tasks with ole db source/destination so I do not have anything under connection manager.
This is the update statement:
update Database.dbo.Table1
set filepath = '\\path\' + ?
? should be the changing servername as in each time it loops through the servers.
Error:
[Execute SQL Task] Error: Failed to acquire connection "ServerB.DB-B". Connection may not be configured correctly or you may not have the right permissions on this connection.
But I do have access to the server/db.
FYI:
I have 2 servers as source.
ServerA,ServerB
Only ServerA directory got created and the update did not work on both the servers.
Thanks
June 7, 2016 at 9:01 am
OK, we're getting somewhere. Not quite all bold yet, though.
Please post the exact error message. Is the Execute SQL task the only task in your ForEach Loop container? Is the table it updates on a central server, or are you connecting to each server to update the table? Please post the connection string from your connection manager. When you say you can access all servers in the loop, how do you do that?
So, if you have access to Server B but your connection is failing, your connection string must be wrong - hence my request that you have ignored twice. That's unless you're using a totally different set of credentials, but you haven't answered that one yet, either. I need to know with what application you are making your successful connection, whether you are making it from the same computer on which you are running the package, and what credentials you are using.
John
June 7, 2016 at 10:05 am
The execute sql task is not the only task in the for each loop.There is file task in the for each loop.
I do not have any connection managers as there is no data flow task , so I not not sure which connection string you are talking about.
I am using my windows login for all servers.If I am still not cler enough please let me know.
June 23, 2016 at 5:59 am
Can anyone please help fix this ?
Thanks
June 23, 2016 at 7:08 am
Clearly not, especially given your unwillingness to provide the necessary details.
John
June 23, 2016 at 9:13 am
If you are having an issue connecting to one specific server, you need to simplify your scope. Get rid of the looping and variables and run your task against a connection manager hitting that specific server. If you can't connect, you've got something wrong with your credentials or your connection string. If you can connect, run your tasks and see if they work, then go back and add in the variables and looping. It's terribly hard to troubleshoot when you have variables/expressions/loops in the mix. Again, simplify, start with a simple connection and work your way out from there.
July 4, 2016 at 4:32 am
Hi
i just completed the task using For each Loop and sql task. I created 2 tables.
Create Table ServerNames
(ServerName varchar(20))
Insert ServerNames
values('ProdServer'), ('StagingServer'),('TempServer')
Create Table Table1(
ServerName varchar(20),
Path varchar(50)
)
Insert into Table1 values('ProdServer',''), ('StagingServer',''),('TempServer','')
select * from table1
Table serverNames is used to load serverNames for which paths needs to be changes. You can use sys.servers.
Another table Table1 was updated with the correct path.
now follow the steps in SSIS.
1) Create one variable lets say ServerNames as Object Type.
2) Create another variable with the name ServerName of string type.
3) Create Execute SQL Task. Configure it as below
4) Make the OLEDB connection, and SourceType as Direct Input. Also ResultSet property as "Full Result Set" and in the SqlStatement type "select ServerName From ServerNames"
5) Go to ResultSet Tab and VariableName as "User::ServerNames" and ResultName as 0.
6) Create a For Each Loop Container and in the Container tab select "Foreach ADO Enumerator". Then select the ADO object Source Variable as "User::ServerNames" .
7) Now go to the variable Mapping tab. There select variable as "User::ServerType" and Index as 0.
8) In the "For Each Loop Container" create "Execute SQL Task".
9)make The ConnectionType, Connection and SQLsource type as previuos SQL Task.
10) Now in the Expression property of the Execute SQL task Put the string
"update table1 set Path='\\\\path\\" + @[User::ServerType] + "'where ServerName = '" +@[User::ServerType] +"'"
Now, it should run smoothly.
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply