Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Sql_20
Starting Member
2 Posts |
 Posted - 2008-08-24 : 21:04:19
|
| Hi AllI am trying to use stored procedure to get data from two sources to make incremental changes to database. Then we have to find how many rows we are getting and if it is more than 0 then only update destinantion table. But before that we have to join two different sources which has different column metadata. Please can someone help to solve this by using Rowcount or any other tasks in ssis?Thanks |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-08-24 : 22:42:46
|
| Don't do this in SSIS.Don't do anything in SSIS except transfer data. Load it into a staging table, and then have a stored procedure verify, cleanse, and distribute the staging data.Boycott Beijing Olympics 2008 |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-25 : 02:35:10
|
quote:
Originally posted by Sql_20
Hi AllI am trying to use stored procedure to get data from two sources to make incremental changes to database. Then we have to find how many rows we are getting and if it is more than 0 then only update destinantion table. But before that we have to join two different sources which has different column metadata. Please can someone help to solve this by using Rowcount or any other tasks in ssis?Thanks
You've different types of tasks available in SSIS for doing the aboveExecute SQL Task can be used to execute tored proceduresThe rowcount can be determined by means of rowcount task or even with simple OLEDB command taskThe joining of data from two sources can be done by means of Mergejoin taskSo i think you're squence of tasks will be as followsuse a dataflow task Inside dataflow tasks use two OLEDB source connections to get your source dataThen use Merge join task to join the data from both sources and select only columns you want.then use a OLEDB commad task to store count onto a variable which you've created in packageUse a conditional precedence constraint to check if count>0 and attach it to final Execute SQL task. here you call the stored procedure. |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-08-25 : 09:57:12
|
| ...and prepare to recreate all this the next time Microsoft upgrades or scraps its ETL tool....and prepare to spend a lot of administrative time tracking down bugs in logic that is spread across several platforms....or, simply load your data into staging tables and have all your transformation logic in sprocs which are virtually guaranteed to be backwards compatible.Boycott Beijing Olympics 2008 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-25 : 10:04:05
|
quote:
Originally posted by blindman
...and prepare to recreate all this the next time Microsoft upgrades or scraps its ETL tool....and prepare to spend a lot of administrative time tracking down bugs in logic that is spread across several platforms....or, simply load your data into staging tables and have all your transformation logic in sprocs which are virtually guaranteed to be backwards compatible.Boycott Beijing Olympics 2008
So what will you do when your source data comes from two different servers or from heterogenous dbs? each time will you be using OPENROWSET or will you create a linked server? In many organisations there are restrictions using OPENROWSET or linked servers. So in the end SSIS might be only solution available. I do agree that it requires some amount of administrative effort. |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-08-25 : 12:56:04
|
| As I said before, use SSIS to load the data. Just don't put business logic into it.Boycott Beijing Olympics 2008 |
|
|
|
Sql_20
Starting Member
2 Posts |
Posted - 2008-08-25 : 23:24:48
|
| Thanks a lot. I will try out these options. |
|
|
|
|
|
|
Stored procedure in SSIS package