updating the sql query result to multiple tables in SSIS

Question

updating the sql query result to multiple tables in SSIS

rraju22

SSC Veteran

Points: 226
More actions
February 22, 2009 at 1:13 pm

#131148

Hi Experts...
I am new to this technology and do not know much about the features of this SSIS. I am trying to pull some data from Oracle database and updating them to several tables in sql server database.I found that SQL server provides SSIS features through which I can connect to oracle and pull data.But the problem is ...I do not want to put the result of that query into a temporary table before updation.
Is there any way to update these data directly into the tables of sql server DB.
Looking for your response..
Thanks in advance..
RRAJU

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Rick Todd Hall of Fame Points: 3302 More actions · Answer 1

You can send the data "directly" from the Oracle data source into the SQL Server tables using an OLE DB Command, but I have to warn you it's MUCH slower than storing the data in a table (via an OLE DB Destination), then using an Execute SQL Task to run a set-based update statement.

Hope that helps,

Rick Todd

Mel3405 SSCommitted Points: 1904 More actions · Answer 2

Rick,

I'm trying to set up an SSIS package to do the following:

1) Run a stored procedure against an OLTP db (Sql Server). This sp will basically denormalize the data.

2) Then, I need to take this huge result set and populate a few tables in a DW. So, this result set will be split up among a few tables.

2 Questions:

1) Is what I am attempting to do possible?

2) Would the process run faster if I insert the result set in a physical table and then read from this table to update the tables in the DW. If so, will the data be committed and available to be read in the next task?

Thanks

Rick Todd Hall of Fame Points: 3302 More actions · Answer 3

I'm not really clear on what you're attempting to do. Do you want to use the SP to denormalize the data because you don't want to reproduce the logic, which is already used for something else, or because you're comfortable with T-SQL, or what?

Am I correct in assuming you have a couple of different servers and you're moving the data between them?

Rick Todd

Phil Parkin SSC Guru Points: 246985 More actions · Answer 4

This is hard to visualise without some sample data. I am picturing a single resultset easily enough - but the splitting into different tables bit is less easy.

If there are 50 lines in the resultset, do you want to populate 50 records in several different tables? Or 50 in one, 20 in another, 1 in another ...? If the first option, you could use a multicast. If the second, you really are better off staging this in SQL Server & then firing off some T-SQL afterwards to populate the different tables (or rewriting the extraction from Oracle).

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Mel3405 SSCommitted Points: 1904 More actions · Answer 5

Let's say the result set is 50K rows and has 50 columns. I want to split up the columns among different tables. I think I can use a multicast. What I don't know is if I can take a result set directly from the stored procedure without having to store them in a physical table first.

If I can direct the rows directly, would it be faster if I store them to a table first. Then pull them out of the table to populate the destination tables.

Mel3405 SSCommitted Points: 1904 More actions · Answer 6

We are at the beginning stages of creating a data warehouse because we have reports that run slow (and often time out) due to the complex logic in the sp, the number of joins and the size of the report.

What I want to do is create a package that runs this stored procedure to denormalize the data and the result set will be inserted into our DW tables (star schema). At this point, we are not creating a Cube. We are just denormalizing the data.

Rick Todd Hall of Fame Points: 3302 More actions · Answer 7

OK, I'm starting to understand what you're trying to do. Sure, use the SP as an OLE DB Source (read this: http://consultingblogs.emc.com/jamiethomson/archive/2006/12/20/SSIS_3A00_-Using-stored-procedures-inside-an-OLE-DB-Source-component.aspx), then multicast. Just to suggest an alternative: if you're only doing this because of a few reports that run slowly, and you're willing to accept the limitations of not having the data always be real-time (which you probably are, since you're building a data mart to house it), you can also pre-cache the reports on Reporting Services. You've probably considered this, but just in case you hadn't.

Cheers,

Rick Todd