Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Bulk Insert vs. Data Flow Task? Expand / Collapse
Author
Message
Posted Tuesday, April 23, 2013 8:25 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 12:39 PM
Points: 200, Visits: 358
Yes, I'm kind of a noob with SSIS. We frequently move hundreds of thousands of records from Text files to Raw tables, and ultimatley to production. After I get the Raw data into a Database table, I use a Data Flow Task to push it to a Staging table.

What does Bulk Insert get me? Just throwing it out there to see what comes back.
Post #1445466
Posted Tuesday, April 23, 2013 9:09 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:52 PM
Points: 5,317, Visits: 12,353
CptCrusty1 (4/23/2013)
Yes, I'm kind of a noob with SSIS. We frequently move hundreds of thousands of records from Text files to Raw tables, and ultimatley to production. After I get the Raw data into a Database table, I use a Data Flow Task to push it to a Staging table.

What does Bulk Insert get me? Just throwing it out there to see what comes back.


Bulk Insert allows you to quickly insert rows into a SQL table from an external source, without resorting to SSIS. I can't see how it would fit well into what you are doing at the moment.

Your data flow sounds odd, by the way.

Text File --> Raw File --> database table --> Staging table

Why would you do this? What happens to data in the staging table?



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1445507
Posted Tuesday, April 23, 2013 9:33 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 12:39 PM
Points: 200, Visits: 358
Hey Phil!

Flow is as follows:

Text File --> Raw Table --> Staging Table --> Production Staging Table (Different VM) --> Production Table.


A lot, if not all of the Transformations happen in the Staging Table. As a general rule, I don't think any CRUD/MACD should happen once the data is pushed to the production environment.

Post #1445526
Posted Tuesday, April 23, 2013 11:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:52 PM
Points: 5,317, Visits: 12,353
CptCrusty1 (4/23/2013)
Hey Phil!

Flow is as follows:

Text File --> Raw Table --> Staging Table --> Production Staging Table (Different VM) --> Production Table.


A lot, if not all of the Transformations happen in the Staging Table. As a general rule, I don't think any CRUD/MACD should happen once the data is pushed to the production environment.



I'm sure that you have your reasons, but that looks like one-too-many staging tables to me.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1445587
Posted Tuesday, April 23, 2013 12:15 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 12:39 PM
Points: 200, Visits: 358
Phil,

I would tend to agree with you, the the practice has been in place for a while now. The main reason is that they are using VM instances of SQL Server. All the new data is brought in on Server "A" and is physically seperated from Server "B". Once all the Crunching has taken place on "A's" Staging table. The data is then pushed via Data Flow Task to the Production Server "B". The Staging table there is then compared to the final landing table. Since the possibility exists for duplicates, the tables are compared at a row level and only new stuff gets through. Yes, it's a couple extra steps, but they're conservative with this and it works.

Server "A" is located in house. Server "B" is located off site in a Co-location as part of the Disaster Recovery plan.

Make Sense?

Crusty.
Post #1445622
Posted Tuesday, April 23, 2013 12:21 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:52 PM
Points: 5,317, Visits: 12,353
I knew that you'd have your reasons - sounds robust and safe to me.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1445626
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse