Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 SSIS and Import/Export (2012)
 Different Results from SSDT Vs SQL Agent Execution

Author  Topic 

Analyzer
Posting Yak Master

115 Posts

Posted - 2015-04-28 : 12:21:35
Hi,

When I run 60 table load packes from single master package directly from SSDT the package completes successfully..

When I DEPLOY the package into Integration Catalogue and run from SQL Agent only 50% packages complete successfully, other 50% never start. Errors just say time-outs.

Would the same package(s) behave differently when run from SQL Agent. Permissions is not the issue knowing the Agent runs with the Agent service account. And to my knowledge SQL does not by default throttle Agent workloads.

Any ideas?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-04-28 : 12:50:50
What kind of timeouts are they? Connection timeouts or query timeouts?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Analyzer
Posting Yak Master

115 Posts

Posted - 2015-04-28 : 16:15:39
Connection time outs.

Ran the SSIS packages via SQL Agent at different times and the complete opposite 50% tables loaded and the other failed to start. All packages run separately run successfully. Unusual behaviour from SQL Agent executed SSIS packages.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-04-28 : 16:17:16
Are the databases in Availability Groups in a multi-subnet configuration?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Analyzer
Posting Yak Master

115 Posts

Posted - 2015-04-28 : 17:25:36
Still a vanilla installation. Still loading test data from a DWH into this ODS. Not even got workloads (reads) running yet.

No AG and single subnet. Source is Teradata DWH but I know each package works, but just 50% when executed from SQL Agent..

Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-04-28 : 17:44:07
You can try increasing the connection timeout output for each data source, and/or add an alias to the SQL Server for each of the servers.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
   

- Advertisement -