x

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?

more ▼

asked May 03, 2010 at 04:13 PM in Default

JimOG gravatar image

JimOG
1 1 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.

more ▼

answered May 04, 2010 at 09:47 PM

Daniel Ross gravatar image

Daniel Ross
2.9k 10 13 14

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered May 05, 2010 at 03:15 PM

Phil Factor gravatar image

Phil Factor
3.9k 8 9 16

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x939
x150
x107
x10

asked: May 03, 2010 at 04:13 PM

Seen: 2537 times

Last Updated: May 03, 2010 at 04:13 PM