Weird Dtexec Errors
Jun 18, 2007
I receive the error below when running a package using dtexec. The package itself runs ok, however. That is, my data loads into the table.
All this package does is execute 3 separate Execute SQL tasks that are simple insert statements into a table.
There are NO script tasks or components in the package. So what is this weird error about?
Error: 2007-06-18 18:01:49.36
Code: 0xC0012024
Source: Script Task
Description: The task "Script Task" cannot run on this edition of Integration
Services. It requires a higher level edition.
End Error
Warning: 2007-06-18 18:01:49.36
Code: 0x80019002
Source: OnPostExecute
Description: The Execution method succeeded, but the number of errors raised
(2) reached the maximum allowed (1); resulting in failure. This occurs when the
number of errors reaches the number specified in MaximumErrorCount. Change the M
aximumErrorCount or fix the errors.
End Warning
View 4 Replies
ADVERTISEMENT
Oct 30, 2006
SQL Server 2000 (DDL below)If I try to run this code in QA:SET IDENTITY_INSERT tblAdminUsers ONINSERT INTO tblAdminUsers(fldUserID,fldUsername,fldPassword,fldFullname,fldPermission,fldEmail,fldInitials,fldLastLogon,fldBatch)SELECTfldUserID,fldUsername,fldPassword,fldFullname,fldPermission,fldEmail,fldInitials,fldLastLogon,fldBatchFROM[BSAVA_26-10-2006].dbo.tblAdminUsersSET IDENTITY_INSERT tblAdminUsers OFFI get an error:IDENTITY_INSERT is already ON for table'BSAVA_Archive_Test_2006.dbo.GPS_CHAR'. Cannot perform SET operationfor table 'tblAdminUsers'.If I try to run:INSERT INTO tblAdminUsers(fldUserID,fldUsername,fldPassword,fldFullname,fldPermission,fldEmail,fldInitials,fldLastLogon,fldBatch)SELECTfldUserID,fldUsername,fldPassword,fldFullname,fldPermission,fldEmail,fldInitials,fldLastLogon,fldBatchFROM[BSAVA_26-10-2006].dbo.tblAdminUsersI get the error:Cannot insert explicit value for identity column in table'tblAdminUsers' when IDENTITY_INSERT is set to OFF.Anyone any ideas? FYI the tables I'm INSERTing into were scripted fromthe [BSAVA_26-10-2006] tables.TIAEdward=====================if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[tblAdminUsers]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)drop table [dbo].[tblAdminUsers]GOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[GPS_CHAR]') and OBJECTPROPERTY(id, N'IsDefault') =1)drop default [dbo].[GPS_CHAR]GOcreate default dbo.GPS_CHAR AS ''CREATE TABLE [dbo].[tblAdminUsers] ([fldUserID] [int] IDENTITY (1, 1) NOT NULL ,[fldUsername] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,[fldPassword] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,[fldFullname] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,[fldPermission] [smallint] NULL ,[fldEmail] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,[fldInitials] [varchar] (3) COLLATE Latin1_General_CI_AS NULL ,[fldLastLogon] [smalldatetime] NULL ,[fldBatch] [char] (1) COLLATE Latin1_General_CI_AS NULL) ON [PRIMARY]GO
View 10 Replies
View Related
Jan 15, 2008
So what I'm trying to do is audit changes on a server. I'm creating a DDL trigger as below:
Code Block
CREATE trigger DDL_changeTracking_tr
on Database
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE,
CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION,
CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,
CREATE_TRIGGER, ALTER_TRIGGER, DROP_TRIGGER,
CREATE_VIEW, ALTER_VIEW, DROP_VIEW
as
SET NOCOUNT ON
SET ANSI_WARNINGS ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET NUMERIC_ROUNDABORT OFF
SET QUOTED_IDENTIFIER ON
BEGIN TRY
BEGIN
declare @login varchar(100)
set @login = eventData().value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(100)')
if (@login <> 'sqladmin' and @login <> 'sqlagentadmin')
BEGIN
insert into DBMonitoring..audit_tbl (databaseId, auditTime, loginName, objectName, objectType, eventType)
select
DB_ID() as databaseId
, getDate() as auditTime
, eventData().value('(/EVENT_INSTANCE/SchemaName)[1]', 'varchar(100)') + '.' +
eventData().value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(100)') as objectName
, eventData().value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(100)') as objectType
, eventData().value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(100)') as LoginName
, eventData().value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(100)') as eventType
END
END
END TRY
BEGIN CATCH
BEGIN
declare @html varchar(max)
select @html = '<html>' + getDate() + '</br>' + eventData().value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(100)')
+ '</br>' + ERROR_MESSAGE() + '</html>'
PRINT 'Warning: Unable to submit change to audit'
SELECT ERROR_MESSAGE()
exec util_EmailOut_DatabaseMail_prc @from = '<address>',
@to = '<address>',
@cc = null,
@bcc = null,
@subject = 'Change Tracking Insert Failure',
@body = null,
@HTMLBody = @html,
@importance = 1,
@file = null
END
END CATCH
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER DDL_changeTracking_tr ON DATABASE
It inserts the trigger data into:
Code Block
CREATE TABLE audit_tbl (
databaseId int not null,
--auditTime datetime default getDate() not null,
auditTime datetime not null,
loginName varchar(255) not null,
objectName varchar(255) not null,
objectType varchar(25) not null,
eventType varchar(40) not null
)
go
ALTER TABLE audit_tbl ADD CONSTRAINT PK_audit_tbl_databaseId_auditTime_objectName PRIMARY KEY (databaseId, objectName, auditTime)
CREATE NONCLUSTERED INDEX IX_audit_tbl_auditTime_loginName ON audit_tbl(auditTime, loginName)
CREATE NONCLUSTERED INDEX IX_audit_tbl_auditTime_objectType ON audit_tbl(auditTime, objectType)
In the same database that I've run this one, I'm running this code to test it:
Code Block
create procedure cow_prc
as select 1
go
drop procedure cow_prc
Occassionally when I run this, I get the following error:
Msg 241, Level 16, State 1, Procedure DDL_changeTracking_tr, Line 42
Conversion failed when converting datetime from character string.
I am completely lost on this. I've had 3 fellow DBAs look at it and they're not sure what's going on with it. I've even tried writing the trigger logic as a CTE which using isDate() to make sure that auditTime actually is a date.
Any insight would be greatly appreciated. Thanks in advance.
View 6 Replies
View Related
Aug 31, 2006
Hey there all,
i am having a weird problem with the send mail task in SSIS. I have tried to different things, and i end up with two different errors:
Firstly, i have setup a data dump to excel, and the send mail taks emails this to specific email addresses.
In the Send mail task i have validated to SMTP server, and its correct.
I have manually entered all the information in the Send mail task, and i am sending to multiple email addresses. These are all seperated by a semi colan. I run the task and it fails on the send mail task with the follwoing error:
Error: 0xC002F304 at Send Mail Task, Send Mail Task: An error occurred with the following error message: "Unable to send to all recipients.".
Task failed: Send Mail Task
I have validated all the email address and they are correct. I did some searching and someone suggested to replace the semi colan with a comma. I do this and i get the follwoing error"
Error: 0xC002F304 at Send Mail Task, Send Mail Task: An error occurred with the following error message: "Mailbox unavailable. The server response was: 5.7.1 Unable to relay for rpwallis@bigpond.com.au".
I have checked that the IP for the SQL server is on the list of allowed relays on our exchange server. Does it make a difference if i am running this from Visual studio on my laptop?? by this, does it pick up the ip of my laptop when i test this or does it use the ip address of the server?? This would explain the relay message error if so..
Could someone please explain if i should use comma's or semi colans to seperate email addresses? and also lead me in the right direction in relatio to my problems..
Many thanks in advance
Scott Lancaster
View 3 Replies
View Related
Jan 11, 2008
Hi,
I have stumbled on a problem with running a large number of SSIS packages in parallel, using the €œdtexec€? command from inside an SQL Server job.
I€™ve described the environment, the goal and the problem below. Sorry if it€™s a bit too long, but I tried to be as clear as possible.
The environment:
Windows Server 2003 Enterprise x64 Edition, SQL Server 2005 32bit Enterprise Edition SP2.
The goal:
We have a large number of text files that we€™re loading into a staging area of a data warehouse (based on SQL Server 2k5, as said above).
We have one €œmain€? SSIS package that takes a list of files to load from an XML file, loops through that list and for each file in the list starts an SSIS package by using €œdtexec€? command. The command is started asynchronously by using system.diagnostics.process.start() method. This means that a large number of SSIS packages are started in parallel. These packages perform the actual loading (with BULK insert).
I have successfully run the loading process from the command prompt (using the dtexec command to start the main package) a number of times.
In order to move the loading to a production environment and schedule it, we have set up an SQL Server Agent job. We€™ve created a proxy user with the necessary rights (the same user that runs the job from command prompt), created an the SQL Agent job (there is one step of type €œcmdexec€? that runs the €œmain€? SSIS package with the €œdtexec€? command).
If the input XML file for the main package contains a small number of files (for example 10), the SQL Server Agent job works fine €“ the SSIS packages are started in parallel and they finish work successfully.
The problem:
When the number of the concurrently started SSIS packages gets too big, the packages start to fail. When a large number of SSIS package executions are already taking place, the new dtexec commands fail after 0 seconds of work with an empty error message.
Please bear in mind that the same loading still works perfectly from command prompt on the same server with the same user. It only fails when run from the SQL Agent Job.
I€™ve tried to understand the limit, when do the packages start to fail, and I believe that the threshold is 80 parallel executions (I understand that it might not be desirable to start so many SSIS packages at once, but I€™d like to do it despite this).
Additional information:
The dtexec utility provides an error message where the package variables are shown and the fact that the package ran 0 seconds, but the €œMessage€? is empty (€œMessage: €œ).
Turning the logging on in all the packages does not provide an error message either, just a lot of run-time information.
The try-catch block around the process.start() script in the main package€™s script task also does not reveal any errors.
I€™ve increased the €œmax worker threads€? number for the cmdexec subsystem in the msdb.dbo.syssubsystems table to a safely high number and restarted the SQL Server, but this had no effect either.
The request:
Can anyone give ideas what could be the cause of the problem?
If you have any ideas about how to further debug the problem, they are also very welcome.
Thanks in advance!
Eero Ringmäe
View 2 Replies
View Related
Apr 17, 2008
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
View 13 Replies
View Related
Jul 1, 2015
I recently updated the datatype of a sproc parameter from bit to tinyint. When I executed the sproc with the updated parameters the sproc appeared to succeed and returned "1 row(s) affected" in the console. However, the update triggered by the sproc did not actually work.
The table column was a bit which only allows 0 or 1 and the sproc was passing a value of 2 so the table was rejecting this value. However, the sproc did not return an error and appeared to return success. So is there a way to configure the database or sproc to return an error message when this type of error occurs?
View 1 Replies
View Related
Jul 31, 2006
I have a parent package that calls child packages inside a For Each container. When I debug/run the parent package (from VS), I get the following error message: Warning: The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
It appears to be failing while executing the child package. However, the logs (via the "progress" tab) for both the parent package and the child package show no errors other than the one listed above (and that shows in the parent package log). The child package appears to validate completely without error (all components are green and no error messages in the log). I turned on SSIS logging to a text file and see nothing in there either.
If I bump up the MaximumErrorCount in the parent package and in the Execute Package Task that calls the child package to 4 (to go one above the error count indicated in the message above), the whole thing executes sucessfully. I don't want to leave the Max Error Count set like this. Is there something I am missing? For example are there errors that do not get logged by default? I get some warnings, do a certain number of warnings equal an error?
Thanks,
Lee
View 5 Replies
View Related
Apr 20, 2006
Starwin writes "when i execute DBCC CHECKDB, DBCC CHECKCATALOG
I reveived the following error.
how to solve it?
Server: Msg 8909, Level 16, State 1, Line 1 Table error: Object ID -2093955965, index ID 711, page ID (3:2530). The PageId in the page header = (34443:343146507).
. . . .
. . . .
CHECKDB found 0 allocation errors and 1 consistency errors in table '(Object ID -1635188736)' (object ID -1635188736).
CHECKDB found 0 allocation errors and 1 consistency errors in table '(Object ID -1600811521)' (object ID -1600811521).
. . . .
. . . .
Server: Msg 8909, Level 16, State 1, Line 1 Table error: Object ID -8748568, index ID 50307, page ID (3:2497). The PageId in the page header = (26707:762626875).
Server: Msg 8909, Level 16, State 1, Line 1 Table error: Object ID -7615284, index ID 35836, page ID (3:2534). The PageId in the page heade"
View 1 Replies
View Related
Oct 12, 2006
Hello,
I have a command line as following, with DTEXEC to launch the execution of a package and to set a value (13335) of an user variable called CIB (string type):
dtexec /f c: empPackageInsert.dtsx
/set PackageDataFlowTask.Variables[Utilisateur::CIB].Properties[Value];13335
But I have got an error message saying that the object is not known in the package. My variable does exist in the variable window of the dataflow part.
Thank you for telling me what to set so that the variable can be set by the command line.
Regards,
Marie-Thérèse
View 5 Replies
View Related
Oct 13, 2006
i am using dtexec in command prompt
it always prompt the path is not valid and error is 0x80070057
below is what i input
dtexec /dts c:ssis******.dtsx
what's wrong? thanks
View 3 Replies
View Related
May 4, 2006
I understand to schedule an SSIS package to run, I need to use the dtexec utility. I want to schedule the job right within SQL. I walked through how to set up the job, but I really could not decipher the proper syntax to use as a Step.
I have the following:
dtexec /File "C:...file.dtsx"
Is the the proper way to schedule and execute an SSIS package or is there some other way I should be doing this. The pacakge will run unattended nightly. I am using SQL Server 2005.
Thanks for the information
View 1 Replies
View Related
Mar 26, 2008
Folks:
I have lots of Stored procedures in which we use 'dtsrun' to run DTS Packages. Now, after I upgrade the server to SQL 2005 will I have to change all the Stored procedure's to reflect 'dtexec' instead of 'dtsrun'?
Thanks !
View 1 Replies
View Related
Jan 30, 2006
Hi
I have a SSIS package which pulls files from a network share and loads data into SQLServer Database. When I execute the application using DTExecUI , It runs fine without any issues , but where as when I run it using the command line arguments, It seems to go in sleep mode and nothing happens. I need to kill the package from Task manager.
Following is the command I use to run my application
Dtexec /FILE "N:TempLoadFirewallData.dtsx" /CONFIGFILE "N:TempLoadFirewallData.dtsConfig" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EWCDI /SET "Package.Variables[User::RunID]";41
Regards
Meghana
View 8 Replies
View Related
Aug 6, 2007
This was directly from BOL.
/Conf[igFile] filespec
(Optional). Specifies a configuration file to extract values from. Using this option, you can set a run-time configuration that differs from the configuration that was specified at design time for the package. You can store different configuration settings in an XML configuration file and then load the settings before package execution by using the /ConfigFile option.
Does this mean that I can specify which configuration file to use during runtime?
Or is just because I'm too desperate for that, I understood that way
Thanks
View 14 Replies
View Related
Feb 2, 2006
Hi,
The DTEXEC Utility has the capability of returning ReturnCodes which have specific meanings e.g.
ReturnCode=3 means: The package was canceled by the user.
Is it possible to set my own ReturnCode values?
For example, my Package contains a Script Task which contains code to search a folder for a file. If the file cannot be found then I make the Script Task fail and hence the Package fails. But, when the Package is invoked by the DTEXEC utility, then the ReturnCode is always set to 1. Is it posssible to set the ReturnCode from within the Package (in, say, a Script Task in the Event Handler) to a different value?
It is necessary to be able to set custom application-specific return codes because these return codes are passed to the Batch Scheduler which alerts Operations (Support) staff in the event of failure. Meaningful (custom) ReturnCodes expedite problem solving and are (usually) mandatory in large production environments.
If it is not possible to make a Package return my own returncodes via DTEXEC then can you suggest alternative solutions, please?
Thanks.
View 1 Replies
View Related
Jul 26, 2006
Hi!
When i excute in the sql server this works fine
xp_cmdshell 'dtexec /f "D:SSISProjectIntegration Services Project1ArchiveTicket.dtsx "'
but when i excute throught the C# code
jobCommand = new SqlCommand(@"xp_cmdshell 'dtexec /f "D:SSISProjectIntegration Services Project1ArchiveTicket.dtsx"'", cconn);
It shows up red lines near the " and ' qoutes. I know that quotation marks are wrong but don't know how to solve it.
some help please
Thanks
jas
View 4 Replies
View Related
Feb 10, 2007
Hi All,
I'm trying to execute DTExec from a workstation and I got some help from a different group without luck maybe someone here can help me.
This is what I try so far.
1. My package run in command line from my sql box using dtexec with
parameters.
2. Set the package with DontSaveSensitive and import into IS under MSDB with
the same option setup.
3. Set package role to public.
4. Share DTS folder with everyone permission just for testing.
5. Execute the package from a workstation using
//sqlServerbox/DTS/BINN/dtexec /dts "msdb/mypackage" /SER "MySQLServer" /set
package.variables[myvariable].Value;"myvalue1,myvalue2"
(myvariable is string and I can pass multiple values separate by commas)
6. Still getting error:
Error: 2007-02-09 10:31:34.31
Code: 0xC0010018
Source: Execute DTS 2000 Package Task
Description: Error loading a task. The contact information for the task
is "E
xecute DTS 2000 Package Task;Microsoft Corporation; Microsoft SQL Server v9;
? 2
004 Microsoft Corporation; All Rights
Reserved;http://www.microsoft.com/sql/supp
ort/default.asp;1". This happens when loading a task fails.
End Error
Anyone has any ideas?
Any help will be appreciate it. Tks in advance...
Rgds
Johnny
View 5 Replies
View Related
Mar 13, 2008
I can't crack this problem and my Google-Fu is failing me. I am using DTEXEC from a stored procedure to set a global variable in an SSIS package and execute the SSIS package, but I am getting an error. I will include the SQL code and the error that I am getting:
Code:
exec xp_cmdshell 'dtexec /SQL "Maintenance PlansSynchronizeWithProduction" /SERVER servernameinstance /USER user /PASSWORD password /SET Package.Variables[User:electMail].Properties[Value];1'
Error:
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: 3:17:02 PM
Warning: 2008-03-13 15:17:02.76
Code: 0x80012017
Source: ProductionToDevelopment
Description: The package path referenced an object that cannot be found: "Package.Variables[User:electMail].Properties[Value]". This occurs when an attempt is made to resolve a package path to an object that cannot be found.
End Warning
DTExec: Could not set Package.Variables[User:electMail].Properties[Value] value to 1.
Started: 3:17:02 PM
Finished: 3:17:02 PM
Elapsed: 0.375 seconds
NULL
All the examples I find over DTEXEC seem to follow what I am doing in the code and making variables in an SSIS package isn't rocket science, so I don't understand where I am going wrong.
I have seen people suggest indirect configuration as a solution, but when I set it up (using the environmental variable TEMP from the dropdown) it doesn't seem to help anything. I have used the exact path from the configuration wizard, though, so I'm pretty sure I have the path right.
Can anybody spare a clue?
View 16 Replies
View Related
May 23, 2008
HI, with a dataflow that has delay validation property = true, DTExec will not try to validate it when I call it with /validate option. Is there a way to see if the dataflow validate even though the delay validation property is set to true?
Thanks,
View 3 Replies
View Related
Jul 5, 2006
It was quite strange with dtexec....
The problem started with my package being scheduled under the SQL Agent.
I have two packages... one with simple SQL tasks and another with complex loop,sql tasks and activex scripts. Both using the XML configuration files for those DB Connection strings, user names and passwords.
First I tried to schedule the two packages under Sql agent using SSIS steps.
Both failed and I found out that I should set the ProtectionLevel to non-default.
So I set the protection level to 'DontSaveSensitive' and it works fine with the first package but the second package failed. After I tried many way (changing configuration files, setting and reinstalling the packages inside SQL DB etc..) I found a blog that I should try with CmdExe step in Sql agent scheduler rather than SSIS step. So I tried but still failing. the message said dtexec could not find the configuration xml file. Even I tried in the command prompt with the same command, it couldn't find the configuration. If I went into the directory where the configuration files are stored and run the command, it run fine. Seem like dtexec could not read the configuration from other directory. but still the sqlagent is failing...
Somebody have a clue?
rgds,
KyawAM
View 1 Replies
View Related
Dec 28, 2006
In my previous post here: https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1044739&SiteID=1 Michael Entin provides a number of responses to my questions regarding programatic execution of remote SSIS packages.
Having experienced some significant reliability problems with the Microsoft.SqlServer.Dts.Runtime components from an ASP.NET process (the page either times out, or inevitably just stops responding), I have been prototyping the DTExec command option which Michael suggests as being a better approach to remote programability.
So, off I've been prototyping this all day today...
I have a stored procedure that wraps a call to xp_cmdshell which takes the DTS (DTEXEC) params as a big long argument. This scenario would hopefully allow me to call the sproc from an ASP.NET application.
The proc is deployed to a SQL 2005 machine running SSIS (which I now understand is a REQUIREMENT for targetting SSIS "remotely"). The package targets a seperate SQL 2000 machine and includes two connection managers which are set to use SSPI. I use configuration option files to allow for configurable connection manager target/sources.
In this scenario, it does not seem that the DTEXEC command runs in the same context as the caller. and as a result, a peculiar account called MACHINENAME$ is used (where MachineName is literally the name of the SQL 2005 machine). The account authentication fails (obviously) when the package tries to establish a connection to any of the connection managers because MACHINENAME$ does not exist on the connection manager servers.
Based on the following excerpt from the MSDN doc on xp_cmdshell, it would seem that MACHINENAME$ is probably the LOCAL SYSTEM, which is the process tha the SQL Server Service is running under:
When xp_cmdshell is invoked by a user who is a member of the sysadmin fixed server role, xp_cmdshell will be executed under the security context in which the SQL Server service is running. When the user is not a member of the sysadmin group, xp_cmdshell will impersonate the SQL Server Agent proxy account, which is specified using xp_sqlagent_proxy_account. If the proxy account is not available, xp_cmdshell will fail. This is true only for Microsoft® Windows NT® 4.0 and Windows 2000. On Windows 9.x, there is no impersonation and xp_cmdshell is always executed under the security context of the Windows 9.x user who started SQL Server.
Obviously, settting the ENTIRE SQL Server service to run as a fixed account or even a domain account is probably not appropriate for client sites. Any opinion to the contrary is welcome.
In reading Kirk Haselden's walkthrough for setting up a SQL Agent Proxy, this seems incredibly involved. Before I go through this exercise, can anyone validate that this is the way to go for doing SSPI?
A work-around is to use SQL Server Auth for the connection managers and use configuration files to try to obfuscate these details, but my preference would be SSPI/Windows Integrated.
Thanks.
View 4 Replies
View Related
Oct 23, 2007
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
View 4 Replies
View Related
Feb 19, 2007
I have a legacy extraction ("E") .NET 1.1 application that is still going to be the driving force for our ETL process. We are going to be utilizing SSIS for "T" and "L". Now, the "E" phase is running on an application server where we have .NET 1.1 framework installed and working. The SSIS packages are running on a separate SQL Server. The problem here is - how do we call the SSIS package and be able to pass in the right parameters from this .NET app that runs on a separate box? We would like to use DTEXEC to call the remote SSIS packages through Integrated Security. The SSIS packages are stored as File System packages.
View 3 Replies
View Related
Aug 7, 2007
When I try to execute a package (in sql server) using DTEXEC I get the following error. The creator of the package is different from the executing user of the package. But I have also set EncryptionLevel FROM EncryptWithUserKey TO DontSaveSensitive (Which I assume should resolve this issue), re-imported the package to sql server, but the error remains the same. Any pointers?
The utility was unable to load the requested package. The package could not be loaded.
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: 3:18:56 PM
Could not load package " est1" because of error 0xC0014062.
Description: The LoadFromSQLServer method has encountered OLE DB error code 0x80040E14 (Only the owner of DTS Package 'test1' or a member of the sysadmin role may create new versions of it.). The SQL statement that was issued has failed.
Source:
Started: 3:18:56 PM
Finished: 3:18:56 PM
Elapsed: 0.328 seconds
View 5 Replies
View Related
Feb 2, 2007
A colleague of mine is having problems when trying to schedule package execution via a .bat file that executes a .dtsx on a sql server (not file system). The scheduling system that is employed by the company requires a .bat file to execute the package. The packages themselves move data from AS400 servers to SQL Servers. Occasionally (very randomly) when the scheduling system runs the statement in the bat files, the package prompts for password information (for connectivity).
We've tried a number of solutions to this, mostly on the "ProtectionLevel" property of the package itself. We did some research and it seems as though there are a few options.What is the best solution to eliminate this from happening? We certainly don't want to have people checking to make sure the packages don't prompt for passwords.
Thanks.
View 9 Replies
View Related
Jul 9, 2007
Hi All,
When I was trying to execute an SSIS package from DTExec using xp_cmdShell, it is giving an error message saying "unrecognized command,...". This error I am getting only in my Staging Environment. But at the same time, it is working fine with Development and Production servers.
I suspect the issue should be with config or access issues. So if anyone of you faced the same problem or if anyone have any solution, please share with me.
Thanks in advance for your help.
Thanks & Regards,
Prakash Srinivasan
View 1 Replies
View Related
Jul 6, 2007
Hi all,
does anybody know if it is possible to use environment variables when calling dtexec utility?
I'd like to run packages stored on server's file system from directory that I've had specified in an environment variable called SSIS_PackagesPath.
Now, I'am trying to write dtexec command, where path to the actual SSIS package would be concatenation of environment variable (i. e. path to package directory) and name of package itself (written explicitly). Is this syntactically possible?
The reason behind is to be able to easily modify package storage directory for multiple scheduled jobs that run SSIS packages.
Any other ideas are hapilly welcomed.
Thanks,
Marek
View 3 Replies
View Related
Jan 9, 2007
I would like to send back to MS-DOS (ERRORLEVEL) an error code 2 ( different from 0 and 1) via a scripting Task.
Our package are started with the DTEXEC Utility.
In general, how can i send back a flag saying "There were warnings" during the execution of a Package
View 1 Replies
View Related
Aug 2, 2006
We've hit a weird problem that we can't resolve. We have a package using ODBC to extract from an AS400. When running the package through DTEXECUI, the package executes successfully. However, it chokes when executing through DTEXEC or as a SQL Agent job on our 64-bit machine. Seems like DTEXECUI is using the 32-bit ODBC driver while DTEXEC and SQL Agent is trying to access an non-existent 64 bit ODBC driver. We're using a DSN-less connection string (Driver={Client Access ODBC Driver (32-bit)};system=AS400;....) so that's not the issue. Anyone else seen this weird error? Any possible workarounds?
View 4 Replies
View Related
May 25, 2007
Currently, we are running a Master Package with sub-Packages that are executed as a result. We run multiple days by executing a .bat file of DTExec commands. For Example:
Code Snippet
DTExec /FILE E:ETLFinancialDataMartMaster.dtsx /SET Package.Variables[ReportingDate].Value;"1/02/2007" > etl_20070102.log
mkdir E:ETLErrorLogsArchive20070102
copy E:ETLErrorLogsProcessing*.txt E:ETLErrorLogsArchive20070102
Date values are incremented for as many days as we want to run. The log gives progress information and the Started, Finished, Elapsed time for the the Master package.
We are interested in manipulating the script entries to get the Start, Finished, Elapsed time for the sub-Packages that are initiated by this script. I think that I could use the Reporting option:
Code Snippet
/Rep[orting] level [;event_guid_or_name[;event_guid_or_name[...]]
Of course I can't find a good example to model the script. Is there anyone else using DTExec to get the run time statistics for each and every package? If so, can you forward that part of the script that accomplishes this task? BTW, we are going to implement run-time auditing to a table at some point but we are not there yet. Of course, my manager would like statistics now.
Thanks in advance.
View 10 Replies
View Related
Dec 28, 2006
I have the following command which uses the /CONFIGFILE option:
master.dbo.xp_cmdshell 'dtexec /DTS "File SystemPackagesMyPackagesMyPackage" /SERVER "MYSERVER" /CONFIGFILE "C:Program FilesMicrosoft SQL Server90DTSPackagesMyPackagescustomersite.dtsconfig" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V '
When the package runs, one of the first things it reports is that the configuration file, 'customersite.dtsconfig' cannot be found:
...
Description: The package is attempting to configure from the XML file "CustomerSite.dtsConfig".
...
Description: The configuration file "CustomerSite.dtsConfig" cannot be found. Check the directory and file name.
...
Description: Failed to load at least one of the configuration entries for the package. Check configurations entries and previous warnings to see descriptions of which configuration failed.
The package hapilly executes (and, somehow does grab the appropriate values from config (in this case, it is the SQL Login passwords).
Can anyone provide some insight into this or is this a bug that should be ignored?
View 1 Replies
View Related
Sep 24, 2007
I have a DTS Package that I am running from a command line via .bat file. Does anyone know if there is a command to have the command window minimized or running in the background? I used the /Rep N command but that still leaves the window open until the package has executed.
Thanks!
View 3 Replies
View Related
