SSIS Dynamic mapping

Question

SSIS Dynamic mapping

naza 21

Hi

I wanted to create a single standard SSIS package that can be used for ETL of multiple streams. I want this to be dynamic so that I dont have to create package for each stream/ modify the meta data mapping each time. "Dynamic SSIS Mapping" suggestions are not of much help/ harder for me with C# script suggestions.
Eventually I landed using "Transfer SQL Server Objects Task" for this and is happy to see it solving my challenge.

Hence the question is : what is the difference between "Transfer SQL Server Objects Task" and normal "Data flow task" for like-to-like as-is ETLs? Why have not people suggested this option for 'dynamic SSIS mapping' ? Is there something I am missing in my understanding of this powerful 'Transfer SQL Server Objects Task" in SSIS?
What I am I Overlooking?

Notes:

I have ~300 Data export ETL from my server to various external ones- all SQL Server so far
I understand I have lift-shift option of objects as-is (Tables in my case) and cant apply filters/transformations. Thats ok. I am glad this task also gives 'Append/Replace' option as well.
I understand the tables/schema cannot be programmed in via 'expressions', but are rather static choices

0 comments

Answer accepted by question author

ZoeHui-MSFT 41,551

Hi @naza ,

The Transfer SQL Server Objects task transfers one or more types of objects in a SQL Server database between instances of SQL Server. For example, the task can copy tables and stored procedures.

When copying tables, you can indicate the types of table-related items to include in the copy process. The following types of items can be copied together with the related table: Indexes, Triggers, Full-text indexes, Primary keys and Foreign keys.

However, the Data Flow task encapsulates the data flow engine that moves data between sources and destinations, and lets the user transform, clean, and modify data as it is moved. Addition of a Data Flow task to a package control flow makes it possible for the package to extract, transform, and load data.

As far as I'm concerned, the Transfer SQL Server Objects Task is used to transfer the objects in sql server and the Data Flow task is more used to the data itself and support for multiple sources and destination.

transfer-sql-server-objects-task
data-flow-task

Temporarily, SSIS DFT doesn't support dynamic column mapping, you may create DFT programmatically with C# which you said is not helpful.

If you want this feature, I'd suggest that you may submit your requiremnet to the Microsoft feedback at this link https://feedback.azure.com/d365community/forum/04fe6ee0-3b25-ec11-b6e6-000d3a4f0da0

Your feedback enables Microsoft to offer the best software and deliver superior services, meanwhile you can learn more about and contribute to the exciting projects on Microsoft feedback.

Regards，

Zoe

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

naza 21 Reputation points

2022-03-15T08:10:35.793+00:00
Thank you @ZoeHui-MSFT
I shall submit my feedback to the MS feedback forum.

On a separate note, I could build a dynamic mapping SSIS solution using the SQLBulkCopy Class in C# , even though I am not a fan of C#/VB.Net solutions. However the SSIS package is inconsistent in handling larger data.
For eg: Data Export from my server to client server works inconsistently for a table with 100k records.
It gives below error intermittently:

System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

Could you advise how to resolve this?
Few notes on the above :

I am triggering this SSIS package from an SQL Jobstep

An additional job step also triggers the same package , but for export of a smaller table

The smaller table consistently succeeds. The larger table fails with above error intermittently.

Happy to get redirected
ZoeHui-MSFT 41,551 Reputation points

2022-03-15T08:21:34.447+00:00

Hi @naza ,

If this post have been resolved, do not forget to click "Accept Answer" which could encourage the experts.

For the "Timeout expired" error commonly occurs when an instance of the SQL Server Database Engine is not running, when the server name was typed incorrectly, or when there are network problems or firewalls.

A firstly recommended solution it to set the Connection Timeout(15 sec default) or Connect Timeout property to a large value such as 200. When you do this, the data provider sets the waiting time (time slice) for the first connection attempt to 200 * 0.08 ~ 16 seconds. For detail, check this link:
https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlconnection.connectiontimeout?redirectedfrom=MSDN&view=dotnet-plat-ext-6.0#System_Data_SqlClient_SqlConnection_ConnectionTimeout

naza 21

Hi @ZoeHui-MSFT
Thanks for these prompt responses.
Please also advise on:

What is the relevance of 0.08 in your derivation? From the link above, my impression was the value provided in 'Connect TimeOut' is in 'seconds'.
I still see this inconsistency after providing a high value as high as 2000.

Design Notes:

I am passing the source and destination connection string as parameters from SQL to create SSIS package execution instance
This is then later consumed into SSIS package as package parameter

