Exporting data from flat file to SQL using SSIS (Multiple files)
-
Hi,
I am facing an issue with exporting data from flat file to SQL using SSIS. The flat file has the following data (also attached as Flat_file):
PAYMENTFILE|250|42|20200224|1300
1|1003003382|GBP|171.95|20200224
2|1003003383|GBP|171.95|20200224
3|1003003384|GBP|171.95|20200224
It needs to be loaded to the SQL table as attached (SQL_Snapshot) using SSIS. There is also an additional requirement to load multiple flat files.
Can somebody please help as to how this can be achieved ?
Thanks.
- This topic was modified 4 years, 9 months ago by pwalter83.
-
What is the issue you are facing? What have you tried so far - and where is it failing or causing issues?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Please describe the issue.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin - pwalter83 wrote:
Hi,
I am facing an issue with exporting data from flat file to SQL using SSIS. The flat file has the following data (also attached as Flat_file):
This delimited file structure seems simple enough to import into SQL Server using SSIS. If all of the files have the same structure, you could easily handle it with a "Foreach File Enumerator" and using an expression in your file connection manager.
-
Looking at this again - is the issue you are having related to the file(s) having a header/trailer record and detail records? If so - that can be handled in several ways depending on how you want to handle the data in the header/trailer record.
If you need a value from the header record to be associated with the detail data then you will need a script component or a script task. Again, all depends on what you actually need to do with the data in the header/trailer record.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
How do you know when one data set begins and another ends? Is the first line always a different dataset to the rest, or could you have something that looks like this?
PAYMENTFILE|250|42|20200224|1300
1|1003003382|GBP|171.95|20200224
2|1003003383|GBP|171.95|20200224
3|1003003384|GBP|171.95|20200224
PAYMENTFILE|350|52|2020025|1700
4|1003003385|GBP|171.95|20200225
5|1003003386|GBP|171.95|20200225
6|1003003387|GBP|171.95|20200225Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Thom A wrote:
How do you know when one data set begins and another ends? Is the first line always a different dataset to the rest, or could you have something that looks like this?
PAYMENTFILE|250|42|20200224|1300
1|1003003382|GBP|171.95|20200224
2|1003003383|GBP|171.95|20200224
3|1003003384|GBP|171.95|20200224
PAYMENTFILE|350|52|2020025|1700
4|1003003385|GBP|171.95|20200225
5|1003003386|GBP|171.95|20200225
6|1003003387|GBP|171.95|20200225Hi Thom,
Thanks for your reply. You are right, I didn't think about this scenario. The example you gave above is exactly how the files will differ from each other. The PAYMENTFILE stays the same in the first row but the other delimiter values will change from one file to another.
I have not been able to work out how to repeat the values from the first row. Attached is the latest snapshot that I am stuck at.
-
You're not going to want to use a Flat File Source for this. You're going to (very likely) want a Script Component, and will need to write your own code (C#, VB.Net is you must) to read the file one line at a time, and put into values into the relevant columns and rows.
Honestly, my C# is rudimentary, and although I could likely achieve this it would take me far too long compared to those more fluent in the language.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Thom A wrote:
You're not going to want to use a Flat File Source for this. You're going to (very likely) want a Script Component, and will need to write your own code (C#, VB.Net is you must) to read the file one line at a time, and put into values into the relevant columns and rows.
Honestly, my C# is rudimentary, and although I could likely achieve this it would take me far too long compared to those more fluent in the language.
Thanks again. I am at the same level with C#. I will try to google this, can you please point me to some useful links if possible ? Thanks.
-
To be honest, I disagree with Thom somewhat. I would use a Flat File source, to break out the data into its columns.
From there, it would be necessary to feed the output into an Asynchronous Script Component Transformation (if you Google this, you will find many examples). The logic required would be something like this:
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply