Exam (elaborations)
SSIS EXAM STUDY SET LATEST UPDATED
- Course
- Institution
SSIS EXAM STUDY SET LATEST UPDATED....
[Show more]
Content preview
SSIS EXAM STUDY SET LATESTUPDATED
ETL Life Cycle - ANSWER Extract-Clean-Transform-Load
1. Extract from the source while using the least amount of resources possible.
Different ways to extract
- Update Notification
- Incremental Extract
- Full Extract
2. Transform and Clean
- Conditional Split
- Derived Column - Nulls
- Data Conversion
- Sorting Data (Using What)
- Generating Surrogate Keys?
- Generating Aggregates
- Making Sure Identifiers are unique
- Join Data using (Union All, Merge, Look Up, Merge Join)
3. Loading
- Disable Constraints and indexes when loading
- Proper Permissions
----
Extraction:
The Extract step covers the data extraction from the source system and makes it
accessible for further processing. The main objective of the extract step is to retrieve
all the required data from the source system with as little resources as possible. The
extract step should be designed in a way that it does not negatively affect the source
system in terms or performance, response time or any kind of locking.
Datasource Types
-TXT
-XML (ExtinsibleMarkup Language)(Single and Multilevel)
-CSV (Comma seperator values) (Liek text but has a deliminator)
-Excel
-Access
-Oracle
-DB2 (IBM)
-SQL Server
Different ways to perform an extraction:
(Find examples of these)
, Update notification - if the source system is able to provide a notification that a
record has been changed and describe the change, this is the easiest way to get the
data.
Incremental extract - some systems may not be able to provide notification that an
update has occurred, but they are able to identify which records have been modified
and provide an extract of such records. During further ETL steps, the system needs
to identify changes and propagate it down. Note, that by using daily extract, we may
not be able to handle deleted records properly.
Full extract - some systems are not able to identify which data has bee
5 Transformations that I have used in SSIS - ANSWER Researching
Describe Delay Validation - ANSWER DelayValidation Property is available on
Task level, Connection Manager, Container and on Package level. By default the
value of this property is set to false that means that when the package start
execution, It validates all the Tasks, Containers, Connection Managers and objects(
Tables,Views, Stored Procedures etc.) used by them. If any object such as table or
destination file etc. is not available then Package validation fails and Package stop
execution.
By setting this property to True, We enforce our SSIS Package not to validate that
Task, Connection Manager or entire Package at start but validate at run time.
Describe an instance when I have used this:
http://www.techbrothersit.com/2014/09/ssis-what-is-delay-validation-property.html
The 5 package configurations - ANSWER 1. XML configuration file - An XML file
contains the configurations. The XML file can include multiple configurations.
2. Environment variable - An environment variable contains the configuration.
3. Registry entry - A Registry entry contains the configuration.
4. Parent package variable - A variable in the package contains the configuration.
This configuration type is typically used to update properties in child packages.
5. SQL Server table - A table in a SQL Server database contains the configuration.
The table can include multiple configurations.
Practice with these
The benefits of buying summaries with Stuvia:
Guaranteed quality through customer reviews
Stuvia customers have reviewed more than 700,000 summaries. This how you know that you are buying the best documents.
Quick and easy check-out
You can quickly pay through credit card or Stuvia-credit for the summaries. There is no membership needed.
Focus on what matters
Your fellow students write the study notes themselves, which is why the documents are always reliable and up-to-date. This ensures you quickly get to the core!
Frequently asked questions
What do I get when I buy this document?
You get a PDF, available immediately after your purchase. The purchased document is accessible anytime, anywhere and indefinitely through your profile.
Satisfaction guarantee: how does it work?
Our satisfaction guarantee ensures that you always find a study document that suits you well. You fill out a form, and our customer service team takes care of the rest.
Who am I buying these notes from?
Stuvia is a marketplace, so you are not buying this document from us, but from seller luzlinkuz. Stuvia facilitates payment to the seller.
Will I be stuck with a subscription?
No, you only buy these notes for $13.49. You're not tied to anything after your purchase.
Can Stuvia be trusted?
4.6 stars on Google & Trustpilot (+1000 reviews)
78252 documents were sold in the last 30 days
Founded in 2010, the go-to place to buy study notes for 14 years now