| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2004-06-09 : 07:11:33
|
| writes "Hi all,here is the question:i want to load a set of files from the data source to the sql server in a daily and monthly basis.Before the dts is started, i want to check on the date of all these files. For example, there is 3 files to be loaded, AAA, BBB and CCC. i want to check on the dates of these 3 files whether 3 of them are having the same date or not, if one of them mismatch, then the checking process will be run again until the 3 files' date is the same, then only generating the dts package.Can anyone give me a way or suggestion on how to do ??Thanks a lot...Rgds,Samantha" |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-09 : 12:31:59
|
| You can do this with VBScript.Tara |
 |
|
|
samanthalee6
Starting Member
7 Posts |
Posted - 2004-06-09 : 21:28:32
|
| Thanks Tara....But is it possible for u to show some sample script, bcos i not very familar in VBScript... |
|
|
|
kselvia
Aged Yak Warrior
526 Posts |
Posted - 2004-06-09 : 22:52:23
|
| [code]Create table ##filedetails(alternatename char(20),size char(20),creation_date char(20),creation_time char(20),last_written_date char(20),last_written_time char(20),last_accessed_date char(20),last_accessed_time char(20),attributes char(20))insert ##filedetails EXEC master.dbo.xp_getfiledetails 'c:\file1.txt'insert ##filedetails EXEC master.dbo.xp_getfiledetails 'c:\file2.txt'insert ##filedetails EXEC master.dbo.xp_getfiledetails 'c:\file3.txt'if select count(distinct last_written_date, last_written_time) from ##filedetails > 1 file dates are different[/code] |
|
|
|
samanthalee6
Starting Member
7 Posts |
Posted - 2004-06-11 : 02:45:35
|
| Thanks kselvia...May i know where to include the script above?? inside the dts package using 'Execute SQL Task'? |
|
|
|
kselvia
Aged Yak Warrior
526 Posts |
Posted - 2004-06-11 : 10:01:32
|
| Ah, my apologies I did not notice this was a DTS specific question. You can add it as an Execute SQL task but I suspect you don't need DTS at all. How are you loading the files? If you can show us the file and table formats we can probably show you how to do it in T-SQL and schedule a SQL Agent job to do it. |
|
|
|
samanthalee6
Starting Member
7 Posts |
Posted - 2004-06-13 : 22:07:07
|
| ya... this is needed to be done during the dts process...Example:File1 23/2/2004File2 23/2/2004File3 23/2/2004the 3 files above is provided daily and the source file format is text file, so when the files ready, the job scheduled will be started everyday. Since this is data loading, so its better to include the check dates script in the dts.The dts will work like this, first before data loading, the package will check on the dates of the source file , whether 3 of them have the same dates or not, if yes, then the data loading process will be executed, else it will not execute the dts process, but will keep on check the dates after sometimes. This is wat my dts package need to be done, hope that u can give me so idea on how to do this...Thanks in advance |
|
|
|
samanthalee6
Starting Member
7 Posts |
Posted - 2004-06-15 : 04:11:50
|
| the file format is fixed-length text files. Do u have any sample script tt can provide for me to review??? thanks ... reply as soon as possible id required..Thank you very much... |
|
|
|
kselvia
Aged Yak Warrior
526 Posts |
Posted - 2004-06-16 : 00:52:37
|
| Remove the schedule for your current DTS package so it will not automatically run.Create a new T-SQL agent job. Schedule it to run as often as you want to check the file dates.Use the following code for the script:Create table ##filedetails(alternatename char(20),size char(20),creation_date char(20),creation_time char(20),last_written_date char(20),last_written_time char(20),last_accessed_date char(20),last_accessed_time char(20),attributes char(20))insert ##filedetails EXEC master.dbo.xp_getfiledetails 'c:\file1.txt'insert ##filedetails EXEC master.dbo.xp_getfiledetails 'c:\file2.txt'insert ##filedetails EXEC master.dbo.xp_getfiledetails 'c:\file3.txt'if (select count(distinct last_written_date, last_written_time) from ##filedetails) > 1 exec msdb..sp_start_job @job_name='StartDTS_yourdts'Replace StartDTS_yourdts with the name of the task you had that you removed the schedule for.This will cause your DTS job to run only when the file dates are different.Hope this helps! |
|
|
|
samanthalee6
Starting Member
7 Posts |
Posted - 2004-06-16 : 22:06:00
|
| i tried out but still cannot work, tried to create job in SQL Server Agent, and defined the T-SQL tt u provided in the job's step, but when execute the job, its keep on fails....Do u have any other advice that are using script other than T-SQL?BTW, the DTS will be execute only when the dates for all 3 files are the same, if the dates is not match , then the checking process will be keep on recurring...Anyway, Thanks a lot:) |
|
|
|
|
Check date