|
I'm trying to map an pipe delimeted, ascii text file via ssis and need to have any extra data columns loaded into an xml data type data column. So, if our standard data file layout is 40 data columns and the customer sends in 45 data columns, we want to take these extra 5 columns and put them into an xml data column in our data table. Each customer may be different however. One may send in 1 extra data column and another could send in 45 extra data columns. Is there a way for SSIS do this, or is there another way to accomplish loading 1..n extra data columns?
(comments are locked)
|
|
I would handle this with a script task in the control flow, but you could also use a script component in the data flow as a source (which I haven't used yet) With a script task you can read the standard columns, then dynamically build the xml from the extra columns in the file, and insert into your table.
(comments are locked)
|
|
I'm not sure exactly how I'd tackle the XML import as I'd need to know a lot more about the data, but if this was a ragular task that needed to be done as quickly as possible, I'd approach the problem in general by reading it into the database in a simple table based on a primary key and a Varchar(max) column, followed by an integer column for every row you want to import. Then you'd convert it, and transfer it to the final destination in SQL. Robyn and I describe the technique here ... http://www.simple-talk.com/sql/t-sql-programming/importing-text-based-data-workbench/ 40 columns represents some unwieldy SQL but it is mostly cut and paste. This will allow you then to take the final data columns and put them in your XML Data column.
(comments are locked)
|
