Can A User Exec A DTS Package...
Aug 24, 2006....in SQL 2000 without having xp_cmdshell permissions.
View 2 Replies
ADVERTISEMENT
Exec Permision For Sql User
May 28, 2004I have a sqlserver user in my DB.I need to grant that user executions permission on all the stored procedures existing in the DB and alos should get the exec permission on newly created SPs in future.How can i achieve this .
Thanks.
DTS Package Exec. From Job Issue
Mar 10, 2003I built a new server from our production server. I had to modify several DTS Packages that were pointing to the old server. I successfully tested them in Enterprise Manager. When the jobs that executed these packages ran, they failed. I realized that the job was still executing the wrong version of the package.
The jobs were executing what appears to be a GUID. Once I changed the GUID value, the jobs run fine.
My question is, how can I run the steps for the DTS packages using a named reference? Is there a different/better way to run them?
Sidney Ives
Database Administrator
Sentara Healthcare
Exec Proc From DTS Package
Mar 6, 2006Hi
I'm trying to execute a Proc from DTS Package and it is rasing an error 'Invalid Pointer'.
This Proc is used to get a result set and I want to transfer this result set into a Excel File and that Excel File is to be sent to my client by Email.
Every thing was fine....but can some one suggest why I'am getting this error 'Invalid Pointer'.
:confused:
How To Exec SQL User Defined Function?
Aug 4, 2005Hi,How to exec a SQL user defined function in query analyzer when it accepts parameters.. I know for a stored procedure we can write EXEC nameofstored procedure abc (@abc is the parameter passed).. But How to run a SQL function ?Thanks
View 2 Replies View RelatedHow To Tell If User Has Exec Auth On Sproc
Feb 6, 2008And no, not through EM or SSMS
I want to interogate the catalog, so I can hae a job execute and do the grants in case a developer forgets
I mean I guess I can do the all everytime, but I don't know what the impact would be. It appears to be none, because of already done that, but in DB2 that would be bad as it would keep adding rows to the system tables
I have this so far
select *
from syspermissions p
inner join sysusers u
on u.uid = p.grantee
inner join sysobjects o
on o.id = p.id
where u.name = 'mepuser'
and o.name not like 'dt_%'
order by p.id
The M$ catalog is a royal pain
User Defined Functions And EXEC
Mar 5, 2008I'm using MS SQL Server 2005.
I want to simulate a table, using a Multi-statement Table-Value User-Defined Function, but I need the function build the SQL statement from scratch each time so I can dynamically define values like the table it references. The only way I know how to run the query after it has been defined in this manner is to run an EXEC command. However I'm getting an error basically saying that the EXEC command is off limits in a User Defined Function.
The Exsact Error is:
>[Error] Script lines: 1-108 ------------------------
Invalid use of side-effecting or time-dependent operator in 'EXECUTE STRING' within a function.
Is there some way to get around this limitation of User Defined Functions. Or perhaps a way to simulate the functionality of Multi-statement Table-Value User-Defined Functions in a Store Procedure, specifically the ability to run where statements, or transform the data on the fly without re-writing the Stored Procedure every time.
The code I’m trying to run is below.
(Note: The code works as a stored procedure, so I'm sure that the core of the statment is correct)
CREATE FUNCTION [dbo].[TrendLine]
(
@Summary as smallint,
@Start as datetime,
@End as datetime,
@Table as varchar(100),
@X as varchar(100),
@Count as varchar(100),
@Duration as varchar(100)
)
RETURNS
@TrendLineTable table (
DATE_DAY datetime
, EQ_REGION varchar(25)
, EQ_MARKET_CLUSTER varchar(30)
, Y float
, X int
, DURATION float
, FORMULA varchar(100)
, a float
, b float
, EX int
, EY float
, EX2 int
, EXY float
, N int
)
AS
BEGIN
DECLARE @SQL as varchar(3000)
, @CountText as varchar(150)
, @StartText as varchar(50)
, @EndText as varchar(50)
SET @StartText = 'cast( ' + char(39) + cast( @Start as varchar(20) ) + char(39) + ' as datetime ) '
SET @EndText ='cast( ' + char(39) + cast( @End as varchar(20) ) + char(39) + ' as datetime ) '
IF @Summary = 1
BEGIN
SET @CountText = 'sum'
END
ELSE
BEGIN
SET @CountText = 'count'
END
SET @SQL = 'INSERT INTO @TrendLineTable
DECLARE TrendlineC cursor for
SELECT a.DATE_DAY
, s2.EQ_REGION
, s2.EQ_MARKET_CLUSTER
, ( ( EY - ( b * EX ) ) / N ) + ( b * X ) AS Y
, X
, Y AS DURATION
, cast( b as varchar(100) ) + ' + char(39) + 'x + ' + char(39) + ' + cast( ( EY - ( b * EX ) ) / N as varchar(100) ) AS FORMULA
, ( EY - ( b * EX ) ) / N AS a
, b
, EX
, EY
, EX2
, EXY
, N
FROM (
SELECT EQ_REGION
, EQ_MARKET_CLUSTER
, sum( X ) AS EX
, sum( Y ) AS EY
, sum( X2 ) AS EX2
, sum( XY ) AS EXY
, count( X ) AS N
, ( ( count( X ) * sum( XY ) ) - ( sum( X ) * sum( Y ) ) ) / ( ( count( X ) * sum( X2 ) ) - POWER( sum( X ), 2 ) ) AS b
FROM (
SELECT ' + @X + ' AS DATE_DAY
, EQ_REGION
, EQ_MARKET_CLUSTER
, row_number() over (partition by EQ_MARKET_CLUSTER order by ' + @X + ' ) AS X
, cast( sum( ' + @Duration + ' ) as float ) / ' + @CountText + '( ' + @Count + ' ) AS Y
, POWER( row_number() over (partition by EQ_MARKET_CLUSTER order by ' + @X + ' ), 2) X2
, row_number() over (partition by EQ_MARKET_CLUSTER order by ' + @X + ' ) * cast( sum( ' + @Duration + ' ) as float ) / ' + @CountText + ' ( ' + @Count + ' ) AS XY
FROM ' + @Table + '
WHERE ' + @X + ' >= ' + @StartText + '
AND ' + @X + ' < ' + @EndText + '
GROUP BY ' + @X + '
, EQ_REGION
, EQ_MARKET_CLUSTER
) s1
GROUP BY EQ_REGION
, EQ_MARKET_CLUSTER
) s2
INNER JOIN (
SELECT ' + @X + ' AS DATE_DAY
, EQ_MARKET_CLUSTER
, row_number() over (partition by EQ_MARKET_CLUSTER order by ' + @X + ' ) AS X
, cast( sum( ' + @Duration + ' ) as float ) / ' + @CountText + '( ' + @Count + ' ) AS Y
FROM ' + @Table + '
WHERE ' + @X + ' >= ' + @StartText + '
AND ' + @X + ' < ' + @EndText + '
GROUP BY ' + @X + '
, EQ_REGION
, EQ_MARKET_CLUSTER
) a ON s2.EQ_MARKET_CLUSTER=a.EQ_MARKET_CLUSTER'
EXEC ( @SQL )
RETURN
END
Exec In User-defined Function
Jun 1, 2006Hi,
How can I do dynamical exec to query in user-defined function? At the end I need to return the result.
Thank's
Alexei
Exec DTS Package From Store Proc In SQL 2K
Nov 8, 2000What command do you use to run a DTS package from a stored procedure....
The XP Copy is not working???
Thanks,
~Lee
Exec SSIS Package Based On A File Existence
May 13, 2008I am not sure if we can execute SSIS package using t-sql, but I want to do the following:
Step1. Execute SSIS package1
Step2. Check for the file1.txt in c drive using the below code:
Declare @result int
exec xp_fileexist 'c:file1.txt', @result output
IF (@result = 1) --if file exists exec ssis package
begin
Execute SSIS package2
end
If we can't use t-sql, please let me know how I can do that as part of sql job. Thanks!!
How To Supply Config File To Exec Package Task?
Feb 22, 2007How do I do this ? Or is there any way to bundle Package with Config file so that they both are deployed in MSDB?
View 5 Replies View RelatedError With Execute DTS 2000 Package Task On 64-bit With 32-bit Exec
Dec 17, 2007Hello all,
I am running a package on a 64-bit server using the 32-bit dtexec. It contains an embedded Execute DTS 2000 package. I deployed the package to the server using the sa account. I set up a SQL Agent job that runs under an account that should have complete admin privileges. The network guys tell me that Legacy components have been installed (although I believe that shouldn't be necessary because SSIS is installed). This is SQL Server 2005, SP2.
When I execute this job, I receive this error message:
Executed as user: Domainuser. ...age Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 11:13:18 AM Error: 2007-12-17 11:13:35.65 Code: 0xC0010018 Source: Execute DTS 2000 Package Task Description: Error loading a task. The contact information for the task is "Execute DTS 2000 Package 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: 2007-12-17 11:13:35.71 Code: 0xC0010026 Source: Execute DTS 2000 Package Task Description: The task has failed to load. The contact information for this task is "Execute DTS 2000 Package Task;Microsoft Corporation; Microsoft SQL Server v9; ? 2004 Microsoft Corporation; All Rights Reserved;http://www.microsoft.com/sql/support/default.asp;1". End Error Error: 2007-12-17 11:13:35.71 Code: 0xC0024. The step failed.
Is there any way to get this to run?
Thank you very much,
Jessica
Create User Only With Permissions, To Select, Insert, Update, Delete, And Exec Sps
May 18, 2006Hello, I recently view a webcast of sql injection, and at this moment I created a user, and give dbo to this user, and this same user, is the one I have in the connection string of my web application, I want to create a user to prevent sql injection attacks, I mean that user wont be able to drop or create objects, only select views, tables, exec insert,update, deletes and exec stored procedures.
Is any easy way to do this?
A database role and then assing that role to the user?
Run Dts Package From Visual Studio(user Will Trigger The Running Of Dts Package)
Nov 5, 2007
Hi all,
I am creating a dts package to export files from one database to another database.
I tried to search for ways to execute the files and found out that i need to add
reference to Microsoft.Sql.managedDts. However, I cannot find this reference from
my reference. Do i have other alternatives to run this file?
Run Package On Server CPU Is Done Through SQL Agent: How To Set User Variable In Package Then?
May 9, 2007Hi,
I have packages stored in SQL store. I was letting users run the packages from a .net app that I made with
Microsoft.SqlServer.Dts.Runtime
Now I have noticed this causes the packages to run on the client pc cpu, as well as the network traffic is done via the client pc, in my particular case this is slow.
From the doc and in this forum I have found that you can run a package on the Server cpu through sql agent, let packages be run in a sql job. after that you can start a package from an application with the SQL sp_start_job .
But How do you set a user::varibale in a package if you have to start the package from a sql agent job ?
T-SQL And Visual Basic 2005 Codes That Execute A User-Defined Stored Procedure In Management Studio:How To Declare EXEC && Sp?
Jan 17, 2008Hi all,
In my SQL Server Management Studio Express (SSMSE), I executed the following sql code suuccessfully:
--insertNewRocord.sql--
USE shcDB
GO
CREATE PROC sp_insertNewRecord @procPersonID int,
@procFirstName nvarchar(20),
@procLastName nvarchar(20),
@procAddress nvarchar(50),
@procCity nvarchar(20),
@procState nvarchar(20),
@procZipCode nvarchar(20),
@procEmail nvarchar(50)
AS INSERT INTO MyFriends
VALUES (@procPersonID, @procFirstName, @procLastName, @procAddress,
@procCity, @procState, @procZipCode, @procEmail)
GO
EXEC sp_insertNewRecord 7, 'Peter', 'Wang', '678 Old St', 'Detroit',
'Michigon', '67899', 'PeterWang@yahoo.com'
GO
=======================================================================
Now, I want to insert a new record into the dbo.Friends table of my shcDB by executing the following T-SQL and Visual Basic 2005 codes that are programmed in a VB2005 Express project "CallshcDBspWithAdoNet":
--Form1.vb--
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Public Class Form1
Public Sub InsertNewFriend()
Dim connectionString As String = "Integrated Security-SSPI;Persist Security Info=False;" + _
"Initial Catalog=shcDB;Data Source=.SQLEXPRESS"
Dim connection As SqlConnection = New SqlConnection(connectionString)
connection.Open()
Try
Dim command As SqlCommand = New SqlCommand("sp_InsertNewRecord", connection)
command.CommandType = CommandType.StoredProcedure
EXEC sp_insertNewRecord 6, 'Craig', 'Utley', '5577 Baltimore Ave',
'Ellicott City', 'MD', '21045', 'CraigUtley@yahoo.com'
Console.WriteLine("Row inserted: " + _
command.ExecuteNonQuery().ToString)
Catch ex As Exception
Console.WriteLine(ex.Message)
Throw
Finally
connection.Close()
End Try
End Sub
End Class
===========================================================
I ran the above project in VB 2005 Express and I got the following 5 errors:
1. Name 'EXEC' is not declared (in Line 16 of Form1.vb)
2. Method arguments must be enclosed in parentheses (in Line 16 of Form1.vb)
3. Name 'sd-insertNewRecord' is not declared. (in Line 16 of Form1.vb)
4.Comma, ')', or a valid expression continuation expected (in Line 16 of Form1.vb)
5. Expression expected (in Line 16 of Form1.vb)
============================================================
I know that "EXEC sp_insertNewRecord 6, 'Craig', 'Utley', '5577 Baltimore Ave',
'Ellicott City', 'MD', '21045', 'CraigUtley@yahoo.com' "in Line 16 of Form1.vb is grossly in error.
But I am new in doing the programming of T-SQL in VB 2005 Express and I am not able to change it.
Please help and advise me how to correct these problems.
Thanks in advance,
Scott Chang
Exec Pkg Task: Error 0xC0202009 While Preparing To Load The Package. An OLE DB Error Has Occurred. Error Code: 0x%1!8.8X!.
Feb 21, 2007I cannot execute a package by using Execute Package task.
I supplied sa credentials to connection manager, and it shows the list of Packages on SQL Server but when running the task it says
Error 0xC0202009 while preparing to load the package. An OLE DB error has occurred. Error code: 0x%1!8.8X!.
Any clue ?
Thanks,
Fahad
DTS Package Behaviour When Used By &> 1 User?
Apr 20, 2004Hi all
I have a DTS package that users of the database can run which basically acts like a 'live update' (as the database is based on a values produced from another system) and it takes roughly 30 or so seconds to run...
The dts package is not going to have a particularly large hit rate but i am interested in knowing what will happen if a user (user 1) attempts to run the package when it is in already in use by another user (user 2) ?
Will User 1 simply have to wait untill the package completes for User 1?
OR
Will a new seperate 'instance' of the package be used by User 1?
Any info would be very helpful
Cheers
DTS Package Fails For Only User
Jan 11, 2008There is a DTS package that fails to run for one user. The DTS runs for other users. The DTS even runs when the user signs onto another Server.
The error message that the user gets is the following:
The number of failing rows exceeds the maximum specified.
TransformCopy 'DTSTransformation_1' conversion error: Conversion invalid for datatypes on column pair 1 (source column 'F1' (DBTYPE_WSTR), destination column 'PostDt'(DBTYPE_DBTIMESTAMP))
As previously stated, if the user signs onto another Server with SQL Server on it, the DTS performs a successful transformation.
Has anyone seen this error. I've been looking over all roles and security and have not been able to find any differences between user setup.
Thanks in advance.
User Context That Package Executes Under ?
May 5, 2006Hi There
I am trying to understand how to predict what user context a package will execute under, more specifically when it comes to operating system access.
For example we have a package that creates a flat file, during developement it was simply created on the C: drive of the users pc.
Now it must create the files on a remote server.
How do permissions to folders work is this case?
When you execute it in BIDS debug does it use the window account you logged onto the server as to connect to the remote folder ?
If so what happens when deployed to sql server and scheduled in a job? Does it use the domain account that the sql server agent account runs under ? Or will it use the credentials of the proxy account?
I am unclear in this scenario ? Obviously we need to grant the correct permissions on the remote server shared folder to the correct user, but i am not sure under what user account the package will be trying to create files on the remote server as ?
In a nutshell i need clarity on what user a package is executed as when running in a sql job ?
Anyone have a good link or the time to clarify this for me please.
Thanx
Can Exec Select But Can't Exec Sp
Oct 31, 2007I have two SQL Server 2000 (one is localhost, one is remote with VPN IP 192.168.5.4).
I can select * from [192.168.5.4].db.dbo.test but I can't exec [192.168.5.4].db..spAdd in localhost.
These select and sp is OK for 1 or 2 week without any problem,but it didn't work one day.
Can some one explain why?
Changing SSIS Package - New User Question
Feb 23, 2006I saved a simple export/import package from the import/export wizard to a .dtsx file/package. Now I want to change the package to delete/truncate all of the tables before adding data. (since this has 80 tables it would have been a pain to edit each of them individually)
How can I easily go about doing this with the SSIS package. I can't find out where this would actually be executed.
Also, how can I actually view the code behind the SSIS package.
Thanks.
User Variables In SSIS Package Scope
May 8, 2008I have created two User variables at Package level (OldRunID{Int32} and NewRunID{Int32}). I am using two simple ExecuteSQL Tasks to get a Single Row Resultset return (each) and loading them (on Result Set Page). Using the Constraint and Expression Flow controls, I have found that they load to True (-1) instead of the 25 and 50, respectively, that I would expect. I then wrote a Script task to evaluate their values and find that I get zero for Dts.Variables.Count. - Why am I not loading the variables correctly and why can I not access them in the Script task?
View 18 Replies View RelatedUser Rights And Executing Of SSIS Package
Mar 31, 2006I have a small SSIS package which:
- drops existing table in Excel,
- creates a new one in Excel,
- copies data from SQL to newly created sheet.
So there are 2 connections: one to a excel file, and second to SQL using sa account. I am working on my account belonging to Administrators group.
SQL Server and Excel file are on my development machine. Everything works ok when I run package from file system using DTExecUI.exe (in context of my user belonging to Administrator group). But when I have created job to execute this package problem with security occured. Even assigning local account on which SQL Server and SQL Server Agent works to Administrator group do not help. I have simulated what can happen when running DTExecUI.exe utility in contex of different user. I have used Administrator (I start it using "Run As..." from context menu in Explorer"). And what occured, SSIS package can not login to SQL Server using sa user!
1. Why running DTExecUI.exe in context of different user impact login to SQL on sa accout? If it were Windows Integrated I could understand it.
2. Especially if the different user is also Administrator!!!
I have found solution for running a task in SQL Agent - SQL Agent has to work on the same account on which SSIS package was created. But this is crazy when I would like to deploy it to a production server.
Please help.
Przemo
Execute Package Logon Failed For User....
Sep 27, 2006Fairly new to SSIS.I have created a package in BIDS, and am trying to execute it so i can test/debug the flow and view the data in the data viewers. I continually get a logon failed for user... its the user id i am using to connect to my SourceConnection OLEDB. I have been struggling with this for a while now, and getting frustrated. I tried Windows Authentication also, still i get an error message logon failed for user......
I have tried all of the security settings, from Do not save sensitive to Encrypt all with password, entered the password, and then try to Execute Package from the solution explorer and still get error message. Thanks for your help!
Here is the error message
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'UserName'.".
ASP.NET 2.0 Call Of MS SQL DTS Package Fails With Unknown User Name/Password
Feb 20, 2007I am writing an ASP.NET intranet application on a Windows XP machine running IIS 5.1 which executes a MS SQL Server 2000 DTS package as part of the process. The application allows the user to select a UNC path of an input source (.txt) file to be procesed by the DTS pacakge and the UNC file path property is then set in the source object name of the DTS package tasks before executing the DTS package. Whenever I run the application using a file path that is on my development machine (the same machine that the web server is on) it works fine. When I select a source file path that is on a network share folder, the DTS package fails with the following error:Event Type: ErrorEvent Source: DataTransformationServicesEvent Category: NoneEvent ID: 81Date: 2/14/2007Time: 12:58:06 PMUser: N/AComputer: PHILSDELL2Description:The execution of the following DTS Package failed: Error Source: Microsoft Data Transformation Services (DTS) PackageError Description:Package failed because Step 'DTSStep_DTSDataPumpTask_2' failed.Error code: 80040428Error Help File:sqldts80.hlpError Help Context ID:700Package Name: ATMRecon_ImportElanACHTxtPackage Description: (null)Package ID: {3A8CC31D-A81F-40B7-BE57-AEB3AA238088}Package Version: {B01420A7-ED22-49A7-B4C7-9FA1732394E3}Package Execution Lineage: {2A9D45E8-35F8-4F0A-8339-7E5E29DA08B7}Executed On: PHILSDELL2Executed By: webaccess23Execution Started: 2/14/2007 12:58:05 PMExecution Completed: 2/14/2007 12:58:06 PMTotal Execution Time: 1.047 secondsPackage Steps execution information:Step 'DTSStep_DTSExecuteSQLTask_1' succeededStep Execution Started: 2/14/2007 12:58:05 PMStep Execution Completed: 2/14/2007 12:58:05 PMTotal Step Execution Time: 0.047 secondsProgress count in Step: 0Step 'DTSStep_DTSExecuteSQLTask_2' succeededStep Execution Started: 2/14/2007 12:58:05 PMStep Execution Completed: 2/14/2007 12:58:05 PMTotal Step Execution Time: 0.047 secondsProgress count in Step: 0Step 'DTSStep_DTSDataPumpTask_1' failedStep Error Source: Microsoft Data Transformation Services Flat File Rowset ProviderStep Error Description:Error opening datafile: Logon failure: unknown user name or bad password.Step Error code: 80004005Step Error Help File:DTSFFile.hlpStep Error Help Context ID:0Step Execution Started: 2/14/2007 12:58:05 PMStep Execution Completed: 2/14/2007 12:58:06 PMTotal Step Execution Time: 1 secondsProgress count in Step: 0Step 'DTSStep_DTSDataPumpTask_2' failedStep Error Source: Microsoft Data Transformation Services Flat File Rowset ProviderStep Error Description:Error opening datafile: Logon failure: unknown user name or bad password.Step Error code: 80004005Step Error Help File:DTSFFile.hlpStep Error Help Context ID:0Step Execution Started: 2/14/2007 12:58:05 PMStep Execution Completed: 2/14/2007 12:58:05 PMTotal Step Execution Time: 0.031 secondsProgress count in Step: 0 When I check the Security Event Log on the server on which the network share is located, I find the following set of messages repeated multiple times:Event Type: Failure AuditEvent Source: SecurityEvent Category: Account Logon Event ID: 680Date: 2/14/2007Time: 1:02:48 PMUser: NT AUTHORITYSYSTEMComputer: FS1ADescription:Logon attempt by: MICROSOFT_AUTHENTICATION_PACKAGE_V1_0 Logon account: ASPNET Source Workstation: PHILSDELL2 Error Code: 0xC000006AEvent Type: Failure AuditEvent Source: SecurityEvent Category: Logon/Logoff Event ID: 529Date: 2/14/2007Time: 1:02:48 PMUser: NT AUTHORITYSYSTEMComputer: FS1ADescription:Logon Failure: Reason: Unknown user name or bad password User Name: ASPNET Domain: PHILSDELL2 Logon Type: 3 Logon Process: NtLmSsp Authentication Package: NTLM Workstation Name: PHILSDELL2 Caller User Name: - Caller Domain: - Caller Logon ID: - Caller Process ID: - Transited Services: - Source Network Address: 10.120.4.61 Source Port: 0The problem appears to be that the DTS package is trying to access the input source file using the local ASPNET Id from PHILSDELL2 which does not have authority to the network share. The ASP.NET application is using impersonation, set up through the web.config file: <identity impersonate="True" userName="registry:HKLMSOFTWARESkylightATMReconidentityASPNET_SETREG,userName" password="registry:HKLMSOFTWARESkylightATMReconidentityASPNET_SETREG,password" />The application can sucessfully access the network share under the impersonated id (the application first verifies the existience of the source files before executing the DTS package). I have wracked my brain trying to determine where the ASPNET security context is coming from to no avail. Can someone shed some light on what security credentials the DTS package is using to access the source files and how to change them?
View 1 Replies View RelatedPackage Won't Run For Domain User With Full Control Permissions
Dec 1, 2007I'm running my job from the command line using DTExec as follows:-
DTExec /FILE "C:MyPathMyPackage.dtsx" /CONFIGFILE "C:MyPathMyDtsConfig.xml" /MAXCONCURRENT " -1 "
When I log in as Administrator the package runs perfectly.
When I log in as Domain User (the one I really want to have running the package) I get:-
Started: 10:49:08 PM
Error: 2007-11-30 22:49:08.30
Code: 0xC0011007
Source: {807048F4-DE2A-465E-B9A7-82E163791556}
Description: Unable to load the package as XML because of package does not have a valid XML format. A specific XML parser error will be posted.
End Error
I have checked, and the Domain User has
"Full Control" permissions to the directory the package is in and
"Full Control" permissions for the DTSX file and
"Full Control" permissions to the directory the dtsConfig is in and
"Full Control" permissions for the dtsConfig fileAny suggestions as to what is wrong?
What User Is Used When Executing An SSIS Package From Management Studio?
May 23, 2008
All:
I am working with a package that executes a stored procedure. This stored procedure makes use of a Linked Server. When I execute it in debug mode in VS 2005, it works flawlessly. However when I deploy it to the production server and execute it by going to Management Studio, connecting to Integration Services, navigating to MSDB, right clicking and choosing "run package" I get the infamous:
Login failed for user '(null)'. Not associated with a trusted SQL Server Connection. OLE DB Provider SQLNCLI for linked server [name] returned message "Communication Link Failer"
error. If I execute the stored procedure by itself on the production server, it works flawlessly. It is only when this stored procedure gets executed via the above mentioned way inside of an SSIS pack that I get this error. To me this suggest it is being executed as a different user, but I don't know which one.
Any help would be wonderful.
Thank you in advance...
AccDev
Legacy DTS Package On SQL 2005 - Give User Rights To Save DTS Packages...
Oct 4, 2007We have a SQL server with many legacy DTS packages. sa and Admins can open them and change them then save them but we need to allow the DTS people (Developers) the rights to save the package after they have opened it and modified it.
Thanks
Login Failed For User Message When Executing A Copy Of A Working Package
Jul 27, 2006I notice when I copy an SSIS package 'A' to a new package 'B', the new package 'B' will generate a "login failed for user" message in the data flow components. To copy I "save copy of Package 'A' as."
Some config info:
Package ProtectionLevel = EncryptSensitiveWithPassword
Connections are Data Sources
Connection strings with password are stored using PackageConfigurations to an SQLServer table. I've verified Package 'A' is in fact using the config table (e.g. it is not using a password or user stored in the package)
Data connectios are all SQLServer Native OLE DB Client
The account is an SQLServer account (not integrated security)
The original Package 'A' works flawlessly and I get success when I test the connections in Package 'B'.
But executing package 'B' I get: The error message I get is: [Connection manager "MyConnection"] Error: An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'dwuser'.".
Does anyone know why this would occur and/or how to work around it? I saw another thread where a potential workaround is to create a new data flow task and copy all the data flow components to that task. That won't work well for us because the data flow is moderately complex and when you copy and paste it, SSIS completely re-orders the layout.
This is a typical data warehouse ETL setup where there is a master package that executes child packages (e.g. 'A', and 'B' mentioned above) that each perform the ETL for a specific dimension or fact table.
Thanks for any help,
Lee Cascio
Failed DTS Package Locks User Out Of SQL2005 (Windows Authentication) ... For Good!
Feb 13, 2006Would be interested in any advice or comment on the issue we are experiencing with SQL 2005.
In order to test some DTS package migrations, we simply created a DTS package on SQL 2000 (using Export) to copy all database objects from one database to another.
Then we restored the source database on SQL 2005 server, migrated the DTS package, and tried to execute it.
The package migrated 'fine', ran part way, and then failed. Since then we have been unable to connect to the server (Database Engine, or Integration Services) using Windows Authentication. (Error 18456, Sev 16, State 11).
We can connect to the Database Engine using SQL Authentication (but not to Integration Services which only allows Windows Authentication).
We have been able to replicate this consistently - every annoying time we've tried it!
(Reinstalling SQL Server does 'fix' the issue ... until you try a similar package again).
Failed To Generate A User Instance Of SQL Server Due To Failure In Retrieving The User's Local Application Data Path. Please Make Sure The User Has A Local User Profile On The Computer. The Connection Will Be Closed
Dec 7, 2006This is my first time to deploy an asp.net2 web site. Everything is working fine on my local computer but when i published the web site on a remote computer i get the error "Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path. Please make sure the user has a local user profile on the computer. The connection will be closed" (only in pages that try to access the database)
Help pleaseee
Package With File System Task Doesn't Work Without Sensitive Data With User Keys
Dec 14, 2006This problem is a bit weird but I'm just wondering if anybody else experienced this.
I have a package that has file system tasks (copying dtsx files actually). Basically the package copies other packages to a pre-defined destination. Thing is, it only works if one of the packages it is configured to copy has some sort of sensitive data (e.g., a connectionstring with a password), otherwise it reports a success message on execution but doesn't actually do anything. I've checked the forcedexecutionresult and it is set to None for that matter.
Just wondering if anybody else experienced this problem and of course if there's a way to solve it.
Thanks.