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
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
Microsoft SQL ServerSSIS
Last Comment
the_b1ackfox
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thank you for answering the question.