Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Fixed Length Flat File Expand / Collapse
Author
Message
Posted Friday, March 11, 2011 10:39 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 25, 2014 1:12 PM
Points: 36, Visits: 266
Hi, I have a flat file with over 200 fixed length columns. I want to use SSIS to load the data into SQL Server.
Is there a way to easily load them into SQL Server without parsing each column?
I will appreciate any help. Thanks,
Post #1077055
Posted Friday, March 11, 2011 11:14 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:51 PM
Points: 4,386, Visits: 3,395
I am afraid there is no "silver bullet" solution.

You can use a Data Flow task, with several flows if data goes into several tables, or a Script Task. In a Script Task you could define a dictionary of offsets and lengths by table and column name and do the parsing in one simple loop, which IMO would be easier to maintain than 200+ individual substrings.

Sorry if my answer is too general. I could perhaps give a more specific opinion if I had more detail.
Post #1077085
Posted Friday, March 11, 2011 12:02 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 25, 2014 1:12 PM
Points: 36, Visits: 266
Than you for the suggestion.
Post #1077121
Posted Friday, March 11, 2011 12:03 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: Moderators
Last Login: Yesterday @ 11:39 AM
Points: 1,814, Visits: 3,454
Not sure I understand the problem?? Why not use the flat file connection, set it to fixed length for the format and load up the data?


Steve.
Post #1077122
Posted Friday, March 11, 2011 1:48 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:51 PM
Points: 4,386, Visits: 3,395
Yes, that would be a data flow. However, you need one dataflow per target table, and for 200+ that would be difficult (although not impossible) to maintain.
Post #1077155
Posted Friday, March 11, 2011 2:22 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: Moderators
Last Login: Yesterday @ 11:39 AM
Points: 1,814, Visits: 3,454
However, you need one dataflow per target table, and for 200+ that would be difficult (although not impossible) to maintain.


Maybe I missed it, but I didn't see the requirement to load each column to an individual table. If you have (or want to create) a target table with the ~ 200 columns, that's easy enough to do. You can then set up the flat file connection as fixed width, and work with SSIS on it's guesses at the widths (ie you'll end up having to make changes where the guesses are wrong). Once you have this, you have a single data flow that will load all required fields into the target table.

Or, with more detail, posted by 'wcm' on 12-13-2010

1. Add a Data Flow Task to the Control Flow.
a)dbl-Click to enter the Data Flow Tab
2. Add a Flat File Source to the Data Flow Tab
a)Identify the Flat File Source in the Flat File Source Editor Dialog
3. Now That you have the Connection manager for the for the flat file created:
a)edit the Flat File Conn Manager
b)View the Advance Properties and you will see "Data Type" property and it's default is
String [DT_STR] and "OutputColumnWidth" is set to 50.
c) To change one column you can highlight the column in the middle list box, and make the change
d) you can also do multiplt selects to do several columns at once.


I'm still not seeing the issue?? Or the concern about 'parsing every field'.



Steve.
Post #1077175
Posted Friday, March 11, 2011 6:48 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:51 PM
Points: 4,386, Visits: 3,395
You do nolt have to load each column into a different table; however, if you are loading columns into more than one table (which is likely the case), you would create an SSIS Data Flow task that would be, in my experience, difficult to maintain.

Been there, done that. Thanks, I do not need another t-shirt.
Post #1077247
Posted Friday, March 11, 2011 8:16 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, July 24, 2014 7:55 PM
Points: 6,582, Visits: 8,860
josetur12 (3/11/2011)
Hi, I have a flat file with over 200 fixed length columns. I want to use SSIS to load the data into SQL Server.
Is there a way to easily load them into SQL Server without parsing each column?
I will appreciate any help. Thanks,


You could use T-SQL. Start with a table that contains each column (order, name, starting position, size). Then, utilizing BULK INSERT, load the data into a #temp table. Run a select to get the data.

This is a start... just expand upon it.
DECLARE @Columns TABLE (ColumnOrder INT, ColumnName varchar(20), StartingPosition smallint, ColumnSize tinyint);
INSERT INTO @Columns
SELECT 1, 'Column1', 1, 10 UNION ALL
SELECT 2, 'Column2', 11, 5 UNION ALL
SELECT 3, 'Column3', 16, 12;

DECLARE @TestData TABLE (RowID INT IDENTITY, RowData varchar(100));
INSERT INTO @TestData
SELECT 'y4gfbnenbeofnoihfgjq3fgwqgnq3ovqvqno' UNION ALL
SELECT 'qgbughqofniufgonqgoinvoqfqnbqnqn3qfo';

WITH cte AS
(
SELECT t.RowID, dt.*
FROM @TestData t
CROSS APPLY (SELECT ColumnOrder,
ColumnName,
ColumnValue = SubString(t.RowData, StartingPosition, ColumnSize)
FROM @Columns) dt
)
SELECT RowID,
Column1 = MAX(CASE WHEN ColumnName = 'Column1' THEN ColumnValue ELSE NULL END),
Column2 = MAX(CASE WHEN ColumnName = 'Column2' THEN ColumnValue ELSE NULL END),
Column3 = MAX(CASE WHEN ColumnName = 'Column3' THEN ColumnValue ELSE NULL END)
FROM CTE
GROUP BY RowID
ORDER BY RowID;



Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1077258
Posted Saturday, March 12, 2011 9:32 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:51 PM
Points: 4,386, Visits: 3,395
#Columns is what I meant by "dictionary of columns". I csan only guess but I think that there will be some parsing involved.
Post #1077330
Posted Saturday, March 12, 2011 11:07 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:38 AM
Points: 36,755, Visits: 31,212
josetur12 (3/11/2011)
I have a flat file with over 200 fixed length columns


We're just guess here because we can't see your file.

Do you have a record layout for this file and can you attach a text file with the first 10 rows? Of course, if you do attach a file, make sure it's not violating any privacy or company private concerns.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1077340
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse