Help calling a DTS from ASP.NET

[bigfoot]

I created a simple DTS. A table that get's transferred to a text file on the PC.

When I call it vrom SQL, it works, but not when I call it from ASP.NET. It gives an error telling me I don't have permissions to write to the drive. I am passing a user and password to it.

Anyone doing this?

I am using a connection, and a textfile out. There is no place to set permissions on the textfile out.

I tried calling a stored procedure from asp.net that calls the dts, and it works fine, so why does it not work right from asp.net??? I am passing the same information.

 

[TitleistDBA]

Does the user name and password you are passing have rights to destination you are writting to?

http://www.acushnetcompany.com

 

[bigfoot]

Yup. Tried that first.

I can get it to write to another maching using \\machname\c

Now I'm trying to move the .net thingy up to the website to test it, and it's giving me errors saying the com control that is PART of Sql Server is missing.

I found a way to call it using a stored procedure. It seems to work fine. Himmm

 

[wbeetge]

Bigfoot.

Have you been able to execute a DTS from ASP.NET as you would from VB ? (Not with a Stored Proc!) I need to be able to open the DTS package object and change the source file (text) and also set up global variables depending on the name of the new source file, from within ASP.NET

If you have, please let me know, I have been searching the web for a solution with no luck.

Thanks

Graham

 

[bigfoot]

Try this: This is some of what I have. I found this all over the place.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q242391

And look in the SQL books online. Look for how to do it in ActiveX Script Tasks, and then copy it to .NET

I created a DTS package called ExportItemMaster, which had some global variables in it. I tries different ways of calling the package, so I am sorry for the bad code. This was my test project.

CALLING IT FROM A STORRED PROCEDURE WORKED TOO.
***********************************************************
Here is some of the code:

Imports DTS
Imports System.Data.SQLClient

Public Class WebForm1
Inherits System.Web.UI.Page

#Region " Web Form Designer Generated Code "

'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

End Sub
Protected WithEvents btnCallDTS As System.Web.UI.WebControls.Button
Protected WithEvents btnCallSP As System.Web.UI.WebControls.Button
Protected WithEvents DataGrid1 As System.Web.UI.WebControls.DataGrid

'NOTE: The following placeholder declaration is required by the Web Form Designer.
'Do not delete or move it.
Private designerPlaceholderDeclaration As System.Object

Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub

#End Region

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
End Sub

Private Sub btnCallDTS_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCallDTS.Click

'**** THIS WAS ONE WAY I TRIED ****
Dim oPkg As New DTS.Package

'Get the package
oPkg.LoadFromSQLServer(&quot;mysqlserver&quot;, &quot;myname&quot;, &quot;mypass&quot;, _
DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, , , , &quot;ExportItemMaster&quot


oPkg.Execute()
oPkg.UnInitialize()

oPkg = Nothing

End Sub


Private Sub btnCallSP_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCallSP.Click
'**** THIS WAS ANOTHER WAY I TRIED WITH A STORED PROCEDURE ****

Dim sConnectionString As String = _
&quot;server=myserver;uid=myname;pwd=mypass;database=mydatabase&quot;


Dim cnArett As New SqlConnection(sConnectionString)
Dim cmdRunDTS As New SqlCommand(&quot;sp_RunPackage&quot;, cnArett)
cmdRunDTS.CommandType = CommandType.StoredProcedure
Dim sSQL As String

sSQL = &quot;Select * From ItemMaster Where ItemNumber = 'A05 41056W'&quot;


' Set up parameter for stored procedure
Dim prmPackageName As New SqlParameter
Dim prmUser As New SqlParameter
Dim prmPassword As New SqlParameter
Dim prmGFileName As New SqlParameter
Dim prmSQL As New SqlParameter

'Get Return Value
Dim prmretvalue As New SqlParameter
prmretvalue.ParameterName = &quot;retvalue&quot;
prmretvalue.Direction = ParameterDirection.ReturnValue
cmdRunDTS.Parameters.Add(prmretvalue)

'Pass DTS package name
prmPackageName.ParameterName = &quot;@Package&quot;
prmPackageName.SqlDbType = SqlDbType.VarChar
prmPackageName.Size = 64
prmPackageName.Value = &quot;ExportItemMaster&quot;
cmdRunDTS.Parameters.Add(prmPackageName)

'Pass User
prmUser.ParameterName = &quot;@User&quot;
prmUser.SqlDbType = SqlDbType.VarChar
prmUser.Size = 15
prmUser.Value = &quot;myuser&quot;
cmdRunDTS.Parameters.Add(prmUser)

'Pass Password
prmPassword.ParameterName = &quot;@Password&quot;
prmPassword.SqlDbType = SqlDbType.VarChar
prmPassword.Size = 15
prmPassword.Value = &quot;mypass&quot;
cmdRunDTS.Parameters.Add(prmPassword)

'Pass Global var - FileName
prmGFileName.ParameterName = &quot;@FileName&quot;
prmGFileName.SqlDbType = SqlDbType.VarChar
prmGFileName.Size = 200
prmGFileName.Value = &quot;c:\NewFile01.txt&quot;
cmdRunDTS.Parameters.Add(prmGFileName)

'Pass Global var - SQL
prmSQL.ParameterName = &quot;@SQL&quot;
prmSQL.SqlDbType = SqlDbType.VarChar
prmSQL.Size = 1000
prmSQL.Value = sSQL
cmdRunDTS.Parameters.Add(prmSQL)

''cnArett.Open()
''Dim myReader As SqlDataReader = cmdRunDTS.ExecuteReader()

'Response.Write(prmReturn.Value)

'Console.WriteLine(&quot;{0}, {1}&quot;, myReader.GetName(0), myReader.GetName(1))
'Console.WriteLine(&quot;{0}, ${1}&quot;, myReader.GetString(0), myReader.GetDecimal(1))

cnArett.Open()
cmdRunDTS.ExecuteNonQuery()
cnArett.Close()

Response.Write(&quot;Affected records = &quot; & cmdRunDTS.Parameters(&quot;retvalue&quot.Value.ToString)


'Get the result
'Dim daGetOrders As New SqlDataAdapter(cmdRunDTS)
'Dim dsOrders As New DataSet
'daGetOrders.Fill(dsOrders, &quot;Orders&quot
'DataGrid1.DataSource = dsOrders.Tables(&quot;Orders&quot
'DataGrid1.DataBind()

'exec spRunPackage 'ExportCart'

End Sub
End Class