Link to home
Create AccountLog in
Avatar of the_b1ackfox
the_b1ackfoxFlag for United States of America

asked on

Importing a datafile via SSIS on MS SQL 2016

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
Avatar of Megan Brooks
Megan Brooks
Flag of United States of America image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of the_b1ackfox


Thank you for answering the question.