CODEWITHSUNDEEP
sql-servert-sqlssis
Krishna Kumar
Krishna Kumar

Reputation: 125

Load data from multiple source into a destination

I have a desktop application through which data is entered and it is being captured in MS Access DB. The application is being used by multiple users(at different locations). The idea is to download data entered for that particular day into an excel sheet and load it into a centralized server, which is an MSSQL server instance. i.e. data(in the form of excel sheets) will come from multiple locations and saved into a shared folder in the server, which need to be loaded into SQL Server. There is a ID column with IDENTITY in the MSSQL server table, which is the primary key column and there are no other columns in the table which contains unique value. Though the data is coming from multiple sources, we need to maintain single auto-updating series(IDENTITY).

Suppose, if there are 2 sources, Source1: Has 100 records entered for the day. Source2: Has 200 records entered for the day.

When they get loaded into Destination(SQL Server), table should have 300 records, with ID column values from 1 to 300.

Also, for the next day, when the data comes from the sources, Destination has to load data from 301 ID column.

The issue is, there may be some requests to change the data at Source, which is already loaded in central server. So how to update the data for that row in the central server as the ID column value will not be same in Source and Destination. As mentioned earlier ID is the only unique value column in the table.

Please suggest some ides to do this or I've to take up different approach to accomplish this task.

Thanks in advance! Krishna

Upvotes: 1

Views: 2187

Answers (1)

djangojazz
djangojazz

Reputation: 13242

Okay so first I would suggest .NET and doing it through a File Stream Reader, dumping it to the disconnected layer of ADO.NET in a DataSet with multiple DataTables from the different sources. But... you mentioned SSIS so I will go that route.

  1. Create an SSIS project in Business Intelligence Development Studio(BIDS).

  2. If you know for a fact you are just doing a bunch of importing of Excel files I would just create many 'Data Flow Task's or many Source to Destination tasks in a single 'Data Flow Task' up to you.

    a. Personally I would create tables in a database for each location of an excel file and have their columns map up. I will explain why later.

    b. In a data flow task, select 'Excel Source' as the source file. Put in the appropriate location of 'new connection' by double clicking the Excel Source

    c. Choose an ADO Net Destination, drag the blue line from the Excel Source to this endpoint.

    d. Map your destination to be the table you map to from SQL.

    e. Repeat as needed for each Excel destination

  3. Set up the SSIS task to automate from SQL Server through SQL Management Studio. Remember you to connect to an integration instance, not a database instance.

  4. Okay now you have a bunch of tables right instead of one big one? I did that for a reason as these should be entry points and the logic to determinate dupes and import time I would leave to another table.

  5. I would set up another two tables for the combination of logic and for auditing later.

    a. Create a table like 'Imports' or similar, have the columns be the same except add three more columns to it: 'ExcelFileLocation', 'DateImported'. Create an 'identity' column as the first column and have it seed on the default of (1,1), assign it the primary key.

    b. Create a second table like 'ImportDupes' or similar, repeat the process above for the columns.

    c. Create a unique constraint on the first table of either a value or set of values that make the import unique.

    c. Write a 'procedure' in SQL to do inserts from the MANY tables that match up to the excel files to insert into the ONE 'Imports' location. In the many inserts do a process similar to:

    Begin try
   Insert into Imports (datacol1, datacol2, ExcelFileLocation, DateImported) values
   Select datacol1, datacol2, (location of file), getdate()
   From TableExcel1
End try

-- if logic breaks unique constraint put it into second table
Begin Catch
   Insert into ImportDupes (datacol1, datacol2, ExcelFileLocation, DateImported)  values
   Select datacol1, datacol2, (location of file), getdate()
   From TableExcel1
End Catch

-- repeat above for EACH excel table

-- clean up the individual staging tables for the next import cycle for EACH excel table
truncate TableExcel1

    d. Automate the procedure to go off

  6. You now have two tables, one for successful imports and one for duplicates.

The reason I did what I did is two fold:

  1. You need to know more detail than just the detail a lot of times like when it came in, from what source it came from, was it a duplicate, if you do this for millions of rows can it be indexed easily?

  2. This model is easier to take apart and automate. It may be more work to set up but if a piece breaks you can see where and easily stop the import for one location by turning off the code in a section.

Upvotes: 1

Related Questions