CODEWITHSUNDEEP
sqlsql-server-2008ms-accessimportssis
John Straka
John Straka

Reputation: 1914

Will SSIS work well for importing to multiple tables?

I won't have access to SSIS until tomorrow so I thought I'd ask for advice before I start work on this project.

We currently use Access to store our data. It's not stored in a relational format so it's an awful mess. We want to move to a centralized database (SQL Server 2008 R2), which would require rewriting much of our codebase (which, incidentally, is also an awful mess.) Due to a time constraint, well before that can be done we are going to need to get a centralized database set up solely for the purpose of on-demand report generation for a client. So, our applications will still be running on Access. Instead of:

Receive data -> Import to Access initial file with one table -> Data processing -> Access result file with one table -> Report generation

The goal is:

Receive data -> Import to Access initial file with one table -> Import initial data to multiple tables in SQL Server -> Export Access working file with one table -> Data processing -> Access result file -> Import result to multiple tables in SQL Server -> Report generation whenever

We're going to use SSRS for the reporting component, which seems like it'll be straightforward enough. I'm not sure if SSIS alone would work well for splitting the Access data up into numerous tables, or if everything should be imported into a staging table with SSIS and then split up with stored procedures, or if I'll need to be writing a standalone application for this.

Haven't done much of any work with SQL Server before, so any advice is appreciated.

Upvotes: 0

Views: 1206

Answers (2)

Jeff Hornby
Jeff Hornby

Reputation: 13640

You certainly can split a data flow into two separate branches and send it to two destinations. All you need to do is put a multi-cast in the dataflow and then the bulk of the transformations will happen after that.

From what you've said, however, a better solution might be to use the Access tables as a staging database and then grab the data from there and send it to SQL Server. That would mean two data flows but it will be a cleaner implementation.

Upvotes: 1

Igor Turman
Igor Turman

Reputation: 2205

In SSIS package, you can write code (e.g. C#) to do your own/custom data transformations. However, SSIS comes with built-in transformations that may be good for your needs. SSIS is very powerful and flexible. Actually, you may do pretty much anything you want with the data in SSIS. The high level workflow for your task could like like the following: 1. Connect to the data source and pull the data 2. Transform the data 3. Output data to the destination data source

Upvotes: 1

Related Questions