With the ZIP data, why cant you load the character data into an int column - wont SQL Server will do an implicit cast during the load?

J

Would vertical partitioning be of any benefit as you cannot normalise the data?  If columns SN10 - SN60 are empty for most rows then you might not need to have...

What's the actual problem with implementing your archiving strategy as it is?  It, presumably, works when it is up and running so is there an actual problem or are you...

The only idea I have is for you to create an FTP batch file to logon onto the FTP site, retrieve a directory listing and download this to a local file. ...

I guess that you also don't want to change your archiving mechanism too much.

If you want to reduce the number of rows processed per execution, you need to reduce the...

Can you delete the remote files when you have processed them?  If you can then any files appearing there will be new.

J

just nest your replace into mine:

declare @t table

( columnId varchar(255))

insert @t select '\\CDR\HR Project\GMP 2.0\ABC'

insert @t select '\\CDR\HR Project\GMP 1.4'

insert @t select '\\CDR\HR Project\GMP 1.2\PQR'

insert @t select '\\CDR\HR Project\GMP 2.2\XYZ'

insert...

Is the problem identifying the new files or is the problem downloading the new files having identified them?

Also, is the FTP area where the new files will be a network...

How are you checking for new files?

Can you check the files in the downloaded folder and compare the list with the 'new' files and then just download the missing files?

J

declare @v-2 varchar(255)

set @v-2 = '2.0\ABC'

select replace(@v, '\', '(')

 + case

  when charindex('\', @v-2) > 0 then ')'

  else ''

 end

J

The Transform Data Task is slower than bulk insert - I think it does row by row processing rather than set processing.  If the input file is small (a few...

I think that the problem is when you create the string for @sql.  Put a print statement before the exec sp_executesql @sql line and see what the string is.

I think...

I still think it is failing on the comma in the NAME column.

Any chance you can do this in DTS using a Transform Data Task?

J

declare @rank int

set @rank = 0

update <table>

set @rank = rankorder = @rank + 1

For each row in the table processed, the value of @rank is incremented by 1.

J

The problem is that there are commas in the NAME column - it is splitting it into two columns and in the first row cannot insert PHYLISS A into the...