Where Do I Start???

[dzdncnfsd]

Hi. I need to know where to start. I need to create a job using SQL Server 2000 that will import data from a text file and insert it into a pre-existing table. The table will get truncated before the import is done. A new file is created only when a batch cycle is run on the mainframe. Normally, Monday - Friday, but occasionally we also get a Sat file. The file that I need to read from has the cycle date in the filename; MM041701.txt, for example. The receiving table has two extra fields that are not part of the text file - a timestamp that needs to be the same date as the filename, and a concatenated field from 3 others in the text file. What I would like to do is schedule the job to run every day. I want it to look for the most recent file, check to see if that file's date is > than the timestamp in the table, and if so, truncate the table and import the file. If it is <=, the program should end. I know how to create a basic DTS import package, but I need help with how to tell it what to look for. (In other words, I need help with the hard part.) Do I add an ActiveX control to the package and create ADO connections, etc, or is there a better way?? BTW, the timestamp is necessary because the contents of this table gets appended to the &quot;master&quot; table. Also, what if the program errored out without importing a legitimate file? I would be missing that day from the master.
Thanks for any guidence you can provide. :
Gladys

Gladys Clemmer
gladys.clemmer@fifsg.com

 

[tlbroadbent]

I suggest creating a stored procedure and using Bulk Insert rather than DTS to perform task. You could schedule a job to run the procedure.

Here is the basic code of the stored procedure. Hopefully, the comments will help you to understand it. If not, please let me know.

Declare @date char(6), @file varchar(12), @retcode int, @filepath varchar(60)

/* Format the date from current date - change as needed to for your process */
Select @date=substring(convert(char(6),getdate(),12),3,4) + substring(convert(char(6),getdate(),12),1,2)

/* Create the file name from the date */
Select @file='MM' + @date + '.txt'
Select @filepath='fullpath' + @file

/* Check if the file exists */
Exec @retcode = sp_MSexists_file 'fullpath', @file

If @retcode = 1
Begin

/* File Exists - begin processing import */

/* Create a temporary table for the import */

Create #temptbl (define columns)

