Loading a Text File Through DTS via ASP Page

Loading a Text File Through DTS via ASP Page

Post by KenB » Wed, 11 Jun 2003 01:01:35



I have recently begun a project where I will have to create a DTS
package where several users will put text files in a directory on a
server and I need to have an ASP page where the users will go and insert
a filename, month and year and hit a button and they will all get passed
to the DTS package as global variables and run a text file load to a
temp SQL table.

When the file is in the table I need to validate the structure against
existing tables in the database. If some of the combinations are not
valid I need to return an error message saying which ones are invalid
and exit.

If the accounts are all valid I need to split the contents of the table
via a lookup in a SQL table and output to 2 different text files. When
this is done I need to kick off a vbs file and pass in the filename,
month and year.

If anyone has done something like this I would love to hear from him or
her. If anyone has any design suggestions that may make this easier as
well I would love to hear.

Thanks,

KenB

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

Loading a Text File Through DTS via ASP Page

Post by JFB » Wed, 11 Jun 2003 04:53:51


Hi Ken,
Here is something to start with
http://www.sqldts.com/default.aspx?6,104,207,7,1
JFB


Quote:> I have recently begun a project where I will have to create a DTS
> package where several users will put text files in a directory on a
> server and I need to have an ASP page where the users will go and insert
> a filename, month and year and hit a button and they will all get passed
> to the DTS package as global variables and run a text file load to a
> temp SQL table.

> When the file is in the table I need to validate the structure against
> existing tables in the database. If some of the combinations are not
> valid I need to return an error message saying which ones are invalid
> and exit.

> If the accounts are all valid I need to split the contents of the table
> via a lookup in a SQL table and output to 2 different text files. When
> this is done I need to kick off a vbs file and pass in the filename,
> month and year.

> If anyone has done something like this I would love to hear from him or
> her. If anyone has any design suggestions that may make this easier as
> well I would love to hear.

> Thanks,

> KenB

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!


 
 
 

Loading a Text File Through DTS via ASP Page

Post by Nigel Rivet » Wed, 11 Jun 2003 06:00:40


Have you been told that you have to use dts for this?
Sounds like it would be much simpler with bcp.

Nigel Rivett (SQL Server MVP)
www.nigelrivett.net

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

Loading a Text File Through DTS via ASP Page

Post by Allan Mitchel » Wed, 11 Jun 2003 16:04:51


You could probably take DTS out of the loop here and use BULK INSERT.

--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


Quote:> I have recently begun a project where I will have to create a DTS
> package where several users will put text files in a directory on a
> server and I need to have an ASP page where the users will go and insert
> a filename, month and year and hit a button and they will all get passed
> to the DTS package as global variables and run a text file load to a
> temp SQL table.

> When the file is in the table I need to validate the structure against
> existing tables in the database. If some of the combinations are not
> valid I need to return an error message saying which ones are invalid
> and exit.

> If the accounts are all valid I need to split the contents of the table
> via a lookup in a SQL table and output to 2 different text files. When
> this is done I need to kick off a vbs file and pass in the filename,
> month and year.

> If anyone has done something like this I would love to hear from him or
> her. If anyone has any design suggestions that may make this easier as
> well I would love to hear.

> Thanks,

> KenB

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

 
 
 

Loading a Text File Through DTS via ASP Page

Post by KenB » Wed, 11 Jun 2003 23:45:02


How could I use Bulk Insert to load a text file to a temp SQL table,
validate that what was in the file matches stuff in the database, then
break the load out into 2 text files based on a join in a SQL mapping
table?

When I have the 2 text files I then need to kick off a 3rd party data
loader though a vbs script and specify the datafile and a configuration
file.

Bulk Insert would get the file to the temp table but I don't see it
getting beyond that. Can it?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

Loading a Text File Through DTS via ASP Page

Post by Allan Mitchel » Thu, 12 Jun 2003 01:27:35


I personally never try to do too much with a text file.  I load it into a
table and then use SQL Server TSQL to scrub it.

