Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Mar 3, 2004

"File Watcher" for SQL Server - Page 2

By Muthusamy Anantha Kumar aka The MAK

DTS Package

Create the DTS package to import the above CSV file to SQL Server.

Make sure you enable the check box for "First row has column names"

In the SQL task, "Logtable," copy and paste the below SQL command.

In the ActiveX script "Rename File," copy and paste the below code. This will rename the file with the current month and day as suffix after the file has been imported.

ActiveX code

'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************
Function Main()
Set oFSO = CreateObject("Scripting.FileSystemObject")
sFilename = "c:\FileImport\FIle.csv"
oFilename ="C:\FileImport\FIle"+cstr(day(now()))+"-"+cstr(month(now()))+".csv"
ofso.MoveFile sFilename , oFilename
Main = DTSTaskExecResult_Success
End Function

Create SQL Job "TEST" to execute this package as shown below:

Create job "File Watcher" as shown below:

Create Job step "File Watcher":

In the command window, copy and paste the below code.

SQL Code

declare @Filestatus smallint
declare @rowstatus smallint
create table #tmp (FileExists smallint,FileisaDirectory smallint,ParentDirectoryExists smallint)
insert #tmp exec master.dbo.xp_fileexist 'c:\FileImport\File.csv'
set @Filestatus = (select FileExists from #tmp)
set @rowstatus = (select count(*) 
  from [Logtable] where convert(varchar(12),date, 112) =
  convert(varchar(12),getdate(), 112)  and status ='imported')
print @rowstatus 
print @Filestatus 
drop table #tmp
if @rowstatus <> 0 
begin
	insert into logtable(status) values ('Skipped DTS - Already Imported')
end
else
begin
	if @filestatus = 0
	begin 
		insert into logtable(status) values ('Skipped DTS - File not found')
	end
	else
	begin
		exec msdb.dbo.sp_start_job 'test'
	end
end


MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM