A Package is a core object with in Sql Server Integration Services (SSIS). It contains
• Business logic
• Work flow elements
• Connections
Business logic to handle the data extraction, manipulation, and transformation tasks needed to move data from one location to another location depends on requirement.
Workflow elements involve running a stored procedure, moving a file from an FTP server to a destination folder on your server, or sending an email message when an error occurs. The work flow elements are in control flow.
Connections to connect to different external systems such as databases, files, File Transfer Protocol (FTP) Servers, Simple Mail Transfer Protocol (SMTP) Servers. Connections are used for this SSIS data processing engine called as dataflow.
Finally the package is best parallels an executable program that maintains workflow and business logic. Simply say that a package is a collection of tasks snapped together to execute in an orderly fashion.
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.
• Work flow elements
• Connections
Business logic to handle the data extraction, manipulation, and transformation tasks needed to move data from one location to another location depends on requirement.
Workflow elements involve running a stored procedure, moving a file from an FTP server to a destination folder on your server, or sending an email message when an error occurs. The work flow elements are in control flow.
Connections to connect to different external systems such as databases, files, File Transfer Protocol (FTP) Servers, Simple Mail Transfer Protocol (SMTP) Servers. Connections are used for this SSIS data processing engine called as dataflow.
Finally the package is best parallels an executable program that maintains workflow and business logic. Simply say that a package is a collection of tasks snapped together to execute in an orderly fashion.
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…
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
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.
“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>
SELECT * FROM <table name>
In my next session we are discussing about saving and Editing Package created by wizard.
Hope you like it.
