Check Points

Check Points in MSBI

Check points allows a package to restart at the point of failure. Steps to configure check Points. Open Business Intelligence development studio   ---------> Create a new package and rename it as check points .dtsx   ---------> In control flow drag and drop 3 execute SQL tasks and configure as mentioned below   --------->   Execute SQL Task: Rename it as task 1   ---------> Double click on it to edit, provide the following information.   Connection: Select New Connection   --------->  Click new   ---------> Provide server name (Local host)   ---------> Select Adventure works data base   ---------> SQL command – select 1/1   Execute SQL Task 1: Rename it as task 1   ---------> Double click on it to edit   ---------> Provide the following information

 Interested in mastering MSBI Training? Enroll now for FREE demo on MSBI Training.

Connection: Select New Connection   ---------> Click New   ---------> Provide server name (local host)   ---------> Select adventure works data base   ---------> SQL command – select 1/0   Execute SQL task 2: Rename it as task 3   ---------> Double click on it to edit   ---------> Provide the following information   Connection: Select new connection   ---------> Click new   ---------> Provide server name (Local Host)   ---------> Select adventure works data base   ---------> SQL  command – select 1/10   ---------> Make  a connection from task 1 to task 2 and task 2 to task 3   ---------> Right click on control flow and select properties   ---------> Set the following settings,   ---------> Check points.   MISSINGLINE PG:50  click Browser   ---------> Provided check points XML as a title name   ---------> Click open   ---------> Check point usage – If exists   ---------> Save check points – True   ---------> Select task 1 and set the following properties   ---------> Fail parent on failure – True   ---------> Fail package on failure – True   ---------> Set the above 2 properties for task 2 and task 3 also   ---------> Execute package   ---------> You will observe that task 2 is failed   ---------> Select or click F5 to start the debugging   ---------> Double click on Task 2 to edit the SQL query, change the SQL command, select one by one (any valid SQL statement)   ---------> Click ok   ---------> Execute package   ---------> The package execution starts at task 2   For Loop Container: For Loop Container is used to loop through a specified task at a specified number of times when ever the evolution expression becomes false automatically the control comes out of the loop

Learn more about MSBI Interview Questions in this blog post.

Steps to configure for Loop container Open Business intelligence development studio   ---------> Create a new package and rename it as for loop .dtsx   ---------> In control flow drag and drop data flow task double click on data flow task to configure it   ---------> Drag and drop excel source and configure it with any valid excel file   ---------> Drag and drop OLEDB destination and make a connection from excel source to OLEDB destination   ---------> Double click on OLEDB destination provide connection manager if exists   ---------> Click new to create destination table and rename it as for loop container.   ---------> Select mappings and click ok   ---------> Note: If you execute the package the data flow text will be executed only once. If you want to execute a task or a container multiple number of times place a specified task or container on for loop container.   ---------> In control flow drag and drop for loop container and place the data flow task on for loop container   ---------> Right click on control flow and select variables declare the following variable, of type integer UV counter.   ---------> Double click on or loop container and provide the following expression   ---------> Int Expression – a. UV counter = 0 Eral Expression –a. UV Counter < 0 Assign expression – a. UV counter = a UV counter+1 Click ok   ---------> Execute package   For each loop container: For each loop container is used to loop through multiple files, data sets, XML files, multiple sheets from a single excel work book. The above mentioned operations can be achieved by using available enumerates is collection  tab