Category: SSIS

Unable to start execution of step 1 (reason: JobOwner domain\domainuser

On a server running SQL 2014 SSIS with SSISDB configured, one of the SQL Agent jobs configured to run using a proxy account kept failing with the error below.

Message

Unable to start execution of step 1 (reason: JobOwner domain\domainuser

doesn‘t have permissions to use proxy 1 for subsystem SSIS). The step failed.

Solution that worked for me in my situation. 

Query your proxys and see if that proxy is disabled. If so, enable it.

SELECT * FROM msdb.dbo.sysproxies

USE msdb ;

GO

EXEC dbo.sp_update_proxy

@proxy_name = ‘MyProxyUSerUser’,

@enabled = 1;

GO

 

Resolving SQL Server Agent Job Issue – System.InvalidOperationException: Unable to generate a temporary class (result=1)

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

 

SSIS 2008 R2 to SSIS 2014

Notes:

After performing an in-place upgrade on a SQL 2008 R2 server to SQL 2014, l had access issue to SSIS 2014.

I also noticed [rom SSCM SQL Server Configuration Mananger] that SSIS 2008 R2 ie SSIS version 10.0 was still running, using the NT Autohoritynetworkservice account while SSIS 2014 ie version 12.0 was also running – but using the nt servicemsdtsserver120 local account.

From a remote SSMS 2008 R2 window, l could access the target SSIS instance and it shows up with version 10.50.4000 [the number for SSIS 2008 R2]

From the local upgraded server’s SSMS 2014 , connecting to the target SSIS 2014 shows a connection with version number 12.0.2000

Steps taken to resolve the initial access issue.

 

ssis_12_issue

Microsoft SSIS 2012 Data Import – WA State Corporation Data

Below are screenshots of SSIS – Microsoft SQL Server Integration Services 2012 importing data from Washington State Secretary of State Corporations public data file. Click here for the source link.

8-1-2013 11-59-08 PM 8-1-2013 11-58-58 PM 8-1-2013 11-58-40 PM 8-2-2013 12-02-16 AM 8-2-2013 12-01-27 AM 8-1-2013 11-59-41 PM 8-2-2013 12-00-00 AM

 

Below is the target table creation T-SQL code

USE [CorpWA]
GO

/****** Object: Table [dbo].[Corporations] Script Date: 8/2/2013 12:16:57 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Corporations](
[UBI] [varchar](50) NULL,
[BusinessName] [varchar](50) NULL,
[Category] [varchar](50) NULL,
[StateOfIncorporation] [varchar](50) NULL,
[DateOfIncorporation] [varchar](50) NULL,
[ExpirationDate] [varchar](50) NULL,
[Duration] [varchar](50) NULL,
[DissolutionDate] [varchar](50) NULL,
[RecordStatus] [varchar](50) NULL,
[Type] [varchar](50) NULL,
[RegisteredAgentName] [varchar](50) NULL,
[RegisteredAgentAddress] [varchar](50) NULL,
[RegisteredAgentCity] [varchar](50) NULL,
[RegisteredAgentState] [varchar](50) NULL,
[RegisteredAgentZip] [varchar](50) NULL,
[AlternateAddress] [varchar](50) NULL,
[AlternateCity] [varchar](50) NULL,
[AlternateState] [varchar](50) NULL,
[AlternateZip] [varchar](50) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

 

 

USE [CorpWA]
GO

/****** Object: Table [dbo].[GoverningPersons] Script Date: 8/2/2013 12:20:44 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[GoverningPersons](
[UBI] [varchar](50) NULL,
[Title] [varchar](100) NULL,
[FirstName] [varchar](100) NULL,
[MiddleName] [varchar](100) NULL,
[LastName] [varchar](100) NULL,
[Address] [varchar](100) NULL,
[City] [varchar](50) NULL,
[State] [varchar](50) NULL,
[Zip] [varchar](50) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Results – Table Report

8-2-2013 12-21-48 AM

Importing Edgar Database

We will be showing how to import database data from Edgar, with SQL code as well as SSIS Import Wizard configuration settings.

“Since 1934, the SEC has required disclosure in forms and documents. In 1984, EDGAR began collecting electronic documents to help investors get information”

First import the latest master.idx from http://www.sec.gov/edgar/indices/fullindex.htm and edit the file as shown below.

1-24-2013 5-27-40 PM 1-24-2013 5-28-05 PM

Create a database called Edgar

Create a table called master1 using T-SQL code below

Right click on Edgar database,click Tasks, then Import dat and use settings below.

 

USE [Edgar]
GO

/****** Object: Table [dbo].[master_02102013] Script Date: 2/10/2013 1:41:59 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[master_02102013](
[MasterID] [int] IDENTITY(1,1) NOT NULL,
[CIK] [varchar](50) NULL,
[Company Name] [varchar](200) NULL,
[Form Type] [varchar](50) NULL,
[Date Filed] [varchar](50) NULL,
[Filename] [varchar](50) NULL,
CONSTRAINT [PK_master_02102013] PRIMARY KEY CLUSTERED
(
[MasterID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

1-24-2013 5-56-23 PM 1-24-2013 5-55-57 PM 1-24-2013 5-55-45 PM 1-24-2013 5-55-20 PM 1-24-2013 5-54-57 PM 1-24-2013 5-53-34 PM 1-24-2013 5-57-19 PM 1-24-2013 5-57-03 PM 1-24-2013 5-56-48 PM

Make sure Identity insert is checked off. Also make sure you select delete existing rows NOT append row data.

Example Search T-SQL Query after import is completed.

SELECT TOP 100000 [MasterID]
,[CIK]
,[Company Name]
,[Form Type]
,[Date Filed]
,[Filename]
FROM [Edgar].[dbo].[master]

Result

1-24-2013 9-07-07 PM

Coming soon – a script, to extract ftp filepaths from above results and possibly a script [in python] tp download from ftp.sec.gov all the files into appropriate columns in our database, which currently houses 53,398 rows of data.

————–

Scriot for downloading ONE file, from say data in first row of table created above. Save this as test.scr

anonymous
<your email address goes in here>
cd edgar/data/1000045/
ls *.txt
get 0001193125-13-046001.txt
quit

Script for a batchfile to execute above. Save as, for example: gosec.bat

cls
ftp -s:test.scr ftp.sec.gov