Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Executing Procedure in SSIS package Expand / Collapse
Author
Message
Posted Thursday, August 12, 2010 4:27 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 24, 2011 3:07 AM
Points: 66, Visits: 315
Hi everyone..

I have two stored procedures in two data bases,two procedures returns some binary record,my question is i have to two variables in my ssis package,i want to store the that two return values to the SSIS variables.Can any one help me its very urgent.



Regards..
guru12
Post #968050
Posted Thursday, August 12, 2010 4:59 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, May 30, 2014 6:27 PM
Points: 2,808, Visits: 7,175
To do this you will need to use a resultset in your execute SQL task.

Edit the task and under the general tab change the resulset setting to the one you need.

Then under the resulset tab add the variable that you need the resultset mapped to
Post #968064
Posted Thursday, August 12, 2010 5:11 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 24, 2011 3:07 AM
Points: 66, Visits: 315
Hi Steveb..

Can you explain briefly ont this,I am posting my procedure

ALTER PROCEDURE GetLastLSN
(
@TableName NVARCHAR(100),
@LastLSN BINARY(10) = NULL OUTPUT
)
AS
BEGIN
/*****************************************************************************************************************
Gets the last LSN that was parsed during the last run for a given table.
When used to run the job again increment the last LSN by 1 so that we are actually not parsing duplicate rows.
If last LSN returned is 0x00000000000000000000, use "sys.fn_cdc_get_min_lsn" to get the minimum LSN to use.
*****************************************************************************************************************/
DECLARE @_LastLSN BINARY(10)
SET @_LastLSN = 0x00000000000000000000

SELECT @_LastLSN = LastLSN FROM LSNLog WHERE TableName = @TableName
SET @LastLSN = @_LastLSN
END


So i need to run this procedure and have to save the lastLSN value to a variable,this procedure returns a binary object..please explain in brief


Regards..
guru12
Post #968070
Posted Thursday, August 12, 2010 6:06 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, May 30, 2014 6:27 PM
Points: 2,808, Visits: 7,175
Edit the sql task and chane the resultset to singlerow under the general tab.

In the resultset tab add a new variable to hold the data that is being returned, make sure you use a compatible data type. It looks like you are returning a binary datatype rather than a binary object.
Post #968094
Posted Thursday, August 12, 2010 6:46 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 24, 2011 3:07 AM
Points: 66, Visits: 315
HI Steveb..

YEs i want to return a binary data type,i did like what you said it is throwing error...

[Execute SQL Task] Error: Executing the query "EXEC GetLastLSN @Tablename=?,@LastLSN=? OUTPUT;" failed with the following error: "No value given for one or more required parameters.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.


My Package variable is
Tablename,


Regards..
guru12
Post #968127
Posted Thursday, August 12, 2010 7:45 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, May 30, 2014 6:27 PM
Points: 2,808, Visits: 7,175
Have you checked the possible issues in the error message?

such as; make sure your parameters have variables associated with them and also check that the resultset is set properly and the resultset is being mapped to a variable..
Post #968189
Posted Friday, August 13, 2010 12:07 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 24, 2011 3:07 AM
Points: 66, Visits: 315
Hi Steveb..

Now it is giving new error
[Execute SQL Task] Error: Executing the query "EXEC GetLastLSN @Tablename=?,@LastLSN=? OUTPUT;" failed with the following error: "Implicit conversion from data type varchar to binary is not allowed. Use the CONVERT function to run this query.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.


I think I din't tell you my requirement properly,here i am sending you again ,please help on this its very urgent.

Actual we are implementing CDC for incremental load,we have tow procedures in two databases,here are my two procedures

Procedure 1:

CREATE PROCEDURE GetLastLSN
(
@TableName NVARCHAR(100),
@LastLSN BINARY(10) = NULL OUTPUT
)
AS
BEGIN
/*****************************************************************************************************************
Gets the last LSN that was parsed during the last run for a given table.
When used to run the job again increment the last LSN by 1 so that we are actually not parsing duplicate rows.
If last LSN returned is 0x00000000000000000000, use "sys.fn_cdc_get_min_lsn" to get the minimum LSN to use.
*****************************************************************************************************************/
DECLARE @_LastLSN BINARY(10)
SET @_LastLSN = 0x00000000000000000000

SELECT @_LastLSN = LastLSN FROM LSNLog WHERE TableName = @TableName
SET @LastLSN = @_LastLSN
END

Proceddure 2.
CREATE PROCEDURE GetEndLSN
(
@TableName NVARCHAR(100),
@EndLSN BINARY(10) OUTPUT
)
AS
BEGIN
/****************************************************************************************************
Gets the end LSN number to be used while fetching the changes that are made to a table's data.
****************************************************************************************************/
SELECT @EndLSN = sys.fn_cdc_get_max_lsn()
END
GO

These two procedures will return two binary type values,i have to put these values into my package variables
my package variables are.
StartLSN,
EndLSn ,
TableName.

i have to store the GetlastLSn value into StartLSn variable and GetEndLsn value into endLsn variable,
for this i have taken Executesqltask,i executed first procedure like this
EXEC GetLastLSN @Tablename=?,@LastLSN=? OUTPUT;
and in parameter mapping Usertable::Tablename input and User::StartLSn output.
and in resultset i selected single row,and result setname=o,User::StartLsn.
i have taken parameterdatatypes both string and package variables also string,

when i run my package it is giving the following error

[Execute SQL Task] Error: Executing the query "EXEC GetLastLSN @Tablename=?,@LastLSN=? OUTPUT;" failed with the following error: "Implicit conversion from data type varchar to binary is not allowed. Use the CONVERT function to run this query.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
..
please help me on this..


Regards..
guru12
Post #968666
Posted Friday, August 13, 2010 12:21 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:52 PM
Points: 13,636, Visits: 11,509
It seems that you are trying to insert a string into a variable of datatype binary, which is causing the error.
You said that your package variables are of the string datatype, so you have to do a conversion first, as it is mentioned in the error itself.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #968670
Posted Friday, August 13, 2010 1:09 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 24, 2011 3:07 AM
Points: 66, Visits: 315
HI da zero ..

Can you Explain briefly how to do conversion in ssis ,i have taken oledb source,
gave one connection and in sql comman
i wrote like this
Exec GETLASTLSN @tablename='Mas_customer',@LastLSN=? output;

but it is throwing an error,
There is a datasource column with no name,there should be a name for each column.


Regards..
guru12
Post #968693
Posted Friday, August 13, 2010 5:02 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 24, 2011 3:07 AM
Points: 66, Visits: 315
HI steve ,DA zero..


THanks for your supporting the issue is resolved,in my procedure for the out put parameter i have given string datatype,string is not supporting the binaray type,so i have changed to int ,it is fine now and running succesful.Thaankyou Steve for your quick response.


Regards..
guru12
Post #968786
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse