SSIS self-test question-Data flow control class

Description: The following is your own understanding of the answer, not the standard answer, if there are inappropriate please point out.

Some topics for the time being no answer, have to know please leave a message, learn from each other, progress together.

133, please describe the use of Conditional Split and the use of the scene? Can it write an expression? Can it access columns, variables, and use functions for upstream data?
Depending on the value of the column, determine the path of the data flow, you can write the expression to judge, only access system variables, user variables, you can use the function
134. Please describe how Data Conversion and Derived Column are used, and what is the difference between them?
Data Conversion: Conversion of mismatched data types in the stream
Derived column: Generate new columns, not only to convert data types, but also to derive new columns
135, please describe the use of multicast multicast and usage scenarios? Please illustrate.
One input, multiple outputs.
136, please describe the use of Aggregate aggregation control, why we try to avoid using the Aggregate control in data flow, under what circumstances may not be able to avoidWant to use it?
Aggregate aggregation: Primarily to summarize data.
In the data stream, the data volume is large and the data aggregation is blocked.
137. How do you understand synchronous synchronization, asynchronous asynchronous, Blocking blocking these concepts?
Synchronization: When the execution results are returned, the input and output of the synchronization components share the same cache
Async: Returns when execution begins, and the input output uses a cache that is not the same.
Block: Wait until executable to return
138. Describe the basic usage of the Lookup control, what can I do to solve the problem?
Finding data for matching or mismatched rows
139. What is the input source, reference data set, and what part of the data is cached in Lookup?
Input source: Data from a data source
Reference datasets: Generating reference Datasets,
The cache is a reference dataset
140. What are the two connection modes of Lookup?
Cache Connection Manager
OLE DB connection Manager
141. What are the different cache modes in the OLE DB Connection connection mode in Lookup? What's the difference?
Full cache: Generates a reference dataset and loads it into the cache before performing a lookup transformation
Partial caching: Generating a reference dataset during a lookup
Loads a row with a match in the reference dataset into the cache and loads a row with no matching in the dataset into the cache
Do not cache: Generate a reference dataset during a lookup
142, in the Lookup string comparison, in the default full Cache condition will appear case sensitive problem, how to design or configure to make the string comparison is not case-sensitive?
The Data conversion column is converted first, and the SQL statement is processed in lookup with functions lower and upper.
143. In the OLE DB Connection connection mode in Lookup, where is the data fully cached in full cache mode, and what is its caching process?
Cache, a reference dataset is generated and loaded into the cache before the lookup transformation is performed.
144. What are the matching buffers and mismatched buffers in the partial cache in the OLE DB Connection connection mode in Lookup?
Matching rows: Referencing matching rows in a dataset
Mismatched rows: Rows with no matches within an exponential data set
145. What is the working mechanism of No Cache in the OLE DB Connection connection mode in Lookup?
To generate a reference dataset during a Lookup transformation
Do not load any data into the cache
146. What is runtime caching and how is the runtime cache implemented in the cache Transform Caching transformation control, describe the process used?
The package runtime invokes the first cache transform to write data to the Connection manager.
If the package contains more than one cache transform, subsequent caches will not be able to perform write operations
147, what is the file cache, cache Transform buffer conversion control in the file cache is implemented, the situation describes the use of the process?

148. What is the difference between runtime cache and file cache in cache Transform, and what are the usage requirements for the same package and different package invocation caches?

149. Describe how merge merge conversion controls are used, and what are the requirements and limitations of the input data source?
The merge transformation merges two sorted datasets into one dataset, and two input data sets must already be sorted
150. Although a sorted SQL statement is used in OLE DB Source queries, there is still an error associated with the Merge control, what is usually the reason, and how should it be resolved?
The dataset is not already sorted in the control settings.
Set the data Source property Issorted=true, and set the order of sorts in the output column
151. Describe how the sort conversion control is used, and how to understand its asynchronous asynchronous and Blocking blocking characteristics?
Sort the input data in ascending or descending order and copy the sorted data to the transformation output
152. What are the characteristics of the Merge Join conversion control? What kinds of joins do they have, and how do they work? Please describe the use of various Join methods.
The input data must be ordered, an inner join, a left outer join, and a full outer join.
153. What is the difference between the use of the Union all conversion control and the use of the Merge,merge Join conversion control? What are the direct similarities and differences between the three of them?
Union All: Input datasets can be sorted without sorting, merging data
Merge: The data set entered must be in order, merging the data
Merge Join: Input data must be ordered, associated data
154. What is the implementation method if you need to record the number of objects inserted from the source in data flow?
Use the packet's event Proexecute and PostExecute events to query the total number of rows and do a poor operation.
155. Describe the use of the Row count conversion control, how to record the number of streams in data flow and assign a value to a variable, and insert it into the log?
Define a variable and assign a variable to the control.
156. How do I implement PIVOT operations in SQL and SSIS?
PIVOT operations and row-to-column operations for data.
157. What is pivot key, Set key, pivot Value in the pivot conversion control in SSIS?
Pivot key: The column for the value of the row above the table (header row)
Set key: Specifies the column used for the value of the left column of the table
PIVOT value: Refers to the values of the columns to be summarized
158. How do I automatically generate converted columns pivoted Output Columns through the PIVOT control in SSIS? What settings do I need to pass?
Enter the values of all the pivot columns first, and click the button "Generate Now".
159. How do I implement UNPIVOT operations in SQL and SSIS? In the process of using UNPIVOT, can I restore the detail data from aggregated data to PIVOT?

