About SSIS
Connection Manager
Difference between Control Flow & Data Flow Task
Click Here
Variables
- Variables store values in SSIS Package at package, container, task level
- Variables can be used in
a. Script task - we can use the variable using Dts.Variables["Blah"]
b. Expression in any transformation
c. Expression in any Connection Manager
d. Execute SQL Task to pass value to SP, and store value from SP
e. Precedence Constraint to define success/failure flow
f. Error output
- 2 type of variables
a. System Variable
b. User-defined Variable
What are the different control flow tasks you have used?
Approach for Error Handling
- Introduced in 2005
- Advanced version of DTS (Data Transformation Services)
- An easy to use, highly flexible, extremely capable, highly scalable ETL (Extract Transform Load) tool
- Creates structure called package
- Package contains mainly following things
o Control Flow (defines overall flow)
o Data Flow (extracts data from multiple sources and putting into multiple destinations)
o Event Handler
o Package Explorer
o Connections Tray (holds all kind of connections)
Connection Manager
- OLEDB connection
- Flat file connection
- Excel connection
- Cache connection
- FTP connection
- Multifile connection
- SMTP connection
Difference between Control Flow & Data Flow Task
Click Here
Variables
- Variables store values in SSIS Package at package, container, task level
- Variables can be used in
a. Script task - we can use the variable using Dts.Variables["Blah"]
b. Expression in any transformation
c. Expression in any Connection Manager
d. Execute SQL Task to pass value to SP, and store value from SP
e. Precedence Constraint to define success/failure flow
f. Error output
- 2 type of variables
a. System Variable
b. User-defined Variable
- Variable name is case-sensitive
- We can create 2 variables with same name at different level. e.g. Container level and then
Data Flow Task Level
- In that case, local scope will get more priority.
What are the different control flow tasks you have used?
- Execute SQL Task (more details)
- Execute Package Task (more details)
- Send Mail Task (more details)
- Data Profiling Task (more details)
- Transfer Logins Task (more details)
- Bulk Insert Task (more details)
- Data Flow Task
- For Loop Container
- For Each Loop Container
- Sequence Container
- File System Task (more details)
- Script Task
- FTP Task
- Backup Database Task
- Rebuild Index Task
- Reorganize Index Task
- Shrink Database Task
- Update Statistics Task
What are the different data flow transformations you have used?
- Simple DFT
- Simple DFT
- Copy Column Transformation (more details)
- Derived Column & Data Conversion Transformation (more details)
- Conditional Split Transformation (more details)
- Character Map Transformation (more details)
- Audit Transformation (more details)
- Aggregate & Multicast Transformation (more details)
- Merge & Sort Transformation (more details)
- Merge Join Transformation (more details)
- Union All Transformation (more details)
- Row Count Transformation (more details)
- Row Sampling Transformation
- Fuzzy Lookup Transformation
- Fuzzy Grouping Transformation
- Cache Transformation (more details)
- LookupTransformation (more details)
How to pass a variable to child package?
To be added
Performance improvement in SSIS
How would you restart package from point of failure? What is checkpoint?
To be added
How you can achieve parallelism in SSIS?
To be added
Difference between Merge, Merge Join & Union All
Difference between Conditional Split & Multicast
To be added
How to deploy a SSIS Package
ReplyDelete1. Build a deployment manifest
2. Copy and paste the .dtsx file from the dev location to the
production location (file system)
3. Send the file to the DBA to import into SQL Server
4. If you have the permissions then you can do this by connecting to
the SSIS service on the production server and importing yourself.
5. Use DTUtil