Asynch call DTS or Stored procedure 1.1

Hi,

I would like to trigger a DTS or a stored procedure from asp.net 1.1 BUT

I don't want to wait for it to finish. In fact the DTS/Storeproc calculates values into different tables.

Those values are not needed immediately. The calculation takes between 20 or 30 minutes.

Do you have any idea how to do it ?

Thanks

The easiest way I can think of to do it is to have a daemon sitting on the server. Tickle the daemon (either via a port, web service, or table on the DB that the daemon watches), and it'll fire off the stored procedure and keep the connection open until the SP completes.|||In this case I have to put a connection timeout to more than 5 minutes ... It's not a good solution neither|||

Valvert:

I would like to trigger a DTS or a stored procedure from asp.net 1.1 BUT

I don't want to wait for it to finish. In fact the DTS/Storeproc calculates values into different tables.

Hello, i'm not a DBA expert and have never tried this, but i guess you could define a job to be started manually; then start the job withsp_start_job and check for termination withsp_help_job.

HTH. -LV

Assigning Passwords to Connection Managers using Expressions

Hi Experts here,

Sorry if this query had been raised earlier. While In DTS Packages we retrieve connection details for all Connections in a Package from a table and then assign them to the following global Variables

gv_Source_User, gv_Source_Pwd, gv_Source_DataSource, gv_Source_InitialCatalog

Finally we reassign these variables to repective Connection Properties using Dynamic Properties Task. After Migrating to SSIS though we are able to assign almost all variables to Properties of Connection Managers via Expression except the Password which we donot find in the drop down list in order to assign gv_Source_Pwd.

Is there any work around to assign passwords dynamically?

Many Thanks

Subhash Subramanyam

Hi, Was also thinking if we can go about using script task to do the same ..

|||No, you can't use script. You can assemble the complete connection string (with password) in an expression-based variable and then assign that to the ConnectionString property through an expression on the connection manager.

See www.connectionstrings.com if you need help building the string.
|||

Many Thanks to JayH.

I already had this in my mind yesterday with a bit of confusion - How to work out the code which uses Different connection Managers (what if it was Oracle). i.e. the Connection string built will be of different format .

A question for you is: Though the security can be compromised from Value set to ConnectionString, Why Password property hasn't been added in the dropdown list?

e.g. SQL OLEDB Connection, Data Source=Serverxyz;User ID=sa;Initial Catalog=PUBS_CATALOG;Provider=SQLOLEDB;Persist Security Info=True;

For Oracle, First Manually provide all the details, so that ConnectionString will be built on Connection Manager Properties. This seems really a burden that we must manually verify every derived connectionstring if it can successfully connect.

Many Thanks once again

Subhash Subramanyam

Assigning Filepath to Global Variable in DTS

How can I assign file path to a DTS Global Variable in an activex script.

i hope u know how to create global variable in DTS. Here we have two global variable DestinationPath and SourcePath and see how its been used in VBScript

'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************

Function Main()
Main = DTSTaskExecResult_Success

sSourceFile1 =dtsglobalvariables("DestinationPath").value
sSourceFile2 = dtsglobalvariables("SourcePath").value

set fso = CreateObject("Scripting.FileSystemObject")
fso.deletefile sSourceFile1
fso.copyfile sSourceFile2, sSourceFile1

End Function

http://www.sqldts.com is one of the best resource in DTS

Madhu

|||

Now that I have assigned a filepath to a global variable and is a dynamic one, since the file that is created is done on a daily basis and each file has a datetime stamp, i have figured out that already, but now i am facing trouble assigning the dynamic path that i have assigned in global variable to the Send Mail Task in DTS, can you help me with this issue.

|||

Consult http://msdn2.microsoft.com/en-us/library/ms141698.aspx for how to create a property expression on your send mail task.

You are just about there!

jkh

ASP.NET application to use DTS

I've got an application that needs to:
1. Look on the user's machine and locate a file
2. Pull that file over to DTS
3. Allow DTS to insert this file into a table

I'm sure this can be done...anyone got any article on how to do it?

Thanks!

You could adopt to the following two approaches;
1. You can created a DTS package (using VB Script) to search for a file on the machine and then pull the file over to DTS and finally date into database.
2. You can register this as scheduled task
3. You can call this package programatically from ASP.NET
or there is another approach as described in the following article;
http://www.dotnetbips.com/7F78A65B-456D-45D1-BD10-2F5E2C218696.aspx?articleid=228

ASP.NET and SQL Server DTS

I want to refill a sql server table through a flat text file(CSV) in ASP.NET just giving the file name. Can somebody help me in this regard that i can do this.

Thanks & Best Regards

Hi saeednawaz,

Check here for solution
http://bhatiaworld.blogspot.com/2005/06/transfering-data-from-text-filesource.html
But this solution is not taking ASP.net into consideration, but it will surely help u.|||

Hello,

Here is an example

' This example assumes the existence of a text file named myFile.txt ' that contains an undetermined number of rows with seven entries ' in each row. Creates a new DataSetDim myDataSet As New DataSet()' Creates a new DataTable and adds it to the Tables collectionDim aTable As New DataTable("Table 1")myDataSet.Tables.Add("Table 1")' Creates and names seven columns and adds them to Table 1Dim Counter As IntegerDim aColumn As DataColumnFor Counter = 0 to 6   aColumn = New DataColumn("Column " & Counter.ToString())   myDataSet.Tables("Table 1").Columns.Add(aColumn)Next' Creates the StreamReader to read the file and a string variable to' hold the output of the StreamReaderDim myReader As New System.IO.StreamReader("C:\myFile.txt")Dim mystring As String' Checks to see if the Reader has reached the end of the streamWhile myReader.Peek <> –1   ' Reads a line of data from the text file   mystring = myReader.ReadLine   ' Uses the String.Split method to create an array of strings that    ' represents each entry in the line. That array is then added as a    ' new DataRow to Table 1   myDataSet.Tables("Table 1").Rows.Add(mystring.Split(","c))End While

In the above example, new dataset will be created with one table that contains the
data of the myfile.txt

If you want to just fill a table then iterate throught myfile.txt, split values
and fill rows.

HTH

regards