Execute SQL Task passing parameters to a restore command

Hi,

I'm very new to SSIS and I’m trying to do the following in a SQL task

RESTORE DATABASE @.DatabaseName FROM DISK = @.Backup WITH FILE = 1, MOVE @.OldMDFName TO @.NewMDFPath, MOVE @.OldLDFName TO @.NewLDFPath, NOUNLOAD, REPLACE, STATS = 10

I'm using an OLE DB connection and I have mapped user variables to the various parameter names. Unfortunately when i test the above command it fails on must declare the scalar variable "@.DatabaseName". How can i get my values to be substituted into the command?

Many thanks

Martin

The best way is to build your SQL statement in a variable via expressions and then use that variable as the SQL source for the Execute SQL task.|||I don't think you can parameterize a RESTORE DATABASE command like that.

The way I'd do it is to create a new parameter named SqlStatement or something of similar meaning. This parameter's EvaluateAsExpression property will be True and the Expression property will be: "RESTORE DATABASE " + @.[User::DatabaseName] + " FROM DISK = ...". Then set the SQLSourceType of your Execute SQL Task to Variable and specify SqlStatement as the variable.