SSIS and SQL Agent – running jobs using proxy vs using MSA

We have multiple servers, each with its own SQL Server instance.

We have external companies that develop applications for us. This companies deploy SSIS packages and run them via SQL Agent.

I’m wondering how our setup should look like security vise.

Scenario 1

  • SQL Agent Service account – Local Virtual Account
  • SSIS login – personal domain login for each employee
  • Job owner – personal domain account of whoever created it
  • Step run as – domain proxy account

It seems quite "normal" scenario, but I’m not sure if it is still relevant. Microsoft changed some things concerning security and introduced Virtual and Managed Service Accounts couple years back. Yet many resources found online still do not mention them and it seems like they treat default service account the old way. So maybe it is now acceptable to use default SQL Service account to run the jobs? Like in scenario 2.

Scenario 2

  • SQL Agent Service account – Local Virtual Account
  • SSIS login – personal domain login for each employee
  • Job owner – personal domain account of whoever created it
  • Step run as – SQL Agent Service account

This uses Virtual Account to run the jobs, but Microsoft documentation mention that in case we need to access remote resources we should use MSA instead. And it is the case for us.

*When resources external to the SQL Server computer are needed, Microsoft recommends using a Managed Service Account (MSA), configured with the minimum privileges necessary. ** When installed on a Domain Controller, a virtual account as the service account isn’t supported.

Scenario 3

  • SQL Agent Service account – Managed Service Account (MSA)
  • SSIS login – personal domain login for each employee
  • Job owner – personal domain account of whoever created it
  • Step run as – SQL Agent Service account (MSA)

There is also a question about who should own the job. There is a possibility of creating generic SSIS login that would be used by everyone to deploy packages and this login would own all the jobs steps.

Scenario 4

  • SQL Agent Service account – Managed Service Account (MSA)
  • SSIS login – generic domain login for working with SSIS
  • Job owner – SSIS domain login
  • Step run as – SQL Agent Service account (MSA)

Which scenario would you recommend? Or maybe there is a different preferable scenario?

Can SSIS packages call other SSIS packages in the connection string?

I am running an SSIS package that transfers data from one (SQL2008) server to another (SQL2000). However after P2V conversion the SQL2008 server cannot execute an SSIS package due to a user authentication error.

Lets say the package is called "Transfer-Go". In the connection string of that package can it call another SSIS package? In the SSIS library there is another package with the same that appears in the string (called Transfer-Now) name The string is below:

Data Source=<IP>;User ID=<user>;Initial Catalog=<db_name>;Provider=SQLNCLI10.1;Persist Security Info=True;OLE DB Services=-13;Auto Translate=False;Application Name=SSIS-<Transfer-Now-name of other SSIS package>-{8ABA18EE-637E-424F-A3F7-F7E4EA50DD9D}<IP.db_name.user>; 

So is this SSIS package connection string calling that package?

And if the credentials are wrong in that package could that be why I am unable to authenticate?

Thanks for any input, not a DB/SQL guy at all so I apologize if I sound green here.

I manage to connect to Azure Analysis Services from SSMS, but not from SSIS

I’m new to the Microsoft Server Suite.

I’ve downloaded SSMS and connected to Azure Analysis Services from it. I’m able to query my data using mdx without any problems.

However, I actually intend to build an ETL pipeline with the AAS cube as one of the sources. So I installed SSIS and have been trying to connect it to the AAS cube.

I first add "Analysis Services Processing Task" to the package. The result looks ok (when I click on "Test connection" the result is positive). But when I click on "Add", it doesn’t detect any cubes (there are two on the AAS server specified):

enter image description here

I assumed it worked anyway, but I can’t query the cube no matter how I try to do that. I added "Execute SQL task", but when I run it, it gives me an error:

enter image description here

enter image description here

enter image description here

The error message is:

An OLE DB record is available. Source: "Microsoft OLE DB Driver for SQL Server" Hresult: 0x80004005 Description: "Login timeout expired". An OLE DB record is available. Source: "Microsoft OLE DB Driver for SQL Server" 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 OLE DB Driver for SQL Server" Hresult: 0x80004005 Description: "Named Pipes Provider: Could not open a connection to SQL Server [53]. ". Error: 0xC00291EC at Execute SQL Task, Execute SQL Task: Failed to acquire connection "asazure://northeurope.asazure.windows.net/xxxx". Connection may not be configured correctly or you may not have the right permissions on this connection. Task failed: Execute SQL Task Warning: 0x80019002 at Package: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. SSIS package "C:\Users176\source\repos\Integration Services Project1\Integration Services Project1\Package.dtsx" finished: Failure. The program ‘[18664] DtsDebugHost.exe: DTS’ has exited with code 0 (0x0).

Any ideas?

Attunity RMSCDC for SSIS not showing up in VS 2017 (SSDT)

I’m working on a task to upgrade our SQL Server Environment from 2008R2 to 2017. There are no databases and only SSIS packages that I need to upgrade/migrate. However, the SSIS packages are configured with the third party component (Attunity) which is making the move challenging. I have successfully deployed all components of Attunity from old server to the new one. All components as in the screenshot below installed successfully without any issue:

enter image description here

Problem:

I am currently stuck at this point:

In the old BIDS (2008) when we right click the solution we can see the RMSCDC for SSIS, see screenshot below: enter image description here

This item is missing in the newer SSDT 2017. enter image description here

Question: I have never worked with this third party tool before and I’m currently lost. I wonder if there is a different way to access it?

As per vendor everything should work but did not provide any solution, in other words not very helpful. So I thought posting it here may caught someone else’s attention who may had dealt with this issue.

Also when I try to upgrade the SSIS packages via SSIS Packages Wizard via SSDT I get fury of errors that I can’t even post the whole Report here because of restrictions on number of characters that can be posted in the body.

Here are some errors starting from the top though:

- Upgrading package BranchCDC.dtsx (Error) Messages Information 0x40019316: : The provider name for the connection manager "LOCALHOST.SSISConfig" has been changed from "SQLNCLI10.1" to "SQLNCLI11".  Information 0x40016019: : The package format was migrated from version 3 to version 8. It must be saved to retain migration changes.   Warning 0x40016044: Write Informational Message and Set Group Name: Found SQL Server Integration Services 2008 Script Task "ST_d877e0bbc6ce43d1b268e1f9b7f0a0f4" that requires migration!  Information 0x4001601a: Write Informational Message and Set Group Name: The Script Task "ST_d877e0bbc6ce43d1b268e1f9b7f0a0f4" has been migrated. The package must be saved to retain migration changes.  Information 0x4001601a: Write Informational Message and Set Group Name: The Script Task "ST_d877e0bbc6ce43d1b268e1f9b7f0a0f4" has been migrated. The package must be saved to retain migration changes.  Information 0x4001601a: Write Informational Message and Set Group Name: The Script Task "ST_d877e0bbc6ce43d1b268e1f9b7f0a0f4" has been migrated. The package must be saved to retain migration changes.  Error 0xc001f02a: BranchCDC Loop Container: Cannot create a task from XML for task "Initialize Change Processing Task", type "Attunity.SqlServer.Dts.Tasks.CDCLaunchTask, Attunity.SqlServer.Dts.CDCLaunchTask.RMS10, Version=1.0.0.0, Culture=neutral, PublicKeyToken=fcbb2bf343e73c50" due to error 0xC001F430 "An error occurred while accessing an internal object. This could indicate a custom extension built for Integration Services 2005 is being used.".   Error 0xc0010018: Initialize Change Processing Task: Failed to load task "Initialize Change Processing Task", type "". The contact information for this task is "".   Error 0xc0047067: Load TMPFL_TROUBL_EVT: The "CDC_TROUBL" failed to cache the component metadata object and returned error code 0x80131600.   Error 0xc0000036: Load TMPFL_TROUBL_EVT: Failed to create COM Component Categories Manager due to error 0xC0047067 "The "%1" failed to cache the component metadata object and returned error code 0x%2!8.8X!.".   Error 0xc0048021: Load TMPFL_TROUBL_EVT: The component is missing, not registered, not upgradeable, or missing required interfaces. The contact information for this component is "".   Error 0xc004801f: Load TMPFL_TROUBL_EVT: The component metadata for "CDC_TROUBL" could not be upgraded to the newer version of the component. The PerformUpgrade method failed.   Error 0xc0047067: Load TMPFL_NODE_EVTSTG: The "CDC_NODE" failed to cache the component metadata object and returned error code 0x80131600.   Error 0xc0000036: Load TMPFL_NODE_EVTSTG: Failed to create COM Component Categories Manager due to error 0xC0047067 "The "%1" failed to cache the component metadata object and returned error code 0x%2!8.8X!.".   Error 0xc0048021: Load TMPFL_NODE_EVTSTG: The component is missing, not registered, not upgradeable, or missing required interfaces. The contact information for this component is "".   Error 0xc004801f: Load TMPFL_NODE_EVTSTG: The component metadata for "CDC_NODE" could not be upgraded to the newer version of the component. The PerformUpgrade method failed.   Error 0xc0047067: Load TMPFL_DESCRP_EVT: The "CDC_DESCRP" failed to cache the component metadata object and returned error code 0x80131600.   Error 0xc0000036: Load TMPFL_DESCRP_EVT: Failed to create COM Component Categories Manager due to error 0xC0047067 "The "%1" failed to cache the component metadata object and returned error code 0x%2!8.8X!.".   Error 0xc0048021: Load TMPFL_DESCRP_EVT: The component is missing, not registered, not upgradeable, or missing required interfaces. The contact information for this component is "". 

Environment info:

old server OS: Windows Server 2008R2 old SQL Server: SQL Server 2008R2 New SQL Server: 2008R2 New SQL Server: 2017 Old Development Tool: BIDS 2008. New Development Tool: VS 2017 with SSDT 2017.  