So I would probably

Step 1

BULK INSERT the file into SQL Server
Using TSQL you can then scrub the data
With the now cleaned data you can then pump using BCP or DTS if you like to
your two text files.
You can then call the external loader through xp_cmdshell.

Food for thought

--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


Quote:> How could I use Bulk Insert to load a text file to a temp SQL table,
> validate that what was in the file matches stuff in the database, then
> break the load out into 2 text files based on a join in a SQL mapping
> table?

> When I have the 2 text files I then need to kick off a 3rd party data
> loader though a vbs script and specify the datafile and a configuration
> file.

> Bulk Insert would get the file to the temp table but I don't see it
> getting beyond that. Can it?

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

 
 
 

Loading a Text File Through DTS via ASP Page

Post by KenB » Fri, 13 Jun 2003 03:09:40


I tried doing a Bulk Insert as part of a DTS package so I could delete
everything in the table before I loaded but I get an error when I try to
setup the bulk insert task which I think is related to insufficient
permissions. I could not get sa so I got Bulk Insert Administrators.
This does not seem to be enough. I get an error EXECUTE permission
denied on object 'xp_availablemedia',database 'master', owner 'dbo'.

To get around this I tried to type in a UNC path and I then save and try
to run and I get an error: COuld not bulk inser because file "\\ then
the UNC path to file" could not be opened. Operating system error code
5(Access is denied.).

Any suggestions on how to resolve this? Should I just switch to try an
do an import text file task and not a bulk insert?

Thanks.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

Loading a Text File Through DTS via ASP Page

Post by Allan Mitchel » Fri, 13 Jun 2003 15:33:18


from BOL

Permissions
Only members of the sysadmin and bulkadmin fixed server roles can execute
BULK INSERT

The xp_availablemedia stored proc lists the available storge devices on the
computer. Can you check you have space on your drives

MASTER..xp_fixeddrives

The BI task is executed relative to the server and is one of the few to be.
Are you running this interactively or scheduled?

Can you do this whilst sat at the server ?

--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


Quote:> I tried doing a Bulk Insert as part of a DTS package so I could delete
> everything in the table before I loaded but I get an error when I try to
> setup the bulk insert task which I think is related to insufficient
> permissions. I could not get sa so I got Bulk Insert Administrators.
> This does not seem to be enough. I get an error EXECUTE permission
> denied on object 'xp_availablemedia',database 'master', owner 'dbo'.

> To get around this I tried to type in a UNC path and I then save and try
> to run and I get an error: COuld not bulk inser because file "\\ then
> the UNC path to file" could not be opened. Operating system error code
> 5(Access is denied.).

> Any suggestions on how to resolve this? Should I just switch to try an
> do an import text file task and not a bulk insert?

> Thanks.

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

 
 
 

Loading a Text File Through DTS via ASP Page

Post by KenB » Sat, 14 Jun 2003 04:09:29


I was having trouble using the bulk insert so I am trying to use a text
file source to load to a sql table and I want to be able to dynamicly
pass the text file name from an asp page that is calling the DTS
package.

I setup a dynamic task that made the filename a global variable but I
think I am hoing in the wrong direction. What is the best way to pass a
parameter to a DTS package so it will be dynamic? I need to be able to
pass it the filename.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

Loading a Text File Through DTS via ASP Page

Post by Allan Mitchel » Sat, 14 Jun 2003 15:35:33


You can do it a few ways

1.  Use a dynamic properties task to read from a location (INI file, DB
table) and assign this to the DataSource property of the text file
connection.
2.  Use a Global Variable and assign this in an Active Script task to the
DataSource property of your text file connection.

In ASP for solution number 2 you could then call DTSRUN with the /A argument
to send the value to DTS.

If you are using the object model then you can change the connection
properties in that directly before you call .Execute on the package object

--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


Quote:> I was having trouble using the bulk insert so I am trying to use a text
> file source to load to a sql table and I want to be able to dynamicly
> pass the text file name from an asp page that is calling the DTS
> package.

