SQL in SSIS

Question

Post reply

SQL in SSIS

mtz676

SSCrazy Eights

Points: 9217
More actions
May 24, 2016 at 3:05 am

#319026

I have an SSIS package where I gather servernames within execute sql task.
Then I have a for each loop which contains a file task.
This creates directories for each servername.
Now among all the servers I have to update a sql table with this sql.
update table1 set location='\\path\@variablevalue'
So in the above statement the location until '\\path\ is same across all servers,then for each server
there is a @variablevalue (same as servername and same as directory name).
I have to update this location so that respective location values are updated on respective servers.
Eg:
Server1
update table1 set location='\\path\Server1'
Server2
update table1 set location='\\path\Server2'
Server3
update table1 set location='\\path\Server3'
How do I do this as part of the update statement in SSIS.
Thanks

Viewing 15 posts - 1 through 15 (of 29 total)

You must be logged in to reply to this topic. Login to reply

Jez-448386 SSCarpal Tunnel Points: 4594 More actions · Answer 1

If you have an Execute SQL Task you can create an expression for SQLStatementSource

"update table1 set location='\\\\path\\" + @[User::variablevalue'] + "'"

This will resolve for each iteration of the loop.

\ is a special character has needs to be escaped with another \ hence the extras in the expression.

Jez

mtz676 SSCrazy Eights Points: 9217 More actions · Answer 2

This is the update statement.

"update [ABCD].[dbo].[Table]

set filepath='\\\\abc002\users\myuser\myfolder\\" + @[User::currentserver'] + "'"

[Execute SQL Task] Error: Executing the query ""update [ABCD].[dbo].[Table]

set ..." failed with the following error: "Incorrect syntax near '+'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Any suggestions are appreciated.

Thanks

Jez-448386 SSCarpal Tunnel Points: 4594 More actions · Answer 3

This needs to be an expression not the actual SQL statement.

In the Properties window for the task, click the ellipsis (...), under Property select SqlStatementSource and then click the ellipsis to enter the expression.

Jez

mtz676 SSCrazy Eights Points: 9217 More actions · Answer 4

It still shows an error.There were errors during task validation and the Execute SQL task editor has not turned green.It displays with a red cross mark.

What are the following values of execute sql task:

Result set:

Connection type:OLE DB

Connection:servername.db name

Source type:Direct Input

SQLStatement:Is this empty ?

Bypass prepare:True or False

Under execute SQL Task Editor

Any values for the following:

General:

Parameter mapping :

Result Set : Anything here ?

Expressions:"update [ABCD].[dbo].[Table]

set filepath='\\\\abc002\\users\\myuser\\myfolder\\" + @[User::currentserver]

+ "'"

When I execute the package this is what I get as error message.

[Execute SQL Task] Error: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_Rowset".

Thanks

mtz676 SSCrazy Eights Points: 9217 More actions · Answer 5

mtz676

SSCrazy Eights

Points: 9217

May 24, 2016 at 8:43 am

#1881122

.

Jez-448386 SSCarpal Tunnel Points: 4594 More actions · Answer 6

The update will not return a result set. Change this to None.

Jez

mtz676 SSCrazy Eights Points: 9217 More actions · Answer 7

I did that and it worked for me.But the update is working only on the source server where I am running this package.The update needs to run across all servers.What am I missing here.

John Rowan SSC Guru Points: 56440 More actions · Answer 8

If you need to change connections, you can make your connection manager dynamic by using the expressions on it and the variable from your foreach loop containing the server name. Set the servername property on the connection manager to use the variable. Then, each iteration of the for each will connect to the proper server to make the update.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

mtz676 SSCrazy Eights Points: 9217 More actions · Answer 9

That is already included in the Connection manager.But that dynamic server name change is not getting into the execute sql task and hence

The execute sql task(update sql) is not updating on all servers.

Where do I make this change on this task?

My execute sql task turns red and also the for each loop container.

Error Message:

[Execute SQL Task] Error: Failed to acquire connection "server.dbname". Connection may not be configured correctly or you may not have the right permissions on this connection.

Any suggestions please.

John Rowan SSC Guru Points: 56440 More actions · Answer 10

You need your execute SQL task to use the connection manager that you've setup to use the dynamic server name from the foreach loop variable. Here's a link that shows a decent example of the technique.

http://www.sanssql.com/2011/08/looping-through-sql-servers-using-ssis.html

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

mtz676 SSCrazy Eights Points: 9217 More actions · Answer 11

I have looped through servers many a times and appreciate the link for the same.

In this case my update statement should like what ?

update tabelA set col1='\path\User::currentservername\'

How do I write the above update statement.

So for the first server in loop 1 the statement should be

update tabelA set col1='\path\User::currentservernameA\'

for second server in loop 2 the statement should be

update tabelA set col1='\path\User::currentservernameB\'

and so on and so forth...

So this variable curent server :User::currentservername will carry a different server name during each loop and the respective servers the update statement will replace this variable User::currentservernameA\ with the current servername as in the loop.

This is where I am stuck.

Thanks.

sestell1 SSChampion Points: 10230 More actions · Answer 12

You can do this one of two ways:

1. Use a parameter to pass the value of your variable into your query.

2. Use an expression in your Execute SQL task to dynamcally set the query string using your variable.

mtz676 SSCrazy Eights Points: 9217 More actions · Answer 13

I have tried the parameter path and its not working.

Can you please give sample update statement/s for both the options you mentioned ..

I have posted what I did on this tread...Is it syntactically correct ?

Thanks

sestell1 SSChampion Points: 10230 More actions · Answer 14

It appears you've created a formula that would be used with an expression, but you are trying to use that directly in the Execute SQL Task as if you were using a query with parameters.

To use parameters, you'd want to just type in your query in the Execute SQL Task, but put parameter marker where you want to substitute your variable value.

Then on the Parameter Mapping tab, you would add a new parameter mapping, select your variable, and assign a parameter 'name'.

Unfortunately, what you use as a parameter marker and parameter name in the mapping depend on what type of connection you are using.

https://msdn.microsoft.com/en-us/library/ms140355.aspx

Assuming you are using an OLE DB connection, you'd want to use a "?" as your parameter marker, and in your parameter mapping give your parameter a name of "0" (zero).

So your query would look something like this:

UPDATE table1 SET location = ?

and your mapping would use @[User::variablevalue] as the variable, and set the parameter name to 0.

I hope this helps.