Tag: SSIS

Below is the final error message l kept getting when setting up a SQL Server Agent Job which ran an SSIS package. The SSIS package is designed to create a folder in a network share as well as copy some files into that folder.

Message
Microsoft (R) SQL Server Execute Package Utility
Version 10.50.4000.0 for 64-bit
Copyright (C) Microsoft Corporation 2010. All rights reserved.

Started:  11:03:22 AM
Error: 2014-10-07 11:03:23.35
Code: 0x00000001
Source: Create Reports
Description: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. —> System.InvalidOperationException: Unable to generate a temporary class (result=1).
error CS2001: Source file ‘C:WindowsTEMPp6e47evu.0.cs’ could not be found
error CS2008: No inputs specified

at System.Xml.Serialization.Compiler.Compile(Assembly parent, String ns, XmlSerializerCompilerParameters xmlParameters, Evidence evidence)
at System.Xml.Serialization.TempAssembly.GenerateAssembly(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, Evidence evidence, XmlSerializerCompilerParameters parameters, Assembly assembly, Hashtable assemblies)
at System.Xml.Serialization.TempAssembly..ctor(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, String location, Evidence evidence)
at System.Xml.Serialization.XmlSerializer.GetSerializersFromCache(XmlMapping[] mappings, Type type)
at System.Xml.Serialization.XmlSerializer.FromMappings(XmlMapping[] mappings, Type type)
at System.Web.Services.Protocols.SoapClientType..ctor(Type type)
at System.Web.Services.Protocols.SoapHttpClientProtocol..ctor()
at ReportExecutionService..ctor()
at ST_960eb2bb9aa341f4a31d1409aa0aadbb.csproj.ScriptMain.Main()
— End of inner exception stack trace —
at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started:  11:03:22 AM
Finished: 11:03:23 AM
Elapsed:  0.546 seconds

Resolution:

• Create a credential on the security level of the sql server instance
• USE [master]

  GO

  CREATE CREDENTIAL [CredOne] WITH IDENTITY = N’DomainCredOneUser’, SECRET = N’Password’

  GO

• Create a proxy on the job module of the sql server instance
• USE [msdb]

  GO

  EXEC msdb.dbo.sp_add_proxy @proxy_name=N’CredOne’,@credential_name=N’CredOne’,

  @enabled=1,

  @description=N’My description’

  GO

  EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N’CredOne’, @subsystem_id=2

  GO

  EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N’CredOne’, @subsystem_id=3

  GO

  EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N’CredOne’, @subsystem_id=10

  GO

  EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N’CredOne’, @subsystem_id=9

  GO

  EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N’CredOne’, @subsystem_id=11

  GO

  EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N’CredOne’, @subsystem_id=12

  GO

• Ensure the network share folder has full control permission given to the user domainuser
• Ensure modify permission is given to that same domain account to the folder c:windowstemp on the server box
• The SQL Job should now run without issues