October 29, 2015 at 1:20 pm
So I have about 10 SSIS packages. One main package which calls the other packages.
There are 3 database's
Master - The end point for all the data
Staging - Extraction DB where all sources are loaded into
sources include: Excel, CSV, SharePoint, and Oracle
SSISConfig - This holds the Package configuration for each package
Every connection is via a SQL Server Authenticated account.
The connection string for the SSISConfig DB is using localhost with the user configured.
The password is passed into the main package and an expression builds this connection and reads the DB.
This password is also passed to the child packages and they do the same thing to read to SSISConfig
Everything works fine, I add all my packages to SQL Server and can right click the package and run it.
I run it as 32-bit since I am pulling data from Excel.
So now I created a SQL Agent Job and ran into problems
First was sharepoint connection. I resolved it by creating proxy account
the job has only one step, to call the main package
I'm passing the password in, this is working
I've checked 32 bit execution. I know this is also working
But every time I try to execute the job, it runs for about 2 minutes. The amount of time it needs to run the Oracle extraction, so I know it's executing, I see the staging tables dropped and recreated and populated.
But it constantly throws errors like the following;
Message
Microsoft (R) SQL Server Execute Package Utility
Version 10.0.5500.0 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 2:55:40 PM
Error: 2015-10-29 14:55:45.92
Code: 0xC0014005
Source:
Description: The connection type "OLEDB" specified for connection manager "dbACS_Stage" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name.
End Error
Error: 2015-10-29 14:55:45.92
Code: 0xC0010018
Source:
Description: Error loading value "<DTS:ConnectionManager xmlns:DTS="www.microsoft.com/SqlServer/Dts">
<DTS:Property DTS:Name="DelayValidation">-1</DTS:Property>
<DTS:Property DTS:Name="ObjectName">dbACS_Stage</DTS:Property>
<DTS:Property DTS:Name="DTSID">{57EE6034-F401-4467-B72B-90CF0E1" from node "DTS:ConnectionManager".
End Error
Error: 2015-10-29 14:55:45.92
Code: 0xC00220E4
Source: Execute Core ETL
Description: Error 0xC0010014 while preparing to load the package. One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.
.
End Error
Error: 2015-10-29 14:55:48.73
Code: 0xC0014005
Source:
Description: The connection type "EXCEL" specified for connection manager "ExcelConnApps" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name.
End Error
Error: 2015-10-29 14:55:48.73
Code: 0xC0010018
Source:
Description: Error loading value "<DTS:ConnectionManager xmlns:DTS="www.microsoft.com/SqlServer/Dts">
<DTS:Property DTS:Name="DelayValidation">0</DTS:Property>
<DTS:Property DTS:Name="ObjectName">ExcelConnApps</DTS:Property>
<DTS:Property DTS:Name="DTSID">{5AF4A5F0-2657-414A-BD51-8810D7" from node "DTS:ConnectionManager".
End Error
Error: 2015-10-29 14:55:48.73
Code: 0xC00220E4
Source: Execute Excel Extraction
Description: Error 0xC0010014 while preparing to load the package. One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.
.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 2:55:40 PM
Finished: 2:57:18 PM
Elapsed: 97.236 seconds
The problem is it's the same error, but a different package every time
If I run this not as a job, everything runs fine.
October 29, 2015 at 1:48 pm
The problem here may be related to the pre-validation phase in SSIS. With the "DelayValidation" property set to "False" (which seems to be the case in your package), SSIS will try to validate every connection before execution to ensure that it works.
If you are dynamically building your connection strings, this pre-validation might fail because of the fact that the connection does not yet have all the properties set. I'd recommend that you set you "DelayValidation" property to "True" on every component/object you are dynamically configuring.
Hope this helps.
October 29, 2015 at 1:58 pm
Have you got all the OLE DB drivers installed on the server you're running this on? When it says EXCEL is not valid, I'd be concerned that I don't have a driver.
Steve.
October 29, 2015 at 1:58 pm
Your connection managers are being invalidated at run time ,this can happen for a number of reasons but the most common is when the protection level is set to do not save sensitive and the password is blanked out at runtime or not being provided before the package execution starts.
Delay validation will help for the excel path for the excel connection , for the OLEDB Connection you might want to confirm that the password is being assigned, have you enabled logging within the package?
October 29, 2015 at 2:08 pm
I restarted the machine and re-ran the job and it worked.
I saw this previously about the Delay Validation and updated all the packages.
I then re-imported them.
I guess SQL Server or Integrations Services had something cached.
But once machine was rebooted, everything ran successfully. I didn't change anything.
October 30, 2015 at 5:59 am
So it worked successfully yesterday.
I scheduled it execute every 2 hrs.
It ran at 6pm and failed again. It didn't run after that.
So this morning I tried to run the job manually and again it fails with the following;
Message
Microsoft (R) SQL Server Execute Package Utility
Version 10.0.5500.0 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 7:53:48 AM
Error: 2015-10-30 07:53:57.52
Code: 0xC0014005
Source:
Description: The connection type "OLEDB" specified for connection manager "dbACS_Stage" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name.
End Error
Error: 2015-10-30 07:53:57.52
Code: 0xC0010018
Source:
Description: Error loading value "<DTS:ConnectionManager xmlns:DTS="www.microsoft.com/SqlServer/Dts">
<DTS:Property DTS:Name="DelayValidation">-1</DTS:Property>
<DTS:Property DTS:Name="ObjectName">dbACS_Stage</DTS:Property>
<DTS:Property DTS:Name="DTSID">{5941E192-4F11-471D-8B33-ED9A677" from node "DTS:ConnectionManager".
End Error
Error: 2015-10-30 07:53:57.52
Code: 0xC00220E4
Source: Execute Sharepoint Extraction
Description: Error 0xC0010014 while preparing to load the package. One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.
.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 7:53:48 AM
Finished: 7:54:50 AM
Elapsed: 61.979 seconds
October 30, 2015 at 9:18 am
Did you try to set all the DelayValidation properties on your connection strings to true?
October 30, 2015 at 9:28 am
On the server running SSIS, have you installed the Microsoft.Jet.OLEDB provider (.XLS) or Microsoft.ACE.OLEDB provider (.XLSX) ?
http://microsoft-ssis.blogspot.com/2014/02/connecting-to-excel-xlsx-in-ssis.html
Also, I'm not sure if the proxy account SQLAgent is running under needs some level of permission to the windows registry, something specific to Excel, but that may be worth looking into as well.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 30, 2015 at 9:32 am
I've tried all these items.
The stupidest part is I can immediately turn around and re-run the job and it will run to completion.
So everything is there.
October 30, 2015 at 9:58 am
Do you have logging enabled, and is the logging method also using a dynamic connection string?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply