July 7, 2016 at 5:09 am
I have an SSIS package which loops through 3 servers gather the data as per the below
SQL code and dumps it into a database.
But when I add 1 more server which has SSIS installed on it it is throwing up the following error.
I have also ran the below SQL code on the server causing the error and it returns data.
Error:
[OLE DB Source [459]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Server.Database" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
[SSIS.Pipeline] Error: component "OLE DB Source" (459) failed validation and returned error code 0xC020801C.
[SSIS.Pipeline] Error: One or more component failed validation.
Error: There were errors during task validation.
Warning: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (5) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
How do I fix this.
Thanks
select Servername=cast(@@servername as varchar(128)),
PackageName = cast(PCK.name as varchar(200)),
--PCK.[description] AS [Description],
PackageDescription=CAST(PCK.description as varchar(200)),
FolderName=CAST(FLD.foldername as varchar(200))
-- ,FLD.foldername AS FolderName
,PackageType1=cast(CASE PCK.packagetype
WHEN 0 THEN 'Default client'
WHEN 1 THEN 'I/O Wizard'
WHEN 2 THEN 'DTS Designer'
WHEN 3 THEN 'Replication'
WHEN 5 THEN 'SSIS Designer'
WHEN 6 THEN 'Maintenance Plan'
ELSE 'Unknown' END as varchar(200))
--,LG.name AS OwnerName
,OwnerName=CAST(LG.name as varchar(200))
,PCK.isencrypted AS IsEncrypted
,PCK.createdate AS CreateDate
,Version=cast(CONVERT(varchar(10), vermajor)
+ '.' + CONVERT(varchar(10), verminor)
+ '.' + CONVERT(varchar(10), verbuild) as varchar(200))
,VersionComments=cast(PCK.vercomments AS varchar(200))
,DATALENGTH(PCK.packagedata) AS PackageSize
FROM msdb.dbo.sysssispackages AS PCK
LEFT JOIN msdb.dbo.sysssispackagefolders AS FLD
ON PCK.folderid = FLD.folderid
LEFT JOIN sys.syslogins AS LG
ON PCK.ownersid = LG.sid
ORDER BY PCK.name
July 7, 2016 at 8:17 pm
As you already possibly have figured out the error message is indicating that it cannot connect to the Server/Database. I would double check the SSIS Connection Managers
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply