InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 16:34:45 GMT20http://www.sqlservercentral.com/Forums/Topic1077055-147-1.aspxNP :cool:Mon, 14 Mar 2011 09:14:31 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic1077055-147-1.aspxBWAA-HAA!!! Great minds DO think alike! Sorry to steal your thunder, Wayne. :-)Mon, 14 Mar 2011 09:02:39 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1077055-147-1.aspx[quote][b]Jeff Moden (3/13/2011)[/b][hr][quote][b]josetur12 (3/13/2011)[/b][hr]Thank you all again. WayneS' solution works.[/quote]If you have the data to exercise Wayne's good solution, you might want to look into what a BCP format file does for performance. It contains mostly the same kind of data.[/quote]Dang it, you did it again. :-D I was waiting to see if he tried this, and was then going to suggest the same.Mon, 14 Mar 2011 07:43:43 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic1077055-147-1.aspx[quote][b]josetur12 (3/13/2011)[/b][hr]Thank you all again. WayneS' solution works.[/quote]If you have the data to exercise Wayne's good solution, you might want to look into what a BCP format file does for performance. It contains mostly the same kind of data.Sun, 13 Mar 2011 20:45:48 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1077055-147-1.aspxThank you all again. WayneS' solution works.Sun, 13 Mar 2011 17:25:09 GMTjosetur12http://www.sqlservercentral.com/Forums/Topic1077055-147-1.aspx[quote][b]josetur12 (3/11/2011)[/b][hr]I have a flat file with over 200 fixed length columns[/quote]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.Sat, 12 Mar 2011 11:07:19 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1077055-147-1.aspx#Columns is what I meant by "dictionary of columns". I csan only guess but I think that there will be some parsing involved.Sat, 12 Mar 2011 09:32:15 GMTRevenanthttp://www.sqlservercentral.com/Forums/Topic1077055-147-1.aspx[quote][b]josetur12 (3/11/2011)[/b][hr]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,[/quote]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.[code="sql"]DECLARE @Columns TABLE (ColumnOrder INT, ColumnName varchar(20), StartingPosition smallint, ColumnSize tinyint);INSERT INTO @ColumnsSELECT 1, 'Column1', 1, 10 UNION ALLSELECT 2, 'Column2', 11, 5 UNION ALLSELECT 3, 'Column3', 16, 12;DECLARE @TestData TABLE (RowID INT IDENTITY, RowData varchar(100));INSERT INTO @TestDataSELECT 'y4gfbnenbeofnoihfgjq3fgwqgnq3ovqvqno' UNION ALLSELECT '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;[/code]Fri, 11 Mar 2011 20:16:22 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic1077055-147-1.aspxYou 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.Fri, 11 Mar 2011 18:48:12 GMTRevenanthttp://www.sqlservercentral.com/Forums/Topic1077055-147-1.aspx[quote]However, you need one dataflow per target table, and for 200+ that would be difficult (although not impossible) to maintain.[/quote]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[quote]1. Add a Data Flow Task to the Control Flow.a)dbl-Click to enter the Data Flow Tab2. Add a Flat File Source to the Data Flow Taba)Identify the Flat File Source in the Flat File Source Editor Dialog3. Now That you have the Connection manager for the for the flat file created:a)edit the Flat File Conn Managerb)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 changed) you can also do multiplt selects to do several columns at once.[/quote]I'm still not seeing the issue?? Or the concern about 'parsing every field'.Fri, 11 Mar 2011 14:22:40 GMTstevefromOZhttp://www.sqlservercentral.com/Forums/Topic1077055-147-1.aspxYes, 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.Fri, 11 Mar 2011 13:48:47 GMTRevenanthttp://www.sqlservercentral.com/Forums/Topic1077055-147-1.aspxNot 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?Fri, 11 Mar 2011 12:03:16 GMTstevefromOZhttp://www.sqlservercentral.com/Forums/Topic1077055-147-1.aspxThan you for the suggestion.Fri, 11 Mar 2011 12:02:50 GMTjosetur12http://www.sqlservercentral.com/Forums/Topic1077055-147-1.aspxI 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.Fri, 11 Mar 2011 11:14:31 GMTRevenanthttp://www.sqlservercentral.com/Forums/Topic1077055-147-1.aspxHi, 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,Fri, 11 Mar 2011 10:39:44 GMTjosetur12