Last week I had my first experience with SQL Server Integration Services. Customer I am working now needed a data transfer from Oracle to SQL Server. The reason they wanted this was because availability of Oracle was not guaranteed. Data needed to be available at all times and Oracle databases were hosted by a third-party and SQL Server database were in house and we control over them. If one has access to SQL Server Instance with SSIS installed and templates for creating solutions are available inside Visual Studio one can start a analysis project, where SSIS packages can be incorporated (I do not know if this the exact approach to do it, but here I found a SSIS packages).
I created a new SSIS Package.
Next thing I did was creating two connection managers, so I can make connection to Oracle and SQL Server. When one creates a new connection manager there are a number of providers available like Native OLEDBMicrosoft OLE DB Provider for Oracle.
Choose this one fill in the details a test connection.
Next I created the package.
I will not go into detail about all the shapes you see here. There are a number of blog posts available I recommend when starting with SSIS in regard with Oracle. There’s one from SSIS junkies and there is a full blown one with explanations and such (this one uses a different approach). This is just a quick outline of what I have done with SSIS this week and is just the beginning. SSIS is a great option if one wants data integration in regard of bulk data instead of using BizTalk Server.
Technorati: 
SSIS
