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..