In this post we will try to crate a package that extract the data from ‘Student Table’ of Source-Database and load it successfully in ‘Student Table’ of ‘Destination-Database’. You can Download or look into the sample database I am using.
System requirements for SSIS or Business intelligence Development:
- You should have installed SQL Server Standard or Enterprise version.
- I am using Visual Studio Add-in (SQL Server Data Tools) in this SSIS tutorial to design SSIS package. This needs SSDT also to be installed.
Once confirmed that SSDT is installed on machine. We are ready to create an ETL(Extract – Transform – Load) package.
So here we go!
Here are the scripts that you can run in order to generate two databases(Source and destination) with Tables in it.
Go to Start Screen or Click Start Button. Go to Microsoft SQL Server and you will find ‘SQL Server Business Intelligence Development Studio’ under this.
|Open SQL Server business Intelligence Development Studio|
This opens a instance of Visual Studio. Click on File tab and select ‘New Project‘. Chose ‘Business Intelligence Projects‘ as project type and select ‘Integration Services Project‘, type a sensible name for your project(I have given it Simple Data Transfer, that’s what it does.). you can chose a location and whether you want to create a separate directory for your project under selected hard-drive location.
|Selecting project template for SSIS using visual studio.|
- Variables: Here variables can be defined that can later used during execution of ETL or package. user should mention a appropriate scope for the variable defined.
- ToolBox: As normal toolbox control of visual studio, contains all the necessary tools to crate a package under SSIS
- Solution Explorer: Hierarchy of the folders of the current project.
- Properties: Shows available properties for selected module/component from editor.
- Control Flow: The main container where everything else resides.
- Data Flow: Data Flow is the main component when we have to transfer data.
Open Toolbox and double click ‘Data Flow Task’ or drag-drop it on editor window. This is the only task needed for this ‘Simple Data Transfer‘ ETL Project. Double click on the ‘Data Flow Task‘ in editor and it opens a editor for selected Data Flow Task.
Double Click the ‘Data Flow Task‘. Select ‘OLE DB Source‘ from toolbox and drag it to editor. as we have to transfer the data from a SQL Server Database.
On ‘Data Flow‘ editor, there is a ‘Connection Manager‘ section at bottom. Right Click on this section and select ‘New OLE DB Connection…’
This opens a ‘Connection Manager‘ window. Select the server here and connect to a database selecting it from drop-down box. To log on to server either use windows Authentication or use SQL Server Authentication credentials of the user you are logging in with. Click ‘OK‘. This will create a new connection manager to connect with.
Now ‘OLE DB Source‘ can be configured using this new connection. select the appropriate connection from list and say ‘OK‘ pressing the button obviously 🙂
Once done with setting-up the database connection for source, chose the table to extract data from.(Student in this case). Click ‘OK‘ and we are done with ‘OLE DB Source‘ configuration.
Now drag-drop a ‘OLE DB Destination‘ component from toolbox on editor window, click on ‘OLE DB Source‘ and connect the Green Line to ‘OLE DB Destination.’ And Configure the destination database same as we configured source database.(just select destination databse while you select database and not source)
Now its the time to disco (mapping actually). Let us now map the columns of source and destination tables. Click on ‘Mapping‘ menu in ‘OLE DB Destination‘ configuration, and there will be 2 tables connecting by some links to appropriate columns.
Click on the first link connecting IDs of both the tables and delete it. As we have defined ID column for tables as Identity it will get auto-generated value by itself.
That’s fine for now. We don’t need so many configuration for this easy task. Save your package file and go to ‘Control Flow‘ tab. Right click on ‘Data Flow task‘ and click on ‘Execute task‘. This will execute the task and transfer the data from source table to destination table.
If it runs successfully(chances are 100% if your are following step by step ETL creation till now.), it will turn ‘GREEN‘.
Or if you want you can also debug SSIS package using debugging events. Right click on ‘Data Flow Task‘ and set debugging brake points.
Cheers and comment or share if this helps!!