TechBubbles

This post explains about creating a SSIS package for demonstrating the small ETL process using transforms and connections in SQL server 2005.

 

1. Create a new SSIS package and drag a DataFlow task onto the control flow. Double click the task to go to the Data Flow tab.

 

2. In the Data Flow tab, drag an OLEDB Source onto the design pane. Select the data connection to Adventure Works Database.

 

3. Make sure that the Data Access Mode option is set to “Table or View”. Select the [Production].[TransactionHistoryArchive] table from Table drop-down box.

4. Select the ProductID,Quantity and ActualCost columns and say OK to exit the editor.

5. Drag a Derived Column transform to dataflow and connect the dataflow task to this task.

6. Double-click on the Derived Column transform to open the editor and type the new column with name TotalCost and configure the column as follows and click ok to exit the editor.

7. Drag an Aggregate transform onto the data flow and connect the arrow from Derived Column transform onto this transform. Double click the editor to configure the properties.

8. Drag a Sort transform onto data flow pane, connect Aggregate transform to this transform by the green arrow. Double-click on transform to configure the properties. Click OK to exit the editor.

9. Now we export this data to a flat file so that interested parties can read the file. Drag a Flat File dataflow to pane and connect the Sort transform by  using the green arrow.

10. Double click on Flat File transform to get the following editor.

11. Now execute the package and you should be see the rows flow through the package as shown below

Conclusion

In this post, we have seen how transformations in SSIS allows us to change the data from a source and pass the results as output to a destination or another transform.

Related Posts:

• SSIS Package Configuration in SQL server 2008
• Runtime Settings with SSIS Package

1 comment

Tasks are fundamental units in SSIS control flow. Every task in SSIS needs to be configure with required fields. This post going to explain how to use FTP and File System task. You can use the File System to automate data load process.

Consider a scenario where you receive a file from source system via FTP and your package needs to be react by moving it to another working directory.

1. Create a new SSIS package using BIDS and drag the FTP task to the design pane.

  

2. Double click the task to set the properties

 

Type a name for the task and set the FTPConnection properties as shown above.

Go to File Transfer page and select the value for remoteVariable. Click Ok to exit the editor. After file being downloaded then you can copy to the local folder for achieving. To copy the file into an archive directory, we will use the File System task. Drag the task to designer and configure the properties.

In the control tab, connect the FTP file task to the File System task with a precedence constraint. The file will be FTPed first and then copied to the archived folder.

Related Posts:

• SSIS Package Configuration in SQL server 2008
• Runtime Settings with SSIS Package
• Creating a SSIS package in VS 2008
• SQL Server Integration Services Features

No comments