April 14, 2011 at 9:04 am
Hello Everyone 🙂
I am having issues with the deployment of an SSIS package, I have tested on my BIDS session and everythign seems to function. However, since I have configured the configuration file for the package data source and deployed it fails on a SQL Agent job. Details are as follows;
System Overview:
SQL Ver: SQL Server 2008 x86 SP1
Windows OS: Server 2003 R2 x86
BIDS: Running on Windows 7 Pro x64
Package Abstract:
Package collects data from a control database with regard to which systems that it is to run against. This information is in a result set that is then passed to a foreach loop that shreds the result set and runs a script task that modifies the connection manager for a shell connection called dynamicConnection via a script task using the following code;
String dSrc = Dts.Variables["str_sqlServerIpInstance"].Value.ToString();
String initalCat = Dts.Variables["str_schemeDb"].Value.ToString();
String uName = Dts.Variables["str_sqlLogin"].Value.ToString();
String pwd = Dts.Variables["str_sqlPwd"].Value.ToString();
ConnectionManager conMan = Dts.Connections["dynamicConnection"];
Dts.Connections["dynamicConnection"].Properties["ServerName"].SetValue(conMan, dSrc);
Dts.Connections["dynamicConnection"].Properties["InitialCatalog"].SetValue(conMan, initalCat);
Dts.Connections["dynamicConnection"].Properties["UserName"].SetValue(conMan, uName);
Dts.Connections["dynamicConnection"].Properties["Password"].SetValue(conMan, pwd);
I have tried running the package in 32bit runtime, job owner is SA, and I have tried a manual run of the job and I always receive the error details below.
The Error that I am receiveing from the SQL Agent history is as follows;
Executed as user: SO-CC\svc-SSIS01-SQLAgent. Microsoft (R) SQL Server
Execute Package Utility Version 10.0.2531.0 for 32-bit Copyright (C)
Microsoft Corp 1984-2005. All rights reserved.
Started: 15:30:00
Error: 2011-04-14 15:30:16.72
Code: 0xC0202009
Source: <%PackageName%> Connection manager "dynamicConnection"
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available.
Source: "Microsoft SQL Server Native Client 10.0"
Hresult: 0x80004005
Description: "Login timeout expired". An OLE DB record is available.
Source: "Microsoft SQL Server Native Client 10.0"
Hresult: 0x80004005
Description: "A network-related or instance-specific error has occurred while establishing a connection to SQL Server.
Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured
to allow remote connections. For more information see SQL Server Books Online.".
An OLE DB record is available.
Source: "Microsoft SQL Server Native Client 10.0"
Hresult: 0x80004005
Description: "SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. ". End Error
Error: 2011-04-14 15:30:16.72
Code: 0xC020801C
Source: Data Flow Task Get Data [1]
Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager
"dynamicConnection" failed with error code 0xC0202009. There may be error messages posted before this with more information
on why the AcquireConnection method call failed. End Error
Error: 2011-04-14 15:30:16.72
Code: 0xC0047017
Source: Data Flow Task SSIS.Pipeline
Description: component "Get Data" (1) failed validation and returned error code 0xC020801C. End Error
Error: 2011-04-14 15:30:16.72
Code: 0xC004700C
Source: Data Flow Task SSIS.Pipeline
Description: One or more component failed validation. End Error
Error: 2011-04-14 15:30:16.72
Code: 0xC0024107
Source: Data Flow Task
Description: There were errors during task validation. End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 15:30:00
Finished: 15:30:16
Elapsed: 16.531 seconds.
The package execution failed. The step failed.
If anyone can throw any light on my issue it would be appreciated, any more information needed please let me know.
Thanks in advance
JQ
April 19, 2011 at 2:32 pm
Not sure this is the same problem but I had same problem,and was bale to solve by saving package with Protection Level prperty as DontSaveSensitive,as your job owner is SA so wouldnt have to cause any probs but I created Credentials and proxy account with that and then created job with proxy account.It is working well now.
April 19, 2011 at 2:54 pm
Last time I saw this it was because the error was lying. Make sure the user running your SQLAgent has rights to all the target databases that you're attempting to connect to.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 20, 2011 at 12:32 am
Craig Farrell (4/19/2011)
Last time I saw this it was because the error was lying.
What is this? An episode from House MD? Everybody lies... 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 20, 2011 at 12:01 pm
Koen Verbeeck (4/20/2011)
Craig Farrell (4/19/2011)
Last time I saw this it was because the error was lying.What is this? An episode from House MD? Everybody lies... 😀
More amusing is that last year my family threatened to get me a cane if I acted any more like him.
"Everyone lies, it's just a matter of why. MS SQL Server lies because it's insecure."
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 20, 2011 at 11:29 pm
Craig Farrell (4/20/2011)
Koen Verbeeck (4/20/2011)
Craig Farrell (4/19/2011)
Last time I saw this it was because the error was lying.What is this? An episode from House MD? Everybody lies... 😀
More amusing is that last year my family threatened to get me a cane if I acted any more like him.
"Everyone lies, it's just a matter of why. MS SQL Server lies because it's insecure."
😀 :hehe:
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply