2

I have a file like as seen below; please help how to import it through SSIS.The problem comes how to skip the starting lines as the rest of the file has all columns tab separated.Also i need some information from the header lines like Name of BSC to be used as another column.

BSS release :   11                      
Name of BSC :   E344_JUB4                       
Type of measurement :   RT110_CELL/TRX related overview counters        
Measurement begin date and time :   5/21/2012 7:00      
Measurement end date and time   :   5/21/2012 8:00              
Input file name :   /alcatel/var/share/AFTR/APME/BSC/PMRES110.30A.16.E344_JUB4.2012-021.05:00:29.35.259                             
Output file name    :   /alcatel/var/share/AFTR/APME/OBSYNT/E344_JUB4/20120521/R11000008.142            
BTS_INDEX   BTS_SECTOR  CELL_NAME   CELL_CI CELL_LAC    BSC_MCC BSC_MNC MC01    MC02    MC02A   MC02B   MC02C   MC02D   MC02E   MC02F   MC02G   MC02H   MC02I   MC03    MC04    MC07    MC10    MC101   MC1040
1   1   JUB3227_4   32274   3719    420 F03 246 597 264 23  37  1   0   0   30  242 0   0   0   0   0   0   0
1   2   JUB3227_5   32275   3719    420 F03 331 6508    6118    25  51  3   0   0   40  271 0   0   0   0   0   0   0
1   3   JUB3227_6   32276   3719    420 F03 156 640 294 75  40  2   0   0   24  205 0   0   0   0   0   0   0
10  1   JUB3227_1   32271   3719    420 F03 151 1821    1584    17  36  3   0   0   8   173 0   0   0   0   0   0   0
mu is too short
  • 426,620
  • 70
  • 833
  • 800
user1820133
  • 41
  • 1
  • 4

1 Answers1

0

Assuming that your file has a fixed number of header lines, then the following should work;

Separate the reading of the source into two separate data flows. The first for passing the header; the second for the detail.

In the first stream, treat the file as colon delimited. Add Source (Excel?) control, Change the Data Access Mode to "SQL Command Mode". Add a SQL statement like SELECT top 2 f1 FROM [Sheet1$]. This will select the top 2 rows from the first sheet and return the f1 column only. You will need to adjust for your file, to cover the number of header rows you require.

Add a derived column to split the Name : Value pair into two. Create 3 new Derived columns.
- First = "Name" with an expression like LEFT(f1,FINDSTRING(f1,":",1) - 1) - Second = "Value" with an expression like RIGHT(f1,LEN(f1) - FINDSTRING(f1,":",1) - 1) - Third = "Key" with Expression 1

Add a Pivot control to switch the many rows (one per parameter) into many columns. Enter "Name" as Pivot Key, "Key" as Set Key and "Value" as Pivot Value. Within Generate Pivot Table Columns from Output Values section, enter a value per header name i.e. [BSS release], [Name of BSC],[...] This will result in a single row with all of your header values as columns. You can then combine this with your detailed data, or store separately.

In the detail source set the connection to skip header rows by x to bypass the header information. Treat as you would a file without the header info. Add the file name etc variables using to the row using a derived column component.

Daryl Wenman-Bateson
  • 3,870
  • 1
  • 20
  • 37
  • thanks for your idea.But can you explain me in detail how to perform these two steps as i am new to SSIS.I mean can you tell me what script to write to get three fields "Name of BSC" Measurement begin time" and "Measurement End time" from the header rows.And how to perform second step so tht the columns from forst step can be added to the second table.I would highly appreciate your guidance.thanks – user1820133 Nov 23 '12 at 21:43
  • How far are you getting before you get stuck? If you have not started yet, start with the detail data. Create a new file (minus the header info). Add a Data Flow Task with a source / destination to read / store the data from your new file. Once you have acieved that add a few blank lines to the top of your file and see if you can spot the bypass x rows option. Meanwhile, I will try to find an example for the header section and add a link to my answer. – Daryl Wenman-Bateson Nov 26 '12 at 10:10