Cannot revert to detail data before PIOVT
160. In SSIS UNPIVOT, what is the Input column in the conversion control, Destination column, and Pivot Key Value column name respectively?
Input columns: The values selected from the available input columns
Destination column: Provides the name of the data column
Pivot Key value: Provides the name of the PIVOT key value column
161. What are the scenarios that Script Component might be able to use in data flow? What types of conversions does it have?
Functionality cannot be implemented using system-provided controls
Target conversion, source conversion, conversion
162. What is the difference between a script component and a script Task in control flow in data flow?
Script Task:
Script tasks are configured in the control flow of the designer and run outside the package's data flow
Script tasks can accomplish almost any general-purpose task
The Script task runs custom code at a point in the package workflow, and runs only once if it is placed in a loop or event
The Script task has three pages: general, script, expression, readonlyvariables,readwritevariables,scriptlanguage
Use Dts properties to access other features of the package.
Script Component:
The script component is formulated on the Data flow page, representing the source, transformation, and destination of the data Flow task.
The script component also runs once, typically running a primary processing routine once for each row of data in the data flow.
The script component has four pages: input columns, input and output, scripts, Connection Manager
The script component accesses a specific package feature using the typed accessor property
Use this. Variables Access variables
163, please explain the Script Component in the default type under PreExecute (), PostExecute (), Inputxxx_processinputrow () method of their respective role?
Event PreExecute: Means before the event begins execution
Event PostExecute: Refers to the end of an event after execution
Event Inputxxx_processinputrow (): triggered when processing data per row during data conversion
164. What is the life cycle of the Inputxxx_processinputrow (input0buffer row) parameter row in Script Component and what does it mean?
Current row, data for the current row
165, how to use Script Component to achieve data flow from the source to the target row count?

166. How do I use Script Component (Source type) to access and traverse each line of a flat file?
Create a flat File connection Manager, read the flat File connection Manager in code, loop through the files,
167. How do I create one or more output outputs in Script Component (Source type)?
To add an output parameter to the Script Component editor page
168. Describe the process of creating an output stream in a script in script Component (Source type), that is, the use of the Createnewoutputrows () method.

169, how to script Component (Source type) to access the output Buffer, how to add a new output line down?

170. What is Script Component sync transformation? How should I set it?
Synchronization: You do not need to copy the buffers into the new memory space.
Set property Synchronousinputid to "None"
171. What is the role of Synchronousinputid in Output in Script Component (transformation type)? If the value is None or 0 means what does it mean?

172. In the Inputxxx_processinputrow (Input0buffer Row) method in Script Component (transformation type), row contains the contents of those two parts?

173. How do you determine whether this transformation component is a synchronous synchronous or an asynchronous asynchronous conversion component in the Data Flow transformation component of the stream? What are the basic methods of judging?

174. What settings do you need to make Script Component (transformation type) change from synchronous synchronous to asynchronous asynchronous?

175. In Script Component (Transformation-type-async), if there is more than one output, can the synchronous and asynchronous transformations coexist?

176. What is the difference between the Inputxxx_processinput () method and the Inputxxx_processinputrow () method in Script Component (Transformation-type-async)?

177. In Script Component (Transformation-type-async), how should the buffer parameter in the Inputxxx_processinput (Input0buffer buffer) method be understood? What should be the data it contains?

178. Why is Script Component (Transformation-Sync) a non-blocking non-blocking conversion, whereas (transformation-async) is a semi-blocking half-block conversion?

179. If we see both methods Inputxxx_processinput () and Inputxxx_processinputrow () in script Component, we are on script Component What are the possible ways to configure the configuration? What are the possible forms of transformation that they want to express?

