variable value in file path in execute SQL Task in SSIS?

Question

I am trying to use Execute SQL task in SSIS. but its not accepting variable value in path? The file name is dynamic so, it has to come from variable. The below code is direct input in Execute SQL task.

USE [master]
go

RESTORE DATABASE MyDb FROM
DISK = N'c:\DBRestores\?'
WITH REPLACE

go

then I used, Parameter Mapping to map that value but the task is failing.

Any help? Thanks

Try using a variable to dynamically construct the entire SQL string. — Tab Alleman
– Tab Alleman, Commented Jul 20, 2015 at 13:49
Hi @Tab Alleman, thanks for the reply. I tried to wrap up in dynamic sql but its still not working in SSIS. I want to pass SSIS variable value to T-sql Statement? I think now you got my point. Thanks — user3583912
– user3583912, Commented Jul 20, 2015 at 14:17
When you tried the dynamic sql, did you change the SQL Source Type to variable? Can you post a screenshot of the task properties? — Tab Alleman
– Tab Alleman, Commented Jul 20, 2015 at 15:13

Jigar · Accepted Answer · 2015-07-20 15:13:52Z

1

You can set dynamic parameter in the Expressions. In Execute SQL Task Editor, goto Expressions -> Property -> SQLStatementSource -> Expression and put below SQL there, replace your dynamic variable name in it and click on Evaluate Expression and check the Evaluated Value.

"USE [master]
go

RESTORE DATABASE MyDb FROM
DISK = N'c:\\DBRestores\\" + @[User::VariableParameter]  + "'
WITH REPLACE

go"

If the evaluated value is as expected, then the script would accept dynamic file names from variable.

answered Jul 20, 2015 at 15:13

Jigar

2141 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user3583912 Over a year ago

Hi @Jigar, thanks for the reply. I will try this and let you know. thanks

user3583912 Over a year ago

Hi @Jigar, I tried with same process its fine when I check Evaluate Expression. the value showing fine but Execute SQL task showing 'there were errors during task validation'. and what should I write in direct input value in General Tab . Because I already used the code in Expressions tab. Thanks

Jigar Over a year ago

In General Tab, SQL Source Type: Direct Input SQL Statement: -- Leave empty -- This will come up automatically from Expression --

user3583912 Over a year ago

Hi @Jigar, thanks. I tried now here at home in my laptop. its working now. but I will try same this at work tomorrow. if I get any problems I will let you know. thanks for the help.

user3583912 Over a year ago

Hi @Jigar, I tried here at work as well its working fine. Thanks for that. Can you please explain why code is used in "USE [master]-----go" Is this dynamic sql? If so why we are not used exec sp_executesql @SQLStatement; Thanks.

Collectives™ on Stack Overflow

variable value in file path in execute SQL Task in SSIS?

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related