SQL Azure and MS SQL Server Integration Services

Enterprise data can be of very different kinds ranging from flat files to data stored in relational databases and XML datasources. The extraordinary number of database related products, and their historic evolution, makes this task exacting. The entry of cloud computing has brought area into the forefront as SSIS has been one of the methods indicated for bringing ground based data to cloud storage in SQL Azure, the next milestone in Microsoft Data Management. Presently the following methods are indicated:

• Run Insert statements SQL Manager to populate tables schema created using SQL Azure MW*
• Configure SSIS to bring in data
• BCP [perhaps in next CTP of SQL Azure]

The first method works and can be very fast (only a small table was tested )

It may be noted that SSIS is another tool to bring in tables (so far only tried this object in the initial tests) to SQL Azure although only table schema is migrated and not the data. If the table were to have a clustered index, data porting may also be possible. In the test conducted the table did not even have a primary key column.

* It appears that the recent tweaking of SQL Azure MW can bring in the entire database according to a posting on the Oakleaf Systems blog site.

Data Transfer from MS Access to EnterpriseDB's PostGres

EnterpriseDB sits atop Postgres, the well known open source database server and leverages it to provide enterprise capabilities to Postgres users.

Out of the box EnterpriseDB's Migration Studio supports migration from SQL Server, Postgres and Sybase only. In this article, the Microsoft Integration Services is used to transfer a table form Microsoft Access 2003 to a database  on the EnterpriseDB Postgres server.

MS SQL Server Integration Services 2008 is a great product that handles various ETL Processes very effectively. However, sometimes its error messages can be confusing as you will see in this article. For example, where as the task execution has failed in this example, the data transfer has taken place.

Read on,

Transferring a table in a MS Access 2003 database to PostGres using SQL Server Integration Services 


This is a subscription site and you can login as guest and read free or join as a member using link for SSWUG.ORG on the main page.

If you are new to EnterpriseDB you may also want to read my other article first

Installation and basic features of EnterpriseDB

Presently this is not working in SQL Server Integration Services 2008

Whereas the Web Service Task can connect to the Report Server Web Service the methods of the service are not accessible. This will be addressed in the next major release [see link below].




https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=367597&wa=wsignin1.0

SSIS 2005 and the building blocks

In working with SQL Server Integration Services it is very important to know the details of the various building blocks that the Visual Studio provides. A building is never built like a monolith. It is rather constructed using the building blocks. Even larger programs are built using reusable smaller programs.

Another important thing to know is how you visualize a building. A good architectural design needs a good knowledge of the materials it can use - the building blocks. Of course you need a good cement and aesthetically pleasing finishing touches. Visual Studio 2005 and now 2008 has all the necessary ingredients.

This article shows one architect's view of using the building blocks to accomplish a particular task and there could be other simpler and more elegant designs possible coming from more experienced and accomplished architects. Read on and see how the blocks are assembled.

http://www.packtpub.com/article/data-processing-using-derived-column-aggregate-data-transformations




I have described in some detail a large number of the building blocks in my book
http://www.packtpub.com/sql-server-integration-services-visual-studio-2005/book

You can get a real jump start by reading this book in a matter of under two weeks.