April 16, 2012 at 6:27 am
I just received a huge new data dictionary (old line length was 1200 bytes, new one is 3000 bytes) that needs to get into SSIS.
Did I miss someone's brilliant exegesis on how to avoid the drudgery of manually copying each attribute name, data type, starting position and length from Excel into XML suitable for consumption by a derived column transformation?
I dread wasting the time and effort of copying what is already known from Excel into SSIS just to get it into XML...
thanks very much
drew
April 16, 2012 at 6:33 am
If you are importing from Excel to SQL Server, where does XML come into it?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 16, 2012 at 6:43 am
i am importing from Unix into SQL Server...the data dictionary is in Excel
April 16, 2012 at 6:48 am
drew.georgopulos (4/16/2012)
i am importing from Unix into SQL Server...the data dictionary is in Excel
OK. Excuse my lack of Unix knowledge here. If this is a start position/length scenario, why are you using derived columns? Why not do it all in the data source?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 16, 2012 at 6:59 am
Unix dumps the output all into one line, so i get a bazillion records with no CRLF.
We use a split function (either in MKS toolkit or in powershell) to find the 1200th byte and insert them there so we know where a record ends.
Then we traverse the line according to the spec, choping off each attribute into a column, so if i know the start of the record, i know the first eight bytes belong to a member id, the next eigth are the date of servce, the next eight the payment date etc etc until we get to the end of the line.
April 16, 2012 at 7:02 am
Light bulb just went off....
did you mean do it in the connection manager?
its still manual, but in the connection manager its dragging lines to define the columns instead of typing them...
April 16, 2012 at 7:03 am
drew.georgopulos (4/16/2012)
Light bulb just went off....did you mean do it in the connection manager?
Yes, sorry - exactly what I meant.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 16, 2012 at 7:05 am
gonna try that...should be less tedious
thanks very much
drew
April 16, 2012 at 7:06 am
drew.georgopulos (4/16/2012)
gonna try that...should be less tediousthanks very much
drew
No problem - hope it works.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply