SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Automating SSIS package execution


Automating SSIS package execution

Author
Message
PradeepVallabh
PradeepVallabh
SSC-Enthusiastic
SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)

Group: General Forum Members
Points: 161 Visits: 526
I have a SSIS package which runs fine when executed from BIDS/SQL Server Integration services but doesn't process flat file(from a shared drive) when package is called from SQL Job...I suspect it may be due to permission issue, i am running sql job under SQL Server Agent Service Account....How do i check the privilege of SQL Server Agent Service Account??? I know i can create a proxy account and run the job under proxy account but doesn't want to create a new account just for running couple of SSIS packages...Any suggestions???
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63064 Visits: 13298
First of all you have to determine which account SQL Agent uses.
You can see this in the SQL Server Configuration Manager, under Services.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Suresh B.
Suresh B.
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5024 Visits: 5330
PradeepVallabh (8/13/2013)
How do i check the privilege of SQL Server Agent Service Account???

Right click on the folder -> Select Properties -> Click on Security tab.
PradeepVallabh
PradeepVallabh
SSC-Enthusiastic
SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)

Group: General Forum Members
Points: 161 Visits: 526
I created a proxy account and ran the job using proxy account (Couldn't change the sql server agent service account to domain acct, so created a new proxy account) but still sql job doesn't recognize the file. Actual file is on a network drive, i am using IP address to find location of the file

Any suggestions???
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63064 Visits: 13298
If you use your browser and you enter the IP address, do you see the file?


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
PradeepVallabh
PradeepVallabh
SSC-Enthusiastic
SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)

Group: General Forum Members
Points: 161 Visits: 526
Yes i can see the file....
PradeepVallabh
PradeepVallabh
SSC-Enthusiastic
SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)

Group: General Forum Members
Points: 161 Visits: 526
Damn...It's the permission issue(proxy account doesn't have permission to access the network drive)
Steven Willis
Steven Willis
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2063 Visits: 1721
Once you know what account it's trying to run under (the failure message will tell you), then you can right click on the SSMS shortcut and "Run As" that user (assuming you have the proper credentials). The server instance and the Agent need to be running from the same account. Also I think more recent releases of the OS have assigned the server administrator as the default SysAdmin account for SQL Server Agent jobs and then disabled those privileges by default. Furthermore, if you create a package in VS under a different user context than the one SQL Agent is expecting you will also run into permissions problems.

I gave up mostly trying to use the agent account for SSIS packages because it's such a pain in the rear unless one is the sysadmin of everything. I make my connections and linked servers all with TSQL code and just paste it into the job step directly. The security is not so hot if someone can get access to your box, but that's the only downside of using code I think but one with fairly low risk in most cases. Your situation will have to dictate the risk factors.

Here's an example of some code I use for running jobs. This one happens to use BCP to do a file transfer, but it's just an example of what can be done. I use it for some very complicated processes and you can even use it to run local stored procedures on your linked servers or execute a remote process on your local machine without the permissions muss and fuss of the agent service account.



USE LocalServer
GO

IF (
SELECT
SRV_DATASOURCE = srv.data_source
FROM
sys.servers srv
WHERE
srv.data_source = 'SQL-LINKED-SERVER-NAME,PORTNUM'
) IS NULL

EXEC master.dbo.sp_addlinkedserver @server = N'SQL-LINKED-SERVER-NAME,PORTNUM', @srvproduct=N'SQL Server', @provider=N'SQLNCLI'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SQL-LINKED-SERVER-NAME,PORTNUM',@useself=N'False',@locallogin=NULL,@rmtuser=N'Your-USERID',@rmtpassword='Your-PASSWORD'
GO
EXEC master.dbo.sp_serveroption @server=N'SQL-LINKED-SERVER-NAME,PORTNUM', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SQL-LINKED-SERVER-NAME,PORTNUM', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SQL-LINKED-SERVER-NAME,PORTNUM', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SQL-LINKED-SERVER-NAME,PORTNUM', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SQL-LINKED-SERVER-NAME,PORTNUM', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SQL-LINKED-SERVER-NAME,PORTNUM', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SQL-LINKED-SERVER-NAME,PORTNUM', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SQL-LINKED-SERVER-NAME,PORTNUM', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'SQL-LINKED-SERVER-NAME,PORTNUM', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'SQL-LINKED-SERVER-NAME,PORTNUM', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SQL-LINKED-SERVER-NAME,PORTNUM', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'SQL-LINKED-SERVER-NAME,PORTNUM', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SQL-LINKED-SERVER-NAME,PORTNUM', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

PRINT 'Exporting the pipe-delimited columns to local file.'
EXEC xp_cmdshell 'bcp "EXEC master..[your export proc]" queryout "C:\target.csv" -w -T -S"SERVER\INSTANCE" -t"|"'
PRINT 'Export to local file complete.'

USE LocalServer
GO

PRINT 'FTP to Remote Site site starting...'

EXEC dbo.UTIL_FTP_GetFile
@FTPServer = 'RemoteSite.com' ,
@FTPUser = 'RemoteUser' ,
@FTPPWD = 'RemotePW' ,
@SourcePath = 'c:\Remote Site\' ,
@SourceFiles = 'target.csv' ,
@DestPath = '\RemoteSite\Target\' ,
@FTPMode = 'binary'


PRINT 'FTP to Remote Site site complete.'

PRINT 'Start build of email'