/* use Bulk Insert to import data to temp table */
Bulk Insert #temptbl
From @filepath
With (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n'))

/* Truncate transaction table. You may want to test if the import succeeded first. */
Truncate table MyTbl

/* Insert records from temp table into transaction table. */
Insert MyTbl (col1, col2, ..., ConcatCol, TimeStampCol)
Select col1, col2, ..., col1+col2+col3, @date
From #temptbl

Drop Table #temptbl

End

Else

Print 'File does not Exist'

 

[dzdncnfsd]

Thanks, tlbroadbent, for the help. Your code looks easy to understand and I will be trying it out shortly. One question, though. In yourline: Insert MyTbl (col1, col2, ..., ConcatCol, TimeStampCol), do I have to list each field, or is there a shortcut? There are 226 fields that need to be imported. If I do have to type it out, I can, I hopefully will only need to do it once.
Thanks again.
Gladys


Gladys Clemmer
gladys.clemmer@fifsg.com

 

[tlbroadbent]

You don't need to type the column names on the Insert if the columns on Mytbl are in the same order as the columns in the select statement.

Instead of

Insert MyTbl (col1, col2, ..., ConcatCol, TimeStampCol)
Select col1, col2, ..., col1+col2+col3, @date
From #temptbl

you can write

Insert MyTbl
Select *, col1+col2+col3, @date
From #temptbl

In the 2nd query, I assume that the first 224 columns on MyTbl match the columns on #temptbl and the concatenated colimn and date column are at the end of the row.

Hope this answers your question.

 

[dzdncnfsd]

Did you create sp_MSexists_file, or should it be available to everyone? I got an error message &quot;Could not find stored procedure 'sp_MSexists_file'.&quot; in Query Analyzer. I tried BOL and the Microsoft web site, but could not find anything.
Thanks.

Gladys Clemmer
gladys.clemmer@fifsg.com

 

[tlbroadbent]

The procedure is from Microsoft and should exist on your master database. You must be a system adminstrator or have the system adminstrator grant permission to execute this undocumented stored procedure.

 

[dzdncnfsd]

I ran Exec sp_stored_procedures against the master DB and it is not in the list. Can I get it from somewhere else?

Gladys Clemmer
gladys.clemmer@fifsg.com

 

[dzdncnfsd]

I found sp_MSexists_file on the SQL 7.0 master. Can I copy it to the 2000 master? Or is it no longer supported? I can't find anything about it on the Microsoft site.

Gladys Clemmer
gladys.clemmer@fifsg.com

 

[tlbroadbent]

The procedure will not display when you execute sp_stored_procedures unless you have permission to run it. Are you a sys admin? If not, ask you sys admin to give you permission.

 

[tlbroadbent]

The proc is unsupported and undocumented in SqL 7 and SQL 2000. It should already exist in SQL 2000 master.

 

[dzdncnfsd]

Ok, I added my nt login name in SQL and made myself a member of System Administrators, even though I was already a member of the Builtin\Administrators group. And maybe I did a dumb thing, but I copied the SQL7.0 sp over to the 2000 server. Anyway, everything seems to be working except that the program cannot find the file. So can you please help me out one more time and tell me how I should write out the path? I am trying to create the path the way the SQL server would see it. This is the code:

/* Create the file name from the date */
Select @filename='mh' + @date + '.txt'
Select @filepath = '\\ftp\d$\ftp\acs\data-in\' + @filename

/* Check if the file exists */
Exec @retcode = sp_MSexists_file '\\ftp\d$\ftp\acs\data-in\', @filename 

If @retcode = 1
 Begin  (etc)

The error message is : &quot;Could not bulk insert. File @filepath does not exist.&quot;

Thanks again.


Gladys Clemmer
gladys.clemmer@fifsg.com

 

[tlbroadbent]

I've tried it several different ways on one of my SQL 7 server and it returns the expected result. Possible causes of the failure:

1- You login doesn't have sufficient permissions on the directory where the file exists. (Not likely if you are an NT Adminstrator.)
2- The path is incorrect. (Not very likely.)
3- THe SQL 7 version will not run properly on SQL 2000. (More likely.)

I don't have SQL 2000 available to test. And I don't even know if the SP exists in SQL 2000. I was going by something I read in a book.

Terry

http://members.home.net/tlbroadbent/index.htm

 

[tlbroadbent]

Question: Do you login with NT or SQL authentication? Terry

http://members.home.net/tlbroadbent/index.htm

 

[dzdncnfsd]

I use SQL authentication (sa) for EM and Query Analyzer. I have a feeling you are right about #3, that it doesn't exist/work in 2000. I am going to see if I can rewrite it to work.
Thank you so much for all the time and trouble you have gone to to help me out. Even though I'm still not done with this, you have taught me some new stuff and I am grateful for that.
Thanks,
Gladys

Gladys Clemmer
gladys.clemmer@fifsg.com

 

[tlbroadbent]

If you use SQL Authentication, you may not have permissions to see the file from a SQL query. Test if you can see the file with the following SQL code.

exec xp_cmdshell 'dir \\ftp\d$\ftp\acs\data-in\filename.txt'

If you can't see the file using xp_cmdshell, then try logging in with NT Authentication. Terry

http://members.home.net/tlbroadbent/index.htm

 

[dzdncnfsd]

That was encouraging. That command returned info about the file as long as I was running it from the master db. But when I tried to incorporate it into the stored procedure you helped me create, I use another db, and it can't find xp_cmdshell. I am going to lunch,and will continue to play with it when I get back.
Thanks,
Gladys

Gladys Clemmer
gladys.clemmer@fifsg.com

 

[tlbroadbent]

You'll probably have to run the procedure from master. You can try

exec master..xp_cmdshell 'dir ...'
and possibly

exec master..sp_msexists_file 'path', 'file' Terry

http://members.home.net/tlbroadbent/index.htm