Then these are passed onto the C# script for SQLBulkCopy class

     EXEC [SSISDB].[catalog].[create_execution]  
             @folder_name  = @folder_name  
         ,   @project_name = @project_name  
         ,   @package_name = @package_name  
         ,   @reference_id = @reference_id  
         ,   @execution_id = @EXECUTION_ID OUTPUT;  

      EXEC [SSISDB].[catalog].[set_execution_parameter_value]  
            @execution_id   = @EXECUTION_ID   
        ,   @object_type    = 30  
        ,   @parameter_name = N'Destination_ConnectionString'   <and N'Source_ConnectionString>  
        ,   @parameter_value =   N'Data Source=<DestServername>;Initial Catalog=<destinationDb>;Integrated Security=SSPI;Trusted_Connection=yes;Connection Timeout=2000'     

     EXEC  [SSISDB].[catalog].[start_execution]  
                              @execution_id = @EXECUTION_ID;

ZoeHui-MSFT 41,551 Reputation points

2022-03-15T09:26:02.173+00:00

Hi @naza ,

The 0.08 is for .NET Framework Data Provider for SQL Server, sorry for that I'm not familiar with C# just concluded from here.

Please also check this same thread to see if it is helpful as I think the issue should be analyzed from C# side. You may also raise a new question with donet-csharp tag for more professional help.

2 additional answers

Your answer

naza 21 Reputation points

2022-03-15T08:10:35.793+00:00

Thank you @ZoeHui-MSFT
I shall submit my feedback to the MS feedback forum.

On a separate note, I could build a dynamic mapping SSIS solution using the SQLBulkCopy Class in C# , even though I am not a fan of C#/VB.Net solutions. However the SSIS package is inconsistent in handling larger data.
For eg: Data Export from my server to client server works inconsistently for a table with 100k records.
It gives below error intermittently:

System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

Could you advise how to resolve this?
Few notes on the above :

I am triggering this SSIS package from an SQL Jobstep

An additional job step also triggers the same package , but for export of a smaller table

The smaller table consistently succeeds. The larger table fails with above error intermittently.

Happy to get redirected
ZoeHui-MSFT 41,551 Reputation points

2022-03-15T08:21:34.447+00:00

Hi @naza ,

If this post have been resolved, do not forget to click "Accept Answer" which could encourage the experts.

For the "Timeout expired" error commonly occurs when an instance of the SQL Server Database Engine is not running, when the server name was typed incorrectly, or when there are network problems or firewalls.

A firstly recommended solution it to set the Connection Timeout(15 sec default) or Connect Timeout property to a large value such as 200. When you do this, the data provider sets the waiting time (time slice) for the first connection attempt to 200 * 0.08 ~ 16 seconds. For detail, check this link:
https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlconnection.connectiontimeout?redirectedfrom=MSDN&view=dotnet-plat-ext-6.0#System_Data_SqlClient_SqlConnection_ConnectionTimeout
naza 21 Reputation points

2022-03-15T08:52:00.167+00:00

Hi @ZoeHui-MSFT
Thanks for these prompt responses.
Please also advise on:

What is the relevance of 0.08 in your derivation? From the link above, my impression was the value provided in 'Connect TimeOut' is in 'seconds'.

I still see this inconsistency after providing a high value as high as 2000.

Design Notes:

I am passing the source and destination connection string as parameters from SQL to create SSIS package execution instance

This is then later consumed into SSIS package as package parameter

Then these are passed onto the C# script for SQLBulkCopy class
EXEC [SSISDB].[catalog].[create_execution] @folder_name = @folder_name , @project_name = @project_name , @package_name = @package_name , @reference_id = @reference_id , @execution_id = @EXECUTION_ID OUTPUT; EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id = @EXECUTION_ID , @object_type = 30 , @parameter_name = N'Destination_ConnectionString' <and N'Source_ConnectionString> , @parameter_value = N'Data Source=<DestServername>;Initial Catalog=<destinationDb>;Integrated Security=SSPI;Trusted_Connection=yes;Connection Timeout=2000' EXEC [SSISDB].[catalog].[start_execution] @execution_id = @EXECUTION_ID;
ZoeHui-MSFT 41,551 Reputation points

2022-03-15T09:26:02.173+00:00

Hi @naza ,

The 0.08 is for .NET Framework Data Provider for SQL Server, sorry for that I'm not familiar with C# just concluded from here.

Please also check this same thread to see if it is helpful as I think the issue should be analyzed from C# side. You may also raise a new question with donet-csharp tag for more professional help.

Answer 1

Deleted

This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Comments have been turned off. Learn more

Answer 2

Olaf Helper 47,621

I want this to be dynamic so that I dont have to create package for each stream/ modify the meta data mapping each time.

You can solve a lot of different task with SSIS packages, but not really in a dynamically way.

naza 21 Reputation points

2022-03-15T08:11:34.247+00:00

Sadly so.

Share via

SSIS Dynamic mapping

2 additional answers

Your answer