Using a variable to create a variable

Question

I have an execute SQL task that finds the path of the latest backup of a database and populates a variable with it (User::BackupFilePath)

I want to pass that into another task that will generate a restore database script and populate another variable to be used to restore the database.

Select (
'ALTER DATABASE [Database] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

RESTORE DATABASE [Database] 
FROM  DISK = ''' + **BackupFilePath** + '''  WITH  FILE = 1, NOUNLOAD,  REPLACE,  STATS = 5 

ALTER DATABASE [Database] SET MULTI_USER
GO'
) as RestoreScript

The second part that would generate the string is however returning this error message

[Execute SQL Task] Error: Executing the query "Select 'ALTER DATABASE [xxxx..." failed with the following error: "An error occurred while extracting the result into a variable of type (DBTYPE_I4)". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I'm using Visual Studio 2008 Professional Edition

Should there be not semicolons between the commands?

sqlab
– sqlab

2014-04-02 12:18:34 +00:00
Commented Apr 2, 2014 at 12:18 — sqlab
– sqlab, Commented Apr 2, 2014 at 12:18

Doug_Ivison · Accepted Answer · 2014-04-02 13:16:53Z

1

So far, it looks like you're having a simple problem: the variable you're setting, to your command string, needs to be a string datatype.

Your error message mentions DBTYPE_I4, which is a long integer:
http://msdn.microsoft.com/en-us/library/windows/desktop/ms711251%28v=vs.85%29.aspx

Whereas what you'd save a command string into, would be a string type such as DBTYPE_STR or DBTYPE_WSTR (see the link above), which in SSIS would commonly be called DT_STR (for ASCII strings) or DT_WSTR (for Unicode strings) -- see the link below:
http://technet.microsoft.com/en-us/library/ms141036.aspx

Hope that helps...

answered Apr 2, 2014 at 13:16

Doug_Ivison

6487 silver badges19 bronze badges

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

Comments

Jayvee · Accepted Answer · 2014-04-02 12:53:52Z

0

One possible cause is the property 'ResulSet' of your second SQL Task (the restore)/ Make sure it's set to 'None'

answered Apr 2, 2014 at 12:53

Jayvee

10.9k3 gold badges35 silver badges43 bronze badges

Collectives™ on Stack Overflow

Using a variable to create a variable

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related