September 22, 2016 at 1:19 pm
Hi Team,
I have deployed my SSIS package into Integration Server Catalogs.
Inside SSIS Package I am trying to read the Share folder Information's using Script task.
Project parameter has the Directory Details which get passed through SQL Server.
DirectoryInformations.GetFiles() Method.
It is working in my account , when others try to execute by invoking the SSIS procedure using SQL procedure I am getting this issue "Exception has been thrown by the target of an invocation."
Even I changed settings also : Version 4.0 to 4.5 for scripts. It didn;t work.. please help me out..
September 23, 2016 at 3:30 am
There will likely be more errors than just the one above. Check your Report Logs for an execution and copy all the errors out. Normally SSIS has a tendency to spit our 3 or 4, which will paint a much more accurate picture of what went wrong.
Which version of SQL Server are you using? When you say you are running the script, how is the SSIS package actually being called? Are you running SQL to call the package within the Catalog, or calling a SQL Agent task to run it?
I assume it's accessing a file within a folder, as you said that it's accessing a share. If it's being called directly in the Catalog, is your Kerboros set up to allow double hopping (as the package will need to run under the user's credentials as well, and that's a second hop), and do the user's have permission to that share? If it's being run via Agent, does the Agent Service account have access to that folder?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 23, 2016 at 10:16 am
Sql Server Version : 2014
It is a problem in accessing Network Share Folder Paths from SQL Server.
Example when others try to upload the blob file , it is throwing an error.
INSERT INTO [dbo].[TEST](LOG_DATA,T_DATA_PROCESS_STATUS,T_USED_ID,T_STAT_ID,T_INSTANCE_ID) SELECT BULKCOLUMN, NULL,
'1',NULL,1 FROM OPENROWSET( BULK '\\NetworkServerName\temp\client1092\output\Blobfile.txt', Single_Blob) as TBL
Cannot bulk load because the file "\\NetworkServerName\temp\client1092\output\Blobfile.txt" could not be opened. Operating system error code 5(Access is denied.).
SSIS package when I try to read directory Information, it is throwing an "Exception has been thrown by the target of an invocation."
This is only problem with others try to upload and read directory information , I checked the network directory settings for me and others same. but it is working fine for me, I can able to upload and read directory information, but others not.
I feel is that SQL Server permission issue , I have added separate account "domain\userid" with permission of BulkAdmin, public.. Even others added with different groups and permission also same.
September 26, 2016 at 2:19 am
It definitely sounds like a permission problem then. My guess is that you're execute the package in the SSIS Catalog via T-SQL (you didn't answer this question), and your Kerboros isn't permitting the double hopped authentication. As a result it's trying to authenicate as NT_Anonymous, which results in a failure. I would suggest checking with whoever maintains the network security that this type of hopping is permitted.
Have you also tried running the task via an Agent job? This only requires one hop on kerboros, so as long as you have it enabled, should work by default (provided the user Agent is running on has permission).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 26, 2016 at 4:04 am
Yes, I am executing the SSIS Catalog via T-SQL is throwing this error. As you mentioned " network security that this type of hopping is permitted". Could you please provide the steps to enable the Kerberos authentication in sql server. so that I will Guide my SQL Administrator to do necessary changes.
September 26, 2016 at 6:39 am
Most likely Kerboros is enabled on your server (you can check by running the below SQL, which should return "KERBOROS"), however, that the double hop isn't working.SELECT c.auth_scheme FROM sys.dm_exec_connections c WHERE c.session_id = @@spid;
This isn't something I handled personally I'm afraid, it was a problem I encountered over a year ago and our Network Administrator looked into it with our other DBA. I made a passing comment to our Network Admin when I saw him at lunch, along the lines of "How did you end up fixing that kerboros issue last year?", but he shrugged his shoulders and said he really can't remember without going back through his emails. I didn't want to hound him on it, as it's not a current issue here, sorry.
A quick Google[/url], however, led me to this site[/url], which discusses a lot of the items that we initially discussed when I raised the matter. Hopefully that'll help. If not, hopefully someone else here may be able to give you a more definitive guide, as the problem isn't solely at the SQL Server.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 27, 2016 at 1:54 pm
If you are able to access the folder and file then you may want to study creating a proxy account for the SQL SERVER Agent account. This allows the agent to use an account other than the one it is configured on when running a job.
On that note, does the error get returned to the environment that called the ssis package with t-sql, or within a SSIS log itself?
----------------------------------------------------
September 28, 2016 at 12:19 am
yes, I am getting this error while executing the t-sql embedded with SSIS package.
we are using Third party scheduler to execute the SSIS package.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply