SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


create a SSIS package which will call the stored procedure and dump the output of the procedure into...


create a SSIS package which will call the stored procedure and dump the output of the procedure into table

Author
Message
Karan_W
Karan_W
SSC Veteran
SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)

Group: General Forum Members
Points: 258 Visits: 135
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.
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)

Group: General Forum Members
Points: 52850 Visits: 21195
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)

Group: General Forum Members
Points: 52850 Visits: 21195
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Karan_W
Karan_W
SSC Veteran
SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)

Group: General Forum Members
Points: 258 Visits: 135
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.
Javed-330570
Javed-330570
SSC Veteran
SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)

Group: General Forum Members
Points: 208 Visits: 172
Hi Karan,

Use the execute SQL task to run your stored procedures in the sequence required.


Regards,
Barkha.
Karan_W
Karan_W
SSC Veteran
SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)

Group: General Forum Members
Points: 258 Visits: 135
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 StepBigGrinrag 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?
sqlusers
sqlusers
Mr or Mrs. 500
Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)

Group: General Forum Members
Points: 591 Visits: 512
Try something like below...

SET NOCOUNT ON
exec sp_name @UserDate = ?

and pass parameter in Parameters Tab (@UserDate = <Variable Name>Wink
jk_in_San_Diego
jk_in_San_Diego
SSC Veteran
SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)

Group: General Forum Members
Points: 238 Visits: 71
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
seddy2765
seddy2765
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 6
Hi Karan.

Your issue sounds similar to mine - if I understand your's correctly?

My scope: Pass SSIS package data to stored procedure parameters. In my case it is email information (EG, To, From, etc).

I'm developing in VS 2008, utilizing Execute SQL Task.

In the SqlStatementSource text box (see Properties pane for task) I entered ...
EXEC usp.your_stored_procedure

In the SqlStatementSourceType drop down box (see Properties pane for task) I selected ...
DirectInput

Read your stored procedure for the parameters it is expecting. In my case some parameters were @To_Address, @From_Address, @Subject_Line, @Status.

In the SqlStatementSource I added/appended the following ...
EXEC usp.your_stored_procedure @To_Address='to@address.com', @From_Address='from@address.com' , @Subject_Line='Test Subject Line', @Status=1

Note:
Values passed to parameter must be in synch with defined sp parms. IE, characters in quotes, numeric not.
Each additional parameter must be delimited with a comma.
The email addresses, stored procedure and parameters are made up for this example.

This solution can be tedious (good ol' fashion hard coding) and not future-support-friendly. w00t
My next hurdle is to utilize SSIS user variables. I've created the variables but not certain the syntax.
It's test, test, test time. I've read of others having similar errors but have yet to find solution. More opportunity to learn! :-)

Hope this helps.

Steve
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search