Using OLE DB Command to insert into a parameterised stored procedure

Posted on 2010-03-26
Medium Priority
Last Modified: 2013-11-10
Hi Experts

I have an ETL task that uses the dataflow component OLE DB Command. I have 24 inputs which I'd like to map to a parameterised stored procedure.

e.g. I specify a sql command of

exec dbo.InsertCustomer @id=?,@name=?,@email=?.... etc etc

However when I try and include >20 of the available input columns I hit an error "Invalid parameter number". In the column mapping tab I only ever see the first 20 destination parameters.

How can I workaround this in SSIS?

Thanks in advance
Question by:CallConnection
  • 2
LVL 22

Assisted Solution

PedroCGD earned 2000 total points
ID: 28699900
Make sure the number of parameters in the SP is the same than OLEDB Command...

use also this command:
EXEC InsertCustomer ?,?,?,?,?,?,?

and go to mapping to link source and destination.

Accepted Solution

CallConnection earned 0 total points
ID: 28700801
Yes I have, but anything over 20 does not come through as an "Available Destination Column".

Why can't I specify a parameter name? if I have EXEC InsertCustomer ?,?,?,?,?,?,?  it's even more confusing - I'll give it a try though.

Author Comment

ID: 28983171
Thank you. I've left it until Monday, a fresh set of eyes and have it working now I think.

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

589 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question