Error is Executing the SSIS package.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Error is Executing the SSIS package.

Author

Topic

deal11deal1
Starting Member

34 Posts

Posted - 2011-01-18 : 11:30:00

Hello folks,

I am recreating a SSIS package from a old one . one of the step of the package is to [execute SQL task] this is task i am runing the sp_help_reslogin.( it is a in-house build stored proc).
when I run it is get the following error.

[Execute SQL Task] Error: Executing the query "exec sp_help_reslogin" failed with the following error: "Windows NT user or group 'Domain\Login' not found. Check the name again.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

in the error this is the ID of the person who created the old ssis before, in my package it did every thing on my own.... when exec the package its looking for his ID... i dont understand it and dont know how and where to go and fix it..
Please help.
I have prase the code in the Store proc , it works .. its just when it connects from the ssis sql task to that stored proc ( it looks for the old user which abviously is not there).

Please help guys
and Thanks in advance.

deal11deal1
Starting Member

34 Posts

Posted - 2011-01-18 : 11:56:40

Any help guys ?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-01-18 : 12:57:58

Connecting to SQL Server with NT login, or SQL login?

deal11deal1
Starting Member

34 Posts

Posted - 2011-01-18 : 13:10:52

sa login

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-01-18 : 13:26:24

What is the package protection level set to? If it is either of the user key options, try changing to "Rely on Storage and Roles."

The Stored Proc doesn't have a RUNAS does it?

What does the proc do?

deal11deal1
Starting Member

34 Posts

Posted - 2011-01-18 : 13:53:05

the proc is set up for DR recovery. it creates the user and logins and move to a table in a databsebase and that database moves to destination. thats what the SSIS is all about?? if it helps i can post the cript here too.
The protection level right now is saved to default which is user key option.
RUNAS , not so sure about this one.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-01-18 : 13:57:12

If it's explicitly creating a logon for a domain user that no longer exists, remove that part from the query.

deal11deal1
Starting Member

34 Posts

Posted - 2011-01-18 : 14:09:56

Thats what I thougth too and look for it but it's not explicitly creating a domain user ( that particular user).

deal11deal1
Starting Member

34 Posts

Posted - 2011-01-18 : 14:15:58

I am going to post the script anyways. it'snot that big.

also the error I am getting is not even letting me execute the SP... so the permission is nto granted to even get in to the SP.

USE [master]
GO
/****** Object: StoredProcedure [dbo].[stored procedurename] Script Date: 01/18/2011 14:11:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[storedprocedurename] AS
DECLARE @name sysname
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_string varchar (514)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)
DECLARE @defaultdb sysname
DECLARE @drop_login_sql varchar (514)

DECLARE login_curs CURSOR FOR

SELECT p.login_name
,p.pass_hash
,p.sid_hash
,p.default_database
,p.policy_checked
,p.expiration_checked
,p.deny_login
,p.hasaccess
,p.disabled
FROM databasenme.dbo.transfer_users p

OPEN login_curs

FETCH NEXT FROM login_curs INTO @name, @PWD_string, @SID_string, @defaultdb, @is_policy_checked, @is_expiration_checked,@denylogin, @hasaccess, @is_disabled
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END

WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
if exists(select 1 from sys.server_principals where name = replace(replace(@name, '[', ''),']',''))
begin
set @drop_login_sql='drop login ' + @name
exec (@drop_login_sql)
end
IF (@PWD_string is null)
BEGIN -- NT authenticated account/group

SET @tmpstr = 'CREATE LOGIN ' + @name + ' FROM WINDOWS WITH DEFAULT_DATABASE = ' + @defaultdb
END
ELSE BEGIN -- SQL Server authentication

SET @tmpstr = 'CREATE LOGIN ' + @name + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = ' + @defaultdb

IF ( @is_policy_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
END
IF ( @is_expiration_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
END
END
IF (@denylogin = 1)
BEGIN -- login is denied access
SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
END
ELSE IF (@hasaccess = 0)
BEGIN -- login exists but does not have access
SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
END
IF (@is_disabled = 1)
BEGIN -- login is disabled
SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
END
exec (@tmpstr)
END

FETCH NEXT FROM login_curs INTO @name, @PWD_string, @SID_string, @defaultdb, @is_policy_checked, @is_expiration_checked,@denylogin, @hasaccess, @is_disabled
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0

.......................................................................................................................
Hope it will help.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-01-18 : 14:36:07

Need to determine if the error is inside the stored proc, or in the attempt to execute it. Or in the attempt to execute the SSI package.

Does it work if you manually execute the SP?
How are you executing the SSIS pkg? Via a SQL Agent Job?

It looks a lot like it's failing on the dynamic sql.

deal11deal1
Starting Member

34 Posts

Posted - 2011-01-18 : 14:51:10

write now i havent even put it on the sql agent.. just from insdie the package.
and yes if i go to the SP from SSMS and run the package i am able to run it.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-01-18 : 15:54:44

Are you sure it isn't using windows authentication?

did you change the protection level yet?

deal11deal1
Starting Member

34 Posts

Posted - 2011-01-18 : 15:58:22

Ok thanks for all your help .
I was finally able to figure out the problem and got a work around it.
In the code if the login was created and than taken out the sript gives an error that certain login is mssing.But the loging that not present is no longer for company use so we dont need it anyways ( the better way would be to write a code if login has been taken out just give a message rather than gives a error( I didnt write it lol). What I did was in the ssis package I used a pass/ fail in the sql task so even if it fails it goes to the second step and if all the logins are there than it goes to pass and stil goes to the next step.
now the package works fine.
Thanks for the help.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-01-18 : 20:31:11

Happy you got it sorted out.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources