Hi

I have created a DTS as part of my month end process to import 40 different
csv files from our various sales offices.

Each file contains the account no and purchase information for the entire
month. eg

13245, box1, box5, box4
45897, box9, box3, box5, box2
98765, box1

The maximum number of boxes is 9, so any row in the csv file could have up to
10 columns (including account number), but if a customer only purchases 1 box
there will only be 2 coluns.

The problem I have is that when I execute the DTS it will only bring through
the 1st 3 boxes and put nulls in all the other columns.

Is there a way to change the Transform Data Task Properties for each files
using VB depending on the maximum number of columns in each files.

This has really stumped me so any help or alternative ways of doing this
would greatfully recieved.

Thanks

"CravenStu" wrote:

import data into excel, text to columns, line-up data, add zeros, then save
file and dts into sql.
although other than for speed of insertions, you really DO want the null
values in the tables.

So what do you want to do?

Read the line as a single string and then SPLIT() the row on commas.
You now know how many elements are in thaat row. You can do with it
then as you please


Like this

Processing The Same Row More Than Once
(http://www.sqldts.com/default.aspx?266)


What does you destination look like?

It sounds as though it looks like this

Account#,
Purchase#1
Purchase#2,
Purchase#3,
Purchase#4,
Purchase#5,
Purchase#6,
Purchase#7,
Purchase#8

Is tht correct?

Allan


"CravenStu" <u16001@uwe> wrote

Thanks Allen,

you are correct about the destination table layout. I will try your idea
today.

Cheers

Stu

Allan Mitchell wrote:
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums...r-dts/200511/1