DTS and ASP

I am developing a webapplication that should allow users to upload their database by means of a textfile and to integrate that data into an sql table. There will be some logic that deletes duplicates and more.

Trying to make this process as user friendly as possible I would like to allow the user to allocate his fields to the fields in my table.

Is there a way to utilize the transformations process of DTS on an ASP page?

Any ideas on how to achieve this would be highly appreciated.

eWW, SwitzerlandI have never really done anything like this before and I think you will have one he#% of a time making it work... and to be honest I don't think it's possible either. The only way I can think of that probably would work is to tell you users what their field-names should be and in what order. But then again, I'm no expert in this area...|||I wonder, would it be possible to do the actual transformation process outside SQL Server (e.g.) in ASP and to forward the resulting transformation definition by means of parameters to DTS?

You can probably tell, that I am not really familiar with the actual programming end of SQL and would appreciate any cookbook kind of advice as to how to go about and do that.

Best

eWW

DTS (Data Transformation Services)

Given:
I'm using a DTS Package to import data into a table. Each row from a text
file gets placed into a corresponding table.
What I need to do:
I'd like to modify the DTS Package to change the data saved to a table based
on values in the records from the text file. In otherwords,
if a value from the text file > 150 then set a value in the table to 1...
if a value from the text file < 150 then set a value in the table to 2.
Thank You
Hi Steve
You might be able to make this column a calculated one! e.g.
CREATE TABLE MyTest ( id int not null identity(1,1),
textval text,
size as DATALENGTH(textval) )
INSERT INTO MyTest ( Textval )
SELECT 'ABC'
UNION ALL SELECT 'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB BBBBBBBBBB'
UNION ALL SELECT
'CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC'
SELECT * FROM mytest
UPDATE MyTest SET Textval = 'A' WHERE id = 3
SELECT * FROM mytest
CREATE TABLE MyTest2 ( id int not null identity(1,1),
textval text,
size as CASE WHEN DATALENGTH(textval) > 10 THEN 1 ELSE 0 END )
INSERT INTO MyTest2 ( Textval )
SELECT 'ABC'
UNION ALL SELECT 'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB BBBBBBBBBB'
UNION ALL SELECT
'CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC'
SELECT * FROM mytest2
UPDATE MyTest2 SET Textval = 'A' WHERE id = 3
SELECT * FROM mytest2
Otherwise you can do this is an ActiveX transformation
http://www.sqldts.com/default.aspx?279,5
John
"SteveS" wrote:

> Given:
> I'm using a DTS Package to import data into a table. Each row from a text
> file gets placed into a corresponding table.
> What I need to do:
> I'd like to modify the DTS Package to change the data saved to a table based
> on values in the records from the text file. In otherwords,
> if a value from the text file > 150 then set a value in the table to 1...
> if a value from the text file < 150 then set a value in the table to 2.
> --
> Thank You
|||ActiveX transformation is the simple option for this one!!!
Thanks,
Sree
"SteveS" wrote:

> Given:
> I'm using a DTS Package to import data into a table. Each row from a text
> file gets placed into a corresponding table.
> What I need to do:
> I'd like to modify the DTS Package to change the data saved to a table based
> on values in the records from the text file. In otherwords,
> if a value from the text file > 150 then set a value in the table to 1...
> if a value from the text file < 150 then set a value in the table to 2.
> --
> Thank You

DTS (Data Transformation Services)

Given:
I'm using a DTS Package to import data into a table. Each row from a text
file gets placed into a corresponding table.
What I need to do:
I'd like to modify the DTS Package to change the data saved to a table based
on values in the records from the text file. In otherwords,
if a value from the text file > 150 then set a value in the table to 1...
if a value from the text file < 150 then set a value in the table to 2.
Thank YouHi Steve
You might be able to make this column a calculated one! e.g.
CREATE TABLE MyTest ( id int not null identity(1,1),
textval text,
size as DATALENGTH(textval) )
INSERT INTO MyTest ( Textval )
SELECT 'ABC'
UNION ALL SELECT 'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
BBBBBBBBBBBBBBBBBBB
B'
UNION ALL SELECT
'CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCC'
SELECT * FROM mytest
UPDATE MyTest SET Textval = 'A' WHERE id = 3
SELECT * FROM mytest
CREATE TABLE MyTest2 ( id int not null identity(1,1),
textval text,
size as CASE WHEN DATALENGTH(textval) > 10 THEN 1 ELSE 0 END )
INSERT INTO MyTest2 ( Textval )
SELECT 'ABC'
UNION ALL SELECT 'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
BBBBBBBBBBBBBBBBBBB
B'
UNION ALL SELECT
'CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCC'
SELECT * FROM mytest2
UPDATE MyTest2 SET Textval = 'A' WHERE id = 3
SELECT * FROM mytest2
Otherwise you can do this is an ActiveX transformation
http://www.sqldts.com/default.aspx?279,5
John
"SteveS" wrote:

