Thanks!

Thanks. Will this work if the linked server is NOT a SQL Server database?

Actually, new data will probably be constantly flowing into Hadoop. The 10-minute latency is just something the business set up as a requirement.

Good idea, but we have new data going into Hadoop/Hive every 10 minutes or so. Are you saying we should use SSIS every 10 minutes or so as well?...

By the way, thanks for the article!

We tried using Multidimensional against our Hadoop/Hive data. It didn't work. The problem was linked server, which made performance unacceptably slow. That's why we are using Tabular. ...

I think it depends on where you live. For example, Oracle seems to be much more popular than SQL Server in the San Francisco Bay Area. Then again,...

One more thing. What if I want the error message from the child sproc's TRY/CATCH to be visible to the parent sproc?

Thanks, I think I understand what you did. Here's my modified version below. Does it make sense?

-- Modified version

CREATE PROCEDURE USP_Wrapper

AS

BEGIN

DECLARE @ReturnValue int

EXEC @ReturnValue = USP_Child01

IF @ReturnValue <>...

Sorry, it is a metadata table, not a logging table. It contains information about the structure of the source system. The question is whether a metadata table of...

Some ETL tools such as Informatica have data lineage built in. To my knowledge, SSIS does not. You'll have to either build on SSIS's auditing features or maybe...

Good, but does the Project Deployment Model have something built in that might work as well or better?

Yes, I have it set to drop the objects before moving them over. That was the original goal of the backup/restore, to have a new copy in staging.

Quick Update:

I decided that using a simple backup/restore might not be the best idea for staging. Why? Because:

1) We are backing up from one server on one domain...

I have two questions.

Scenario 1

Let's say I connect the SSIS package directly to the production database and do the full ETL into the destination star schema database. The process...