May 12, 2010 at 7:30 pm
Can someone guide me on how to utilise SSIS features for a datawarehousing application.
We have a big datawarehousing application currently running on SQL Server 2000.
Following are the data pull task that we do currently on our existing application
1) Pulling data from Multiple DB sources like Mainframe(100 tables) , Oracle(100 tables) etc to seperate Staging DB's for each source system.These Staging DB tables have identical structure as in source system
2) Accepting data as flat files from Printer queues (third party tool used)
3) Populating data to various schema tables (fact,dim and sup) from Staging tables
Currently we follow below method with SQL Server 2000 DTS and Stored Proc
1) Enitre work flow is put up as seperate SQL task in a DTS
2) Each SQL task (task 1 and 3) will call a particular Stored Proc which will inturn call numerous child SP's for various sub tasks . Note that most of the SP's have dynamic SQL as we have made the data pull configurable using certain driving tables (accessible to admin through a UI screen)
3) Source system connectivity is currently accomplished using multiple link servers to mianframe (exposed as an ODBC connection on the DB server) and Oracle (link server points to TNS details as mentioned in TNSORA on the DB server)
We are trying to migrate this application to SQL 2005 or 2008.
All we need is to effectively use SSIS and still retain the current dynamic configurable features of our data pull job.
May 14, 2010 at 2:28 am
Normally, you can do all of these tasks quite easily with SSIS.
1. Use OLE DB Sources to connect to the various databases. Specify the SQL statement to select the data within the OLE DB Source. For the linked servers, use Execute SQL Tasks and utilize the OPENQUERY command. Write the data to the staging tables with OLE DB Destinations (use the fast load option).
2. Store the flat files in a folder and use the Flat File Source to read the data. To make it flexible, use the For Each Loop Container to loop over directories and read each source file.
3. To populate your final tables, use again OLE DB Source and Destination. Data manipulation can easily be done with the standard components of SSIS (derived column, lookup, union et cetera. If needed, you can use script tasks/components with VB.NET or C#.NET. If necessary, you can use Execute SQL Statement to use T-SQL or to call procedures).
To make everything flexible, use package configurations. This will read configuration info from either a configuration table or a configuration file. Further configuration can be done using variables and expressions.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply