Failing Execute SQL Task

Inside of an SQL Task (below), I have a call to a stored procedure with 7 parms.

[Audit].[spBatchPackage_OnExtract] , , , , , ,

User:: ParentExecution_Idx Long

User:: Batch_Idx Long

System:: PackageName Nvarchar

System:: PackageID GUID

User:: TargetTable Nvarchar

System:: ContainerStartTime DBTimestamp

User:: DataFilePattern Nvarchar

In my SP I have this:

ALTER PROCEDURE [Audit].[spBatchPackage_OnExtract]

@Execution_Idx int

,@Batch_Idx int

,@Source nvarchar(1024)

,@SourceId uniqueidentifier

,@Destination nvarchar(1024)

,@StartTime DATETIME

,@FileSpecification nvarchar(2)

When I execute the SQL Task, I get this error:

[Execute SQL Task] Error: Executing the query "[Audit].[spBatchPackage_OnExtract] , , , , , , " failed with the following error: "Invalid character value for cast specification". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Any help on clearing up this error would be appreciated!

Jamie, thanks for the quick response. Here are the datatypes in SSIS for the variables:

ParentExecution_Idx Int32

Batch_Idx Int32

PackageName String

PackageID String

TargetTable String

ContainerStartTime DateTime

DataFilePattern Str

Lindsay wrote:

I tried changing it inside the SP and in the parameter mapping inside the SQL task to nvarchar, but I still no luck. If you have any other ideas, let me know! Thank you!

Because I need to do some calculations on the StartTime param in comparision with GETDATE(), I decided to try and find a better variable type. I ended up changing ContainerStartTime in SSIS to a Date, and in the stored procedure, it is DATETIME. The calculation works just fine.

Another thing to note - after I had changed the variable to be nvarchar, the package was still blowing up with an error about inserting records and violating FK constraints. I changed the Isolation Level on the container to be 'Read Uncommitted' as opposed to 'Serializable' and it worked.