| Author |
Topic |
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
 Posted - 2011-07-05 : 08:20:10
|
| Hi,I am using a cursor inside the stored procedure to loop through rows and place values into variables i.e. @dsss, etc.Is there such a fuunctionality in SSIS or should I just use the execute sql task to execute this sql with cursor in it?Thanks |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-07-05 : 08:53:32
|
| Well ssis uses row based processing in the dataflow so you probably already have the functionality.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
Posted - 2011-07-05 : 09:10:44
|
| Basically in my storedprocedure which is huge, there are several cursors and for each cursor, there are if statements. And for each if statement, there are insert and updates based on the variables passed from the cursor.Can I simplify this stored procedure into a SSIS package?Thanks |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2011-07-05 : 09:18:01
|
I think you can use a SCD for this, if you insist in using SSIS.But why use SSIS? I think you can use MERGE command to do this.
N 56°04'39.26"E 12°55'05.63" |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
|
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
Posted - 2011-07-05 : 09:50:47
|
quote:
Originally posted by SwePeso
I think you can use a SCD for this, if you insist in using SSIS.But why use SSIS? I think you can use MERGE command to do this.
N 56°04'39.26"E 12°55'05.63"
Do you mean to use a ssis package with merg join task in it? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-07-05 : 09:54:29
|
| Not a merge join, and not SSIS, but this: http://technet.microsoft.com/en-us/library/bb510625.aspx |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-07-05 : 09:56:22
|
| No - a merge staement in the sp.It depends a lot on what the code actually is.The question is really why you want to use ssis for this.You can import the output of an sp into an object then loop through that - sounds a bit like you want to call a package from within a cursor stting variables - which is easy but probably not a good idea.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2011-07-05 : 09:58:53
|
Try to solve the problem without SSIS first.We can't help you since we don't know what you are trying to accomplish.
N 56°04'39.26"E 12°55'05.63" |
|
|
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
Posted - 2011-07-05 : 10:14:12
|
quote:
Originally posted by nigelrivett
No - a merge staement in the sp.It depends a lot on what the code actually is.The question is really why you want to use ssis for this.You can import the output of an sp into an object then loop through that - sounds a bit like you want to call a package from within a cursor stting variables - which is easy but probably not a good idea.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy.
just to re-elaborate my query.I have a large stored proc in which there are several cursors and a lot of if statements. For each if statement, there are update and insert statements with the @variables passed by cursors.Question is, can I simplify this Stored proc by placing the logic into a ssis package?Thanks |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-07-05 : 10:17:53
|
| Probably not - you can probably simplify the logic but the issue is translating the requirements rather than the technology used.You can probably get rid of the cursors in the SP which would simplify that but just translating to ssis would probably make it more complicated.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
Posted - 2011-07-05 : 10:29:19
|
quote:
Originally posted by nigelrivett
Probably not - you can probably simplify the logic but the issue is translating the requirements rather than the technology used.You can probably get rid of the cursors in the SP which would simplify that but just translating to ssis would probably make it more complicated.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy.
Let it me put it this way;In execute sql task i.e. SQL1. I am executing a select statement.i.e. select field1, field2, field3, etc from table1...for each row, I want to pass the value of Field1 to the next sql task i.e. SQL2.Thanks |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-07-05 : 10:34:07
|
| No simpler on ssis than t-sql.The problem sounds like the underlying design.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
cursors in ssis