Any help or guidance would be greatly appreciated.

SSIS 2017 – Parse flat file with multiple columns and headers on multiple lines

I receive a daily CSV file that contains 600+ company employee positions, each of which are formatted as follows:

Position,Description SUP1015,Shipping Supervisor Day Work UOM:,Hours,Active:,Yes,Permanent:,Yes,, Default Rate Level:,0,Default Rate Source:,Master,Default GL Source:,Master,, Effective Date:,,Expiry Date:,,Created Date:,29-Apr-2014,Revised Date:,06-Jun-2019 Job Class:,,,,,Location:,,1004 - Shipping,, Union Code:,,,,,Reports To:,,MGR1056 - Delivery & Shipping Manager,, Position FTE:,,1.0000,,,,,, 

My goal is to transform all 600+ records into one table:

Position | Description                     | Work UOM | Active | Permanent | Default Rate Level | Default Rate Source | Default GL Code | Effective Date | Expiry Date | Created Date | Revised Date | Job Class | Location                 | Union Code | Reports to                                    | Position FTE | ========================================================================================================================================================================================================================================================================================================================= SUP1015  | Shipping Supervisor Day         | Hours    | Yes    | Yes       | 0                  | Master              | Master          |                |             | 29-Apr-2014  | 06-Jun-2019  |           | 1004 - Shipping          |            | MGR1056 - Delivery & Shipping Manager         | 1.0000       | 

I have no idea how to parse this, given the connection managers in SSIS. Any help and guidance is greatly appreciated.

Using Transactions To Rollback SSIS package

I’m at a loss with an SSIS package I inherited. It contains: 1 Script Task 3 Execute SQL tasks 5 Data flow tasks (each contains a number of merges, lookups, data inserts and other transformations) 1 file system task of the package.

All of these are encapsulated in a Foreach loop container. I’ve been tasked with modifying the package so that if any of the steps within the control/data flow fails, the entire thing is rolled back. Now I’ve tried two different approaches to accomplish this:

I. Using Distributed Transactions.

I ensured that:

MSDTC was running on target server and executing client (screenshot enclosed) msdtc.exe was added as an exception to server and client firewall Inbound and outbound rules were set for both server and client to allow DTC connections. ForeachLoop Container TrasanctionLevel: Required All other tasks TransactionLevel: Supported My OLEDB Connection has RetainSameConnection set to TRUE and I’m using SQL Server Authentication with Save Password checked

When I execute the package, it fails right after the script task (first step) After spending an entire week trying to figure out a workaround, I decided to try SQL Tasks to try to accomplish my goa using 3 Execute SQL Tasks:

BEGIN TRAN before the foreach loop container COMMIT TRAN After the ForeachLoop Container with a Success Constraint ROLLBACK TRAN After the ForeachLoop Container with a Failure constraint

In this case, foreachloop container and all other tasks have TransactionLevel property set to Supported. Now here, the problem is that the package executes up to the fourth data flow task and hangs there forever. After logging into SQL Server and verifying the running sessions, I noticed sys.sp_describe_first_result_set;1 as a headblocker session

Doing some research, I found it could be related to a few TRUNCATE statements in some of my Data flow tasks which could cause a schema lock. I went ahead and changed the ValidateExternalMetaData property to False for all tasks within my data flow and changed my truncate statements to DELETE statements instead. Re-ran package and still hangs in the same spot with the same headblocker. As an alternative, I tried creating a second OLEDB connection to the same database, assigned that new OLEDB Connection to my BEGIN, ROLLBACK and COMMIT SQL tasks with RetainSameConnectionProperty set to TRUE and changed the RetainSameConnectionProperty to FALSE (and tried it with TRUE as well) in the original OLEDB connection (the one used by the data flow tasks). This worked in the sense that the package appeared to execute (It ran and Commit Tran executed fine) and then I ran it again with a forced error to cause it to fail and the Rollback TRAN task executed successfully, however, when I queried the affected tables, the transaction hadn’t rolled back, all new records were inserted and old ones were updated (the begin tran was clearly started in a different connection and hence didn’t affect the package’s workflow). I’m not sure what else to try at this point. Any help would be truly appreciated, I’m about to go nuts with this!

P.S. additionally, all objects have “DelayValidation” set to true on everything and SQL Server version is 2012.

Depurar paquetes SSIS en VS 2019 y SQL Server 2016 no funciona. El visor de datos no se muestra

Estoy con Visual Studio 2019 v16.2.3. Estoy desarrollando paquetes SSIS para SQL Server 2016. Ya tengo configurada la propiedad TargetServerVersion a SQL Server 2016. Al añadir un punto de interrupción en cualquier flujo de datos, el flujo ni se detiene ni la pantalla de visor de datos se muestra. El depurador solo funciona cuando configuro TargetServerVersion a SQL Server 2017 o 2019. ¿A qué se puede deber?

Muchas gracias!