Soe Tun

my attempt to solve common tech problems

  Home  |   Contact  |   Syndication    |   Login
  14 Posts | 0 Stories | 212 Comments | 0 Trackbacks

News

Twitter







Archives

Post Categories

Image Galleries

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

Feedback

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 4/6/2009 10:54 AM Brian Brinley
thank you for this great posts, the screenshots helped soo much.

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 4/21/2009 8:11 PM Daniel Garcoa
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

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 5/19/2009 3:07 PM lester
Your diligence in posting this is appreciated. You have saved me alot of time.

Thanks!

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

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 6/17/2009 5:00 AM mercy
Thank you for your detailed write up. The solution saved my time.

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 6/29/2009 5:38 PM Chris Glick
I had given up on using stored procs till today!

You are the man!

Thanks so much for this posting.

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 7/8/2009 8:44 AM Marco
Thanks! I'd been using either ADO or Expressions to create the sql string manually. This really helped!

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

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 7/21/2009 11:01 AM Lhay
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!

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 7/21/2009 3:10 PM Soe
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

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 7/23/2009 4:09 AM Virender Sihag
good job done dear Soe

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 7/23/2009 3:46 PM Lhay
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.

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 7/24/2009 12:31 PM Lhay
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)

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 7/24/2009 12:46 PM sam
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

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 7/24/2009 2:02 PM Soe
@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

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 7/24/2009 5:15 PM Sam
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

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 7/24/2009 9:07 PM Sam
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

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 7/27/2009 1:28 PM Wes
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.

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 7/27/2009 4:45 PM Lhay
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!

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 7/27/2009 7:44 PM Soe
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.

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 7/28/2009 1:20 PM Sam
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.


# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 7/28/2009 1:43 PM Lhay
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



# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 7/28/2009 2:32 PM Soe
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

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 7/28/2009 2:43 PM Soe
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

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 7/29/2009 12:05 PM Lhay
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;


# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 7/29/2009 3:49 PM Lhay
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;







# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 8/4/2009 9:38 AM MK
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...

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 8/13/2009 12:49 PM John Shanks
Great info. Thanks.

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 8/31/2009 4:34 PM Tim
Thanks. was very useful

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 9/14/2009 10:46 AM Tom H.
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.

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 9/28/2009 10:53 AM Stefanovic
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

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 12/2/2009 3:59 AM Santosh
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.. :)

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 12/10/2009 2:31 AM Sridhar
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

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 12/20/2009 3:57 PM Soe Tun
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

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 1/10/2010 9:39 PM Sridhar
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?

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 1/11/2010 7:41 AM Abinash Patra
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.

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 1/19/2010 5:39 AM Debbie Charles
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.

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 2/1/2010 3:39 PM Michael
Thank you so much for the article, you saved me from insanity!

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 2/28/2010 9:26 PM JOnthemoon
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 ;-)

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 3/10/2010 6:05 PM JL
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".

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 4/30/2010 12:04 PM Trilok
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

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 5/18/2010 2:11 AM Rama Rao
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....

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 6/21/2010 3:32 AM peleg
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" :)


# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 6/29/2010 7:59 PM Dina Kwan
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.

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 7/1/2010 6:03 AM jaya arora
The article was a big help. The text was precise and valuable.

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

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 9/15/2010 7:58 PM DBA Dave
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..

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

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 9/22/2010 12:27 PM Eper
Thanks a lot for the info. Saved me a lot of time!

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 9/22/2010 3:42 PM Gigy
This was a greattttttttt Help

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 10/5/2010 6:36 PM Tommy
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.

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

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

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 11/10/2010 4:06 PM Satish
Thanks Dear.. it saved me also

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 1/4/2011 3:02 PM Monali
Very Helpful!!!.... Great Article!!

Thanks,
Monali

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 1/20/2011 8:54 AM EiSu
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.
:)


# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 2/28/2011 4:46 AM Haryadi Santoso
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

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 3/22/2011 5:29 AM suv
thanks mate save 1/2 a days work

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 4/8/2011 11:09 AM Shelly
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.

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 4/14/2011 9:39 AM Bineesh Thomas
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.

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

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 5/25/2011 12:23 PM Kenny
Good Stuff. Love the screenshots.

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

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 6/30/2011 4:26 PM Sanali
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..

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 7/7/2011 3:46 PM Naresh Reddy
Thank you!! it is a great help

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 7/10/2011 3:27 AM Oleksa
I like avatars!Where it I can loding ?

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 7/22/2011 4:04 PM Naresh Reddy
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.


# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 8/1/2011 10:20 AM gopal
Thanks man

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 8/2/2011 5:05 PM Ragz
Hi,

i am trying to execute a SP on DB2 using oledb for db2,will that work if i use the same steps?

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 8/2/2011 10:01 PM Soe Tun
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.

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 8/23/2011 9:54 AM Ralf
Perfect Screenshots, thank you very much

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 8/30/2011 9:11 AM VS
Thanks, screenshots helped

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 8/31/2011 2:16 AM Vinay
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.

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 9/13/2011 3:15 AM malar
This article helped me lot . Thank You for Posting

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 9/20/2011 4:51 PM Pat Mundy
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!

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 9/22/2011 6:44 PM fran
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.

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 10/13/2011 3:34 PM DT
Thanks. Nice explanation.

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 11/17/2011 12:32 PM Hui Shi
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


# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 11/29/2011 10:46 AM BlueRed
i love you!

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 12/10/2011 11:24 AM Benny Blanco from the Bronx
Excelent Post.... Great work and congratulations for the screen shots...very helpful

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 12/25/2011 3:58 PM Ehrar
Thank you So much.... the explanation with snapshots is just too good.

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 2/2/2012 7:21 AM Sachin Jha
Gud Work..

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 2/23/2012 1:40 PM Peter Seewald
Thank you for this post. You saved me lots of time.

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 3/21/2012 5:35 PM mahesh
Thanks, it helped me.

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 3/23/2012 12:16 AM madhu
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

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 5/2/2012 2:50 AM Harry
Thanks a lot for this great article, it helped me so much.

Keep up the good work!

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 6/11/2012 4:33 AM Dhivya
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

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 6/13/2012 1:34 PM Tillu
Hi Soe,

Thanks a lot, You have saved a lot of time for me.

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 7/19/2012 1:05 PM shanty
ur awesome thanks a lot

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 8/21/2012 10:13 PM SSIS
Thanks so much...this solved my issue....much appricated!

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 9/17/2012 1:50 PM Christian Bahnsen
Thanks, this helped a lot

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 9/21/2012 5:47 PM SteveA
Thanks for posting this - really helpfuf

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 11/7/2012 11:32 AM Nirmal sahar
Thank you very much. Saved my time.

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

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 12/24/2012 9:43 AM SS
Thank you for the helpful article. Saved me time and effort.

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 1/8/2013 9:55 AM Michael Pearson
Thanks for the info. This worked but only when I added SET FMTONLY OFF to the top of my stored procedure.

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 1/15/2013 9:12 AM LUCAS
Thanks! This post help me. Respect from Brazil.

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 1/22/2013 5:49 PM mcliff
"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.

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 2/4/2013 3:24 PM Sreens Sayini
Thank you for the post, the screen shots helped to fix the issues with parameter mappings in my packages

Sreens

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 4/2/2013 11:02 AM Lori K
Thank you for being so clear. I wish my ton of books were half as clear.

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 5/19/2013 7:11 AM Sam
Brilliant mate!!!! so simple and easy it looks to work with SPs as data source

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

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 8/17/2013 7:32 AM kumarvik
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 =?;

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 8/27/2013 9:21 PM jampa
thanks for the great article, it is quite helpful

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

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 10/10/2013 2:11 PM Neely
Great post! This was driving me crazy!

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 12/9/2013 10:12 AM Ricardo
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.

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 1/23/2014 5:12 PM Shrestha_SQL
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



# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 2/4/2014 7:55 AM RAM
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...

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 2/11/2014 4:20 PM Jay
Soe, thank you!

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

# re: Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor 6/6/2014 10:56 AM Hiral
Good Job

Post A Comment
Title:
Name:
Email:
Comment:
Verification: