CODEWITHSUNDEEP
ssis
user3159292
user3159292

Reputation: 1

How to load data from fixedwidth file to SQLServer, here the main concept is handling data very carefully

I want to load fixed width flat file data into SQL Server, but here the major task is the data is very critical.

  1. the data should load row by row and each row have certain specifications like row 1 belongs to 1 header details and row 2 belongs another part of the details like this.

  2. Finally, here the most critical point is , in my file I have some portions of data comes in different segments with different delimiters, how can I handle these different delimiters in a single file and how can I load data from this file to SQL Server.

Please provide your valuable suggestions here and thanks in advance

Upvotes: 0

Views: 35

Answers (1)

CRAFTY DBA
CRAFTY DBA

Reputation: 14925

Wow, It sounds like your file layout is a mess. Here are two options.

1 - Load the data into a SSIS buffer as a blob of text. Write custom transformations to fix the mess. Might even involve C# scripting. Multiple passes of the data?

Output the formatted data to your target, SQL server.

This is called ETL - extract, translate, load.

2 - Load the data directly to SQL Server as a blob of text into a staging table. Write transformations in TSQL as stored procedures. Kick off the stored procedures from SSIS to fix the mess.

This is called ELT - extract, load, translate.

Again, you are very vague with this question. I can only suggest design patterns.

Upvotes: 1

Related Questions