> Given:
> I'm using a DTS Package to import data into a table. Each row from a text
> file gets placed into a corresponding table.
> What I need to do:
> I'd like to modify the DTS Package to change the data saved to a table bas
ed
> on values in the records from the text file. In otherwords,
> if a value from the text file > 150 then set a value in the table to 1...
> if a value from the text file < 150 then set a value in the table to 2.
> --
> Thank You|||ActiveX transformation is the simple option for this one!!!
Thanks,
Sree
"SteveS" wrote:

> Given:
> I'm using a DTS Package to import data into a table. Each row from a text
> file gets placed into a corresponding table.
> What I need to do:
> I'd like to modify the DTS Package to change the data saved to a table bas
ed
> on values in the records from the text file. In otherwords,
> if a value from the text file > 150 then set a value in the table to 1...
> if a value from the text file < 150 then set a value in the table to 2.
> --
> Thank You

DTS - import flat textfile into two separate tables

I have a report that's created each day as a flat textfile.

Because I came from the Access world, I created a macro that imports
it with a schema that gives meaningful names to the various columns,
and then uses a query to massage some of the data for me (deletes the
first blank row and does a couple of calculations)

Then I use DTS to import the Access query as a table.

the textfile has a column called "File_num", and among several others,
a column called "Serial_num". (the file numbers represent shipments,
and sometimes there are more than one serial number in the shipment,
etc., so there is a separate line for every serial number)

Naturally, I would like to split this info into two tables..one that
does not contain the serial numbers and has a primary key on the
"File_num" column, and another table that would contain just the
"File_num" and "Serial_num" columns. That way I could relate them
later...but most importantly, it will give me a table where I can use
the "File_num" as my primary key.

What would be the best way to import these two tables from one source
textfile? The other thing that gives me problems is that the text
file has no column names, and the first row is always blank.

I'm very new to SQL and DTS and would appreciate any direction.

Thanks,

Larry

- - - - - - - - - - - - - - - - - -
"Forget it, Jake. It's Chinatown.""Larry Rekow" <larry@.netgeexdotcom> wrote in message
news:3h1cj0976dtpkliussk5c1nr7jmubvt4rp@.4ax.com...
>I have a report that's created each day as a flat textfile.
> Because I came from the Access world, I created a macro that imports
> it with a schema that gives meaningful names to the various columns,
> and then uses a query to massage some of the data for me (deletes the
> first blank row and does a couple of calculations)
> Then I use DTS to import the Access query as a table.
> the textfile has a column called "File_num", and among several others,
> a column called "Serial_num". (the file numbers represent shipments,
> and sometimes there are more than one serial number in the shipment,
> etc., so there is a separate line for every serial number)
> Naturally, I would like to split this info into two tables..one that
> does not contain the serial numbers and has a primary key on the
> "File_num" column, and another table that would contain just the
> "File_num" and "Serial_num" columns. That way I could relate them
> later...but most importantly, it will give me a table where I can use
> the "File_num" as my primary key.
> What would be the best way to import these two tables from one source
> textfile? The other thing that gives me problems is that the text
> file has no column names, and the first row is always blank.
> I'm very new to SQL and DTS and would appreciate any direction.
> Thanks,
> Larry
> - - - - - - - - - - - - - - - - - -
> "Forget it, Jake. It's Chinatown."

A common technique is to import the source file directly into a staging
table, then transform the data using SQL. This is often easier than trying
to implement complex transformations in DTS itself. In your case, you could
do something like this:

insert into dbo.FileTable
(file_num, col1, col2, ...)
select file_num, col1, col2, ...
from dbo.StagingTable

insert into dbo.FileSerialTable
(file_num, serial_num)
select file_num, serial_num
from dbo.StagingTable

Simon