unable to pass string from proc to SSIS variable - HELP!!!

#31

Todd,

I have a similiar problem. I am unable to pass datetime from SQL query to
SSIS Date variable. I have it set up like you wrote in your post except I am
using datetime. I am also trying to pass three variables instead of one. My
Result Names are 0, 1, and 2. When I run the query in SSMS I do get results
(one row with dates for each field). Is there a problem binding DateTime
datatype to SSIS Date datatype?

Fred

"Todd C" wrote:

Quote:

Jim and Joan:
I tested this out on my system and it worked just fine for me:

create proc dbo.TEST_PRC
AS
SELECT 'hello world' AS ColA

Created Variable(with Package Scope) "Test", type = String; value: "blah
blah blah"

Created Execute SQL Task with following:
ResultSet: Single Row
Connection Type: OLE DB
Source Type: Direct input
SQL Statement: exec dbo.TEST_PRC

Then on the Result Set page:
Result Name: 0 (and this is key for OLE Db connections)
Varialbe Name: User::test

I set a breakpoint on the Post-Execute event of the task and examine the
value of variable User::test and it is "hello world"

I am NOT using the Parameter mapping to get proc Output parameters.

HTH
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]

"Joan Sants" wrote:

Hi Todd!

I just have the same problem. I call a stored procedure. I assign the value
of a Guid to a varchar(50) variable and at last I perform a SELECT to pass it
to the variable of SSIS. I use an Execute SQL Task and the variable of SSIS
is a string. With integer it works all right, but with varchar(50) there is
no way to make it work. Code of the stored proc is something like this

DECLARE @IDTipoArchivoFotografia varchar(50)

SET @IDTipoArchivoFotografia = NEWID()

SELECT @IDTipoArchivoFotografia AS IDTipoArchivo

and the error
The type of the value being assigned to variable "User::IDTipoArchivo"
differs from the current variable type

thanks,

Joan

"Todd C" wrote:

Hello Jim:
Can you tell us what Task you are using to accomplish this? Is it an Execute
SQL Task? What type of connection does it have? OLE DB or ADO.NET?
How are you setting up the Result Set?
--
Todd C

"Jim" wrote:

I am trying to pass a value from a stored proc output variable to a user
variable in SSIS. When I define the variable as integer in both SSIS and the
stored proc, every thing is fine, however, it gives me a data type mismatch
error when I declare the variable as varchar in stored proc and as STRING in
SSIS.

I have tried every other reasonable combination but nothing seems to work.
How can I pass a string from a stored proc to the SSIS variable?

#32

Todd,

I have a similiar problem. I am unable to pass datetime from SQL query to
SSIS Date variable. I have it set up like you wrote in your post except I am
using datetime. I am also trying to pass three variables instead of one. My
Result Names are 0, 1, and 2. When I run the query in SSMS I do get results
(one row with dates for each field). Is there a problem binding DateTime
datatype to SSIS Date datatype?

Fred

"Todd C" wrote:

Quote:

Jim and Joan:
I tested this out on my system and it worked just fine for me:

create proc dbo.TEST_PRC
AS
SELECT 'hello world' AS ColA

Created Variable(with Package Scope) "Test", type = String; value: "blah
blah blah"

Created Execute SQL Task with following:
ResultSet: Single Row
Connection Type: OLE DB
Source Type: Direct input
SQL Statement: exec dbo.TEST_PRC

Then on the Result Set page:
Result Name: 0 (and this is key for OLE Db connections)
Varialbe Name: User::test

I set a breakpoint on the Post-Execute event of the task and examine the
value of variable User::test and it is "hello world"

I am NOT using the Parameter mapping to get proc Output parameters.

HTH
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]

"Joan Sants" wrote:

Hi Todd!

I just have the same problem. I call a stored procedure. I assign the value
of a Guid to a varchar(50) variable and at last I perform a SELECT to pass it
to the variable of SSIS. I use an Execute SQL Task and the variable of SSIS
is a string. With integer it works all right, but with varchar(50) there is
no way to make it work. Code of the stored proc is something like this

