70-463 · Question #184
You are designing a SQL Server Integration Services (SSIS) 2012 package that imports data from a Windows Azure SQL Database database into a SQL Server database. The SSIS package has the following…
The correct answer is D. OLE DB. The OLE DB destination with the 'Table or View – Fast Load' option uses the SQL Server bulk copy API (similar to BCP), which produces minimal transaction log activity when the target database is in SIMPLE or BULK_LOGGED recovery mode. The SQL Server destination also uses bulk…
Question
You are designing a SQL Server Integration Services (SSIS) 2012 package that imports data from a Windows Azure SQL Database database into a SQL Server database. The SSIS package has the following requirements:
- Every night, a very large amount of data is imported into the staging
database.
- Package processing time must be minimized.
- The package must run on its own dedicated server when it is deployed
to production.
- Transaction log activity when data is imported must be minimized.
You need to design the package to meet the requirements. Which destination component should you use?
Options
- ARaw File
- BODBC
- CBulk Insert
- DOLE DB
How the community answered
(36 responses)- A11% (4)
- B8% (3)
- C3% (1)
- D78% (28)
Explanation
The OLE DB destination with the 'Table or View – Fast Load' option uses the SQL Server bulk copy API (similar to BCP), which produces minimal transaction log activity when the target database is in SIMPLE or BULK_LOGGED recovery mode. The SQL Server destination also uses bulk copy and minimizes logging, but it requires the SSIS package to run on the same physical machine as SQL Server - the requirement states the package runs on its own dedicated server, ruling that out. Bulk Insert task (C) requires a flat file source on a path accessible to SQL Server, not data coming from Azure SQL Database. Raw File (A) writes binary SSIS-internal files, not a SQL Server table. ODBC destination (B) does not support bulk loading. OLE DB with Fast Load is the correct and supported choice here.
Topics
Community Discussion
No community discussion yet for this question.