Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mikebird
Aged Yak Warrior
529 Posts |
 Posted - 2010-02-18 : 07:00:08
|
| Flat File only see the first column, column 0, of the reqd 233 columnsRAW file has no idea. Maybe I have to put everything into Excel (only have version 2003), manually..? Every day? The next set of files will have 904 columns...Is there another option at all? Data looks like this: vertical bar column delimiterXMLTSHDR130000154000001GE6OPI0020|020|0201093430005661696C91534432 ||060|2009-12-01|2010-01-08|09:48:00|2009-12-01|2009-12-31|9|2010-01-08|09:48:00|NT340|5NP||||||||000008025|NT340|4606217928|1951-12-06||01|1|TAYLOR,RAYMOND THOMAS|MR|RAYMOND THOMAS|TAYLOR||||44 WHARTON GARDENS WINSFORD CHESHIRE |44 WHARTON GARDENS||WINSFORD|CHESHIRE||CW7 3AZ|5NP|4049915|120|120|||||||||||||||||||||||||||||||0934300056|01|5|1|03|8|2|2009-12-01|57||ISECS||702033|000139648909|NT340|5NP|||||||||||||||||||||||||||||||||||||||||||||||||||01|NT340|G8204163|N81040|1|1|03|2009-10-28|G8341109|5NP||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||X09000139648909|X09000139648909|X09|34|2009-10-28|2009-12-10020|020|0201093430005961698C81034436 ||060|2009-12-03|2010-01-08|09:48:00|2009-12-01|2009-12-31|9|2010-01-08|09:48:00|NT340|5NP||||||||000008063|NT340|6144959777|1970-02-16||01|2|WILSON,SHARON VICTORIA|MISS|SHARON VICTORIA|WILSON||||8 RUBIN DRIVE CREWE CHESHIRE |8 RUBIN DRIVE||CREWE|CHESHIRE||CW1 3GF|5NP|2925895|502|502|||||||||||||||||||||||||||||||0934300059|01|5|1|03|8|2|2009-12-03|39||ISECS||702033|000140021271|NT340|5NP|||||||||||||||||||||||||||||||||||||||||||||||||||01|NT340|G8501743|N81015|1|1|03|2009-11-10|G9804777|5NP||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||X09000140021271|X09000140021271|X09|34|2009-11-10|2009-12-10020|020|0201093430006061698C84034436 ||060|2009-12-03|2010-01-08|09:48:00|2009-12-01|2009-12-31|9|2010-01-08|09:48:00|NT340|5NP||||||||000008071|NT340|4825087558|1964-01-18||01|2|TOOKE,NICOLA D|MS|NICOLA D|TOOKE||||102 MILLSTONE LANE NANTWICH CHESHIRE |102 MILLSTONE LANE||NANTWICH|CHESHIRE||CW5 5PE|5NP|2925895|502|502|||||||||||||||||||||||||||||||0934300060|01|5|1|03|8|2|2009-12-03|45||ISECS||702033|000140247548|NT340|5NP|||||||||||||||||||||||||||||||||||||||||||||||||||01|NT340|G8038155|N81010|1|1|03|2009-11-16|G8038155|5NP||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||X09000140247548|X09000140247548|X09|34|2009-11-16|2009-12-10020|020|0201093430006361702C82534241 ||060|2009-12-07|2010-01-08|09:48:00|2009-12-01|2009-12-31|9|2010-01-08|09:48:00|NT340|5NP||||||||000007955|NT340|4461867757|1953-08-23||01|2|REBBECK,CAROL IRENE|MRS|CAROL IRENE|REBBECK||||14 PORTREE DRIVE HOLMES CHAPEL CREWE CHESHIRE |14 PORTREE DRIVE|HOLMES CHAPEL|CREWE|CHESHIRE||CW4 7JB|5NP|2837736|110|110|||||||||||||||||||||||||||||||0934300063|01|5|1|03|8|2|2009-12-07|56||ISECS||702033|000140132060|NT340|5NP|||||||||||||||||||||||||||||||||||||||||||||||||||01|NT340|G9011423|N81077|1|1|03|2009-11-12|G8138316|5NP||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||X09000140132060|X09000140132060|X09|30|2009-11-12|2009-12-10020|020|0201093430006461702C81034241 ||060|2009-12-07|2010-01-08|09:48:00|2009-12-01|2009-12-31|9|2010-01-08|09:48:00|NT340|5NP||||||||000008066|NT340|4460764229|1948-07-05||01|1|BUSBRIDGE,LESTER JOHN|MR|LESTER JOHN|BUSBRIDGE||||14 DILLORS CROFT CREWE CHESHIRE |14 DILLORS CROFT||CREWE|CHESHIRE||CW1 4UB|5NP|2837736|110|110|||||||||||||||||||||||||||||||0934300064|01|5|1|03|8|2|2009-12-07|61||ISECS||702033|000140125863|NT340|5NP|||||||||||||||||||||||||||||||||||||||||||||||||||01|NT340|G8105013|N81053|1|1|03|2009-11-12|G8710240|5NP||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||X09000140125863|X09000140125863|X09|34|2009-11-12|2009-12-10020|020|0201093430006561702C66031564 ||060|2009-12-07|2010-01-08|09:48:00|2009-12-01|2009-12-31|9|2010-01-08|09:48:00|NT340|5NP||||||||000008072|NT340|4924322601|1939-03-24||01|2|LATHAM,JUDITH M|MRS|JUDITH M|LATHAM||||69 MURRAYFIELD DRIVE WILLASTON NANTWICH CHESHIRE |69 MURRAYFIELD DRIVE|WILLASTON|NANTWICH|CHESHIRE||CW5 6QF|5NP|3611968|100|100|||||||||||||||||||||||||||||||0934300065|01|5|1|03|8|2|2009-12-07|70||ISECS||702033|000139933437|NT340|5NP|||||||||||||||||||||||||||||||||||||||||||||||||||01|NT340|G9009369|N81084|1|1|03|2009-11-16|G9801231|5NP||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||X09000139933437|X09000139933437|X09|34|2009-11-16|2009-12-10020|020|0201093430006661702C64531564 ||060|2009-12-07|2010-01-08|09:48:00|2009-12-01|2009-12-31|9|2010-01-08|09:48:00|NT340|5NP||||||||000008053|NT340|4169055373|1935-10-03||01|2|DRYSDALE,JENNIFER MINNIE|MRS|JENNIFER MINNIE|DRYSDALE||||32 CHERRY LANE CHURCH LAWTON STOKE-ON-TRENT STAFFORDSHIRE |32 CHERRY LANE|CHURCH LAWTON|STOKE-ON-TRENT|STAFFORDSHIRE||ST7 3QF|5NP|3611968|100|100|||||||||||||||||||||||||||||||0934300066|01|5|1|03|8|2|2009-12-07|74||ISECS||702033|000139914200|NT340|5NP|||||||||||||||||||||||||||||||||||||||||||||||||||01|NT340|G8507718|N81111|1|1|03|2009-11-06|G9408236|5NP||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||X09000139914200|X09000139914200|X09|34|2009-11-06|2009-12-10020|020|0201093430006761702C91534433 ||060|2009-12-07|2010-01-08|09:48:00|2009-12-01|2009-12-31|9|2010-01-08|09:48:00|NT340|5NP||||||||000008085|NT340|6231107884|1963-02-24||01|2|HUGGINS,DENISE E|MRS|DENISE E|HUGGINS||||6 CALDICOTT CLOSE WINSFORD CHESHIRE |6 CALDICOTT CLOSE||WINSFORD|CHESHIRE||CW7 1LW|5NP|3073469|100|100|||||||||||||||||||||||||||||||0934300067|01|5|1|03|8|2|2009-12-07|46||ISECS||702033|000140352870|NT340|5NP|||||||||||||||||||||||||||||||||||||||||||||||||||01|NT340|G8912406|N81127|1|1|03|2009-11-19|G8810418|5NP||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||X09000140352870|X09000140352870|X09|34|2009-11-19|2009-12-10020|020|0201093430006861702C93034433 ||060|2009-12-07|2010-01-08|09:48:00|2009-12-01|2009-12-31|9|2010-01-08|09:48:00|NT340|5NP||||||||000008087|NT340|6120577181|1973-08-24||01|2|SHARRATT,JOANNE|MS|JOANNE|SHARRATT||||26 GREENACRES CREWE CHESHIRE |26 GREENACRES||CREWE|CHESHIRE||CW1 4JU|5NP|3073469|100|100|||||||||||||||||||||||||||||||0934300068|01|5|1|03|8|2|2009-12-07|36||ISECS||702033|000140501956|NT340|5NP|||||||||||||||||||||||||||||||||||||||||||||||||||01|NT340|G8704931|N81044|1|1|03|2009-11-20|G9311484|5NP||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||X09000140501956|X09000140501956|X09|30|2009-11-20|2009-12-10020|020|0201093480000261703C795902250 ||060|2009-12-08|2010-01-08|09:48:00|2009-12-01|2009-12-31|9|2010-01-08|09:48:00|NT340|5NP||||||||000008088|NT340|4663618499|1952-02-13||01|1|MORREY,KEVIN|MR|KEVIN|MORREY||||62 DANEBANK AVENUE CREWE CHESHIRE |62 DANEBANK AVENUE||CREWE|CHESHIRE||CW2 8AD|5NP|4452474|100|100|||||||||||||||||||||||||||||||0934800002|01|5|1|03|8|2|2009-12-08|57||ISECS||702033|000140506159|NT340|5NP|||||||||||||||||||||||||||||||||||||||||||||||||||01|NT340|G8704931|N81044|1|1|03|2009-11-20|G8647173|5NP||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||X09000140506159|X09000140506159|X09|34|2009-11-20|2009-12-15020|020|0201093480000361703C780902250 ||060|2009-12-08|2010-01-08|09:48:00|2009-12-01|2009-12-31|9|2010-01-08|09:48:00|NT340|5NP||||||||000008036|NT340|4409069969|1945-03-20||01|2|WESTON,HEATHER M|MRS|HEATHER M|WESTON||||17 DRURY CLOSE CREWE CHESHIRE |17 DRURY CLOSE||CREWE|CHESHIRE||CW1 5LA|5NP|4452474|100|100|||||||||||||||||||||||||||||||0934800003|01|5|1|03|8|2|2009-12-08|64||ISECS||702033|000139794562|NT340|5NP|||||||||||||||||||||||||||||||||||||||||||||||||||01|NT340|G8704931|N81044|1|1|03|2009-11-02|G8133940|5NP||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||X09000139794562|X09000139794562|X09|34|2009-11-02|2009-12-15020|020|0201093510000161710C90034432 ||060|2009-12-15|2010-01-08|09:48:00|2009-12-01|2009-12-31|9|2010-01-08|09:48:00|NT340|5NP||||||||000008051|NT340|4581079233|1956-12-25||01|1|TWITCHETT,ANDREW R|MR|ANDREW R|TWITCHETT||||18 HAMILTON CLOSE HASLINGTON CREWE CHESHIRE |18 HAMILTON CLOSE|HASLINGTON|CREWE|CHESHIRE||CW1 5PQ|5NP|4049915|120|120|||||||||||||||||||||||||||||||0935100001|01|3|1|03|1||2009-12-15|52||ISECS||702033|000139938528|NT340|5NP|02|D072|2009-12-15||||||||||||||||||||||||||||||||||||||||||||||||01|NT340|G8704931|N81044|1|1|03|2009-11-06|G8133940|5NP||C55|3.5|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||X09000139938528|X09000139938528|X09|34|2009-11-06|2009-12-18020|020|0201093510000261709C66031564 ||060|2009-12-14|2010-01-08|09:48:00|2009-12-01|2009-12-31|9|2010-01-08|09:48:00|NT340|5NP||||||||000008076|NT340|6120672494|1979-12-18||01|2|HORNE,LISA JAYNE|MISS|LISA JAYNE|HORNE||||35 MOORHOUSE AVENUE ALSAGER STOKE-ON-TRENT STAFFORDSHIRE |35 MOORHOUSE AVENUE|ALSAGER|STOKE-ON-TRENT|STAFFORDSHIRE||ST7 2DT|5NP|2487571|100|100|||||||||||||||||||||||||||||||0935100002|01|3|1|03|8||2009-12-14|29||ISECS||702033|000140264857|NT340|5NP|||||||||||||||||||||||||||||||||||||||||||||||||||01|NT340|G8507718|N81111|1|1|03|2009-11-17|G9303070|5NP||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||X09000140264857|X09000140264857|X09|34|2009-11-17|2009-12-18020|020|0201093510000361709C54034247 ||060|2009-12-14|2010-01-08|09:48:00|2009-12-01|2009-12-31|9|2010-01-08|09:48:00|NT340|5NP||||||||000008092|NT340|4562086408|1951-12-13||01|1|DAWKINS,EDWIN CHARLES|MR|EDWIN CHARLES|DAWKINS||||WILLOWDALE 230 LONDON ROAD, LEFTWICH NORTHWICH |WILLOWDALE|230 LONDON ROAD, LEFTWICH|NORTHWICH|||CW9 8AQ|5NP|3474228|110|110|||||||||||||||||||||||||||||||0935100003|01|5|1|03|8|2|2009-12-14|58||ISECS||702033|000140755047|NT340|5NP|||||||||||||||||||||||||||||||||||||||||||||||||||01|NT340|G3382251|N81055|1|1|03|2009-11-23|G8904205|5NP||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||X09000140755047|X09000140755047|X09|34|2009-11-23|2009-12-18020|020|0201093510000461709C55534247 ||060|2009-12-14|2010-01-08|09:48:00|2009-12-01|2009-12-31|9|2010-01-08|09:48:00|NT340|5NP||||||||000008105|NT340|4983248498|1957-07-20||01|1|MACDOUGALL,ANGUS|MR|ANGUS|MACDOUGALL||||22 LAURESTON AVENUE CREWE CHESHIRE |22 LAURESTON AVENUE||CREWE|CHESHIRE||CW1 5HU|5NP|3474228|110|110|||||||||||||||||||||||||||||||0935100004|01|5|1|03|8|2|2009-12-14|52||ISECS||702033|000149467104|NT340|5NP|||||||||||||||||||||||||||||||||||||||||||||||||||01|NT340|G8704931|N81044|1|1|03|2009-11-25|G9311484|5NP||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||X09000149467104|X09000149467104|X09|34|2009-11-25|2009-12-18020|020|0201093510000561706C58034461 ||060|2009-12-11|2010-01-08|09:48:00|2009-12-01|2009-12-31|9|2010-01-08|09:48:00|NT340|5NP||||||||000007964|NT340|4522457367|1953-09-03||01|2|JAMES,JANET MARGARET|MRS|JANET MARGARET|JAMES||||OAKLAND 55 COPPICE ROAD, WILLASTON NANTWICH |OAKLAND|55 COPPICE ROAD, WILLASTON|NANTWICH|||CW5 6QD|5NP|2307046|110|110|||||||||||||||||||||||||||||||0935100005|01|5|1|03|8|2|2009-12-11|56||ISECS||702033|000140175602|NT340|5NP|||||||||||||||||||||||||||||||||||||||||||||||||||01|NT340|G9009369|N81084|1|1|03|2009-11-16|G8836339|5NP||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||X09000140175602|X09000140175602|X09|34|2009-11-16|2009-12-18 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-18 : 07:09:05
|
| Not sure I'm understanding what the problem is.Is the source file delimited, or fixed width?Putting it in Excel will most likely muck up the data (leading zeros will be removed, anything that looks like a date will be converted to a date, etc.) |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-18 : 07:44:10
|
| I would use BCP (but SSIS would do as well) to push it into a Staging table - with each column set up to be a decent sized VARCHAR (i.e. not a more relevant DataType), then run some tests on it to ensure that the formatting of dates, etc., is "safe", and then import it from there. |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-02-18 : 07:52:03
|
| Hi.I've cut and pasted your data.I think you want:input source : flat filedelimitedcolumn delimiter : |row delimiter : Either <cr><lf> or if it is actually a unix file it will just be <cr> (I think)Then you should be good to go.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-18 : 08:06:12
|
| " if it is actually a unix file it will just be <cr> "I think Unix is actually <lf> ... but its a coin-toss for sure !! |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-02-18 : 08:20:44
|
| well it's definitely one of them (and only one of them) I can never remember which.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
mikebird
Aged Yak Warrior
529 Posts |
Posted - 2010-02-18 : 12:04:38
|
| I got all the data on one side, but with no column-mapping it's a mess. 904 columns in Excel OK. Put these two sources connected with a Merge. They stick together OK, but still errors:TITLE: Package Validation Error------------------------------Package Validation Error------------------------------ADDITIONAL INFORMATION:Error at Data Flow Component [SSIS.Pipeline]: input column "Flat File Source Error Output Column" (1724) has lineage ID 1714 that was not previously used in the Data Flow task.Error at Data Flow Component [SSIS.Pipeline]: "component "Problems" (1715)" failed validation and returned validation status "VS_NEEDSNEWMETADATA".Error at Data Flow Component [SSIS.Pipeline]: One or more component failed validation.Error at Data Flow Component: There were errors during task validation. (Microsoft.DataTransformationServices.VsIntegration)------------------------------BUTTONS:OK------------------------------ |
|
|
|
|
|
|
|
|
What's Best Source Object for UNIX file?