DECLARE @IDTipoArchivoFotografia varchar(50)

SET @IDTipoArchivoFotografia = NEWID()

SELECT @IDTipoArchivoFotografia AS IDTipoArchivo

and the error
The type of the value being assigned to variable "User::IDTipoArchivo"
differs from the current variable type

thanks,

Joan

"Todd C" wrote:

Hello Jim:
Can you tell us what Task you are using to accomplish this? Is it an Execute
SQL Task? What type of connection does it have? OLE DB or ADO.NET?
How are you setting up the Result Set?
--
Todd C

"Jim" wrote:

I am trying to pass a value from a stored proc output variable to a user
variable in SSIS. When I define the variable as integer in both SSIS and the
stored proc, every thing is fine, however, it gives me a data type mismatch
error when I declare the variable as varchar in stored proc and as STRING in
SSIS.

I have tried every other reasonable combination but nothing seems to work.
How can I pass a string from a stored proc to the SSIS variable?

#33

Todd,

I have a similiar problem. I am unable to pass datetime from SQL query to
SSIS Date variable. I have it set up like you wrote in your post except I am
using datetime. I am also trying to pass three variables instead of one. My
Result Names are 0, 1, and 2. When I run the query in SSMS I do get results
(one row with dates for each field). Is there a problem binding DateTime
datatype to SSIS Date datatype?

Fred

"Todd C" wrote:

Quote:

Jim and Joan:
I tested this out on my system and it worked just fine for me:

create proc dbo.TEST_PRC
AS
SELECT 'hello world' AS ColA

Created Variable(with Package Scope) "Test", type = String; value: "blah
blah blah"

Created Execute SQL Task with following:
ResultSet: Single Row
Connection Type: OLE DB
Source Type: Direct input
SQL Statement: exec dbo.TEST_PRC

Then on the Result Set page:
Result Name: 0 (and this is key for OLE Db connections)
Varialbe Name: User::test

I set a breakpoint on the Post-Execute event of the task and examine the
value of variable User::test and it is "hello world"

I am NOT using the Parameter mapping to get proc Output parameters.

HTH
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]

"Joan Sants" wrote:

Hi Todd!

I just have the same problem. I call a stored procedure. I assign the value
of a Guid to a varchar(50) variable and at last I perform a SELECT to pass it
to the variable of SSIS. I use an Execute SQL Task and the variable of SSIS
is a string. With integer it works all right, but with varchar(50) there is
no way to make it work. Code of the stored proc is something like this

DECLARE @IDTipoArchivoFotografia varchar(50)

SET @IDTipoArchivoFotografia = NEWID()

SELECT @IDTipoArchivoFotografia AS IDTipoArchivo

and the error
The type of the value being assigned to variable "User::IDTipoArchivo"
differs from the current variable type

thanks,

Joan

"Todd C" wrote:

Hello Jim:
Can you tell us what Task you are using to accomplish this? Is it an Execute
SQL Task? What type of connection does it have? OLE DB or ADO.NET?
How are you setting up the Result Set?
--
Todd C

"Jim" wrote:

I am trying to pass a value from a stored proc output variable to a user
variable in SSIS. When I define the variable as integer in both SSIS and the
stored proc, every thing is fine, however, it gives me a data type mismatch
error when I declare the variable as varchar in stored proc and as STRING in
SSIS.

I have tried every other reasonable combination but nothing seems to work.
How can I pass a string from a stored proc to the SSIS variable?

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

unable to pass string from proc to SSIS variable - HELP!!!

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

RE: unable to pass string from proc to SSIS variable - HELP!!! - 08-11-2008 , 03:47 PM

RE: unable to pass string from proc to SSIS variable - HELP!!! - 08-11-2008 , 03:47 PM

RE: unable to pass string from proc to SSIS variable - HELP!!! - 08-11-2008 , 03:47 PM