| Author |
Topic |
|
Tart_SQL
Starting Member
41 Posts |
 Posted - 2008-01-12 : 18:44:32
|
| Hello,I am trying to use an Execute SQL Task to execute a stored procedure with parameters in SSIS but it keeps giving me errors, for example I type in 'exec someproc ?, ?, ?' in SQL Task it wouldnt parse correctly and when I set the bypassPrepare to 'true' the package runs but gives me error at the destination saying couldnt find stored procedure 'someProc'. Does anybody know how I can solve this problem? Also how do I pass the results of the queries in the Execute SQL Task into the Data Flow Task? The proc is supposed to retrieve data and transform them into XML format to be placed at a XML file. I am not sure if I should used an OLEDB source or an XML source since the data has been transformed into XML. Any help will be greatly appreciated. Please note proc runs fine when it does not have any parameters. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-01-13 : 01:10:41
|
| Are mappings of stored proc correct? Does the sp exists in destination with all parameters & have you ensured you are using correct connection manager?I think for retrieval path you should return xml as output param from sp and map it to a variable in o/p paramter mappings. Then link this to data flow task to perform further operations. |
 |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2008-01-13 : 02:15:29
|
| make sure to use the 3 part naming convention when calling a stored proc. <database name>.<owner name>.<procedure name>master.dbo.sp_who2 for example.-ec |
|
|
|
Tart_SQL
Starting Member
41 Posts |
Posted - 2008-01-13 : 04:47:13
|
| I have the mappings done correctly, but why would I need to have the SP at the destination? The SP is supposed to retrieve data and some aggregates to be loaded into an XML file. Has anyone used a SP with the Execute SQL Task? If so how did you do it with parameters?Also if I link the SQL task to a data flow task, the data flow tasks connection would be the same as the SQL task right? That is where the proc is coming from right? Please pardon my indulgence, I just need to know. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-01-13 : 05:13:29
|
quote:
Originally posted by Tart_SQL
I have the mappings done correctly, but why would I need to have the SP at the destination? The SP is supposed to retrieve data and some aggregates to be loaded into an XML file. Has anyone used a SP with the Execute SQL Task? If so how did you do it with parameters?Also if I link the SQL task to a data flow task, the data flow tasks connection would be the same as the SQL task right? That is where the proc is coming from right? Please pardon my indulgence, I just need to know.
Sorry i meant db from where retrieval is happening.I've used SP with parameters. It just needs to call sp in the SQL task with '?' as placeholders for params and we need correctly map them to values in mapping tab.Whats your data flow tasks current source? |
|
|
|
igorblackbelt
Constraint Violating Yak Guru
407 Posts |
Posted - 2008-01-13 : 23:50:22
|
| Can you post the error message? |
|
|
|
Tart_SQL
Starting Member
41 Posts |
Posted - 2008-01-14 : 12:35:28
|
| Thanks Guys, I figured out the problem.What I need to know now is how to pass the results of the SQL Task to a Data Flow Task, step by step explanation would be greatly appreciated. Thank you. |
|
|
|
Tart_SQL
Starting Member
41 Posts |
Posted - 2008-01-14 : 14:50:14
|
| Hi,To further clarify the issue I am facing now: I am executing a stored procedure with parameters in the Execute SQL Task, the problem I have is when I connect this SQL Task to a Data Flow Task (DFT) and use an OLEDB source to retrieve the variable reading in the records in an XML format, no records can be displayed because the SP is not executed until you run the package. I tried using an XML source but it won’t work either. Does any have any suggestions on what I can do in this case? Please note I only want to use SP’s in the ETL process.Thanks |
|
|
|
rgombina
Constraint Violating Yak Guru
319 Posts |
Posted - 2008-01-16 : 08:16:44
|
quote:
Originally posted by Tart_SQL
Hi,To further clarify the issue I am facing now: I am executing a stored procedure with parameters in the Execute SQL Task, the problem I have is when I connect this SQL Task to a Data Flow Task (DFT) and use an OLEDB source to retrieve the variable reading in the records in an XML format, no records can be displayed because the SP is not executed until you run the package. I tried using an XML source but it won’t work either. Does any have any suggestions on what I can do in this case? Please note I only want to use SP’s in the ETL process.Thanks
Can't you use Package Configurations to pull your variables? That's easier solution. Otherwise you need Forearch Loop and more work. |
|
|
|
|
Need help executing a proc in Execute SQL Task