DECLARE
@tableHTML NVARCHAR(MAX)
,@MailSubject NVARCHAR(75)
,@MailRecipients NVARCHAR(1000)
,@dateCompleted DATETIME

SET @MailSubject ='Export to Remote Site completed at ' + CONVERT(NVARCHAR(50),GETDATE(),120)
SET @MailRecipients = 'your.email@mailbox.com'

SET @tableHTML = ''

SET @tableHTML =
N'<H3>The following is the status for Remote Site Export</H3>' +
N'<table width= 70% border="0">' +
N'<tr><td align=left> 3) Job completed at '+CONVERT(NVARCHAR(50),GETDATE(),120)+'</td></tr>' +
N'<tr><td>

<td></tr>' +
N'</table>' ;

PRINT 'Sending email'

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DBMail to OutLook'
,@recipients = @MailRecipients
,@subject = @MailSubject
,@body = @tableHTML
,@body_format = 'HTML'

PRINT 'Job Complete'



/*

CREATE PROCEDURE [dbo].[UTIL_FTP_GetFile]

@FTPServer VARCHAR(128) ,
@FTPUser VARCHAR(128) ,
@FTPPWD VARCHAR(128) ,
@SourcePath VARCHAR(128) ,
@SourceFiles VARCHAR(128) ,
@DestPath VARCHAR(128) ,
@FTPMode VARCHAR(128)

AS
BEGIN

DECLARE @cmd VARCHAR(1000)
DECLARE @workfilename VARCHAR(128)
DECLARE @workfile VARCHAR(128)
DECLARE @nowstr VARCHAR(25)


-- Get the %TEMP% environment variable.
DECLARE @tempdir VARCHAR(128)
CREATE TABLE #tempvartable ( info VARCHAR(1000) )
INSERT #tempvartable
EXEC master..xp_cmdshell 'echo %temp%'

SET @tempdir = ( SELECT TOP 1
info
FROM #tempvartable
)
IF RIGHT(@tempdir, 1) <> '\'
SET @tempdir = @tempdir + '\'
DROP TABLE #tempvartable

-- Generate @workfile
SELECT @nowstr = REPLACE(REPLACE(CONVERT(VARCHAR(30), GETDATE(), 121),
' ', '_'), ':', '-')
SELECT @workfile = 'FTP_SPID' + CONVERT(VARCHAR(128), @@spid) + '_'
+ @nowstr + '.txt'

-- Deal with special chars for echo commands.
SELECT @FTPServer = REPLACE(REPLACE(REPLACE(@FTPServer, '|', '^|'),
'<', '^<'), '>', '^>')
SELECT @FTPUser = REPLACE(REPLACE(REPLACE(@FTPUser, '|', '^|'), '<',
'^<'), '>', '^>')
SELECT @FTPPwd = REPLACE(REPLACE(REPLACE(@FTPPwd, '|', '^|'), '<',
'^<'), '>', '^>')
SELECT @DestPath = REPLACE(REPLACE(REPLACE(@DestPath, '|', '^|'), '<',
'^<'), '>', '^>')
IF RIGHT(@SourcePath, 1) <> '\'
SET @SourcePath = @SourcePath + '\'

-- Build the FTP script file.
SELECT @cmd = 'echo ' + 'open ' + @FTPServer + ' > ' + @tempdir
+ @workfile
EXEC master..xp_cmdshell @cmd
SELECT @cmd = 'echo ' + @FTPUser + '>> ' + @tempdir + @workfile
EXEC master..xp_cmdshell @cmd
SELECT @cmd = 'echo ' + @FTPPwd + '>> ' + @tempdir + @workfile
EXEC master..xp_cmdshell @cmd
SELECT @cmd = 'echo ' + 'prompt ' + ' >> ' + @tempdir + @workfile
EXEC master..xp_cmdshell @cmd
IF LEN(@FTPMode) > 0
BEGIN
SELECT @cmd = 'echo ' + @FTPMode + ' >> ' + @tempdir
+ @workfile
EXEC master..xp_cmdshell @cmd
END
IF LEN(@DestPath) > 0
BEGIN
SELECT @cmd = 'echo ' + 'cd ' + @DestPath + ' >> ' + @tempdir
+ @workfile
EXEC master..xp_cmdshell @cmd
END
SELECT @cmd = 'echo ' + 'mput ' + @SourcePath + @SourceFiles + ' >> '
+ @tempdir + @workfile
EXEC master..xp_cmdshell @cmd
SELECT @cmd = 'echo ' + 'quit' + ' >> ' + @tempdir + @workfile
EXEC master..xp_cmdshell @cmd

-- Execute the FTP command via script file.
SELECT @cmd = 'ftp -s:' + @tempdir + @workfile
CREATE TABLE #tempFTPGet
(
id INT IDENTITY(1, 1) ,
s VARCHAR(1000)
)
INSERT #tempFTPGet
EXEC master..xp_cmdshell @cmd
SELECT id ,
ouputtmp = s
FROM #tempFTPGet

-- Clean up.
DROP TABLE #tempFTPGet
SELECT @cmd = 'del ' + @tempdir + @workfile
EXEC master..xp_cmdshell @cmd


END
GO

*/



PradeepVallabh
PradeepVallabh
SSC-Enthusiastic
SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)

Group: General Forum Members
Points: 161 Visits: 526
Thanks....that was good info
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search