|
|||||||||
|
|||||||||
|
|||||||||
 |
|||||||||
| |
||
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
|
|||||||||
| |
||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
ASP Free Forums Sponsor:
|
|
|
|
#1
April 13th, 2004, 07:30 AM
|
|||
|
|||
|
DTS: Output file, except when no records
Hi,
I have a DTS package which exports the records in a view into a CSV file. However, when the view returns no records, the DTS package outputs a blank csv file. There must be a way of preventing this, i only want the file to be created if there are records in the view. Any ideas??
|
|
#2
April 13th, 2004, 09:19 AM
|
||||
|
||||
|
you would need to write a script that checks if records were returned from the view, if there are records then run the DTS package.
|
|
#3
April 13th, 2004, 11:25 AM
|
|||
|
|||
|
Thanks for the reply.
 I did try that, but apparently VB script is different from VB?? As my code that runs fine in VB just returns a multitude of errors when i try to enter it into an Active X Script Task. The views name is '01_ReceivingsNotSent', any chance you could suggest what the code would be, or point me in the direction of a good VB scriping site, it cant be THAT different from VB.
|
|
#4
April 13th, 2004, 03:17 PM
|
||||
|
||||
|
You can do it something like this
Code:
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Your ConnectionString"
Set rs = Conn.Execute("SELECT COUNT(*) As 'count' FROM ViewName")
if rs.count > 0 Then
there are records, so run the dts package
const DTSSQLStgFlag_Default = 0
const DTSStepExecResult_Failure = 1
Set oPkg = Server.CreateObject("DTS.Package")
blnSucceeded = True
LoadFromSQLServer ServerName, Username, Password, Flags, PackagePassword, PackageGUID, PackageVersionGUID, Package Name, PersistsHost
oPkg.LoadFromSQLServer "(local)", "sa", "", DTSSQLStgFlag_Default, "", "", "", "DTSPackageName", ""
oPkg.Execute
for each oStep in oPkg.Steps
if oStep.ExecutionResult = DTSStepExecResult_Failure then
strResult = strResult & "Package " & oStep.Name & " failed.<br>"
blnSucceeded = false
else
strResult = strResult & "Package " & oStep.Name & " succeeded.<br>"
end if
next
if blnSucceeded then
Response.Write "<h1>Package Succeeded</h1>"
else
Response.Write "<h1>Package Failed</h1>"
end if
Response.Write strResult
|
|
#5
April 14th, 2004, 05:39 AM
|
|||
|
|||
|
Thats fantastic, thanks soooooo much
Problem is now sorted.
|
|
| Viewing: ASP Free Forums > Database > Microsoft SQL Server > DTS: Output file, except when no records |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
|
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
