People who are new to programming can also easily gel with
MSBI batch conducted at Mumbai. First day of the training starts from basic fundamentals of the MSBI and SQL Server technology. Below are few such basics questions covered from past training.
What are Control Flow and Data flow in SSIS?
Control flow invokes the data flow. A package consists of a control flow and, optionally, one or more data flows.Control flow deals with orderly processing of individual tasks, these tasks are linked through precedence constraints. Also, the output for task has finite outcome i.e., Success, Failure, or Completion. A subsequent task does not initiate unless its predecessor has completed.
SSIS tool box for control flow.
SQL Server Integration Services provides three different types of data flow components: sources, transformations, and destinations. Sources extract data from data stores such as tables and views in relational databases, files, and Analysis Services databases. Transformations modify, summarize, and clean data. Destinations load data into data stores or create in-memory datasets.
Adding a Data flow task to the control flow of a package is the first step in implementing a data flow in a package. A package can include multiple data flow tasks, each with its own data.
This SSIS toolbox for Data flow.
Different between Control Flow and Data Flow.
In Control Flow tasks require completion (Success, fail or completion)
before moving to the next task.
In Data Flow one component will not wait for other component to finish,
all of them work together for processing data in the streaming way.
There is no proper structure for control flow task as we have for data
It consists of Source, transformation and destination task items.
Smallest unit task
Smallest unit is Component.
Made up of Tasks connected with Lack of precedence.
Made up of Source(s), Transformation(s) and Destination(s).
We can add the constraint in the link between the control flow tasks.
We can’t add any constraint between the links Control flow, we only can
add the data viewer to watch out the intermediate Data.
When does failed to start project error occurs?
When the package is executed after successfully “build” is done and if you find the following image screen error which showsexception of deserializing the package. This error come because of 32-bit and 64-bit runtime issues.So what does that means? It is the SSIS execution issue when OS is 64 bit and is tried to run on SSDT 32 bit this error occurs. Once 64BitRuntime option is set from True to False this error will go away.
So here is how we can do it practically. Just do right click on project under Solution Explorer and go to the properties.
When you click on the “Properties”.
Solution of the error. Right click on the project and go to properties. Then after go to “Configuration properties” and click on the “Debugging”.
After that go to “Debug option” and click on the “Run64BitRuntime” and select to “false”.
The following screen will come.
How to see error details occurred within package?
So, in order to get the error details under Package.dtsx file click on the "Progress" tab and then go to the red mark and just next to it written description for the cause of that error following image shows the red cross mark error with its details. This Progress tab actually tells what exactly is happening behind when the Package.dtsx file is executing. If the error, you are not able to read it then just do right click and copy-paste it on notepad to understand the error clearly.
How to disable warning of prevent saving changes which require table recreation in SQL Server?
If you want to update any Column in the table. You face the error. Saving changes is not permitted. This is one of the primary warning whichcan be skipped from getting it in SQL Server. This error says that the changes you have made require the table to be dropped and needs to re-created it again.
To disable this warning screen following are the steps need to be followed.
First you go to “Tools”àOptions. The following screen will come.
You will see Option window as shown in the following screen shot. In that click on the “Designers” option on the left side then do uncheck on the option at the right the “Prevent saving change that require table re-creation” box.
With the above the changes made it will now not show warning.