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!

Monitor when SSIS Master Package is Halfway Complete and Call Another SSIS Project

We have an SSIS Master package which is calling other miniature SSIS packages, stored procedure bubbles, other script tasks, etc.

The project Loads from say A Legacy System — > B Legacy DataWarehouse.

We want to find when package hits certain points midway, finishes a stored procedure bubble or another SSIS package, and then want to call another SSIS Package Project which sees when A is partially done and copies some data to —> C New OLTP System.

The conundrum is, we are not allowed to touch the Old legacy SSIS Master package from client or mainframe system or intercept its path, “Don’t fix whats not broken”.

Whats the best way to poll when a Master package has finished midway, then call another SSIS Project Master package? Should we just poll SSISDB.Catalog Execution History or SSISDB.CATALOG.operation_messages . Is there any fancy way to trigger another package midway with SQL Server?

How I can store the user id of the user who last modified an item in a list if the list is being modified every night by an SSIS script?

I have a custom list in SharePoint 2010 that contains about a 1000 items. An SSIS script writes to this list every night and hence changes the “Modified” and “Modified By” columns at that time for all items. I want to store the value of “Modified” and “Modified By” columns in the list item if the item is updated by a regular user (not the user ID used by SSIS) or an admin. I tried using the “Me” technique to get the user name of the user making a change during the day but each night, as the “Modified By” column is updated, so will be the column using “Me”.

Just to explain the technique I used, I created a column called Me and another column called “Last Modified By”. Both were created as “Single line of text”. I then set the default value of the “Last Modified By” column to be calculated and put in the following formula: =IF(AND(Me<>”DOMAIN\ssis-user”,Me<>”DOMAIN\admin1″,Me<>”DOMAIN\admin2″,Me<>”DOMAIN\admin3″),Me,””). I then deleted the Me column so the “Me” in “Last Modified By” column now points to the default [Me] that gives the username of the person who modified the list item. In this way, I am able to get the user name of the user making an update to the list item, but it will be overwritten by the SSIS script user name overnight. I do not want it to be overwritten unless the user making the change is not the SSIS or one of the admins.

I have access to UI functionality or SharePoint Designer workflows to create this solution, although I would prefer to do this using without workflows if possible. All ideas are welcome.

Cómo puedo conectarme desde SSIS a Listas de SharePoint

Buen día comunidad.

Necesito conectarme a las listas de SharePoint Online desde SSIS y la única herramienta que me funciono fue de KingswaySoft, lo ejecuto manual y me funciona pero cuando lo pongo de manera manual desde JOB de SQL SERVER no conecta con la lista de SharePoint.

¿Como puedo conectarme a una lista de SharePoint sin el componente KigswaySotf para ejecutarlo de manera automática?

¡Muchas gracias por su ayuda!