Geeks With Blogs
Soe Tun my attempt to solve common tech problems

I was working on a SSIS Data Flow Task by passing Package Variables into a Stored Procedure.
I will be using [AdventureWorks] sample database included with the SQL Server installation. Below is the screenshot of bits and pieces of my SSIS Package configuration.

As you can see, I have defined 2 Package Variables and using the [AdventureWorks] database connection and a Flat File Connection Manager to dump out the [uspGetWhereUsedProductID] Stored Procedure's output.

My SSIS Package Configuration Overview

This is the SQL command text I am using in the OLE DB Source Editor dialog window.

EXEC [dbo].[uspGetWhereUsedProductID] ?, ?
Here is the screenshot of the Parameter Mapping.

Incorrect Stored Procedure Parameter Mapping

As you can see, Parameter0 and Parameter1 were used to match the Stored Procedure's parameters' ordinal positions.
When I execute the Data Flow Task, I get the following error message.

The SQL command requires a parameter named "@ParameterName", which is not found in the parameter mapping.
component "OLE DB Source" (1) failed the pre-execute phase and returned error code 0xC0207014.

It was obvious that I am not mapping the Stored Procedure's parameters with the SSIS Package Variables correctly.
I looked up OLE DB Source in the MSDN Library Documentation.
The Specifying Parameters by Using Names section used AdventureWorks database's [uspGetWhereUsedProductID] stored procedure as an example and passing in @StartProductID and @CheckDate parameters.

The following SQL statement runs the uspGetWhereUsedProductID stored procedure, available in the AdventureWorks database.

EXEC uspGetWhereUsedProductID ?, ?

The stored procedure expects the variables, @StartProductID and @CheckDate, to provide parameter values.
The order in which the parameters appear in the Mappings list is irrelevant.
The only requirement is that the parameter names match the variable names in the stored procedure, including the @ sign.

That is *exactly* what I am doing and I am getting the error.
So I am pretty sure the MSDN Documentation is not clear enough (or maybe even incorrect).

Additionally, I looked up How to: Map Query Parameters to Variables in a Data Flow Component listed under the Configuring the OLE DB Source section.
Again, it doesn't give any more information. By this point, I was getting pretty frustrated.

I browsed the MSDN Forums and discovered that many other developers are having the SAME problem.
I carefully re-read the OLE DB Source documentation in case I missed something and finally noticed this line.

The only requirement is that the parameter names match the variable names in the stored procedure, including the @ sign.
I added/replaced the following with the actual Stored Procedure's Parameter names.
  • The Question Marks (?) in the SQL command text
  • Parameter0 and Parameter1 in the Set Query Parameters dialog window
Correct Way to Map Stored Procedures Parameters in OLE DB Source

I was very happy to discover that my SSIS Package runs successfully after the changes.

I really wished the MSDN Documentation team gave more details on OLE DB Source Parameter Mapping.
It would have saved me the headache of looking for a solution and feeling like pulling out my hair.

Happy Programming,
Soe

Posted on Thursday, March 5, 2009 4:20 PM | Back to top


Comments on this post: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
thank you for this great posts, the screenshots helped soo much.
Left by Brian Brinley on Apr 06, 2009 10:54 AM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Hello

Mi friend thanks a lot.
I convert my query to stored procedures because sql 2000 dosn't some options like recompile.

You discover help me a lot
Left by Daniel Garcoa on Apr 21, 2009 8:11 PM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Your diligence in posting this is appreciated. You have saved me alot of time.

Thanks!
Left by lester on May 19, 2009 3:07 PM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Thank you so much for the post... I had been having nightmare to find this solution...
Left by Raj on May 20, 2009 12:43 PM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Thank you for your detailed write up. The solution saved my time.
Left by mercy on Jun 17, 2009 5:00 AM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
I had given up on using stored procs till today!

You are the man!

Thanks so much for this posting.
Left by Chris Glick on Jun 29, 2009 5:38 PM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Thanks! I'd been using either ADO or Expressions to create the sql string manually. This really helped!
Left by Marco on Jul 08, 2009 8:44 AM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
dude - I seriously was about to punch a wall. this tip was worth more than the $40 book i just bought.
Left by Scott Elkin on Jul 10, 2009 3:14 AM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Hi Soe!
I followed this but it doesn't seem to work for me. I'm using 2005 then I found this link http://www.bokebb.com/dev/english/2006/posts/2006107267.shtml

It's giving me error message "attempted to read or write protected memory. This is often an indication that other memory is corrupt." But if I manually put in the value for my pay period parameters it works perfectly fine using OLE DB Source.

