question

JimOG avatar image
JimOG asked

ssis bulk insert using ascii text

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?

ssisxmlinsertbulk
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

2 Answers

·
Daniel Ross avatar image
Daniel Ross answered

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.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Phil Factor avatar image
Phil Factor answered

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.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.