We help IT Professionals succeed at work.

Importing a datafile via SSIS on MS SQL 2016

the_b1ackfox asked
Last Modified: 2017-04-30
Hello Experts,

I have to ask this as a hypothetical question because I cannot show the actual files for compliance reasons.   That being said, I have several files that follow a format.  I can modify the format of the incoming files, if need be to accomplish the goal.  The goal is to import the files into my database where each line basically goes into a separate table.  At the end of the day, I need to be able to recreate the file data from the database in full fidelity as compared to any of the specific files.

Line 1 contains File/customer data   This is the only time this data will be in the file.  Each line is prefaced with a 4 digit code so I can tell what data to pull from each line.  Line items form blocks of data starting with the incident data line and ends when there in no longer a line item.  A file could be as small as 4 lines, but it could also be several thousand lines.

Someone I know who works with SSIS (but hasn't done it very long), wasn't sure if SSIS could handle this.  I believe it is possible, and the motivation for using SSIS is that I suspect it will upload the data as an efficient multi threaded process (maybe timed or polled).

What I need from you are answer to the following questions:

Is SSIS the tool for this job?
How could I change the file format to make this work, or how can I get SSIS to load this type of data?
Can the SSIS be set as a timed or polled event?
And is it efficient, mutlti threaded?  
Can multiple instances be run?  (So if I get like 30 client uploading files)

Line 1 Customer/File Data

Line 2  Incident data
Line 3  Cost data
Line 4  Line item
Line 5  Line item

Line 6  Incident data
Line 7  Cost data
Line 8  Line item

Line 9  Incident data
Line 10  Cost data
Line 11  Line item
Line 12  Line item

And on and on
Watch Question

SQL Server Consultant
Unlock this solution and get a sample of our free trial.
(No credit card required)


Thank you for answering the question.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.