Running a SQLCMD script from SSIS Execute Process Task with variables

Question

I have an .sql file with scripts that include SQLCMD elements.

I need to have it run from an SSIS package.

I was unable to execute it from an "Execute SQL Task".

According to this http://www.sqlservercentral.com/Forums/Topic818940-147-1.aspx I should use an "Execute Process Task" with:

Executable: C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE
Arguments: -S server -U user -P pass -i C:\path\to\file.sql

This is working fine.

But, I need the Arguments to include a Variable. What ever I try, it is failing.

Arguments: -S server -U user -P pass -i @[User::FileNameView]

The error:

[Execute Process Task] Error: In Executing "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE" "-S server -U user -P pass -i @[User::FileNameView]" at "", The process exit code was "1" while the expected was "0".

My questions is: how do I include a variable in the Arguments string? Is there a different approach I should take?

Extra info:
SQL Server 2008R2
The reason I need this to be a variables is because I will put the "Execute Process Task" in a "For each loop" to be able to execute all .sql files in a folder.

Bacon Bits · Accepted Answer · 2016-06-27 14:24:19Z

2

I believe you need to create an expression named Arguments with the value:

"-S server -U user -P pass -i " + @[User::FileNameView]

You may need to do some type casting if @[User::FileNameView] isn't a string.

answered Jun 27, 2016 at 14:24

Bacon Bits

32.5k6 gold badges64 silver badges79 bronze badges

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

3 Comments

Martin Carlsson Over a year ago

This is the error I get when I try that:

[Execute Process Task] Error: In Executing "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE" ""-S server -U user -P pass -i " + @[User::FileNameView]" at "", The process exit code was "1" while the expected was "0".

Martin Carlsson Over a year ago

Arh - you are correct! I have not used "Execute Process Task" before - so I misunderstood what you ment with an expression. It is working correctly now. Thanks.

Bacon Bits Over a year ago

Ah, yes, I was debating if I should explain what to do or not in better detail. Glad you were able to figure it out!

Collectives™ on Stack Overflow

Running a SQLCMD script from SSIS Execute Process Task with variables

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related