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

Header row is different than detail row Expand / Collapse
Author
Message
Posted Thursday, June 27, 2013 4:58 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 2:08 PM
Points: 653, Visits: 3,841
Hello Folks,

I have a file with a header row that is different than all the other detail rows.

ABC 20130627 123
1|A|20120102|
1|A|20130301|
3|C|20030102|
5|S|20111111|

It looks like the header data is dependent on its position. I'd be happy to jam it all in one column and split on the tsql side.

What's the best way to handle this type of file in SSIS, I'm used to working with files where all lines are consistent.

Thanks
Post #1468389
Posted Friday, June 28, 2013 7:45 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 5:41 AM
Points: 1,382, Visits: 1,554
Are the column names changing very often? If not, your best bet is probably to ignore the header row and name the columns explicitly. In other words, uncheck Column names in the first data row and specify 1 Header row to skip.

An alternative is to use a Script Component to split each record rather than splitting in T-SQL. It will probably be easier to write and would allow further processing if necessary.

As an aside, did you mean for each record to end with a delimiter? I'm currently working with such a file and haven't seen that before. Perhaps its more common than I thought.
Post #1468549
Posted Friday, June 28, 2013 8:09 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 2:08 PM
Points: 653, Visits: 3,841
I was previously skipping row 1 but now I need to extract some data out of the header.

I'll take a look at script components which I haven't used before.

The file actually does end with the delimiter (and a carriage return). I think its sourced from a old system, perhaps from the mainframe world.

Thanks
Post #1468565
Posted Friday, June 28, 2013 9:22 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 5:41 AM
Points: 1,382, Visits: 1,554
Easiest path is probably to switch the file connector to see a single column. Use a Script component and specify it as a transform. The code is very straightforward and there are tons of examples on-line.

I'm interested in your statement that you want some information from the header. That will mean treating it like a data row. The Script task will again help, since you can tell you are on the first record. You might also want to send the header record to a separate output, unless you really want it loaded in the database.

Let me know if you have any difficulties or questions.
Post #1468589
Posted Friday, June 28, 2013 11:02 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 2:08 PM
Points: 653, Visits: 3,841
brdudley (6/28/2013)
Easiest path is probably to switch the file connector to see a single column.


I could bulk insert into a single column and then use the Moden string splitter. If I am going into one column anyway SSIS is not really doing very much for me.

If I was going to use SSIS its almost if I want two data flows, one for the first line and a second that skips the first line.

I'm clearly not a fan of this type of file. I'd rather embed metadata in the file name rather than a header row.
Post #1468633
Posted Tuesday, July 2, 2013 8:13 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 10:34 AM
Points: 386, Visits: 624
I would create a second flat file connection for the data source with a single column and then use conditional split to ignore all but first line.

Provided the format of that first line is consistent you can then use string functions (replace, trim, substr) etc to get the data you need.

Post #1469537
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse