question

subhashkomy avatar image
subhashkomy asked

SSIS import Excel Data Into SQL Server Staging Table

Hi Friends

Where I am working I get Excel files in Bulk from various sources. We have specified file structure i.e. Column Name, Data Type etc. However this file created by different user manually. I have setup SSIS ETL process to upload data in Staging table. The issue I am having is This file created manually so sometime users make mistake, sometime they change column order or column name, and make data error. i.e. Date so my package get fail. I do have to process 100s of files together so can't check each file manually. Can anyone tell me the best way to resolve this issue.

Is it a good idea if I upload all data in Text Format from Excel and then I can easily check data or type using stored procedure?

ssisexcel
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Jon Crawford avatar image
Jon Crawford answered

1. Educate your users, it's not your fault if they mess up the file format

2. fix the issue so they don't have to create the file manually

3. capture the errors when the file fails and escalate by bouncing back to the users and their manager so they can see why it failed. Maybe set up SLAs around how long they have to fix it/what the expectation is when it doesn't get loaded because they screwed up the format.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.