Am I still missing something still? Let me know please.
Thanks!
Left by Lhay on Jul 21, 2009 11:01 AM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Hi Lhay,

When are you getting that "attempted to read or write protected memory" error?
Are you getting the error during:
(1) the SSIS runtime?
(2) the OLE DB Source Editor Designer dialog UI window?

Are you using a Stored Procedure (or) just SqlCommand syntax?


Right now, I just tested with a SQL Server 2005 for the [AdventureWorks] database.
Here is what I did step-by-step.
(1) In the property panel (press the 'F4' key), set the "AccessMode" for OLE DB Source Editor to "SQL Command".
(2) Use this query syntax.

SELECT [ProductID]
,[Name]
,[ProductNumber]
,[ModifiedDate]
FROM [AdventureWorks].[Production].[Product]

WHERE [ModifiedDate] > ?
and [ModifiedDate] < ?
ORDER BY [ModifiedDate]

(3) In the OLE DB Source Editor dialog window, assign 'Parameter0' and 'Parameter1' to the corresponding SSIS Package Variables (in my case, they are User::StartDate and User::EndDate).

(4) Then export it to a Flat File Connection (C:\OutputText.txt in my case)


Everything ran fine for me. So you must be doing something totally different.
I would check the OLE DB Source properties and also the Connection Managers you are using.

Hope it helps,
Soe
Left by Soe on Jul 21, 2009 3:10 PM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
good job done dear Soe
Left by Virender Sihag on Jul 23, 2009 4:09 AM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Hi Soe,
Thank you for this info but it still doesn't work for me. I'm not sure why, I've done the instruction as it is but unfortunately doesn't work still.

I read it somewhere that the placeholder ? only works for OLE DB then 0 or 1 depends on the number of parameters works on I forgot.. I was trying to find that thread somewhere.

Thanks again.
Left by Lhay on Jul 23, 2009 3:46 PM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
When I tried using Execute SQL Task, this query works and I mapped my variable User::xpay_ending_dates1 = 0, User::xpay_ending_dates2 = 1

But when I do the same thing in OLE DB Source It wouldn't get me far and couldn't even parse the query. I did the instruction above as it is but it wouldn't work yay!

SELECT Empno as Employee_Number, PayEndingDate as Pay_Ending_Date, EarnDeductCode as Earning_Code, ChequeNo as Cheque_Number, PayGrp as Pay_Group, Rate, Unit, Earn as Earning, Bena as Benefit, Dedu as Deduction from History_Detail where (PayEndingDate =0 or PayEndingDate =1) and (Rate <> 0 or Earn <>0 or Bena <> 0 or Dedu <> 0)
Left by Lhay on Jul 24, 2009 12:31 PM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
This is a great writeup with screen shots that are really helpful. I followed to the 'T', but still getting a message "No value for one or more required parameters. (Microsoft SQL Native Client)".
Did you use Microsoft SQL Native Client?
Thnx
Sam
Left by sam on Jul 24, 2009 12:46 PM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
@Sam
I am using [OLE DB Connection] in the [Connection Managers] as you can only use [OLE DB Source] data flow task with an OLE DB Connection only.

You cannot use the [Preview] button either.
You can only see the output at runtime so I'd recommend Executing the SSIS Package and see for yourself.



@Lhay
Hi Lhay, I cannot troubleshoot anymore from what you have described.

Why don't you do the following?
(1) create a *test* SSIS package based on SQL Server 2005's version of [AdventureWorks] database.

(2) then try doing the same thing yourself and see if you are still getting the error.

(3) if you are getting the error, please contact me through the http://geekswithblogs.net/stun/contact.aspx link

(4) I will reply to your email when I receive it and you can send me the SSIS package as an attachment to me.

Because I prefer not to post my email address online to avoid spam emails.


Hope it helps,
Soe
Left by Soe on Jul 24, 2009 2:02 PM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Hi Soe,
Thnx Soe for the reply. That explains it, when I use "Preview" button I get the error message.
I am new to SSIS. I am trying to execute the stored procedure and put the results into a flat file or excel. How can you get the columns without preview, either flat file or excel needs columns from 'OLE DB Source' for mapping.
Sam
Left by Sam on Jul 24, 2009 5:15 PM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Add to the above:
As a separate test I executed the stored procedures as SQL COMMAND for this 'OLE DB Source', with parameter values hard coded (for eg. exec dbo.sptest 'A','B','C'), preview works. However, under COLUMNS there are no 'Avaliable external columns'. Am I missing some thing. I am using STD version of SQL SERVER 2005. This is not exactly related to this article. Thnx for your help. SAM
Left by Sam on Jul 24, 2009 9:07 PM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
I'm having the same issue as well. I can't see the columns therefore can't use them in Scripts or anything like that.
Left by Wes on Jul 27, 2009 1:28 PM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
geeeezz I had it working in OLE DB Editor. I followed the instructions above and didn't check the parse query and just run it and it works! Thanks Soe!!! I find out when I did the same thing in Adventure Works and run the parse query and it gives out an error message. Don't worry about parsing just run the package as it is. Last thing I need to do now is how to pass that parameter from the frontend to SSIS. Suggestion please? Thanks again!
Left by Lhay on Jul 27, 2009 4:45 PM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
I run the SSIS package using the dtsrun.exe utility by calling it from a Stored Procedure that is configured to accept the SSIS Package Variables.
Left by Soe on Jul 27, 2009 7:44 PM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
I finally got it to work. Those of you who were having problems with no available columns, you must first publish meta data in the stored procedure (Note-do it as 1st thing). Follow the advice in this link http://www.windows-tech.info/15/e7e4a96178d88c7e.php
It worked for me. Don't forget the statement 'If 1=0' statement you need it, it publishes the meta data with no output.
Good luck.
Left by Sam on Jul 28, 2009 1:20 PM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Helloooo,
I had this stored procedure below using dtec utility. It works for me.

The thing is I created variable like this for ssis mypackage:

name scope data type value
startdate mypackage string c:\startdate.txt
enddate mypackage string c:\enddate.txt

I can't use the datetime data type for the variable declaration since that would constant and can only be change when you open the package. Instead I passed the value to startdate.txt and enddate.txt from the frontend. Problem I'm having right now is how to convert it to date since my OLE DB SOURCE sql command is like this

EXEC [dbo].[spGetPayrollDetails] @PayStartDate= ?, @PayEndDate=?

and the parameter :

Parameters Variables
@paystartdate User::paystartdate
@payenddate User::payenddate

These 2 are in different data types. Let me know please almost there!

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


CREATE PROCEDURE [dbo].[sp_mtg_upload]

AS
Set NOCOUNT OFF;


DECLARE @rc int
EXEC @rc = master.dbo.xp_cmdshell 'dtexec /f c:\ssis\packages\fba\mtg_upload.dtsx'
print @rc
IF @rc <> 0
BEGIN
PRINT 'Copy Failure Skip work'
END
ELSE
BEGIN
Print 'Copy worked now we can do some more stuff'

END

Left by Lhay on Jul 28, 2009 1:43 PM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Hi Lhay,

here is how I do it using the DTExec command line utility. It works for me without any problem.
The only thing you have to be careful is setting up the dtsExecCmd string.




CREATE PROCEDURE [dbo].[TestRunMySSISPkg]

@ssisPkgFilePath varchar(500),
@ssisPkgConfigFilePath varchar(500), -- SSIS pkg config file physical file location in case you want to use extra config settings

@startDate varchar(10), -- notice how I am passing in the Date variables as VarChar? You can probably pass in a DateTime type also
@endDate varchar(10) -- but if you pass in as DateTime data type, you'll need to convert it into a string format

AS
begin
--{ stored proc starts


DECLARE @dtsExecCmd varchar(4000)
SET @dtsExecCmd = 'dtexec /F "' + ltrim(rtrim(@ssisPkgFilePath)) + '"'
SET @dtsExecCmd = @dtsExecCmd + ' /CONFIGFILE "' + @ssisPkgConfigFilePath + '"' -- SSIS package config file for Database Connection
SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::StartDate].Properties[Value]";"\"' + @startDate + '\""'
SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::EndDate].Properties[Value]";' + @endDate


-- ================================================================================
-- Run the SSIS package by using DTSEXEC command
-- ================================================================================
-- Variable to hold the RETURN code from the DTEXEC utility
DECLARE @result INT

-- Declare TEMP table variable to hold the output messages from the SSIS package
DECLARE @output TABLE( [ssisOutput] varchar(max) )
INSERT INTO @output
--{
EXEC @result = master..xp_cmdshell @dtsExecCmd
--}




-- ================================================================================
-- Check if any error occured
-- ================================================================================
IF (@result is not null and @result<>0) OR EXISTS(select 1 from @output where ssisoutput like '%error%')
begin
--{
PRINT 'SSIS package ''' + @ssisPkgFilePath + ''' failed.'

SELECT * FROM @output

-- ===================================
-- Perform error logging as needed.
-- ===================================
--}
end



-- RETURN code from the DTEXEC utility
RETURN @result;



--} stored proc ends
end
Left by Soe on Jul 28, 2009 2:32 PM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
You might have to change the following lines because the above code I have was copy-n-pasted from my existing code and I changed some of it afterwards.
So you may need to replace it with these two lines below.

SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::StartDate].Properties[Value]";"\"' + @startDate + '\""'
SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::EndDate].Properties[Value]";"\"' + @endDate + '\""'




Good luck with your project,
Soe
Left by Soe on Jul 28, 2009 2:43 PM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Hi Soe,
That is very informative!!!
Here's the stored proc which I run from my OLE DB Source. The actual field from the table is datetime. As I mentioned I can't used datetime in the package since I'm passing the value from the frontend to a textfile then get the result passed it back to the variable created in my package.

It works if it's datetime variable from my package but when I used that textfile it no longer works for me.yay!!!!

SQL Command in OLE DB Source:
EXEC [dbo].[spGetPayrollDetails] @PayStartDate=? , @PayEndDate =?


---Actual stored proc

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go



create PROCEDURE [dbo].[spGetPayrollDetails]
(
@PayStartDate varchar(10),
@PayEndDate varchar(10)
)
AS
BEGIN
SET NOCOUNT ON;


SELECT Empno as Employee_Number, PayEndingDate as Pay_Ending_Date,
EarnDeductCode as Earning_Code, ChequeNo as Cheque_Number,
PayGrp as Pay_Group, Rate, Unit, Earn as Earning, Bena as Benefit,
Dedu as Deduction from History_Detail
where
(PayEndingDate =cast(@PayStartDate as datetime) or PayEndingDate = cast(@PayEndDate as datetime))
and (Rate <> 0 or Earn <>0 or Bena <> 0 or Dedu <> 0)


end;
Left by Lhay on Jul 29, 2009 12:05 PM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
So ok if I put the value for the variable in my package as 2009-06-26 and 2009-07-03 then it would work no problem. But if use the C:\paystartingdate.txt and C:\payendingdate.txt, since it changes and I passed the value to these text file from the frontend it wouldn't work! I keep repeating myself. *sigh

I've changed and convert the datetime value in my stored proc which look like this: I don't know what else should I do. Help please heeeellllppppppp...

CREATE PROCEDURE [dbo].[spGetPayrollDetails]
(
@PayStartDate varchar(10) ,
@PayEndDate varchar(10)
)
AS
BEGIN
SET NOCOUNT ON;

declare @conPayStartDate datetime ,
@conPayEndDate datetime


set @conPayStartDate = convert(datetime ,@PayStartDate,20)
set @conPayendDate = convert(datetime ,@PayendDate,20)


SELECT Empno as Employee_Number, PayEndingDate as Pay_Ending_Date,
EarnDeductCode as Earning_Code, ChequeNo as Cheque_Number,
PayGrp as Pay_Group, Rate, Unit, Earn as Earning, Bena as Benefit,
Dedu as Deduction from History_Detail
where
(PayEndingDate =@conPayStartDate or PayEndingDate = @conPayEndDate )
and (Rate <> 0 or Earn <>0 or Bena <> 0 or Dedu <> 0)


end;





Left by Lhay on Jul 29, 2009 3:49 PM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Thanks a lot for documenting 'undocumented features' of SSIS.
The moral of the story:
Use SSIS componenets only when it is a MUST. otherwise use your expertise: SQL, XP batch commands, VB script etc.
Microsoft beware: get your features documented, make them simple or else...
Left by MK on Aug 04, 2009 9:38 AM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Great info. Thanks.
Left by John Shanks on Aug 13, 2009 12:49 PM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Thanks. was very useful
Left by Tim on Aug 31, 2009 4:34 PM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Thanks so much. I had the exact same problem and you've saved me a lot of searching and trial & error. It would be really nice if SSIS would just fill in the parameter names instead of filling in "Parameter#" when you click on the parameters tab.
Left by Tom H. on Sep 14, 2009 10:46 AM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Thanks a lot! Any suggestion about the possibility to use 'dynamic' variable as input of a SP executed by an SSIS OLE DB Source Editor? I need to schedule a SP but the parameter in input can change as time goes on.

Thanks in advance
Left by Stefanovic on Sep 28, 2009 10:53 AM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Thank you so much for this post. I was struggling with different other options for this task since the source editor wasn't taking the parameters directly with a ?

Cheers.. :)
Left by Santosh on Dec 02, 2009 3:59 AM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
I am trying to set a parameter to a bapi call..as the source that I need to fetch the daa is from SAP.Everything works fine if I specify "exec BAPIGETDETAILS @Date='20091212'".But how do I set a variable to the date and getdate() into that parameter and pass it to the BAPI call.I tried declaring the variable at the data flow task scope.But I am not able to pass the parametr to the BAPI.Please advice
Left by Sridhar on Dec 10, 2009 2:31 AM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Hi Sridhar,

I'm not sure what you mean by BAPI call.
Here is how I execute the SSIS package.

Set up a *separate* Stored Procedure and pass in Parameter Values for the SSIS Package Variables.
Look at the sample SQL code I posted in this comment.

http://geekswithblogs.net/stun/archive/2009/03/05/mapping-stored-procedure-parameters-in-ssis-ole-db-source-editor.aspx#483033


Just use the appropriate Date Format for you SQL Server when passing in the Parameters as VARCHAR into the stored procedure.
Since mine is en-US, the date format is 'MM/dd/yyyy'. Yours might be 'dd/MM/yyyy'.

It *should* work. If it doesn't, I wouldn't be able to figure out what is wrong from the info you've given.

Hope it helps,
Soe
Left by Soe Tun on Dec 20, 2009 3:57 PM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Hi Soe,Thanks for your comment.I am trying to fetch data from SAP so I have to connect to SAP.And I have to execute a BAPI by passing the system date as the parameter.For that reason I have to setup a variable and pass the sysdate into that variable.I managed to set up the variable at the control flow scope.But when I am trying to execute the package I am getting this error "Unable to cast object of type 'System.Data.SqlClient.SqlParameter' to type 'Microsoft.Adapter.SAP.SAPParameter'." any ideas?
Left by Sridhar on Jan 10, 2010 9:39 PM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Am very new to SSIS and am working on some existing packages.

I have a SP which returns some records. Am executing that SP and writing the result to a txt file using SSIS. Now I have the requirement of appending the number of records written in to the file in the Name of the file.

Also I need to have a footer in the file. WHich will have some text and the number of records written in the file.

How can I achieve these things.
Left by Abinash Patra on Jan 11, 2010 7:41 AM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Thanks for this very useful article SOE. I was about to throw my pc out of the window as I couldn't work out what I was doing wrong! Like you, I didn't find the MS documentation to be very helpful at all. Everythings working fine now.
Left by Debbie Charles on Jan 19, 2010 5:39 AM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Thank you so much for the article, you saved me from insanity!
Left by Michael on Feb 01, 2010 3:39 PM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
I struggled a lot with all this and this article is a real time saver!

Just note that the parameters are CASE SENSITIVE... :-) Cost me half an hour to learn that ;-)
Left by JOnthemoon on Feb 28, 2010 9:26 PM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Thanks, best explanation out there. MSDN can learn something from here. In case you're not using a stored proc.

select *
from dbo.whatever_table
where Part_Number = ?

In the Parameters dialog, in the Parameter column, type in "Part_Number".
Left by JL on Mar 10, 2010 6:05 PM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
I was looking this help from long time. I appreciate the efforts you put in to make it very simple and easy to understand at very first instance.

Trilok
Left by Trilok on Apr 30, 2010 12:04 PM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
i dont have words to praise you....helping others like me wont forget ur help my friend...you are saving our time by giving this code......i realy thanks for ur help....
Left by Rama Rao on May 18, 2010 2:11 AM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
thanks alot!
great info, and more then that, the screen shot is ciritcal in this case, beacuse not always the explanation is as good as screen shot .
"A picture is worth 1000 words" :)
Left by peleg on Jun 21, 2010 3:32 AM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
I am trying to do something similar. For every row in a csv file, I need to get data from the OLEDBSource. How do I pass the data from the csv to the OLEDBSource? Thanks.
Left by Dina Kwan on Jun 29, 2010 7:59 PM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
The article was a big help. The text was precise and valuable.
Left by jaya arora on Jul 01, 2010 6:03 AM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Thank you very much, this article give me a big big help. :)
Left by sandals on Jul 19, 2010 3:08 AM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Great article!!

My wish is that the SSIS development team would make passing parameters use the same pattern throughout SSIS. In some places you must specify the parameters names, like this case. In other places it is by ordinal position. The documentation is never clear on when to use which pattern. Frustrating ...

But people like you, sharing what they've found, make solving these problems easier, thank you..
Left by DBA Dave on Sep 15, 2010 7:58 PM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Great article..i had similar issuea nd i almost felt like killing microsoft people... thank you for sharing the solution... :)
Left by Sasidhar on Sep 20, 2010 8:16 AM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Thanks a lot for the info. Saved me a lot of time!
Left by Eper on Sep 22, 2010 12:27 PM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
This was a greattttttttt Help
Left by Gigy on Sep 22, 2010 3:42 PM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Thanks for the tip! I was having trouble and had everything looking like it was supposed to work -- just like you did! Apparently not...

But after renaming Parameter0 to the name of the parameter in the sproc, life is good! I might even be able to hit my impossible deadline now.
Left by Tommy on Oct 05, 2010 6:36 PM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Thanks! You solved my problem very quickly. the screen shots made it easy to get to the root of the problem.
Left by Marianne Daye on Oct 21, 2010 4:40 PM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
This article helped me so much, I spent couple of hours trying to find out how to get rid of this error. Thank you
Left by Saša Dragičević on Nov 10, 2010 10:24 AM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Thanks Dear.. it saved me also
Left by Satish on Nov 10, 2010 4:06 PM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Very Helpful!!!.... Great Article!!

Thanks,
Monali
Left by Monali on Jan 04, 2011 3:02 PM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Thanks for the article, it is very helpful :)
Btw, your name sounded like Burmese name? are you Burmese?
I'm Burmese too.
Anyway, thanks for the article man.
:)
Left by EiSu on Jan 20, 2011 8:54 AM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Hi, thx for the article, by the way in my another case, i need to run this SSIS package witihin SQL syntax, such as

exec master..xp_cmdshell 'C:\"Program Files (x86)"\"Microsoft SQL Server"\90\DTS\Binn\DTEXEC.EXE /sq "leadDist4SO" /SET \Package.Variables[User::PlannedDateFrom].Properties[Value];"2011-02-007"'

why above syntax is always fail, how to parse a datetime variable for this, because if we parse integer or character, the syntax is running OK
Left by Haryadi Santoso on Feb 28, 2011 4:46 AM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
thanks mate save 1/2 a days work
Left by suv on Mar 22, 2011 5:29 AM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
You do not need the Parameter name before the ?.

From your example: The only thing wrong with the Incorrect Mapping example is that you didn't name your parameters to match that of the Stored Procedure. If you would set the "Set Query Parameters" the same as the picture for the Correct Mapping, but continue to set the SQL command text as... EXEC [dbo].[uspGetWhereUsedProductID] ?, ? -- it'll work just fine. Please note that the Preview... option just doesn't work, but the package will run successfully and you will be able to see your Columns and Error Output just fine.
Left by Shelly on Apr 08, 2011 11:09 AM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
EXEC uspGetWhereUsedProductID ?, ?

The above statement also will work. only thing is thatr yopu need to give the correct @Parameter name in the maping screen instead of the default one.
Left by Bineesh Thomas on Apr 14, 2011 9:39 AM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Thanks for your Post Soe....Nice post with clear explanation and screen shot...it really helped me
Left by Nithya on May 25, 2011 7:14 AM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Good Stuff. Love the screenshots.
Left by Kenny on May 25, 2011 12:23 PM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
How to handle the output parameters, my stored has one input parameter and one output parameter.
Left by Surendra on Jun 28, 2011 3:33 AM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Hi Soe,
Could you please help me , my sproc doesnt have any parameters..now how I should execute it and dump its result in flat file..
Left by Sanali on Jun 30, 2011 4:26 PM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Thank you!! it is a great help
Left by Naresh Reddy on Jul 07, 2011 3:46 PM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
I like avatars!Where it I can loding ?
Left by Oleksa on Jul 10, 2011 3:27 AM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Thanks a lot for your tip....its a life saver.

I was running this SSIS Package from .net program.

Previously I found following frustrating error:

"No column information was returned by the SQL command."

The reason Strored proc could not return any result set is parameters were not mapped correctly as I was using Native OLEDB\Microsoft OLEDB Provider for SQL Server.

Later I changed Provider to Native OLEDB\SQL Server Native Client 10.0 I could able to execute SSIS package with out any ERROR.
Left by Naresh Reddy on Jul 22, 2011 4:04 PM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Thanks man
Left by gopal on Aug 01, 2011 10:20 AM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Hi,

i am trying to execute a SP on DB2 using oledb for db2,will that work if i use the same steps?
Left by Ragz on Aug 02, 2011 5:05 PM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Hi Ragz, that I don't know how to answer.
SSIS should be Database-Vendor independent.

So I assume anything you can connect using the OLE DB Connection, it should work.
Left by Soe Tun on Aug 02, 2011 10:01 PM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Perfect Screenshots, thank you very much
Left by Ralf on Aug 23, 2011 9:54 AM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Thanks, screenshots helped
Left by VS on Aug 30, 2011 9:11 AM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Hi,

I have two tables
Ex: Employee and Department [Departments: 10,20,30]

For each department I have 10 employee records in Employee table and I want to generate text files for each department no.
for example
Txt 1
EmpId Empname DeptId
Ap1 A 10
Ap2 B 10
'
'
'
'
'Ap10 J 10

and the same thing for dept =20 and 30 also.

Could you please help me on this.

Thanks Vinay N.
Left by Vinay on Aug 31, 2011 2:16 AM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
This article helped me lot . Thank You for Posting
Left by malar on Sep 13, 2011 3:15 AM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Thanks Soe... it IS very frustrating that we use the parameter ordinal positions when setting up parameters for an Execute SQL Task that calls a stored procedure, but have to remember to use the actual parameter NAME if we are calling the stored procedure from within an OLE DB Source. Very annoying of Microsoft to allow for that inconsistency, but thanks to your article, my solution which required a call of a stored proc as a data source now runs!
Left by Pat Mundy on Sep 20, 2011 4:51 PM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Thank you, thank you for posting your findings!

Microsoft documentation is lacking for setting up the parameters.

I had the additional headache of setting up a stored procedure that is using a parameter with a DATE data type. I got an error message "Operand type clash: int is incompatible with date". After altering the stored procedure's parameter data type to DATETIME, it was a piece of cake to set up the OLE DB Source.
Left by fran on Sep 22, 2011 6:44 PM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Thanks. Nice explanation.
Left by DT on Oct 13, 2011 3:34 PM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Hi Soe. Thanks for the nice document. I had the same issue and did some tests. It turns out that the issue is on the parameter mapping piece. You have to put the parameters used in the SP instead of default names and those parameters have to be EXACT same . (if parameters in SP are uppercase, they have to be uppercase).

Also in the SQL command text, you do not need to put parameter names explicitly. In your case,
You can put EXEC dbo.uspGetWhereUsedProductID ?,?
Instead of EXEC dbo.uspGetWhereUsedProductID @StartProductID=?, @CheckDate=?.

I've tested in my case and it works without parameter names.

Thanks
Left by Hui Shi on Nov 17, 2011 12:32 PM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
i love you!
Left by BlueRed on Nov 29, 2011 10:46 AM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Excelent Post.... Great work and congratulations for the screen shots...very helpful
Left by Benny Blanco from the Bronx on Dec 10, 2011 11:24 AM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Thank you So much.... the explanation with snapshots is just too good.
Left by Ehrar on Dec 25, 2011 3:58 PM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Gud Work..
Left by Sachin Jha on Feb 02, 2012 7:21 AM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Thank you for this post. You saved me lots of time.
Left by Peter Seewald on Feb 23, 2012 1:40 PM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Thanks, it helped me.
Left by mahesh on Mar 21, 2012 5:35 PM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
thanks lot of about this sinario,but i need the solution how to execute the multiple stored procedure and how to load the data to the excel destination using ssis package
Left by madhu on Mar 23, 2012 12:16 AM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Thanks a lot for this great article, it helped me so much.

Keep up the good work!
Left by Harry on May 02, 2012 2:50 AM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
I faced this problem. can u pls tell. how to pass parameter in OLEDB.
I select SQL Command
exec [dbo].[cal] @p_table_name = ?,
@p_include_foreign = ?
and Mapping the parameter
After finished.I check preview.
Sqlcmd: Error: Microsoft SQL Native Client : No value given for one or more required parameters
kindly help me
Left by Dhivya on Jun 11, 2012 4:33 AM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Hi Soe,

Thanks a lot, You have saved a lot of time for me.
Left by Tillu on Jun 13, 2012 1:34 PM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
ur awesome thanks a lot
Left by shanty on Jul 19, 2012 1:05 PM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Thanks so much...this solved my issue....much appricated!
Left by SSIS on Aug 21, 2012 10:13 PM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Thanks, this helped a lot
Left by Christian Bahnsen on Sep 17, 2012 1:50 PM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Thanks for posting this - really helpfuf
Left by SteveA on Sep 21, 2012 5:47 PM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Thank you very much. Saved my time.
Left by Nirmal sahar on Nov 07, 2012 11:32 AM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
This is great. Way better than other posts I've researched. The screen shots are great. And you write very well. Very helpful post!
Left by ED on Dec 05, 2012 1:32 PM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Thank you for the helpful article. Saved me time and effort.
Left by SS on Dec 24, 2012 9:43 AM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Thanks for the info. This worked but only when I added SET FMTONLY OFF to the top of my stored procedure.
Left by Michael Pearson on Jan 08, 2013 9:55 AM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Thanks! This post help me. Respect from Brazil.
Left by LUCAS on Jan 15, 2013 9:12 AM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
"No column information was returned by the SQL command."

I thought I would post this it might help somebody. if you get t his error message when running a temp table in your stored procedure add the command Set fmtonly OFF to your procedure. Also set the ValidateExternalMetadata to false.
Left by mcliff on Jan 22, 2013 5:49 PM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Thank you for the post, the screen shots helped to fix the issues with parameter mappings in my packages

Sreens
Left by Sreens Sayini on Feb 04, 2013 3:24 PM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Thank you for being so clear. I wish my ton of books were half as clear.
Left by Lori K on Apr 02, 2013 11:02 AM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Brilliant mate!!!! so simple and easy it looks to work with SPs as data source
Left by Sam on May 19, 2013 7:11 AM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Good things should get appreciated... Thanks alot buddy... You saved loads of time... Thank you so much.... :)
Left by Unais on Jun 12, 2013 5:01 AM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
I have used SQL command acccess mode in OLEDB source and wrote below query. Then i clicked Parameters, to set parameter but i get error "Parameters cannot be extracted from the SQL command. ===================================

SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005." So one.
below is the query please suggest solution for this'


Query:
EXEC [dbo].[USP_SE_WSL_One_Stop_Shop_Parameter]
@InterfaceName =? ,
@HQAccountNumber =? ,
@YearQtr =? ,
@WeeksReported =? ,
@MaxWeeksReported =? ,
@HQAccountName =?,
@PEVpercentage =?,
@OutboundOpcoVol =? ,
@TranshipOpcoVol =?,
@InternetOpcoVol =?,
@NRNDDOpcoVol =?,
@OtherOpcoVol =?,
@ExcessOfCapVol =?,
@PaymentOpcoVol =? ,
@PEVObligCutOffPct =?;
Left by kumarvik on Aug 17, 2013 7:32 AM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
thanks for the great article, it is quite helpful
Left by jampa on Aug 27, 2013 9:21 PM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Excellent post, you've saved me a lot of headache and time. I really appreciate this post.
Left by Ryan on Oct 04, 2013 10:39 AM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Great post! This was driving me crazy!
Left by Neely on Oct 10, 2013 2:11 PM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Hi Soe.

It's very useful this information but I've got a doubt, how could I get a resourcet from the SP and catch it into a flat file? I mean my SP retunrs a resulset instead of one variable, so when I write the EXEC line and press the buton Build Query send me a warning that say: "SQL EXEC function is not admited", Thanks and regards.
Left by Ricardo on Dec 09, 2013 10:12 AM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
This was really helpful, i had to struggle for a while. Just to add, you really don't need to add the variable name in exec SP statement. The only requirement is to match the parameter mapping name exactly to the variable defined in the SP.

In this case example:

SQL command:
EXEC uspGetWhereUsedProductID ?, ?

Parameter mapping should be:
Parameters Variables
@StartProductID User::StartProductID
@CheckDate User::CheckDate

Left by Shrestha_SQL on Jan 23, 2014 5:12 PM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
The parameter mapping is available jus in case of a stored procedure? I want to use this syntax in a function... but I have errors...
Left by RAM on Feb 04, 2014 7:55 AM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Soe, thank you!
Left by Jay on Feb 11, 2014 4:20 PM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
I was pulling my hair out on this one. you kept me from going BALD!!!
Left by VS2012 Newbie on Feb 19, 2014 10:44 AM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Good Job
Left by Hiral on Jun 06, 2014 10:56 AM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
Thank you for the post, it helped me fix the issue
Left by Ramesh on Oct 09, 2014 11:38 AM

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor
Requesting Gravatar...
So procedures can not be understood by SSIS because of the complex IF logic or because of compilation errors
Left by zhrist on Jun 16, 2015 7:29 AM

Your comment:
 (will show your gravatar)


Copyright © Soe Tun | Powered by: GeeksWithBlogs.net | Join free