January 27, 2014 at 9:43 am
I have a master package and child package in an SSIS 2012 Project. The server on which the package lives is the same server to which the SSIS packages write (resident server and target server are the same).
In the child package there is a flat file connection manager pointing to a source file on the corporate network.
If I remote into the target server, open up SSMS, and run T-SQL, the flat file connection manager can see the network file and loads successfully.
HOWEVER, if I am on a my work laptop on the same network and domain, open SSMS on the laptop, connect to the same target server, and run the SAME EXACT T-SQL CODE to launch the package, using the same credentials, the flat file connection manager of the same child package FAILS TO OPEN the same exact file on the same exact network path! I am stumped as was Jamie Thomson when I asked him. I just read Koen's article today and am wondering if the bug he mentioned in his article would be the cause for this.
Here is the T-SQL:
Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution] @package_name=N'MasterClaims.dtsx', @execution_id=@execution_id OUTPUT, @folder_name=N'SSIS_Claims', @project_name=N'SSIS_CLAIMS', @use32bitruntime=False, @reference_id=2
Select @execution_id
DECLARE @var0 sql_variant = N'MyDomain\AnalyticsSSIS'
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=20, @parameter_name=N'ExecutingOperator', @parameter_value=@var0
DECLARE @var1 sql_variant = N'HAP - 1006078'
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=20, @parameter_name=N'HAProjectID', @parameter_value=@var1
DECLARE @var3 sql_variant = N'1184706152_HEADER_20110101_20130622_144379.txt'
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=20, @parameter_name=N'strFileNameHeader', @parameter_value=@var3
DECLARE @var6 smallint = 1
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=50, @parameter_name=N'LOGGING_LEVEL', @parameter_value=@var6
EXEC [SSISDB].[catalog].[start_execution] @execution_id
GO
Why would the child package succeed in one scenario and not the other? I am a SQL Server admin and also have access to all the resources used by the package.
Here is the error messsage:
Cannot open the datafile "\\11.13.12.102\Dept\ANALYTICS DATA\AnalyticsClaimsData\01-WaitingForImport\1184706152_HEADER_20110101_20130622_144379.txt".
I have verified every aspect and component of the packages. They are the same. Could this be a bug....or possible a windows/security thing?
January 27, 2014 at 2:45 pm
edit: nm, the answer was in your original post
January 27, 2014 at 3:39 pm
I still cannot figure it out, but we used a workaround by creating a SQL Agent Job and having our launching application execute sp_update_job and sp_start_job.
However, I would still LOVE to know what would cause this issue. I hear KOEN lurking somwhere......haha.
January 28, 2014 at 12:41 am
Sorry, I am stumped as well.
Your laptop has access to the shared network drive?
I could understand this in SSIS 2008R2 or earlier, as SSIS packages are executed on the calling computer, but in SSIS 2012 they are always executed in the catalog.
What if you run the SSMS on your laptop as administrator? Just to make sure there aren't any UAC issues.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 28, 2014 at 8:08 am
I was running SSMS on my laptop as both the laptop admin and the SQL sysadmin when this happened. I even pasted the file path in windows explorer and the files opened without error (both from my laptop AND when remoting into the server).
The only thing I can think of is I had to have screwed up somewhere......or else it has something to do with what is happening in the CLR. I believe a CLR sproc is called in the SSISDB 2012 model if I am not mistaken. Unfortunately, my level of .NET experience is just not enough to even know where to begin.
I'll keep you appraised if I find anything out.
January 28, 2014 at 1:51 pm
sneumersky (1/28/2014)
I was running SSMS on my laptop as both the laptop admin and the SQL sysadmin when this happened. I even pasted the file path in windows explorer and the files opened without error (both from my laptop AND when remoting into the server).The only thing I can think of is I had to have screwed up somewhere......or else it has something to do with what is happening in the CLR. I believe a CLR sproc is called in the SSISDB 2012 model if I am not mistaken. Unfortunately, my level of .NET experience is just not enough to even know where to begin.
I'll keep you appraised if I find anything out.
Did you literally right-click on SSMS on chose "Run As Administrator"? UAC can be a pain sometimes.
There's a lot of CLR behind the scenes of the SSIS catalog.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 28, 2014 at 3:07 pm
Tried that too. No good.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply