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?
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
Hi,
We have a problem with visual studio. It hangs when i use the "execute package" option. New packages are running correctly but the packages which i've already built are not executing anymore...Any ideas to get things on track again?
Gr,
Hennie
Hi All,
I developed my package in BI studio and tested runnning it through BI. Now when i have to deploy the package to main server it will be executed via batch job. This package requires an input file name too.
Now when i am trying to run the package on my loacal box using following command on command prormpt i get error given below.
COMMAND USED BY ME: DTExec /FILE 'C:ProjectIntegrationServicesSFRIntegrationServicesinpkgSFRLocation.dtsx' /SET Package.Variables[InputParamFileName].Value;'C:DataDumpInputLOC00022'
ERROR MESSAGE:
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 1:29:54 PM
Error: 2008-02-22 13:29:54.96
Code: 0xC0011007
Source: {9B1FAA88-A6FA-44CF-A2E2-DB3F737FDA65}
Description: Unable to load the package as XML because of package does not ha
ve a valid XML format. A specific XML parser error will be posted.
End Error
Error: 2008-02-22 13:29:54.96
Code: 0xC0011002
Source: {9B1FAA88-A6FA-44CF-A2E2-DB3F737FDA65}
Description: Failed to open package file "'C:ProjectIntegrationServicesSFR
IntegrationServicesinpkgSFRLocation.dtsx'" due to error 0x800700A1 "The speci
fied path is invalid.". This happens when loading a package and the file cannot
be opened or loaded correctly into the XML document. This can be the result of
either providing an incorrect file name was specified when calling LoadPackage o
r the XML file was specified and has an incorrect format.
End Error
Could not load package "'C:ProjectIntegrationServicesSFRIntegrationServices
inpkgSFRLocation.dtsx'" because of error 0xC0011002.
Description: Failed to open package file "'C:ProjectIntegrationServicesSFRInt
egrationServicesinpkgSFRLocation.dtsx'" due to error 0x800700A1 "The specifie
d path is invalid.". This happens when loading a package and the file cannot be
opened or loaded correctly into the XML document. This can be the result of eit
her providing an incorrect file name was specified when calling LoadPackage or t
he XML file was specified and has an incorrect format.
Source: {9B1FAA88-A6FA-44CF-A2E2-DB3F737FDA65}
Started: 1:29:54 PM
Finished: 1:29:54 PM
Elapsed: 0.047 seconds
PLease let me know solution to creating batch job for executing this pkg with input param.
I have a package that executes fine in BIDS. However, when calling it using xp_cmdshell it cannot find the path to the source flat file. Anyone have any suggestions?
Here is the statement:
xp_cmdshell 'dtexec /SQ NatlAcctsImport /SER CR1-SQL-01 /CONN SourceConnectionFlatFile;Q:BINATNLACCTS'
Here is the result:
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
NULL
Started: 9:50:56 AM
Progress: 2006-02-10 09:50:57.40
Source: Data Flow Task
Validating: 0% complete
End Progress
Progress: 2006-02-10 09:50:57.40
Source: Data Flow Task
Validating: 50% complete
End Progress
Progress: 2006-02-10 09:50:57.42
Source: Data Flow Task
Validating: 100% complete
End Progress
Progress: 2006-02-10 09:50:57.43
Source: Data Flow Task
Validating: 0% complete
End Progress
Progress: 2006-02-10 09:50:57.43
Source: Data Flow Task
Validating: 50% complete
End Progress
Progress: 2006-02-10 09:50:57.43
Source: Data Flow Task
Validating: 100% complete
End Progress
Progress: 2006-02-10 09:50:57.43
Source: Data Flow Task
Prepare for Execute: 0% complete
End Progress
Progress: 2006-02-10 09:50:57.43
Source: Data Flow Task
Prepare for Execute: 50% complete
End Progress
Progress: 2006-02-10 09:50:57.43
Source: Data Flow Task
Prepare for Execute: 100% complete
End Progress
Progress: 2006-02-10 09:50:57.43
Source: Data Flow Task
Pre-Execute: 0% complete
End Progress
Warning: 2006-02-10 09:50:57.43
Code: 0x80070003
Source: Data Flow Task Source - NATNLACCTS [1]
Description: The system cannot find the path specified.
End Warning
Error: 2006-02-10 09:50:57.43
Code: 0xC020200E
Source: Data Flow Task Source - NATNLACCTS [1]
Description: Cannot open the datafile "Q:BINATNLACCTS".
End Error
Error: 2006-02-10 09:50:57.43
Code: 0xC004701A
Source: Data Flow Task DTS.Pipeline
Description: component "Source - NATNLACCTS" (1) failed the pre-execute phase and returned error code 0xC020200E.
End Error
Progress: 2006-02-10 09:50:57.43
Source: Data Flow Task
Pre-Execute: 50% complete
End Progress
Progress: 2006-02-10 09:50:57.43
Source: Data Flow Task
Cleanup: 0% complete
End Progress
Progress: 2006-02-10 09:50:57.43
Source: Data Flow Task
Cleanup: 50% complete
End Progress
Progress: 2006-02-10 09:50:57.43
Source: Data Flow Task
Cleanup: 100% complete
End Progress
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 9:50:56 AM
Finished: 9:50:57 AM
Elapsed: 0.985 seconds
NULL
Hi all,
I have a serious problem with my SSIS Package while executing using 32-bit DTExec and 64-bit DTExec.
Here are the details:
Environment:
Windows Server 2003 64-bit (Build 3790: Service Pack 2)
SSIS 32-bit & 64-bit installed
SQL Server 2005 (Microsoft SQL Server 2005 - 9.00.1399.06 (X64) - RTM)
SSIS Package details (compiled in 64 bit)
Script tasks only
Microsoft Visual Basic .NET (using TRY...CATCH block)
PreCompileScriptIntoBinaryCode = TRUE
Run64BitRunTime = TRUE
Execution
Batch file that uses DTExec to execute the Package.
SCENARIO
I am trying to exeucte the above SSIS package using both 32-bit and 64-bit DTExec to make it failure by providing invalid connection string. Here are the details,
Wrong connection String using 32-bit Execution
While establishing the connection the error message has been nicely captured in to my Exception block and writes into the log file.
Wrong connection String using 64-bit Execution
While establishing the connection the error has not been catpured anywhere (although I have TRY CATCH block) and it haults there itself with the message "Process is terminated due to StackOverflowException". Later I found that the error is due to the connection string along with the unhandled exception.
Please suggest any one of the following my findings, also if you have any other advice would be very much appreciated.
1. Shall I go ahead and fix the issue by handling those unhandled errors? (e.g Appdomain, application). I tried several but still not working using 64-bit DTExec.
2. Shall I go ahead and use 32-bit DTExec to execute the package? If so, is there any other major issue...like performance or anyother bug?
P.S: We cannot apply any service pack for SQL Server 2005 at the moment. Sorry abt it. If you have any specific hotfix for DTExec (without affecting SQL Server) then we can decide.
Sorry for the lengthy one and Thanks very much for you help in advance .
Thanks again!
Suresh
Hi,
I have a package which has 2 file system tasks and 2 data flow tasks all in a for each loop container. if i execute the package from the development studio its executes fine but when i try to run it from the command prompt using the dtexec utility..it just runs upto the first data flow task and then it hangs unexpectedly.
Any help on this would be really appreciated.
Thanks
Aashna
Hi,
I am running my package in this way:
exec xp_cmdshell 'dtexec /SQL "SBLoadExcelDBLog" /SERVER test /USER **** /PASSWORD ***** /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW
/LOGGER "{6AA833A1-E4B2-4431-831B-DE695049DC61}";"Test.SuperBowl"
/Set Package.Variables[User::varExcelFileName].Properties[Value];"aaab"
/Set Package.Variables[User::varExcelWorkbookName].Value;"Sheet1$"
/Set Package.Variables[User::varExcelFileFullPath].Value;"D: estshareaaab.xls"
/Set Package.Variables[User::varDestinationTableName].Value;"FeaturesTmp"
/Set Package.Variables[User::varPreSQLAction].Value;"delete from FeaturesTmp" '
I got errors:
Started: 3:49:51 PM
Progress: 2006-01-30 15:49:52.34
Source: Extract AdHoc Data from Excel
Validating: 0% complete
End Progress
Error: 2006-01-30 15:49:52.46
Code: 0xC0202009
Source: Extract AdHoc Data from Excel Excel Source [649]
Description: An OLE DB error has occurred. Error code: 0x80040E37.
End Error
Error: 2006-01-30 15:49:52.46
Code: 0xC02020E8
Source: Extract AdHoc Data from Excel Excel Source [649]
Description: Opening a rowset for "Sheet1$" failed. Check that the object exists in the database.
End Error
Error: 2006-01-30 15:49:52.51
Code: 0xC004706B
Source: Extract AdHoc Data from Excel DTS.Pipeline
Description: "component "Excel Source" (649)" failed validation and returned validation status "VS_ISBROKEN".
End Error
Progress: 2006-01-30 15:49:52.51
Source: Extract AdHoc Data from Excel
Validating: 25% complete
End Progress
Error: 2006-01-30 15:49:52.51
Code: 0xC004700C
Source: Extract AdHoc Data from Excel DTS.Pipeline
Description: One or more component failed validation.
End Error
Error: 2006-01-30 15:49:52.51
Code: 0xC0024107
Source: Extract AdHoc Data from Excel
Description: There were errors during task validation.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 3:49:51 PM
Finished: 3:49:52 PM
Elapsed: 0.703 seconds
Hi everybody.
I'm trying to run a package, stored in MSDB, using dtexec utility from cmd.
I do this using "dtexec /sq Main_Package /ser local /user sa /Password Oracle"
But all I got is:
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.3042.00 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 12:14:15
Could not load package "Main_Package" because of error 0xC0014062.
Description: The LoadFromSQLServer method has encountered OLE DB error code 0x80004005 (Login timeout expired). The SQL statement that was issued has failed.
Source:
Started: 12:14:15
Finished: 12:14:34
Elapsed: 18.297 seconds
It runs perfectly from "Integration Services" directly(SQL Management Studio), but not from cmd.
I tried to run it withuot login and password, the same result
Any ideas?
Thanks.
Hi,
I have a file-deployed package which I need to call N times at once using DTEXEC. When I attempt to execute N instances of the package, the second instance doesn't execute. I can execute different packages at the same time, but never the same package more than once. Does anyone know how to get this scenario working? Just to clarify a bit more - I can call the package as many times as I like as long as I wait until the previous run of DTEXEC is finished. Please help!
Thanks,
dcb99
I am executing a package via vb.net with package.execute using the IDTSEvents interface inheriting DefaultEvents. I am trying to emulate DTExec functionality. The package runs fine and I am catching events, but I don't get nearly as many info and progress events as are put out by DTExec. From my understanding DTExec also used the managedDTS application object, is that not the case? If it uses this does anyone have an ideas on how it is grabbing more events? For example I don't get these events that DTExec shows ..
Info: 2006-09-26 14:22:27.97
Code: 0x40016041
Source: DWB02130
Description: The package is attempting to configure from the XML file "D:SSISConfigurationsDatabase.dtsConfig".
End Info
Info: 2006-09-26 14:22:28.05
Code: 0x40016040
Source: DWB02130
Description: The package is attempting to configure from SQL Server using the configuration string ""Configurations Database";"[dbo].[SSISConfigurations]";"DWB02130";".
End Info
I also don't get as many progress updates during validation, I basically get 0 and 100% while DTExec shows several 1%, 3%, 10%, etc.
Any help is greatly appriciated.
Thanks!
Harry
Hi,
If this is the syntax to set a property in a package using dtexec:
/set PackageMyForEachLoopMyDataFlow.Disable;False
What is the syntax for setting a global variable in the package?
Thanks
Hi all,
Here's is the situation :
I have a script component that uses the aspnet membership provider to create users from a csv file in an aspnetdb database. In my Business Intelligence Developpement Studio (BIDS) everything works well. I added the aspnet membership provider in DtsDebugHost.exe.config and when I run the package from the BIDS it does what it have to do.
The problems begin when I deploy the package in file system as well as in sql server. When I run it doesn't use the DTExec.exe.config to find where are added the membership provider.
If I refer to that post : http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=541086&SiteID=1
When I run the package from SQL Server it should use the DTExec.exe.config as an app.config for my script component. But it doesn't use it at all and stop with an error that have nothing to do with that. I'm sure that it doesn't use because even when I have a wrong DTExec.exe.config it shows the same error.
If some one knows something about that it will be really great. I need to force my package when it's in stored packages to use one of the *.config as an app.config file.
Hoep it's clear
Thanks in advance for all help
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.
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
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 RelatedIt looks like you can run an SSIS package via the DTEXEC command. You can also specifiy a configuraiton file flag on the command. Is there anyway to specify a configuraiton filter if you stored it into a table vs and xml file?
View 1 Replies View Relatedthanks in advance
Hi, I am web developer. But for the last few weeks i have been working on SQL2005. I actually exported data from a table (SQL 2000) into the Excel sheet and then i have been trying to import data from Excel sheet into another table(SQL 2005). For this i had created SSIS package successfully. I had added this into integration services project(MicroSoft). After pressing CTRL+F5. I am repeatedly getting the following error
DTExec:The Package Execution returned DTSER_FAILURE<1>
Please help me because requirement is urgent.
Hello,
I'm having an odd issue trying to run an SSIS package on a 64 Bit SQL 2005 SP2 (build 3042) Windows 2003 SP2 64 bit AMD Cluster. I have to use the dtexec cmd line utility from SQL Server Agent because I need to connect to an Access database. When running the package from dtexec, it doesn't seem to be able to load the package, i keep getting this error:
Executed as user: USSI-CORPsql02admin. ...00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 1:56:00 PM Error: 2008-03-03 13:56:01.28 Code: 0xC0010018 Source: {DFBF51D9-DF81-41B9-9370-9DFC68E28FBA} Description: Error loading value "<DTSropertyExpression xmlnsTS="www.microsoft.com/SqlServer/Dts" DTS:Name="Subject">@[User::vString]</DTSropertyExpression>" from node "DTSropertyExpression". End Error Error: 2008-03-03 13:56:01.28 Code: 0xC0010018 Source: {DFBF51D9-DF81-41B9-9370-9DFC68E28FBA} Description: Error loading a task. The contact information for the task is "Send Mail Task; Microsoft Corporation; Microsoft SQL Server v9; ? 2004 Microsoft Corporation; All Rights Reserved;http://www.microsoft.com/sql/support/default.asp;1". This happens when loading a task fails. End Error Error: 2008-03-03 13:56:01.53 Code: 0xC001000E Source: TestMsgBox Description: The connection... Process Exit Code 1. The step failed.
The @[User::vString is a string variable in the package, and this particular package is actually just using the SendMail task without using an Access DB, as i'm trying to just get the dtexec utility to work. I believe i'm either doing something wrong or have found a bug, as i've used this in the past at another company without any issue.
Here's the cmd line that I'm using (I'm using the 32 bit version of dtexec):
E:Progra~1Micros~190DTSBinndtexec.exe /SQL "TestMsgBox" /SERVER D1PSQL02V /CHECKPOINTING OFF /REPORTING EW
The version of the dtexec file is: 2005.90.3042.0
Any help would be greatly appreciated.
Andy
Hello,
I want execute package with SQL Server Agent. I create a job and I try all the solutions : my job doesn't want to work.
I success to launch the package but the execution failed.
I try the same job launching from the server : it doesn't work either.
So, I copy the command line (/FILE, /DTS or /SQL) and with DTEXEC utility it works when this command is executed on the server (on a client computer, the problem "the product level is insufficient" appears).
Why is it working with this utility and not with a job ?
I try to change every secured option (credential --> proxy --> Run as ; change the owner of the job ; etc.)
What options must be checked and modified ?
Thanks for your help !
I am finishing a port of a project that was done with RS2000/AS2000/DTS2000. The cube process is triggered remotely by a Korn Shell script from Services for Unix that runs a DTS package:
dtsrun.exe /S "$ANALYSIS_SERVICES_HOST" /U "$ANALYSIS_SERVICES_USER" /P "$ANALYSIS_SERVICES_PASSWORD" /N "$PACKAGE_NAME"
I have finished porting the AS and RS parts to their 2005 equivalents and now I am trying to get the cube processing converted. I have built a SSIS package that processes the cube and I have deployed it to the SQL Server in MSDB. When I am logged on to the SQL Server box directly, I can execute the package via the Management Studio and from the command line via:
dtexec /Ser "$ANALYSIS_SERVICES_HOST" /DT "$PACKAGE_PATH_NAME"
When I try to register the SQL Server in Integration Services from my client PC Management Studio it fails with "Access Denied". When I try to run the dtexec from my client PC it fails with "Access Denied".
I have walked through Kirk Haselden's instructions at:
http://sqljunkies.com/WebLog/knight_reign/archive/2006/01/05/17769.aspx
(adding myself to Distributed COM Users, noting the correct DCom Config settings for MsDtsServer and restarting the Integration Services service) and the problem is still not resolved. I have posted to the DTS group on Technet and now I am posting here. This can not be run scheduled by SQL Agent as it needs to be triggered by other load/postload activities. Are there any other fixes for this problem that I can try?
Thanks,
Keehan
By the way, being able to have all the parts of this project in one dev environment is great. The previous application was developed in 3 separate locations which was pretty clunky.
hi!
i am trying to execute through command line the following:
dtexecui /SQL "ArchiveMain" /SERVER "SE413695AASQL2005" /WARNASERROR /MAXCONCURRENT " -1 " /CHECKPOINTING OFF
I am actaully creating an archiving package.
1. All it does is get some data from the Table.
2. creating directory and set usedirectoryifexist = true
shows some warning when i run that directory already exists cos i don't want to create new one i want to use the existing one.
3. actual archiving to get call to another package for archiving - warning while running the package that parent variable is missing
The pacakge seems to run while i execute it thorugh command prompt but i get this message
DTExec: The package execution returned DTSER_SUCCESS (0) but had warnings, with
warnings being treated as errors!
Thanks,
Jas
Running eval. edition of Sql Server Standard 2005. "Insufficient product level" error is thrown during validation phase of an OleDBCommand data flow task. This task type is not licensed in Sql Server 2005 standard? The component runs a very simple sql update statement against a one row table in Sql Server 2005.
If it works from BIDS, should it not work from dtexec.exe on the same box?
Does dtexec run under the security context of the logged in user?
Thanks,
Ken
I have this problem executing an SSIS pkg using dtexec.
dtexec /f d:smappsssis_pkgsdts_SOAR.dtsx /REP E
Code Snippet
Started: 10:28:58 PMEnd Error
Error: 2008-03-03 22:28:59.00
Code: 0xC0202009
Source: dts_SOAR Connection manager "SourceConnectionExcel"
Description: An OLE DB error has occurred. Error code: 0x80040154.
An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".
End Error
Error: 2008-03-03 22:28:59.00
Code: 0xC020801C
Source: Data Flow Task Source - CHANNEL_DATA [1]
Description: The AcquireConnection method call to the connection manager "SourceConnectionExcel" failed with error code 0xC0202009.
Code Snippet
However, it runs fine when I double click the package and execute it using the Execute package utility. That really puzzles me.
The package transfers data from worksheets in excel to SQL Server 2005 DB.
Both source and destination are on the same system.
Please advise.
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
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
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
Is it possible to execute a package in a trigger or stored procedure ?
View 3 Replies View RelatedCan 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
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
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
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...
I'm looking for an example of how to execute an existing DTS* packagefrom anASP (VB)script and would appreciate any and all response. *I don'tevenknow if it's possibleThanks- Chuck GattoDan Guzman Apr 27 2000, 12:00 am show optionsNewsgroups: comp.databases.ms-sqlserverFrom: "Dan Guzman" <DGuz...@nospamplease-earthlink.net&g*t; - Findmessages by this authorDate: 2000/04/27Subject: Re: Executing a DTS Package using an ASP (VBScript) ScriptReply to Author | Forward | Print | Individual Message | Show original| Report AbuseThis VBScript example loads and executes an existing DTS pac*kage fromSQLServer.Option ExplicitConst PackageName = "PackageName"Const ServerName = "ServerName"Const UserName = "UserName"Const Password = "Password"Dim DTSPkSet DTSPk = CreateObject("dts.package")DTSPk.LoadFromSQLServer ServerName, UserName ,Password,,,,,*PackageNameDTSPk.ExecuteIf DTSPk.Steps(1).ExecutionResult = 0 ThenResponse.Write "Package execution completed"ElseResponse.Write "Package execution failed"End IfSet DTSPk = NothingYou can also create the entire package from scratch from wit*hin yourasp andexecute it.Hope this helps.Chuck Gatto <cga...@anchorsystems.com> wrote in messagenews: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 froman[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 optionsNewsgroups: comp.databases.ms-sqlserverFrom: "Chuck Gatto" <cga...@anchorsystems.com> - Find messages by thisauthorDate: 2000/05/01Subject: Re: Executing a DTS Package using an ASP (VBScript) ScriptReply to Author | Forward | Print | Individual Message | Show original| Report AbuseBelow 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)aswell but I can't be sure.Any idea what I should look for.Thanks"Dan Guzman" <DGuz...@nospamplease-earthlink.net> wrote in m*essagenews: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 aspand[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]packagefrom[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 optionsNewsgroups: comp.databases.ms-sqlserverFrom: "Dan Guzman" <DGuz...@nospamplease-earthlink.net&g*t; - Findmessages by this authorDate: 2000/05/01Subject: Re: Executing a DTS Package using an ASP (VBScript) ScriptReply to Author | Forward | Print | Individual Message | Show original| Report AbuseYou can specify a trusted connection with flag 256 instead o*fusername andpassword. For example:DTSPk.LoadFromSQLServer ServerName, , , 256,,,,PackageNa*meAssuming this is an intranet application running under NT 4.*0 and youwantto execute the package under the invoking user's account, yo*u can dothis asfollows:Specify 'clear text' for the IIS Directory Securityauth*enticationRemove 'Everyone' from the access list on the files (req*uiresNTFS) andgrant permissions to the usersGrant logins access to the database serverWith this method, users must enter their DomainUserName and* passwordwhenprompted.NT authentication presents a challenge when multiple servers* areinvolvedbecause NT 4.0 does not support delegation. Seehttp://msdn.microsoft.com/workshop/...re/security.asp fordetails.I understand Windows 2000 provides delegation capabilities b*ut thiscan be abit tricky to implement.BTW, if your DTS package does not access SQL Server, you can* save itto afile and use the LoadFromStorageFile method instead.Hope this helps.If you need to useChuck Gatto <cga...@anchorsystems.com> wrote in messagenews: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 optionsNewsgroups: comp.databases.ms-sqlserverFrom: "Chuck Gatto" <cga...@anchorsystems.com> - Find messages by thisauthorDate: 2000/05/06Subject: Re: Executing a DTS Package using an ASP (VBScript) ScriptReply to Author | Forward | Print | Individual Message | Show original| Report AbuseHey DanThanks 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* virusatacksidetracked me.Thanks again."Dan Guzman" <DGuz...@nospamplease-earthlink.net> wrote in m*essagenews: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 thisas[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 bea[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 optionsNewsgroups: comp.databases.ms-sqlserverFrom: chris.duni...@agwsha.nhs.uk - Find messages by this authorDate: 31 Jan 2005 10:05:00 -0800Subject: Re: Executing a DTS Package using an ASP (VBScript) ScriptReply | Reply to Author | Forward | Print | Individual Message | Showoriginal | Report AbuseHi,I've tried adding this function to my ASP pages and get the *followingerror message;Microsoft VBScript runtime error '800a01ad'ActiveX component can't create object: 'DTS.Package'/asp/pages/dts.asp, line 21Does 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 View Related