> I setup a dynamic task that made the filename a global variable but I
> think I am hoing in the wrong direction. What is the best way to pass a
> parameter to a DTS package so it will be dynamic? I need to be able to
> pass it the filename.

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

 
 
 

Loading a Text File Through DTS via ASP Page

Post by KenB » Sat, 14 Jun 2003 23:10:20


I was trying this based on an example I found:

Set oPkg = Server.CreateObject("DTS.Package")

'Code to change text file source                
sFilename = Request.Form("txtFileName")
sFilename = oPkg.GlobalVariables("FilePath").Value & sFilename

Set oConn = oPkg.GlobalVariables.Parent.Connections("Data Load File")
oConn.DataSource = sFilename
Set oConn = Nothing

oPkg.LoadFromSQLServer sServer, sUID, sPWD, iSecurity , sPkgPWD, "", "",
sPkgName

' Execute the Package
oPkg.Execute

It comes up with "Microsoft Data Transformation Services (DTS) Package
error '800403eb'
Connection 'Data Load File' was not found." I also tried it using ("Text
File (Source)") but no luck either.

I made a global variable called FilePath and used a dynamic property
task and assigned the gloabal variable as the DataSource for the Data
Load File connection.

Any suggestions on what I am doing wrong?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

Loading a Text File Through DTS via ASP Page

Post by Allan Mitchel » Sat, 14 Jun 2003 23:28:55


Have a look in the package "Disconnected Edit" for the name of the
connection

I would have used

Set oConn = oPkg.Connections("Data Load File")

--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


Quote:> I was trying this based on an example I found:

> Set oPkg = Server.CreateObject("DTS.Package")

> 'Code to change text file source
> sFilename = Request.Form("txtFileName")
> sFilename = oPkg.GlobalVariables("FilePath").Value & sFilename

> Set oConn = oPkg.GlobalVariables.Parent.Connections("Data Load File")
> oConn.DataSource = sFilename
> Set oConn = Nothing

> oPkg.LoadFromSQLServer sServer, sUID, sPWD, iSecurity , sPkgPWD, "", "",
> sPkgName

> ' Execute the Package
> oPkg.Execute

> It comes up with "Microsoft Data Transformation Services (DTS) Package
> error '800403eb'
> Connection 'Data Load File' was not found." I also tried it using ("Text
> File (Source)") but no luck either.

> I made a global variable called FilePath and used a dynamic property
> task and assigned the gloabal variable as the DataSource for the Data
> Load File connection.

> Any suggestions on what I am doing wrong?

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

 
 
 

1. Importing a Text File via DTS and adding the File Name as a column via DTS

Hello - I did a brief search on this subject and did not find anything in
this newsgroup.

Here's my problem:

I have a series of text files which all have the same format and structure.
They are files my company receives on a daily basis and uploads for use on
that day. The files do not feature a date field but the date they are for is
identifiable via the file name, e.g., EXT30_20010518.txt would be today's.

As is typical we now need to do historical analyses using these files but
since they do not feature a date column any uploaded files would be
meaningless in their current format. I need to add a date field and extract
the date from the file name. To your knowledge, can I do this via a DTS
transformation? (Even if I could simply get the file name in a column I
could do parsing in SQL).

Any tips, help or resources would be greatly appreciated. Thanks for your
time,

Baris

2. MI-Madison Heights- Programmer ( 277250 )

3. Executing DTS via ASP & web page

4. How to diplay Access forms in VB 4.0 ???

5. Activate DTS Package from Web page via asp.net (VB)

6. 1 small Access problem.

7. Problem creating a counter for page views via stored procedures called from an asp page

8. North Carolina - Oracle DBA

9. trigger asp file via DTS

10. Importing a text file into different tables in a Access via ASP

11. Loading a ascii text file into ms-access via VB

12. how to run a dts from a web page like an asp.net page

13. Problems with load text file in DTS