Executable or Way for User to Launch an SQL Package?

I have a sitation where I need a dts package or similar to run at a user initiated time. I do not want to give the user access to the server. Any ideas on how one goes about something like this?Howdy

If its through a web or VB type app, let the web app execute a stored procedure called by the app.

Cheers,

SG.|||Hey,
from a vb app or vb script you can do this function:

Public Sub ExecuteEDIPackage(FileName As Variant)

Dim sServer As String
Dim sUsername As String
Dim sPassword As String
Dim sPackageName As String
Dim lErr As Long
Dim sSource As String
Dim sDesc As String

Set oPKG = New DTS.Package

' Set Parameter Values
sPackageName = "EDIPackage"

' Load Package
oPKG.LoadFromSQLServer DataSource, UserName, Password, _
DTSSQLStgFlag_Default, , , , sPackageName

' Set Exec on Main Thread
For Each oStep In oPKG.Steps
oStep.ExecuteInMainThread = True
Next

' Execute
oPKG.Execute

' Get Status and Error Message
For Each oStep In oPKG.Steps
If oStep.ExecutionResult = DTSStepExecResult_Failure Then
oStep.GetExecutionErrorInfo lErr, sSource, sDesc
sMessage = sMessage & "Step """ & oStep.Name & _
""" Failed" & vbCrLf & _
vbTab & "Error: " & lErr & vbCrLf & _
vbTab & "Source: " & sSource & vbCrLf & _
vbTab & "Description: " & sDesc & vbCrLf & vbCrLf
Else
sMessage = sMessage & "Step """ & oStep.Name & _
""" Succeeded" & vbCrLf & vbCrLf
End If
Next

oPKG.UnInitialize

Set oStep = Nothing
Set oPKG = Nothing

End Sub

EXCEPTION_ACCESS_VIOLATION

hello!

i am trying to import a text file using dts into a table created by the
package. everything checks out until it is run. the table is created
but then i get an error:
"need to run the object to perform the operation. Provider generated
code execution exception EXCEPTION_ACCESS_VIOLATION"

any ideas?
this is a development box. maybe the server cant handle it?
thanks in advance!
Tom<tomcaml@.yahoo.com> wrote in message
news:1106153734.711603.163950@.z14g2000cwz.googlegr oups.com...
> hello!
> i am trying to import a text file using dts into a table created by the
> package. everything checks out until it is run. the table is created
> but then i get an error:
> "need to run the object to perform the operation. Provider generated
> code execution exception EXCEPTION_ACCESS_VIOLATION"
>
> any ideas?
> this is a development box. maybe the server cant handle it?
> thanks in advance!
> Tom

You don't mention your version of MSSQL, but here are a couple of related KB
articles:

http://support.microsoft.com/kb/268413/EN-US/
http://support.microsoft.com/kb/271889/EN-US/

Another possibility is to try setting the task to execute on the main
package thread:

http://www.sqldts.com/default.aspx?232

If that doesn't help, you might want to post to
microsoft.public.sqlserver.dts, with more details of your environment and
exactly what task is failing.

Simon

excel problem

hi I have an excel sheet with no header row.
so I used DTS package to transfer data to the table,
however, transform data task think my first row is the header column,
as the result, I can never transfer my first row data to the table.
is there a way to do this? maybe I have to use activex?
regardsI'm trying to leave work, but
HDR=Yes;IMEX=1
is something I remember about hte header row.
"Kevin" <Kevin@.discussions.microsoft.com> wrote in message
news:AAAA9CB5-206D-4721-B033-09EBD825100F@.microsoft.com...
> hi I have an excel sheet with no header row.
> so I used DTS package to transfer data to the table,
> however, transform data task think my first row is the header column,
> as the result, I can never transfer my first row data to the table.
> is there a way to do this? maybe I have to use activex?
> regards
>|||where do I put this code?
I tried to put the code in activex,
nothing happen. still same problem..
thanks for helping
"sloan" wrote:

> I'm trying to leave work, but
>
> HDR=Yes;IMEX=1
> is something I remember about hte header row.
>
>
>
> "Kevin" <Kevin@.discussions.microsoft.com> wrote in message
> news:AAAA9CB5-206D-4721-B033-09EBD825100F@.microsoft.com...
>
>|||Refer this link
http://www.sqldts.com/default.aspx?254
Regards
Sudarshan Selvaraja
"Kevin" wrote:
> where do I put this code?
> I tried to put the code in activex,
> nothing happen. still same problem..
> thanks for helping
>
> "sloan" wrote:
>

Excel Locked File DTS Woes

Hello All,

Hopefully one of you gurus can offer a solution to this problem:

First off I do not have the ability to use SQLMail - so that is not an option.

I have a DTS package that performs the following steps in order to populate an excel sheet with some data and email it off to a coworker:

1) FTP Task to copy excel sheet template from one folder to another on same machine

2) Data pump between SQL server connection (local) and copied excel sheet.

3) SQL script task which truncates a table on the sql server

4) activeX script task which contains the following code that uses CDO to attach the excel sheet that was just populated to an outgoing email:

Function Main()

Dim iMsg

set iMsg = CreateObject("CDO.Message")

Dim objMail

Set objMail = CreateObject("CDO.Message")

objMail.From = "from@.from.com"

objMail.To = "to@.to.com"

objMail.AddAttachment("F:\copied\excelsheet.xls")

objMail.Subject="subject"

objMail.HTMLBody = "Body"

objMail.Send

Set objMail = nothing

Main = DTSTaskExecResult_Success

End Function

The result of DTS execution is a failure on the activeX step:

Error Description: The process cannot access the file because it is being used by another process.

I assume this means Excel still has its grubby paws on the file - but I've tried alleviating this by (1) using a waitfor delay between the excel connection and the activex script task and even by (2) using the run package task to run a separate package that only has the activex mailsender script in it (thinking the parent package would terminate along with the excel process before running the next package - maybe I'm wrong here?).

Can anyone suggest a solution to this!?

Thanks!
KSeriously? *Nobody*has any suggestions on this? I can't believe this has everyone stumped..|||take the line out of active X

objMail.AddAttachment("F:\copied\excelsheet.xls")

are you still getting error? This will tell you if you have an error with the EXCEL file?

If you still get the error there is adifferent problem and will proceed from there.

do you have master..dba_sendmail|||rbackmann,

Thanks for the reply. I know that it is the file attachment method that is causing the error - I'm just not sure how to resolve it.

I have the default MSDN stored proc for sending mail (I called it CDONTS_SendMailAttach so I would remember that it uses CDONTS). I tried doing the above with that instead and it failed with no real error message. I assume if I could get the error from somewhere it would be the same "file is already in use" kind of thing.

I figured doing it this way was better as the errors are a little more verbose and accessible.

I'm still stumped :/

Regards,
K|||i can guess another reason. is your script trying to attach the file while you data pump routine has not yet completed the export? in DTS things run parallel and to put them in sequence you need to add work-flow links between them. try it if you are not already having one in place.|||Upalsen,

Thank you for your reply however I do have workflow links between each task, specifically on success ones. It almost seems like they're being ignored. I have another package which does something similar (though less complex) and it works without error.

Another thing I should mention - if I run each task by itself (by using execute step) the package runs fine - it's only when the package runs each step itself that the error occurs. I'm almost positive it has something to do with the excel interop that occurs when you initiate a connection to an excel workbook.|||I had similar problems when dealing with an instance of EXCEL using VB script connecting trhourg Access. I had to to include a second close of the excwl app.

' save and close workbook -- needs to be done from xlx so Excel knows it is saved
xlx.ActiveWorkbook.Close False
xlx.ActiveWorkbook.Close False
' close Excel object
xlx.Quit

I don't know if this will help but....