MSBI - II

Create a package in SSIS :

Go to Start --> Programs --> Microsoft SQL Server 2008 (o)r 2012 --> 

SQL Server Business Intelligence Development Studio (or) SQL Server Data Tools as shown in the below figure:

It will open a BIDS as shown in the below figure.Once the BID studio is open, we need to create a solution based on our requirement. Go to File -> New Project (or) Ctrl + Shift + N. It will opens a wizard where we need to select Integration Services Project .

Select the Integration services project and provide the project name, solution name and location, where we want to store the SSIS files as shown in the below screen:

After creating new project, we can see that by default a new SSIS package is added (Package.dtsx), you can right click on it and rename.

Sample Example :

The below example explains a simple SSIS package that can fetch the data from "Notepad" to a "SQL Server Table",
From the below figure

1. A Source server contains some data in a Notepad and a Destination server contains an empty table in a database. 
2. Source connection manager contains Source server Name, Notepad name and location,
3. Validation process will checks the sources for meta data which you mentioned in query is valid or not,(Meta data means : Objects, columns, data on the Source)
4. Destination connection manager contains Destination server Name, Database name and Table name.

I have taken a "Data Flow" task from control flow Toolbox to "Control Flow" editor,

Right click on "Data Flow Task" and click on "Edit...",

The controle will go to the "Data Flow",

I have taken a "Flat file Source"  and a "SQL Server Destination" to the Data flow, First I defined the Source with Source server Name, Notepad name and location as,right click on "Flat file Source" and click on "Edit..",

There will be a "Flat file source Editor" window is opens for "Flat file Connection manager", this is our first package, no connection managers have been defined earlier. So click on "New.."

There will be a "Flat file Connection manager Editor" is opens. In "General" tab provide the name for connection manager, location of the notepad (Browse..),Format as Delimited(Either the data in the notepad separated by comma{,} or tabs or fixed length) and select the check box for display first row data as header in the output .

Now go to the Columns tab, set the column delimeter as "Comma{,}" and find the data as preview

Make a connection between source and destination by drag the arrow from source to destination. Now defined the Destination connection by right clicking on "SQL Server Destination" and click on "Edit..". It will displays a "Destination editor" window, click on "New.." for Connection, again it will displays OLEDB Connection Managers window again click on "New..". 

Provide the Destination Server name, Username, Password and Select the database(In which database you want to store). Click on OK. You will returns to previous window.

If your destination table is already existed, just click on drop down list, else click on "New.." for create a new table, here the system provides predefined table structure, if you want just rename it,

Go to Mappings Tab, Map the Input and Outpu columns,

Now come back to Controle flow, right click on "Data Flow Task" and click on "Execute Task". Debugging will starts  and if your Task executed successfully you get a right mark in green color. Stop the Debugging. Go to the SQL Server see the destination table has the data or not.