November 24, 2015 at 2:37 pm
I'm using SSIS 2008R2.
I have a data flow task with an OLE DB connection manager using Data access mode: SQL command. I want to execute a stored procedure and use SSIS variables to replace my input parameters. I've tried exec [procname] ?,?,?,? (4 input parameters) and then hitting the Parameters button and mapping them there. (Initially, I left the Parameter0, Parameter1,Parameter2, Parameter3 defaults and then used my User::xxxx variables.) When I couldn't make that work, I explicitly mapped them the way they are named in the proc: exec [procname] @myparm1=?,@myparm2=?,@myparm1=3,@myparm4=? and again used my User::xxxx variables in the mapping.) If I don't use variables at all and just pass hard-coded values, I get the results I want. When I'm trying with variables, SSIS bombs with
[OleDB Source - Sql Server [1]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E21 Description: "Invalid character value for cast specification.".
Two of my parameters are indeed DateTime parameters, which I thought might be my issue so I took those out completely. Now, I'm questioning if I can even use variables this way in an OLE DB connection manager. Can anyone help?
November 24, 2015 at 5:04 pm
The parameter "name" for OLE DB is the ordinal position of the parameter starting with 0.
So with 4 parameters you'd have parameter names, 0 through 4.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply