<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
SQL Server 2005 Integration Services
»
create a SSIS package which will call the...
create a SSIS package which will call the stored procedure and dump the output of the procedure into table
Rate Topic
Display Mode
Topic Options
Author
Message
Karan_W
Karan_W
Posted Wednesday, January 19, 2011 3:07 AM
SSC Rookie
Group: General Forum Members
Last Login: Tuesday, April 23, 2013 6:42 AM
Points: 36,
Visits: 109
I want to create a SSIS package which will call the stored procedure and dump the output of the procedure into table.
This stored procedure accept an input parameter e.g. Date which i can retrieve from another table like select max(Date) from userid and pass to procedure.
SSIS packae will return the output and i need this resultset ouput to dump into another table.
But before inserting i want to delete the rows from that tables if any rows exist for that date and then finally insert the resultset.
Post #1049873
Phil Parkin
Phil Parkin
Posted Wednesday, January 19, 2011 3:23 AM
SSCarpal Tunnel
Group: General Forum Members
Last Login: Today @ 9:29 AM
Points: 4,240,
Visits: 9,487
Karan_W (1/19/2011)
I want to create a SSIS package which will call the stored procedure and dump the output of the procedure into table.
This stored procedure accept an input parameter e.g. Date which i can retrieve from another table like select max(Date) from userid and pass to procedure.
SSIS packae will return the output and i need this resultset ouput to dump into another table.
But before inserting i want to delete the rows from that tables if any rows exist for that date and then finally insert the resultset.
..and your question is?
____________________________________________________________________________________________
Help us to help you. For better, quicker and more focused answers to your questions, consider following the advice in this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.
Post #1049882
Phil Parkin
Phil Parkin
Posted Wednesday, January 19, 2011 3:51 AM
SSCarpal Tunnel
Group: General Forum Members
Last Login: Today @ 9:29 AM
Points: 4,240,
Visits: 9,487
OK, maybe that was a bit harsh on an SSIS newbie.
After looking at this, I must admit that I found myself thinking that it would be easier to get the stored procedure to do everything, rather than faffing around with the various SSIS tasks that you would need.
Everything you have asked for (barring the initial parameterised call to the sp) can easily be accommodated in T-SQL - so why have you decided on using SSIS?
____________________________________________________________________________________________
Help us to help you. For better, quicker and more focused answers to your questions, consider following the advice in this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.
Post #1049897
Karan_W
Karan_W
Posted Wednesday, January 19, 2011 6:24 AM
SSC Rookie
Group: General Forum Members
Last Login: Tuesday, April 23, 2013 6:42 AM
Points: 36,
Visits: 109
Hi ,
Thanks for reply. Yes it can be done by SP also but requirement is to get it done by SSIS only so any help would be really appreciable.
Post #1049995
Javed-330570
Javed-330570
Posted Wednesday, January 19, 2011 6:43 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Monday, September 03, 2012 10:57 PM
Points: 192,
Visits: 157
Hi Karan,
Use the execute SQL task to run your stored procedures in the sequence required.
Regards,
Barkha.
Post #1050006
Karan_W
Karan_W
Posted Friday, January 21, 2011 12:53 AM
SSC Rookie
Group: General Forum Members
Last Login: Tuesday, April 23, 2013 6:42 AM
Points: 36,
Visits: 109
I am still not able to make any progress.
The steps i am doing:
1st step:Execute Sql task--To get the max(date) mdate from table and assign the madate to user defined variable in resultset otopn of task.
2nd Step: Another Execute Sql task to delete the data from table based on passed parameter
3rd Step:Drag n drop a data flow task--it contains one oledb source
and one oledb destination.
Inside oledb source, i have defined oledb connection manager.
Data access mode is sql command and passing SP name e.g.
exec sp_name @UserDate = ?
@UserDate is actual input parameter passed to SP.
After this i am maaping parameter with User variable.
Once i click on preview i get the error messgae..no value given for one or more required parameter.
If i change the sql command like exec sp_name ? then also same error.
Can anyone help me to resolve this?
Post #1051366
sqlusers
sqlusers
Posted Friday, January 21, 2011 5:18 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Today @ 2:55 AM
Points: 195,
Visits: 463
Try something like below...
SET NOCOUNT ON
exec sp_name @UserDate = ?
and pass parameter in Parameters Tab (@UserDate = <Variable Name>)
Post #1051455
jk_in_San_Diego
jk_in_San_Diego
Posted Tuesday, May 07, 2013 10:50 AM
Forum Newbie
Group: General Forum Members
Last Login: Today @ 11:26 AM
Points: 4,
Visits: 16
Pretty cryptic response....
I tried it in my call to a stored proc from a Foreach loop and the "No value given for one or more required parameters." is still issued.
/john
Post #1450252
« Prev Topic
|
Next Topic »
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
