SSIS usimg varible with ADO.Net Execute SQL Task "Failed to convert parameter value from a String to a Int32."

Question

Post reply

SSIS usimg varible with ADO.Net Execute SQL Task "Failed to convert parameter value from a String to a Int32."

bdkdavid

Ten Centuries

Points: 1220
More actions
June 29, 2016 at 1:11 pm

#323658

Error message
[Execute SQL Task] Error: Executing the query "create database
'@DBasset'" failed with the following error:
"Failed to convert parameter value from a String to a Int32.".
Possible failure reasons: Problems with the query,
"ResultSet" property not set correctly, parameters not set correctly,
or connection not established correctly.
I have a package variable with the name of Level-1
I created an Execute SQL Task.
paramer mapping to @DBasset
net result should be
@DBasset = Level-1
I am trying to use the variable to change the database name.
I get the above error
The command is create database @DBasset
I originally attempted
DECLARE @AssetsDB as varchar(50)
@AssetsDB = @DBasset
--select db_id(@AssetsDB)
if db_id(@AssetsDB) is not null
drop database @AssetsDB;
else
create database @AssetsDB;
same error above

Viewing 4 posts - 1 through 3 (of 3 total)

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

bdkdavid Ten Centuries Points: 1220 More actions · Answer 1

I made a Mistake above

The error is

[Execute SQL Task] Error: Executing the query "DECLARE @AssetsDB as varchar(50)

set @AssetsDB

= ..." failed with the following error: "Incorrect syntax near '@AssetsDB'.".

Possible failure reasons: Problems with the query, "ResultSet" property not set correctly,

parameters not set correctly, or connection not established correctly.

DECLARE @AssetsDB as varchar(50)

set @AssetsDB = @Assets (the variable mapping)

--select db_id(@AssetsDB)

if db_id(@AssetsDB) is not null

drop database @AssetsDB;

else

create database @AssetsDB;

go

USE @AssetsDB;

GO

Jez-448386 SSCarpal Tunnel Points: 4570 More actions · Answer 2

I don't think you can use a variable in a create DB statement.

If you want this to be dynamic, create a package variable with an expression which creates the statement you want to execute. Then use this package variable in your Execute SQL task

You can pass in the name of the DB from another package variable into this first variable.

Jez

bdkdavid Ten Centuries Points: 1220 More actions · Answer 3

You right! I forgot about that.

with the create statements

You gut's can delete this thread

it was the sql statement that was giving me the error.

I was looking at the wrong symptom.

When I ran

select db_id('@DB')

@DB is the variable

It worked fine. My Bad!