Executing A DTS Package Using An ASP (VBScript)
Jul 23, 2005
I'm looking for an example of how to execute an existing DTS* package
from an
ASP (VB)script and would appreciate any and all response. *I don't
even
know if it's possible
Thanks
- Chuck Gatto
Dan Guzman Apr 27 2000, 12:00 am show options
Newsgroups: comp.databases.ms-sqlserver
From: "Dan Guzman" <DGuz...@nospamplease-earthlink.net&g*t; - Find
messages by this author
Date: 2000/04/27
Subject: Re: Executing a DTS Package using an ASP (VBScript) Script
Reply to Author | Forward | Print | Individual Message | Show original
| Report Abuse
This VBScript example loads and executes an existing DTS pac*kage from
SQL
Server.
Option Explicit
Const PackageName = "PackageName"
Const ServerName = "ServerName"
Const UserName = "UserName"
Const Password = "Password"
Dim DTSPk
Set DTSPk = CreateObject("dts.package")
DTSPk.LoadFromSQLServer ServerName, UserName ,Password
,,,,,*PackageName
DTSPk.Execute
If DTSPk.Steps(1).ExecutionResult = 0 Then
Response.Write "Package execution completed"
Else
Response.Write "Package execution failed"
End If
Set DTSPk = Nothing
You can also create the entire package from scratch from wit*hin your
asp and
execute it.
Hope this helps.
Chuck Gatto <cga...@anchorsystems.com> wrote in message
news:8eapo8$frj$1@slb7.atl.mindspring.net...
- Hide quoted text -
- Show quoted text -
[color=blue]
> I'm looking for an example of how to execute an existing D*TS[/color]
package from
an[color=blue]
> ASP (VB)script and would appreciate any and all response.* I don't[/color]
even[color=blue]
> know if it's possible
> Thanks
> - Chuck Gatto[/color]
Chuck Gatto May 1 2000, 12:00 am show options
Newsgroups: comp.databases.ms-sqlserver
From: "Chuck Gatto" <cga...@anchorsystems.com> - Find messages by this
author
Date: 2000/05/01
Subject: Re: Executing a DTS Package using an ASP (VBScript) Script
Reply to Author | Forward | Print | Individual Message | Show original
| Report Abuse
Below code works 100% in VB but the load fails in ASP. I g*et...
"Microsoft OLE DB Provider for SQL Server. Login failed for *user "".
error.
I think the IIS (server a) is set for NT auth. and sql7 (on *server b)
as
well but I can't be sure.
Any idea what I should look for.
Thanks
"Dan Guzman" <DGuz...@nospamplease-earthlink.net> wrote in m*essage
news:sgi3hi617qo89@corp.supernews.com...
- Hide quoted text -
- Show quoted text -
[color=blue]
> This VBScript example loads and executes an existing DTS p*ackage[/color]
from SQL[color=blue]
> Server.[/color]
[color=blue]
> Option Explicit
> Const PackageName = "PackageName"
> Const ServerName = "ServerName"
> Const UserName = "UserName"
> Const Password = "Password"
> Dim DTSPk
> Set DTSPk = CreateObject("dts.package")
> DTSPk.LoadFromSQLServer ServerName, UserName ,Password[/color]
,,,*,,PackageName[color=blue]
> DTSPk.Execute
> If DTSPk.Steps(1).ExecutionResult = 0 Then
> Response.Write "Package execution completed"
> Else
> Response.Write "Package execution failed"
> End If
> Set DTSPk = Nothing[/color]
[color=blue]
> You can also create the entire package from scratch from w*ithin[/color]
your asp
and[color=blue]
> execute it.[/color]
[color=blue]
> Hope this helps.[/color]
[color=blue]
> Chuck Gatto <cga...@anchorsystems.com> wrote in message
> news:8eapo8$frj$1@slb7.atl.mindspring.net...[color=green]
> > I'm looking for an example of how to execute an existing* DTS[/color][/color]
package
from[color=blue]
> an[color=green]
> > ASP (VB)script and would appreciate any and all respons*e. I[/color][/color]
don't even[color=blue][color=green]
> > know if it's possible
> > Thanks
> > - Chuck Gatto[/color][/color]
Dan Guzman May 1 2000, 12:00 am show options
Newsgroups: comp.databases.ms-sqlserver
From: "Dan Guzman" <DGuz...@nospamplease-earthlink.net&g*t; - Find
messages by this author
Date: 2000/05/01
Subject: Re: Executing a DTS Package using an ASP (VBScript) Script
Reply to Author | Forward | Print | Individual Message | Show original
| Report Abuse
You can specify a trusted connection with flag 256 instead o*f
username and
password. For example:
DTSPk.LoadFromSQLServer ServerName, , , 256,,,,PackageNa*me
Assuming this is an intranet application running under NT 4.*0 and you
want
to execute the package under the invoking user's account, yo*u can do
this as
follows:
Specify 'clear text' for the IIS Directory Security
auth*entication
Remove 'Everyone' from the access list on the files (req*uires
NTFS) and
grant permissions to the users
Grant logins access to the database server
With this method, users must enter their DomainUserName and* password
when
prompted.
NT authentication presents a challenge when multiple servers* are
involved
because NT 4.0 does not support delegation. See
http://msdn.microsoft.com/workshop/...re/security.asp for
details.
I understand Windows 2000 provides delegation capabilities b*ut this
can be a
bit tricky to implement.
BTW, if your DTS package does not access SQL Server, you can* save it
to a
file and use the LoadFromStorageFile method instead.
Hope this helps.
If you need to use
Chuck Gatto <cga...@anchorsystems.com> wrote in message
news:8ekrkd$pmq$1@slb7.atl.mindspring.net...
- Hide quoted text -
- Show quoted text -
[color=blue]
> Below code works 100% in VB but the load fails in ASP. I* get...
> "Microsoft OLE DB Provider for SQL Server. Login failed fo*r user[/color]
"".
error.[color=blue]
> I think the IIS (server a) is set for NT auth. and sql7 (o*n server[/color]
b) as[color=blue]
> well but I can't be sure.
> Any idea what I should look for.
> Thanks[/color]
Chuck Gatto May 6 2000, 12:00 am show options
Newsgroups: comp.databases.ms-sqlserver
From: "Chuck Gatto" <cga...@anchorsystems.com> - Find messages by this
author
Date: 2000/05/06
Subject: Re: Executing a DTS Package using an ASP (VBScript) Script
Reply to Author | Forward | Print | Individual Message | Show original
| Report Abuse
Hey Dan
Thanks again. I really appreciate your input and help.
I actually solved the problem by calling dtsrun...
I apologize for the delay getting back w/you but thee recent* virus
atack
sidetracked me.
Thanks again.
"Dan Guzman" <DGuz...@nospamplease-earthlink.net> wrote in m*essage
news:sgsfh8spoog87@corp.supernews.com...
- Hide quoted text -
- Show quoted text -
[color=blue]
> You can specify a trusted connection with flag 256 instead* of[/color]
username and[color=blue]
> password. For example:[/color]
[color=blue]
> DTSPk.LoadFromSQLServer ServerName, , , 256,,,,Package*Name[/color]
[color=blue]
> Assuming this is an intranet application running under NT *4.0 and[/color]
you want[color=blue]
> to execute the package under the invoking user's account, *you can[/color]
do this
as[color=blue]
> follows:[/color]
[color=blue]
> Specify 'clear text' for the IIS Directory Security[/color]
au*thentication[color=blue]
> Remove 'Everyone' from the access list on the files (r*equires[/color]
NTFS)
and[color=blue]
> grant permissions to the users
> Grant logins access to the database server[/color]
[color=blue]
> With this method, users must enter their DomainUserName a*nd[/color]
password when[color=blue]
> prompted.[/color]
[color=blue]
> NT authentication presents a challenge when multiple serve*rs are[/color]
involved[color=blue]
> because NT 4.0 does not support delegation. See
> http://msdn.microsoft.com/workshop/...re/security.asp for[/color]
details.[color=blue]
> I understand Windows 2000 provides delegation capabilities* but this[/color]
can be
a[color=blue]
> bit tricky to implement.[/color]
[color=blue]
> BTW, if your DTS package does not access SQL Server, you c*an save[/color]
it to a[color=blue]
> file and use the LoadFromStorageFile method instead.[/color]
[color=blue]
> Hope this helps.[/color]
[color=blue]
> If you need to use
> Chuck Gatto <cga...@anchorsystems.com> wrote in message
> news:8ekrkd$pmq$1@slb7.atl.mindspring.net...[color=green]
> > Below code works 100% in VB but the load fails in ASP. * I get...[/color][/color]
[color=blue][color=green]
> > "Microsoft OLE DB Provider for SQL Server. Login failed *for user[/color][/color]
"".[color=blue]
> error.[color=green]
> > I think the IIS (server a) is set for NT auth. and sql7 *(on[/color][/color]
server b) as[color=blue][color=green]
> > well but I can't be sure.
> > Any idea what I should look for.
> > Thanks[/color][/color]
chris.duni...@agwsha.nhs.uk Jan 31, 10:05 am show options
Newsgroups: comp.databases.ms-sqlserver
From: chris.duni...@agwsha.nhs.uk - Find messages by this author
Date: 31 Jan 2005 10:05:00 -0800
Subject: Re: Executing a DTS Package using an ASP (VBScript) Script
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse
Hi,
I've tried adding this function to my ASP pages and get the *following
error message;
Microsoft VBScript runtime error '800a01ad'
ActiveX component can't create object: 'DTS.Package'
/asp/pages/dts.asp, line 21
Does anyone have any idea what I need to fix to get the DTS *to work?
I'm v. new to SQL Server and ASP so any help would be apprec*iated.
Many thanks,
Chris Dunigan
View 4 Replies
ADVERTISEMENT
Jan 8, 2008
Hi,
I am novice to SQL reporting services.
I have created a report using SSRS.
In this report I would like to show a column value as button and wants to execute vbscript code on click.
Or at least execute vbscript code on click of that field (button is just an option!)
The code will launch another application (exe file or else)
Help in this regard would be appreciated.
Thanks
View 2 Replies
View Related
Jan 16, 2004
Hi,
Thanks for reading.
I am creating a DTS package to import a .txt file into sql. I have everything in place, but the text file needs to have the last record deleted before the import. I need help with this part
I would like to delete the last record from a fixed width text file before I import it into sql. The number of rows will vary from file to file.
Can any one offer suggestions on the best way to do this.
I understand that I have to use the FSO to open and read the file, but I am not sure the best way to proceed after that.
Thanks in advance,
Steve
View 1 Replies
View Related
Feb 2, 2007
Hi,
I have developed an SSIS package for ETL purpose. I am invoking the SSIS package through .Net console application by referencing the ManagedDTS Assembly. I am able to execute the package in Sql Server 2005 Developer Edition and it runs fine till completion.
But when i try to execute the packahe in Sql Server 2005 Standard edition, by invoking the package through .Net console application the status of the package is failure.
Can any one help me how to over come this problem.
View 1 Replies
View Related
Feb 2, 2007
Hi,
I am planning to develop a single package that will download files from ftp server, move the files to internal file server and upload it in the database. But I want to run this package for multiple ftp file providers. For each provider the ftp server might be different and the transformation to upload the files into a database table might be different.
So can I create a single package and then multiple configuration files (xml), which will contain the details fo the ftp file providers and then pass the xml file as a parameter while executing the package. The reason being that the timings of fetching the files is different for each ftp file provider and hence cannot be combined into one.
Is this possible?
Thanks for your help.
$wapnil
View 6 Replies
View Related
Jun 3, 2014
I would like to fetch the data flow component name while package is executing. Since system variable named [System::SourceName] only fetches name of the control flow tasks? Is there a way to capture them?
View 5 Replies
View Related
Nov 16, 2004
Hello,
I have a web application that I kick off a DTS package. I tested the package and it works fine. However, when running the package through ASP.NET, I get the following error:
Type mismatch. (mscorlib (80004005): Missing parameter does not have a default value. Parameter name: parameters)
I've traced it to a dynamic task step. I have this as the first step in the workflow, but for some reason, it is executing last. Which is definitely not the place where I want it. Is there anyway I can force it, or why is it doing that?
Any ideas?
Brian
View 2 Replies
View Related
Dec 27, 2006
Hi Friends
I am trying to execute the DTS package from the T-Sql
Its basically Exporting the table from Sql Server to tab delimited text file
DECLARE @Command varchar(1000)
declare @partcode varchar(100)
set @command = 'DTSRun /S "servername" /U "username" /P "password" /N "TabFormat" /G "{459784D8-0170-400C-8919-AC25423D7F34}" /W "0" '
EXEC master..xp_cmdshell @Command
It Comes up with the Following Error
output
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSDataPumpTask_1
DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005)
Error string: Error opening datafile: The system cannot find the path specified.
Error source: Microsoft Data Transformation Services Flat File Rowset Provider
Help file: DTSFFile.hlp
Help context: 0
Error Detail Records:
Error: 3 (3); Provider Error: 3 (3)
Error string: Error opening datafile: The system cannot find the path specified.
Error source: Microsoft Data Transformation Services Flat File Rowset Provider
Help file: DTSFFile.hlp
Help context: 0
DTSRun OnFinish: DTSStep_DTSDataPumpTask_1
DTSRun: Package execution complete.
NULL
(22 row(s) affected)
Vic
View 5 Replies
View Related
May 8, 2006
Hi,
I am having trouble executing a package as a job in SQL Server 2005. I can run the package fine manually from sql server but when I create a job for that package and run the job I get an error that says
The package execution failed. The step failed.
I am completely lost at this point. Any help would be greatly appreciated.
Thanks
Brian
View 1 Replies
View Related
Feb 13, 2006
Is it possible to execute a package in a trigger or stored procedure ?
View 3 Replies
View Related
Aug 21, 2000
Can some one point me in the right direction? I need to execute a DTS package after a set of T-SQL statements run in a stored procedure. The starting of the DTS package needs to be the last thing executed/called from the stored procedure.
Thanks
View 1 Replies
View Related
Oct 26, 2004
When run in query analyzer, the procedure below returns no errors however it doesn't execute the dts package I am passing to it.
The package works beautifully if you run it directly in Enterprise Manager.
Can anyone help me with a possible causes or perhaps help me improve my error trapping to help me resolve this?
create procedure sp_ExecuteDTSPackage
@login varchar(15),
@userpwd varchar(15),
@svr varchar(15),
@packagename varchar(255),
@pkgPwd varchar(15)
as
declare @hr int
declare @object int
--create a package object
EXEC @hr = sp_OACreate 'DTS.Package', @object OUTPUT
if @hr <> 0
Begin
Print 'Package Creation Failed'
RETURN
end
declare @flag int
select @flag = 0 -- 0 = sql svr authentication, 256 = windows authentication
EXEC @hr = sp_OAMethod @object, 'LoadFromSqlServer',NULL, @ServerName=@svr, @ServerUserName=@login,
@PackageName=@packagename, @Flags=@flag, @ServerPassword = @userpwd, @PackagePassword = @pkgPwd
if @hr <>0
begin
Print 'Package load failed'
RETURN
end
-- execute the package
EXEC @hr = sp_OAMethod @object, 'Execute'
IF @hr <> 0
BEGIN
print 'Package execution failed'
RETURN
END
--return the step errors as a recordset
--
EXEC @hr = sp_OAMethod @object, 'UnInitialize'
IF @hr <> 0
BEGIN
print 'UnInitialize failed'
RETURN
END
-- release the package object
EXEC @hr = sp_OADestroy @object
if @hr <> 0
Begin
RETURN
end
View 1 Replies
View Related
Mar 7, 2002
hi, I have created a DTS package that export data to another database. I always click on the package itself to run it. Now I need to be able to create an asp page to execute that DTS pakcage how can I do that.
Thanks for your help
Al
View 2 Replies
View Related
Dec 21, 2007
Hi All,
I hav a strange problem. I created a SSIS package on my machine with protection level as "EncryptAllWithPassword".
When I deploy this package on a SQL Server on another machine (either in File System or SQL Server)and add it as a job in SQL Server Agent and try to run it through a scheduler or manually the job fails with the following message -
Executed as user:IBM-P4-1976SYSTEM. The package could not be loaded. The step failed.
However if I deploy it on my own machine the job runs successfully.
Also if I run the package using dtexec utility on the another machine it runs successfully.
It is not running as a job on another machine. All the logged in users have administrative rights. My databases are altogether on a different server.
Can any one help me out with this issue...Pleassssseeee...
View 1 Replies
View Related
Dec 19, 2007
Hi,
I have to execute package in a Batch. I have done adding SSIS packages to SQL Server Agent and it is working. Now, my requirement is such that, when a user clicks "Transfer to batch" button in Windows application, i have to add all the packages to batch automatically without need for user to manually open SQL Server Agent and add packages. Is there any code available to do this?
Please help!
Thanks,
View 6 Replies
View Related
Jun 29, 2006
hi!
I am able to run the pacakge in command line but when i try running the package through agent . The it starts the pacakge it says that it has been started by me local user and fails in next step saying that it has been execute by administrator.
I understand that there is a miss match between the permissions. Cos the SQL server Agent uses my login in but the SQL server agent on the Server that i am trying to execute has the Administrator log in. How to solve the issue?
Thanks in advance
jasmine
View 3 Replies
View Related
Feb 4, 2008
Can someone tell me the best way to "automatically" execute a SSIS package. I have approximately 100 computers that I need to execute packages on. Here is the scenario:
I have some text files that are coming from our AS400 to a local computer (the package will NOT be used to bring files from our AS400. I will be using Client Access for that). The text files will be stored in a specified directory on the local computer. I will be importing these text files into a SQL Database on all 100 machines. Once I create the Import Package, what is the best way to execute this package on the devices?
I thought maybe after creating the package, I can put an EXE on the desktop that will execute the SSIS package. This way all I would have to do is click a icon on the desktop. I don't know if this is feasible or not. After building a package, do you make it a EXE or what? Pease advise. Thank in advance.
View 2 Replies
View Related
Nov 27, 2006
Hi,
I'm having another go at attempting to call an SSIS package from a web service. The Web Service is set up on the same server as the sql server and SSIS package deployment. When i attempt to run the pacage from the web service it starts and then inmediately fails. I can see this in both the Integration Service Logs and the event viewer.
If i click on the package itself and run it using the DTExecUI, it runs without a problem. No logs are output by the package when called by the web service.
Can anyone suggest anything that i could to see if i can diagnose where the problem stems from.
Many thanks in advance,
Grant
View 7 Replies
View Related
Oct 4, 2006
Hi,
Simplification of setup:
- SQL Server Box with Enterprise Edition of SQL Server 2005
- Workstation with the client tools (Managment Studio, Business Intelligence Studio, etc) SQL Server 2005 installed running Windows XP
1. Am I right in saying that I need to install the SSIS service in order to run this package on my workstation from the command line, the Execute Package Utility tool, or through Management Studio?
2. I can however execute the package on my workstation in the Business Intelligence Studio without the SSIS service installed, correct?
3. Assuming I need to install the SSIS service on my workstation, if I execute the package via the command line or the Execute Package Utility utility, will it run on my local machine or on the server (i.e. which gets the load)?
4. Does Service Pack 1 address these issues?
Thanks a lot for any help you could provide!
John
View 12 Replies
View Related
Feb 7, 2007
Hi
I've a doubt. I don't know whether it is possible or not.
I've a very less knowledge in DTS packages.
When I execute a package in C#. it is displaying predefined GUI for showing process.
Can I've my own UI when executing DTS.
Thanks in advance.
View 10 Replies
View Related
Jan 3, 2008
Hi all,
I have a dts package that wil load data from excel file to sql server 2005 database tables deployed in our sql server. I have a asp.net application in my machine. I need to call execute that package from my asp.net page. i need to pass values for 2 varaibles in the package from my asp.net code based on that the package will choose the excel file to load. When i directly call the package from the server it works fine. but when i call it from the asp.net page the dts package cant access the excel file. I dont know the reason for this. please help me to solve the problem. if u still have any doubts plz let me know.
Sathish.N
View 6 Replies
View Related
Mar 24, 2006
Hi again
First I explain my environment:
- SSIS Packages are stored on 64bit Server (Win 2003 64bit, SQL 2005 64bit)
- .NET (C#) application (on Win 2003 32bit Server) which loads and executes the SSIS Packages.
I can execute my SSIS Package localy on the 64bit Server and they work fine.
When I execute the SSIS Package from the Application I get following exeption:
Retrieving the COM class factory for component with CLSID {697AC67E-FDD5-46D1-90D7-F3D0DF1B2A47} failed due to the following error: 80040154. ...
at Microsoft.SqlServer.Dts.Runtime.Package..ctor()
The code looks like:
Microsoft.SqlServer.Dts.Runtime.Package local_Package = new Microsoft.SqlServer.Dts.Runtime.Package();
Microsoft.SqlServer.Dts.Runtime.DTSExecResult local_DTSExecResult = new Microsoft.SqlServer.Dts.Runtime.DTSExecResult();
Microsoft.SqlServer.Dts.Runtime.Application local_Application = new Microsoft.SqlServer.Dts.Runtime.Application();
local_Package = local_Application.LoadFromDtsServer("PackageName", "64bitServer", null);
local_DTSExecResult = local_Package.Execute();
Other question would be: how can I execute a package directly on the server??
I mean without "LoadPackage"?? I guess the problem on top is because on the
32bit Server there is only Framework installed but not Integration Services Tools ...
Would be happy for any comment!
Best regards
Frank Uray
View 5 Replies
View Related
Mar 26, 2007
Hi,
when I run the following from the command prompt I am getting an error saying that can not set the variable Status as int.
dtexec /File "C:workSSIS PackagesDataflowControlRoom.dtsx" /SET
Package.Variables[ChannelCode].Value;"test" /SET
Package.Variables[Status].Value;1
both variables as you can see are in the top level.
the problem is that ChannelCode is string but Status is Integer.
if I take out the /SET Package.Variables[Status].Value;1 part from the command line it will work fine. I think th eproblem is down to diffenrt types of variable as there are two types of String and Interger.
any ideas on what the problem migth be.
p.s. I am runnning SQL server 2005 Dev edition (without any SP)
Cheers
View 13 Replies
View Related
May 17, 2006
I wrote package using Integration Services and tried execute it on Visual Studio 2003 (Visual Basic). Package was loaded, but execute method has failed without any error messages. When I tried to run it on Visual Studio 2005, execution was successful.
Codes:
Dim App As DTSLib.IDTSApplication90 = New DTSLib.Application
Dim p As DTSLib.IDTSPackage90 = App.LoadPackage("d: empPackage.dtsx", True, Nothing)
p.Execute()
What can be the reason of the trouble?
Thanks,
Alexander
View 1 Replies
View Related
Feb 7, 2008
Hi All,
I created a Package that calls Three other Packages through the Execute package task.
I saved The main package and the sub-packages in Dontsavesensitive mode and Referred to the config file.
I checked the Package thru VSS and When iam running thru the Bat file iam getting the following error
Description: Error 0x80070002 while loading package file "C:Documents and SettingsUSERNAMEMy DocumentsVisual Studio 2005ProjectsImportFILEImportFILEPACKAGE.dtsx". The system cannot find the file specified.
Iam not sure why its asking for this path in the production server.
This is the initial path where i saved the package in BIDS environment.
Please help me.Help is greatly appreciated.
Thanks,
SVGP
.
View 1 Replies
View Related
Oct 24, 2007
Hi,
I have created a SSIS package through SSIS Tool. Protection Level is 'EncryptSensitiveWithUserKey'. When I execute , it works fine.
I have written a code to execute SSIS package through .net application. When i try in my local PC. The package works fine.
When I deployed in Webserver, it throws an error,
DTS Package: Package1 failed. Error Details as follows. 1. Source : Execute SQL Task Description : Failed to acquire connection "DatabaseConnection ADO.NET". Connection may not be configured correctly or you may not have the right permissions on this connection.
"DatabaseConnection ADO.NET". is a connection which is specified in the SSIS.
Can any one pls tell me how to solve this issue.
Thanks & Regards,
Ganesan
View 4 Replies
View Related
Sep 7, 2007
I created a package and stored in SQL server.
I am running it as follows
master.dbo.xp_cmdshell dtexec /sq "ABC DEF GHI" /ser "Prod"
ABC DEF GHI --> is my package name.
When i run this from a job, it runs without error
but when i run the above sql i am getting error as
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '/'.
Am i missing some thing, please advice. Is that the problem between spaces in the package name?
View 1 Replies
View Related
Aug 28, 2004
I have a MS Access front-end (not web) that creates a Comma Separated Values (.CSV) file. I need to dlete all records in a temp table in the SQL SERVER (2000) backend, and then import the CSV file. Once that is done, I need to open the temp table (linked from Access ODBC) for the user and let them update records.
I already created a DTS package that deletes the records in the temp table and imports the records from the CSV.
What I don't know is how to execute that existing DTS package from VBA in Access. Any ideas?
Also, once the temp table is re-populated by manually executing the DTS package, I haven't been able to figure out how to "refresh" the temp table link. If it originally had 100 records (examined via the Access link) and I manually run the DTS package to upload 50 records, when the process completes, the Access linked table is still showing 100 records. I then go into the Access linked table manager and refresh the link. It then shows 50 records, but it won't let me update them.
Any ideas on solving this multi-part problem?
View 2 Replies
View Related
Oct 1, 2005
My current task in my company is...
I got a Spreadsheet from which i have to get the data into a "Staging Table" and from there the data has to go into 10 different Tables in my database..
I had created a DTS package using ActiveX script and written a Stored Proc to get the records from Staging table to Database tables...every thing is working fine... but...
they asked me to send "TapeType' and "DealName" into two fields and "current datetime" into one more field of the stagingtable fields which are not present inthe Spreadsheet....
so in the Activex script i hard coded... destination"TapeType"= "BidTape" and destination"DealName"= "ABCD" and its working...
and for currentdate i had defined a DTS Task and in that i had written a SQL stmts..
update table....
set currentdate = getdate()
where TapeType = 'BidTape' ..... which is also working....but the problem is
if i got one more spread sheet with same "TapeType" i.e. BidTape but with different "DealName" i.e. "XYZ".... every thing is working out but....the Currentdate field is getting updated every where with as one date..???
can u suggest me a better solution here.....
and after this Iam planning to create one more Task to Execute the Proc..... but I have to pass 2parameters i.e. "Dealname" and "TapeType"... they want every thing to be get done with single mouse click...
can u suggest me here how to pass these 2parameters in the DTS package,..
I will be waiting for any one your valuable suggestions....
View 3 Replies
View Related
Jul 31, 2001
How can I execute a package from a store procedure. The package function will export a table to a text file in c:export.
I can run the package manually by clicking on it to execute it. I am looking for another way to run the package from a store procedure. any ideas
Thanks
Ali
View 1 Replies
View Related
Nov 22, 2004
Hello,
I've created a test application in windows ( vb.net ). When executing this app with the DTS package it seems to work fine ( by right clicking on the package and selecting “Execute Package” ). It finishes successfully and does what it supposed to. But when I try to schedule the package with the SQL Server Enterprise Manager in Management > SQL Server Agent > Jobs it Fails every time. I schedule it by right clicking on the package and selecting the “Schedule Package” option. Does any one have an idea why the schedule keeps on failing? Thanks in advance.
Daniel B
View 3 Replies
View Related
Jan 28, 2005
Hi everyone,
I just got assigned to help out with a couple of problems with the running of DTS packages using the SQL Server Agent, this is the problem and a list of what I've done so far:
There are 4 packages that:
a) Deletes all records from a table and then populates it using a flat file that contains that day's transactions.
b) Creates a report in Excel
c) Creates a summary report in Excel
d) Does a), b) and c) for another table.
All these packages are scheduled to run weekdays starting at 8:00 am, and they were owned by sa. Last month we had a massive change of passwords thanks to an audit and unfortunately the dba left without letting us know that password. After the massive change, these packages started failing with errors like:
The job failed. The Job was invoked by Schedule 18 (DLYINVDT_XLS). The last step to run was step 1 (DLYINVDT_XLS).
The job failed. The Job was invoked by Schedule 22 (AGAR830_dts). The last step to run was step 1 (AGAR830_dts)
Yesterday I went into all the packages and changed the connection properties from user sa to another user that has permission over the tables and ran the 4 of them at least 10 times each, both using my personal laptop and directly in the server (just wanted to check remote and local, even though I know it really has nothing to do with the execution that its always local to the server).
Now, the funny thing is that I came in this morning and guess what..... yeap, the packages failed, I can run them manually with NO problem at all, but if I run them using the SQL Server Agent then they just will not run.
At the time I was writing this I found out the password for sa, so now I'm using it but unfortunately no change, the errors now are :
The job failed. The Job was invoked by User sa. The last step to run was step 1 (AGAR830_dts)
I'm an informix dba and I started training myself in sql a couple of months ago so I'm confortable with what I'm doing, but at this point in time I'm about to throw something to the screen :) Since I can execute the packages manually then the users are happy, but I would really like to find out why the Agent is not working right.
Any help will be GREATLY appreciated
Luis Torres
View 3 Replies
View Related
Jul 3, 2007
Is it possible to execute an SSIS package from Linux? Before with 2005, we were able to execute a job that would execute the DTS, now we're trying to execute an SSIS package in 2005.
---
"Try not to become a man of success but rather try to become a man of value."
View 3 Replies
View Related
