Hi,

I'm trying to dynamically import data into MS SQL Server 2000 from a
text file using a DTS Script written in VB. The first row of the text
file contains the column headings. However, the column headings for a
file will not always be the same, so I need to dynamically set them.
Is there an easy way to do this in a DTS Script? I know I select the
property: "First row contains column headings" but later in the
sample script I had the Import Wizard generate, it has hard coded the
column headings. THere must be a way to keep that dynamic? Any help
would be great!

Thanks.


Anthony
Gamma Investors

Anthony,

Thank you for the idea!. It made me think in a way to
implemet it. You just need to place this code in a T-SQL
task in DTS and you are in business.

here is my 2 cents:

file is called tbl.txt and in this example is tab
delimited:

ID FirstName LastName Certifications
1 Edgardo Valdez MCSD, MCDBA, MCSE, MCP+I

--BEGIN OF T-SQL Script
create table ##tbl (line varchar(1000))

bulk insert ##tbl
from 'C:tbl.txt' -- PATH OF THE FILE
with (FIRSTROW=1)

declare @tempstr varchar(1000)
declare @col varchar(1000)
declare @createtbl varchar (1000)
declare @loadtbl varchar (1000)
declare @newtblname varchar(1000)

/*
================================================== =========
=========
Generates a unique identifier for the table based
on date and time
================================================== =========
=========*/

declare @month char(2)
declare @date char(2)
declare @year char(4)
declare @hour char(2)
declare @min char(2)
declare @sec char(2)
declare @msec char(3)

set @month = DATEPART(m, getdate())
if LEN(RTRIM(DATEPART(m, getdate()))) = 1
set @month = '0'+RTRIM(DATEPART(m, getdate()))
set @date = DATEPART(d, getdate())
if LEN(RTRIM(DATEPART(d, getdate()))) = 1
set @date = '0'+RTRIM(DATEPART(d, getdate()))
set @year = DATEPART(yyyy, getdate())
set @hour = DATEPART(hh, getdate())
if LEN(RTRIM(DATEPART(hh, getdate()))) < 2
set @hour = '0'+RTRIM(DATEPART(hh, getdate()))
set @min = DATEPART(mi, getdate())
if LEN(RTRIM(DATEPART(mi, getdate()))) < 2
set @min = '0'+RTRIM(DATEPART(mi, getdate()))
set @sec = DATEPART(ss, getdate())
if LEN(RTRIM(DATEPART(ss, getdate()))) < 2
set @sec = '0'+RTRIM(DATEPART(ss, getdate()))
set @msec = DATEPART(ms, getdate())
if LEN(RTRIM(DATEPART(ms, getdate()))) < 3
begin
if LEN(RTRIM(DATEPART(ms, getdate()))) = 2
set @msec = '0'+RTRIM(DATEPART(ms, getdate
()))
if LEN(RTRIM(DATEPART(ms, getdate()))) = 1
set @msec = '00'+RTRIM(DATEPART(ms, getdate
()))
end

set @col = ''
set @tempstr = (select top 1 RTRIM(Replace(Line, char
(9), ',')) from ##tbl)

while CHARINDEX(',',@tempstr) > 0
begin
set @col = @col+SUBSTRING(@tempstr, 1, CHARINDEX
(',',@tempstr)-1)+' varchar(1000),'
set @tempstr = SUBSTRING(@tempstr, CHARINDEX
(',',@tempstr)+1, len(@tempstr))
end

set @col = @col+@tempstr+' varchar(1000))'

set @createtbl = 'create table load_'+RTRIM
(@month+@date+@year+@hour+@min+@sec)+' ('+@col
set @newtblname = 'load_'+RTRIM
(@month+@date+@year+@hour+@min+@sec)

drop table ##tbl

exec (@createtbl)

set @loadtbl = 'bulk insert '+@newtblname+' from '+char(39)
+'C:\tbl.txt'+char(39)+' with (FIRSTROW=2)'

exec (@loadtbl)

-- END OF T-SQL Script


Edgardo Valdez
MCSD, MCDBA, MCSE, MCP+I
Database Administrator