SSIS package

In my previous article I am trying to explain related to What is data warehousing. If you don’t read it please follow this link before going to this…

http://sqlknowledgebank.blogspot.in/2012/05/data-warehousing.html

In this article I am trying to explain related to SSIS package.

A Package is the core object within SQL server Integration Services (SSIS) that contains the business logic to handle workflow and data processing. SSIS package can be used to move data from source to destinations and also handle the timing precedence of when thing process.

**BIDS [ Microsoft SQL Server Business Intelligence Development Studio ]

SSIS package can be accomplished by two ways.

Built-in wizard
By using the Built-in wizard in SQL Server 2005 that asks you to move the data from source to destination and automatically generate the SSIS package.


SSIS BIDS
By explicitly create a project in SSIS BIDS. We need to create projects the new package is automatically created and developed.

So we now trying to discuss about our first option and that is

By Built-in Wizard

In SQL Server 2005 we can use the Import and the Export Wizard to Import and Export the data. For Import Wizard the source is the SQL Server 2005 table and destination should be SQL Server database, ORACLE database, Flat file, Microsoft Excel spread sheet, Microsoft Access database.

Exporting data with the wizard lets us send the data from SQL Server 2005 tables, Views or custom query to flat file or database connection.

Initialize the Import Export Wizard

To initialize, please follow this steps mentioned bellow.

What we want to do

We want to import a flat file to our existing database.

1.    Through the SSMS connects to the installed database engine. That should be your source or destination.

2.    Click on view menu select Object Explorer (or press F8). From the database folder select the desired database. Then right click of the desired database and select Tasks. From Tasks we can select Import or Export wizard.

3.    Select the Tasks. If the database is source of data that needed to send out to the different system, select the “Export Data” and if the database is destination for the file currently exists outside the system, than select “Import Data”. Here is this example we are choosing “Import data”.

Database is source of data  à Export Data

Database is destination for the file àImport Data

4.     If we choose any one the “Welcome to SQL Server Import Export Wizard” appears. Then click the next button on the wizard. “Choose the data source” allow you to specify from the data is coming from. Here in this example I am choosing Flat file source and brows the flat file. Please specify others options if needed.

“Choose a Destination” allow us to specify the destination where the data will be sending. We can choose the destination if needed. The server name and the security settings must be specified. If we select a relational database source that allow customer queries.

5.    For now in “Save and Execute” page of wizard we choose the options Execute Immediate for now. In the complete the wizard gives us all the information that we selected. If needed we can go back and modified it. Now use the SQL query to see the result output.

SELECT * FROM <table name>

In my next session we are discussing about saving and Editing Package created by wizard.

Hope you like it.

Posted by: MR. JOYDEEP DAS