This is continuous From My previous Post to create SQL Server Integration Services to get the updated data to MultiDimensionalAnalysisProject (MDAP) Database.
These are the following steps
1. Create SQL Server Integration Services (SSIS) projects.
2. Define Control flows and Data flows.
3. Deploy SSIS projects.
4. Schedule SSIS packages.
1. Create SQL Server Integration Services (SSIS) projects.
First we need to set up the framework for an SSIS project in our current MDAP Solution.This is done by adding a new project to the solution that is currently opened in Microsoft Visual Studio.
(Select integration services project in the new project)
This will result in the SSIS project being created, and the new project should be immediately visible in the Solution Explorer.
2. Define Control flows and Data flows.
In the SSIS Toolbox in the left hand side of the screen and drag the Data Flow Task item into the design area.Double click it to go to the Data Flow tab of the Design window.
To work with data flow we should define source and destination connections.For this we should create OLEDB Connections for the MDAP database, by Right clicking in the connection Manager which is the lower part of the Designer window.After completion of Connection setting Drag and drop the OLEDB source and Destination Controls.Then Define the Source table(from Live Database ) for Source and Destination Table(Database what we Create in initial step) in Destination Control.( if You get any time stamp error after mapping edit mapping relation ship and delete the time stamp mapping link).
Start Debugging we can see successful package run.
But each time if run it will create a new set of records in the destination tables, adding to the already existing records.
So, before we transfer the latest set of data to the destination tables, we must ensure that they are emptied first.
Let us move back to the Control Flow page of the SSIS package, where we will add an Execute SQL Task item.it to define it. The connection must point to the Database what we Create in initial step because it is the tables in there we would like to empty.Next, click the button in the SQL Statement field to enter the SQL Query that we would like to execute. The truncate statement removes the content of the table without removing the table itself.
Connect the two Control Flow items so that the Execute SQL Task item is executed before the Transfer Data Flow item.
3. Deploy SSIS projects.
We need to process the Multidimensional database every single time the data have been updated.
For that go to the toolbox and select an Analysis Service Processing Task item.
This Processing Task also requires a Connection Manager to be able to know which multidimensional database needs to be processed. So we will need to add an Analysis Services Connection to our list of
Connection Managers.
Debug it again for successful creation.
4. Schedule SSIS packages.
Assigning this job for SQL Agent. Already we know how to create a job and schedule it .
Open the SQL server Management studio:
Right click the Jobs folder to add a new job.Give name and other details,Go to the Steps page and click the New button. At the New Job Step page we must enter information about Name, Type and Source.
• Step name: Run the name of your SSIS Package.
• Type: SQL Server Integration Services Package.
• Run as: SQL Server Agent Service Account.
• Package Source: File system.
in next step shedule in when to run. ok
Finally right click the job and select "Start job at step" option.Make sure that the job can execute and complete without errors.(close all window related to this task before start).
That's it we completed the SQL Server Integration Services process ready to use in any BI Clients.
These are the following steps
1. Create SQL Server Integration Services (SSIS) projects.
2. Define Control flows and Data flows.
3. Deploy SSIS projects.
4. Schedule SSIS packages.
1. Create SQL Server Integration Services (SSIS) projects.
First we need to set up the framework for an SSIS project in our current MDAP Solution.This is done by adding a new project to the solution that is currently opened in Microsoft Visual Studio.
(Select integration services project in the new project)
This will result in the SSIS project being created, and the new project should be immediately visible in the Solution Explorer.
2. Define Control flows and Data flows.
In the SSIS Toolbox in the left hand side of the screen and drag the Data Flow Task item into the design area.Double click it to go to the Data Flow tab of the Design window.
To work with data flow we should define source and destination connections.For this we should create OLEDB Connections for the MDAP database, by Right clicking in the connection Manager which is the lower part of the Designer window.After completion of Connection setting Drag and drop the OLEDB source and Destination Controls.Then Define the Source table(from Live Database ) for Source and Destination Table(Database what we Create in initial step) in Destination Control.( if You get any time stamp error after mapping edit mapping relation ship and delete the time stamp mapping link).
Start Debugging we can see successful package run.
But each time if run it will create a new set of records in the destination tables, adding to the already existing records.
So, before we transfer the latest set of data to the destination tables, we must ensure that they are emptied first.
Let us move back to the Control Flow page of the SSIS package, where we will add an Execute SQL Task item.it to define it. The connection must point to the Database what we Create in initial step because it is the tables in there we would like to empty.Next, click the button in the SQL Statement field to enter the SQL Query that we would like to execute. The truncate statement removes the content of the table without removing the table itself.
Connect the two Control Flow items so that the Execute SQL Task item is executed before the Transfer Data Flow item.
3. Deploy SSIS projects.
We need to process the Multidimensional database every single time the data have been updated.
For that go to the toolbox and select an Analysis Service Processing Task item.
This Processing Task also requires a Connection Manager to be able to know which multidimensional database needs to be processed. So we will need to add an Analysis Services Connection to our list of
Connection Managers.
Debug it again for successful creation.
4. Schedule SSIS packages.
Assigning this job for SQL Agent. Already we know how to create a job and schedule it .
Open the SQL server Management studio:
Right click the Jobs folder to add a new job.Give name and other details,Go to the Steps page and click the New button. At the New Job Step page we must enter information about Name, Type and Source.
• Step name: Run the name of your SSIS Package.
• Type: SQL Server Integration Services Package.
• Run as: SQL Server Agent Service Account.
• Package Source: File system.
in next step shedule in when to run. ok
Finally right click the job and select "Start job at step" option.Make sure that the job can execute and complete without errors.(close all window related to this task before start).
That's it we completed the SQL Server Integration Services process ready to use in any BI Clients.
No comments:
Post a Comment