troubleshooting Question

Importing a datafile via SSIS on MS SQL 2016

Avatar of the_b1ackfox
the_b1ackfoxFlag for United States of America asked on
Microsoft SQL ServerSSIS
2 Comments1 Solution191 ViewsLast Modified:
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
Megan Brooks
SQL Server Consultant

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros