Issue Deploying SSIS Package

1
votes

I am working at a small company with no DBA but we use SQL Server for reporting. I'd like to get SSIS up and running for some ETL and back end processes. I used to develop in the past so have some basic skills but no DBA experience at all. I've been able to get SSIS installed at least I see the integration services folders and the SSIS database. But after working through what I thought were security issues and getting CLR enabled I'm receiving the following message. Thought I'd ask this group as I start to research to see if anyone had a quicker answer.

We are using SQL Server version 2019, Visual Studio 2019 and SSMS version 15.

TITLE: SQL Server Integration Services
An error occurred in the Microsoft .NET Framework while trying to load assembly id 65536. The server may be running out of resources, or the assembly may not be trusted. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error: System.IO.FileLoadException: Could not load file or assembly 'microsoft.sqlserver.integrationservices.server, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. An error relating to security occurred. (Exception from HRESULT: 0x8013150A) System.IO.FileLoadException: at System.Reflection.RuntimeAssembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, RuntimeAssembly locationHint, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks) at System.Reflection.RuntimeAssembly.InternalLoadAssemblyName(AssemblyName assemblyRef, Evidence assemblySecurity, RuntimeAssembly reqAssembly, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks) at System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean forIntrospection) at System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection) at System.Reflection.Assembly.Load(String assemblyString) (Microsoft SQL Server, Error: 10314)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=15.00.2000&EvtSrc=MSSQLServer&EvtID=10314&LinkId=20476

1
sql-serverdeploymentssis
Can you walk me through what security issues you were running into? Adding the SSISDB is usually a few clicks and you're done. Is there anything unusual about your SQL Server installation? What smells weird to me is that it's reporting Version 12 assembly error but V12 is SQL Server 2014. - billinkc
If you run SELECT SERVERPROPERTY('ProductVersion') AS ProductVersion; what does version does it return? - billinkc
Hey there- after researching what I think i needed was a sql server login that has windows authentication which i didn't want my domain id to have that level of security. So, I created a windows auth ssisxxxx account then created a windows ad that I used to deploy the package from visual studio. open to other ideas but thought this the cleanest approach as I didn't want my day to day domain acct having what I thought was elevated privilege's on the db. - Steve
I stand corrected on the version it's actually 15.02. Was told by the vendor that supports our server that it was 2019 so I probably need to get corrected before I do anything else. Apologizes as i should have checked that first but took their word for it. - Steve
15.0.2000.5 would be the RTM version of SQL Server 2019 so they didn't lead you astray. Now, you could use some patching but I think your bigger issue is the auth you identified in the previous comment - billinkc

1 Answers

0
votes

The SSIS is installed using the same installation package as the sql server.Sql server version and SSIS version mismatch and thats why the SSISDB is having issues.