180. Describe the use of Script Component (Destination-type) in the projects or exercises you have experienced.
Iterating through the values of each column in the function input 0_processinputrow
181. How do I access the data tables in the connected database in Script Component? Do you want to use OLE DB connection or the ADO connection method?
Accessed with the attribute this.componentmetadata.inputcollection[0], both can be
182. Describe the process of accessing the connection database in Script Component and inserting data into the specified data table, what are the SQL objects involved?
Get database connection string first, build with SqlConnection, SqlCommand execute Insert SQL command
Sqlconnection,sqlcommand
183. Please describe roughly the four conversion operations in the Script Component component (Source, synchronous transformation, asynchronous
transformation,destination) usually which one or two of the efficiency of the conversion is higher, which one or a few of the conversion efficiency is lower, please give a rough analysis of the reasons.

184. Combine these components in the data stream Conversion, Derived Column, Aggregate,sort,script Component Describe how you understand the concepts of blocking, semi-blocking, completely blocking, synchronizing, and Asynchrony. and describe the controls you know which are blocking, semi-blocking, completely blocking, synchronous, asynchronous type?
Synchronization components: The output and input of the synchronization component share a cache, that is, the number of rows to enter the output of how many lines, input and output synchronization, the simultaneous occurrence
Data Conversion, Derived Column
Asynchronous component: Its output is a new cache, which does not reuse the input cache.
Sort,aggregate,merge,merge Join,script Component
Blocking: Also known as row conversions, the entire process does not create new rows, nor does it delete rows
Semi-blocking: The transformation component controls the input line for a period of time. Converting a component to a row of data is not immediately processed, and is returned after the batch has finished processing.
Merge,merge Join,pivot,unpivot,union All
Full blocking: Full control of all data. The whole process is finished before returning.
Aggregate,fuzzy Grouping,fuzzy Lookup,sort
185. What are the similarities and differences between the OLE DB Command component in the data stream and the Execute SQL Task in the control flow?
Same: All SQL statements can be executed directly
Similarities and differences: Execute SQL Task, execute SQL statement once in control flow unless loop
OLE DB Command, how many rows are executed, how many times
186. What are the similarities and differences between the OLE DB Command component and the old DB Destination in the data stream?
Run the SQL statement for each row in the data flow
187. What are the nuances of using common parameter SQL statements in OLE DB Command with Stored Procedure with parameters?
OLE DB Command: Each execution of the SQL statement needs to be compiled once, Stored Procedure, not required
188, although the OLE DB Command is a synchronous transformation component, but why still say it is a performance Killer performance killer, what is its implementation characteristics?
Every time you run SQL, you need to reconnect and consume a higher performance
189, the efficiency of the synchronous conversion component is higher than the efficiency of the asynchronous conversion component, if not necessarily can cite two examples to illustrate.

190. In some usage scenarios, what can be done to replace the use of OLE DB Command in order to achieve the goal of increasing efficiency?
May use a data source component or a Target data source component, or a script component
191, describe what is slowly changing Dimension slowly changing dimension, in what scenario we need to use this concept to solve some practical problems?
Assists in updating and inserting records in a Data warehouse dimension table
Used when maintaining the Data Warehouse dimension table
192. Describe some of the design methods and methods that you know to solve the slow slowly changing dimension?

193, what is business key service primary key, what is surrogate key agent primary key? What are the benefits of using surrogate Key to solve any problem?
Only mark a record and the value does not change
Proxy castings, alternate keys for primary keys
194. What controls can be implemented in SSIS to implement SCD, and describe the steps to implement it, including what are the three SCD types mentioned by SCD in SSIS. ETL?
Lookup,union All
195, describe the process of SCD data flow execution in SSIS, SCD type 1 and SCD type 2, the logical implementation process?

196, in addition to the SCD conversion controls that are brought with the SSIS + ETL, what are the ways to implement SCD?

197, in the Microsoft SSIS system ETL, for SCD Type 2 can be set by the start time, or the state of the setting two to realize the identity of the current dimension and maintain the historical dimension of the row, how to design let the two coexist in the dimension table?

198. What is the implementation of incremental data loading? For example, how can you design and implement an incremental load of raw data that has time stamps and no time stamps?

199. Please describe the use scenario of Check point for its implementation and basic configuration.

200, how to design an ETL to monitor a specified database, such as the size of the data warehouse tables, the number of rows per day change? And based on these changes, a general understanding of the daily growth, the forecast of future growth trends?

201, how to design an ETL monitoring or synchronization under the specified directory files, or specify the suffix file size changes, and related property changes, such as the database file size change monitoring?

SSIS self-test question-Data flow control class