January 13, 2017 at 8:40 am
Query 1:
I have tried to execute a SSIS package and the following is the result:
DataFlow Task 1:Error: Cannot open the datafile".OUT file".
DataFlow Task 1:Error: Source - (.OUT file) failed the pre-execute phase and returned error code 0xC020200E.
Can someone suggest why it inst loading the file, it is stored on a shared network but the user has permissions to read the file?
Query 2(If it makes any sense):
We have developed an SSIS package that opens a source from our network drive, of which the file permissions are authorised for the user.
Initially we have created a package and have deployed it to an Integration Service Catalog. From there we scheduled the package as a job and it worked without an issue.
Then we want to make it an automated process with a variable destination filename - we used the sql server command prompt to execute the package.
This passed in the parameters for the package to use.
However now when the package runs we get the error message - DataFlow Task 1:Error: Cannot open the datafile "xxxx"
second message - failed the pre-execute phase and returned error code 0xC020200E.
We are using a new user with permissions to access the files, could there be anything else we are missing which would mean that sql server wont open the file?
We believe it is permission related, Can anyone share some ideas?
Thanks in advance.
January 13, 2017 at 8:49 am
EwanAC - Friday, January 13, 2017 8:40 AMQuery 1:
I have tried to execute a SSIS package and the following is the result:
DataFlow Task 1:Error: Cannot open the datafile".OUT file".
DataFlow Task 1:Error: Source - (.OUT file) failed the pre-execute phase and returned error code 0xC020200E.
Can someone suggest why it inst loading the file, it is stored on a shared network but the user has permissions to read the file?Query 2(If it makes any sense):
We have developed an SSIS package that opens a source from our network drive, of which the file permissions are authorised for the user.
Initially we have created a package and have deployed it to an Integration Service Catalog. From there we scheduled the package as a job and it worked without an issue.
Then we want to make it an automated process with a variable destination filename - we used the sql server command prompt to execute the package.
This passed in the parameters for the package to use.
However now when the package runs we get the error message - DataFlow Task 1:Error: Cannot open the datafile "xxxx"
second message - failed the pre-execute phase and returned error code 0xC020200E.
We are using a new user with permissions to access the files, could there be anything else we are missing which would mean that sql server wont open the file?
We believe it is permission related, Can anyone share some ideas?Thanks in advance.
Are you running it via a job?
- Damian
January 13, 2017 at 8:54 am
DamianC - Friday, January 13, 2017 8:49 AMEwanAC - Friday, January 13, 2017 8:40 AMQuery 1:
I have tried to execute a SSIS package and the following is the result:
DataFlow Task 1:Error: Cannot open the datafile".OUT file".
DataFlow Task 1:Error: Source - (.OUT file) failed the pre-execute phase and returned error code 0xC020200E.
Can someone suggest why it inst loading the file, it is stored on a shared network but the user has permissions to read the file?Query 2(If it makes any sense):
We have developed an SSIS package that opens a source from our network drive, of which the file permissions are authorised for the user.
Initially we have created a package and have deployed it to an Integration Service Catalog. From there we scheduled the package as a job and it worked without an issue.
Then we want to make it an automated process with a variable destination filename - we used the sql server command prompt to execute the package.
This passed in the parameters for the package to use.
However now when the package runs we get the error message - DataFlow Task 1:Error: Cannot open the datafile "xxxx"
second message - failed the pre-execute phase and returned error code 0xC020200E.
We are using a new user with permissions to access the files, could there be anything else we are missing which would mean that sql server wont open the file?
We believe it is permission related, Can anyone share some ideas?Thanks in advance.
Are you running it via a job?
You might need to set up credentials and a SSIS proxity to run as
- Damian
January 13, 2017 at 9:03 am
DamianC - Friday, January 13, 2017 8:49 AMEwanAC - Friday, January 13, 2017 8:40 AMQuery 1:
I have tried to execute a SSIS package and the following is the result:
DataFlow Task 1:Error: Cannot open the datafile".OUT file".
DataFlow Task 1:Error: Source - (.OUT file) failed the pre-execute phase and returned error code 0xC020200E.
Can someone suggest why it inst loading the file, it is stored on a shared network but the user has permissions to read the file?Query 2(If it makes any sense):
We have developed an SSIS package that opens a source from our network drive, of which the file permissions are authorised for the user.
Initially we have created a package and have deployed it to an Integration Service Catalog. From there we scheduled the package as a job and it worked without an issue.
Then we want to make it an automated process with a variable destination filename - we used the sql server command prompt to execute the package.
This passed in the parameters for the package to use.
However now when the package runs we get the error message - DataFlow Task 1:Error: Cannot open the datafile "xxxx"
second message - failed the pre-execute phase and returned error code 0xC020200E.
We are using a new user with permissions to access the files, could there be anything else we are missing which would mean that sql server wont open the file?
We believe it is permission related, Can anyone share some ideas?Thanks in advance.
Are you running it via a job?
I have run it as job and it works properly, then when we use some SQL to execute the the package(whilst we pass the parameters in too). Does that make sense?
January 13, 2017 at 9:15 am
EwanAC - Friday, January 13, 2017 9:03 AMI have run it as job and it works properly, then when we use some SQL to execute the the package(whilst we pass the parameters in too). Does that make sense?
Are the job and the SQL on the same server? Do SQL Server and SQL Server Agent run under the same service account? Please will you post the command from your job step and the SQL command? You may obfuscate as necessary.
John
January 13, 2017 at 9:25 am
John Mitchell-245523 - Friday, January 13, 2017 9:15 AMEwanAC - Friday, January 13, 2017 9:03 AMI have run it as job and it works properly, then when we use some SQL to execute the the package(whilst we pass the parameters in too). Does that make sense?Are the job and the SQL on the same server? Do SQL Server and SQL Server Agent run under the same service account? Please will you post the command from your job step and the SQL command? You may obfuscate as necessary.
John
The SQL Server and Agent are both on the same service account. They are being run on the same server.
The SQL:
DECLARE @execution_id BigInt;
EXEC [SSISDB].[catalog].[create_execution] @package_name = N'SEI_Accounts_SSIS.dtsx',@execution_id = @execution_id OUTPUT, @folder_name = N'xx', @project_name = N'xxx', @use32bitruntime = False, @reference_id = Null; SELECT @execution_id; DECLARE @var0 smallint = 3; EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=50, @parameter_name=N'LOGGING_LEVEL', @parameter_value=@var0; EXEC [SSISDB].[catalog].[set_execution_parameter_value ]@execution_id, @object_type=30, @parameter_name=N'CM.SourceConnectionFlatFile.ConnectionString', @parameter_value=N'filepath';EXEC [SSISDB].[catalog].[start_execution] @execution_id
SELECT [STATUS] FROM [SSISDB].catalog.executions WHERE execution_id = @execution_id
Does this help?
January 13, 2017 at 9:31 am
EwanAC - Friday, January 13, 2017 9:25 AMJohn Mitchell-245523 - Friday, January 13, 2017 9:15 AMEwanAC - Friday, January 13, 2017 9:03 AMI have run it as job and it works properly, then when we use some SQL to execute the the package(whilst we pass the parameters in too). Does that make sense?Are the job and the SQL on the same server? Do SQL Server and SQL Server Agent run under the same service account? Please will you post the command from your job step and the SQL command? You may obfuscate as necessary.
John
The SQL Server and Agent are both on the same service account. They are being run on the same server.
The SQL:
DECLARE @execution_id BigInt;
EXEC [SSISDB].[catalog].[create_execution] @package_name = N'SEI_Accounts_SSIS.dtsx',@execution_id = @execution_id OUTPUT, @folder_name = N'xx', @project_name = N'xxx', @use32bitruntime = False, @reference_id = Null; SELECT @execution_id; DECLARE @var0 smallint = 3; EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=50, @parameter_name=N'LOGGING_LEVEL', @parameter_value=@var0; EXEC [SSISDB].[catalog].[set_execution_parameter_value ]@execution_id, @object_type=30, @parameter_name=N'CM.SourceConnectionFlatFile.ConnectionString', @parameter_value=N'filepath';EXEC [SSISDB].[catalog].[start_execution] @execution_idSELECT [STATUS] FROM [SSISDB].catalog.executions WHERE execution_id = @execution_id
Does this help?
Oooh, sorry. I'm out of my depth now - I've never used the SSIS Catalog.
John
January 13, 2017 at 9:33 am
John Mitchell-245523 - Friday, January 13, 2017 9:31 AMEwanAC - Friday, January 13, 2017 9:25 AMJohn Mitchell-245523 - Friday, January 13, 2017 9:15 AMEwanAC - Friday, January 13, 2017 9:03 AMI have run it as job and it works properly, then when we use some SQL to execute the the package(whilst we pass the parameters in too). Does that make sense?Are the job and the SQL on the same server? Do SQL Server and SQL Server Agent run under the same service account? Please will you post the command from your job step and the SQL command? You may obfuscate as necessary.
John
The SQL Server and Agent are both on the same service account. They are being run on the same server.
The SQL:
DECLARE @execution_id BigInt;
EXEC [SSISDB].[catalog].[create_execution] @package_name = N'SEI_Accounts_SSIS.dtsx',@execution_id = @execution_id OUTPUT, @folder_name = N'xx', @project_name = N'xxx', @use32bitruntime = False, @reference_id = Null; SELECT @execution_id; DECLARE @var0 smallint = 3; EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=50, @parameter_name=N'LOGGING_LEVEL', @parameter_value=@var0; EXEC [SSISDB].[catalog].[set_execution_parameter_value ]@execution_id, @object_type=30, @parameter_name=N'CM.SourceConnectionFlatFile.ConnectionString', @parameter_value=N'filepath';EXEC [SSISDB].[catalog].[start_execution] @execution_idSELECT [STATUS] FROM [SSISDB].catalog.executions WHERE execution_id = @execution_id
Does this help?
Oooh, sorry. I'm out of my depth now - I've never used the SSIS Catalog.
John
No problem, are there any issues with permissions with windows and SSIS that you know of?
January 13, 2017 at 9:33 am
DamianC - Friday, January 13, 2017 8:54 AMDamianC - Friday, January 13, 2017 8:49 AMEwanAC - Friday, January 13, 2017 8:40 AMQuery 1:
I have tried to execute a SSIS package and the following is the result:
DataFlow Task 1:Error: Cannot open the datafile".OUT file".
DataFlow Task 1:Error: Source - (.OUT file) failed the pre-execute phase and returned error code 0xC020200E.
Can someone suggest why it inst loading the file, it is stored on a shared network but the user has permissions to read the file?Query 2(If it makes any sense):
We have developed an SSIS package that opens a source from our network drive, of which the file permissions are authorised for the user.
Initially we have created a package and have deployed it to an Integration Service Catalog. From there we scheduled the package as a job and it worked without an issue.
Then we want to make it an automated process with a variable destination filename - we used the sql server command prompt to execute the package.
This passed in the parameters for the package to use.
However now when the package runs we get the error message - DataFlow Task 1:Error: Cannot open the datafile "xxxx"
second message - failed the pre-execute phase and returned error code 0xC020200E.
We are using a new user with permissions to access the files, could there be anything else we are missing which would mean that sql server wont open the file?
We believe it is permission related, Can anyone share some ideas?Thanks in advance.
Are you running it via a job?
You might need to set up credentials and a SSIS proxity to run as
Why would that be? Can you explain?
January 13, 2017 at 9:38 am
EwanAC - Friday, January 13, 2017 9:33 AMNo problem, are there any issues with permissions with windows and SSIS that you know of?
Not really. If you're running the same command on the same server in the same security context, I can't think why it would succeed in one place but fail in another.
John
January 13, 2017 at 9:50 am
John Mitchell-245523 - Friday, January 13, 2017 9:38 AMEwanAC - Friday, January 13, 2017 9:33 AMNo problem, are there any issues with permissions with windows and SSIS that you know of?Not really. If you're running the same command on the same server in the same security context, I can't think why it would succeed in one place but fail in another.
John
Okay it might be something to do with the file server which we are getting the files off. I will take another look. Thanks for your help.
January 13, 2017 at 9:55 am
EwanAC - Friday, January 13, 2017 8:40 AMQuery 1:
I have tried to execute a SSIS package and the following is the result:
DataFlow Task 1:Error: Cannot open the datafile".OUT file".
DataFlow Task 1:Error: Source - (.OUT file) failed the pre-execute phase and returned error code 0xC020200E.
Can someone suggest why it inst loading the file, it is stored on a shared network but the user has permissions to read the file?Query 2(If it makes any sense):
We have developed an SSIS package that opens a source from our network drive, of which the file permissions are authorised for the user.
Initially we have created a package and have deployed it to an Integration Service Catalog. From there we scheduled the package as a job and it worked without an issue.
Then we want to make it an automated process with a variable destination filename - we used the sql server command prompt to execute the package.
This passed in the parameters for the package to use.
However now when the package runs we get the error message - DataFlow Task 1:Error: Cannot open the datafile "xxxx"
second message - failed the pre-execute phase and returned error code 0xC020200E.
We are using a new user with permissions to access the files, could there be anything else we are missing which would mean that sql server wont open the file?
We believe it is permission related, Can anyone share some ideas?Thanks in advance.
Firstly, note that .out is an invalid file name, so I am not surprised that this is giving you an error.
I have seen instances where running an SSISDB package from SQL Agent is successful and running from T-SQL is not – even where the T-SQL being executed is identical. I never got to the bottom of these errors, because I heard someone muttering the word Kerberos and had a sudden attack of narcolepsy. SQL Agent always seems to work, though.
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy