August 12, 2010 at 4:27 am
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
August 12, 2010 at 4:59 am
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
August 12, 2010 at 5:11 am
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
August 12, 2010 at 6:06 am
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.
August 12, 2010 at 6:46 am
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
August 12, 2010 at 7:45 am
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..
August 13, 2010 at 12:07 am
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
August 13, 2010 at 12:21 am
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.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 13, 2010 at 1:09 am
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
August 13, 2010 at 5:02 am
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
June 15, 2013 at 6:30 pm
Hi guru12
I am facing same problem while working on SQL server CDC.
Per your last comment did you change in procedure @LastLSN to INT ( instead of BINARY(10))?
I understood that u put
in parameter mapping Usertable::Tablename input and User::StartLSn output.
and in resultset selected single row,and result setname=o,User::StartLsn.
parameterdatatypes both string and package variables also string,
I am not getting option to select parameterdatatypes as string, did you put varchar here ?
Could you please clarify what changes fixed your issue??
Thanks in advance.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply