DECLARE @inputString NVARCHAR(100) = '21(1,2)' -- Remove the parentheses around the numbers DECLARE @prefix NVARCHAR(10) = LEFT(@inputString, CHARINDEX('(', @inputString)) -- '21(' DECLARE @numbers NVARCHAR(50) = REPLACE(SUBSTRING(@inputString, CHARINDEX('(', @inputString) + 1, LEN(@inputString)), ')', '') -- Split and reconstruct the result SELECT @prefix + value + ')' AS SplitResult FROM STRING_SPLIT(@numbers, ',')

@ thank you

You are most welcome.

You can download the data set of every video from the link in the description of the video.

The error might be related to Devart component. Can you raise a ticket to them and see if they can help you support.devart.com/portal/en/community

Ideally if you want to import file with any name from the source folder, then you should use the foreach loop container with file enumerator to loop through all files (or specific type of files with a specific name) in a folder and then import the data to a sql table. You can take a look at this video kzbin.info/www/bejne/eJvTkoaugr-ArtU

Glad you were able to figure it out. Yeah you can watch this video, here I have shown how to use project parameters in ssis package and how to set them from sql agent job kzbin.info/www/bejne/jJPbloBngJ1_d7c

You are most welcome.

Glad you liked the video.

You are welcome.

Sorry did not get your question?

Yes, if you cover all the videos, then you can handle majority of projects in SSIS.

@learnssis tq so much sir Cam you suggest me where to learn ssas ,and ssrs as well

Can you suggest me where to learn ssrs ssas as well

​ @chandrasekhar1046 Sorry I have not watched much videos of SSRS or SSAS online thus not sure which channel is good. You can try couple of channel and follow anyone who you feel comfortable with.

@@chandrasekhar1046 ​ @chandrasekhar1046 Sorry I have not watched much videos of SSRS or SSAS online thus not sure which channel is good. You can try couple of channel and follow anyone who you feel comfortable with.

Thank you.

Yes we can do that. we can use variables in Salesforce source component. And we can assign any date or id to those variables maybe from sql table or from a flat file etc.

@@learnssis do you have any doc or link to understand it?

@@prithviraj1568 In this video I have shown how to do this from sql to sql. kzbin.info/www/bejne/enbbaKB6nbieppY In your case instead of sql it will be salesforce but we can use the variable in salesforce source as well, so this method should work. But here our source table contains lastmodified date column and according to that we are pulling data.

Thank you.

Then create a view in such a way so that it pics only distinct data from sql table. Mybe use Row_number function or distinct queries.

@@learnssis Thanks! I tried but it seems that it would impact other tables. I'll look into it deeper.

Thank you.

we need to update registry settings in remote machine as well.

No. A view with only SCHEMABINDING is not a materialized view. It’s still just a regular view that runs the query each time you access it. SCHEMABINDING simply ensures that the underlying tables cannot be modified in a way that breaks the view (e.g., dropping a column). For it to be a materialized view, you must create a clustered index on the view.

Currently it is one of the widely used technology for ETL and migrating data to data warehouses. I can't predict how much useful it will be in future. But if you learn how ssis works, like how data migration packages are created then it can help you to learn similar data migration technology like ADF (Azure data factory) for data migration.

@learnssis thanks for your response.. !

Currently I am busy on some projects and not getting time to make video. Maybe will try to make video during weekend.

@@learnssisOk..I Waite you...I'm finished from course I get more Benefit and skills To do data analysis....you are best one explain ssis in KZbin.

Thank you.

yeah actually both are different dbms and they use a slightly different language with some differences in syntax, thus procedures or views will have issues in automatic conversion.

Glad it worked for you.

You can use this code inside Script task before loading data. This will close any instance of excel file or flat\csv file if those files are opened in notepad using System; using System.Diagnostics; --------------------- public void Main() { CloseProcessesByName("EXCEL"); // If CSV is open in Excel CloseProcessesByName("notepad"); // If CSV is open in Notepad Dts.TaskResult = (int)ScriptResults.Success; } static void CloseProcessesByName(string processName) { try { // Get all processes with the given name Process[] processes = Process.GetProcessesByName(processName); foreach (Process process in processes) { Console.WriteLine($"Closing process: {process.ProcessName}, ID: {process.Id}"); process.Kill(); // Force close the process process.WaitForExit(); // Wait until the process is terminated } Console.WriteLine($"All {processName} processes have been closed."); } catch (Exception ex) { Console.WriteLine($"Error while closing processes: {ex.Message}"); } }

replace(replace(column_name, 'First value',''),'second value','')

@@learnssis thanks bro

You are most welcome.

In flat file connection manager there is a property RowsToSkip or something, by default the value is 0, it means no rows will be skipped, if you want to skip for example first 100 records, then set the value to 100.

Thank you.

Thank you.

If header changes then you would need to write some C# code to export it. SSIS data flow task won't be able to do it.

@learnssis Thanks a lot for replying. Can you suggest some page or video. I am not too familiar with C#

I think that's what I shown in the video that I passed the value to input parameter (DateinYYYYMMDD) dynamically from ssis package.

Thank you.

At the moment, I am not aware about any other method.

Check this code. try { string FilePath = Dts.Variables["FilePath"].Value.ToString(); string FileName = Path.GetFileNameWithoutExtension(FilePath); string LoadFile = "No"; if (!string.IsNullOrEmpty(FileName) && FileName.Contains("_")) { string dateValue = FileName.Substring(FileName.LastIndexOf('_') + 1); // Get yesterday's date in the "yyyyMMdd" format string yesterdaysDate = System.DateTime.Now.AddDays(-1).ToString("yyyyMMdd"); // Check if the extracted date matches yesterday's date if (dateValue == yesterdaysDate) { LoadFile = "Yes"; } } Dts.Variables["LoadFile"].Value = LoadFile; } catch (Exception ex) { Dts.Events.FireError(0, "Script Task", $"Error: {ex.Message}", "", 0); throw; }

Shukriya Bhai.

Thank you.

yes, we can do that. Below is the updated sp. CREATE PROCEDURE ExecuteSSISPackage @ServerName NVARCHAR(100), @Database NVARCHAR(100) AS BEGIN BEGIN TRY -- Declare variables DECLARE @execution_id BIGINT; DECLARE @status INT; -- Create execution EXEC [SSISDB].[catalog].[create_execution] @package_name = N'Package.dtsx', @execution_id = @execution_id OUTPUT, @folder_name = N'Test Project', @project_name = N'121 How to execute SSIS package from Stored Procedure', @use32bitruntime = 0, @reference_id = NULL, @runinscaleout = 0; -- Set parameters DECLARE @varDatabase SQL_VARIANT = @Database; EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type = 20, @parameter_name = N'Database', @parameter_value = @varDatabase; DECLARE @varServerName SQL_VARIANT = @ServerName; EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type = 20, @parameter_name = N'ServerName', @parameter_value = @varServerName; -- Set logging level DECLARE @logging_level SMALLINT = 1; EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type = 50, @parameter_name = N'LOGGING_LEVEL', @parameter_value = @logging_level; -- Start execution EXEC [SSISDB].[catalog].[start_execution] @execution_id; -- Wait for execution to complete (Optional - Adjust timeout as needed) WAITFOR DELAY '00:00:05'; -- Check execution status SELECT @status = status FROM [SSISDB].[catalog].[executions] WHERE execution_id = @execution_id; -- If status indicates failure, raise an error IF @status NOT IN (7, 8) -- 7 = Success, 8 = Canceled BEGIN DECLARE @error_message NVARCHAR(MAX) = ''; SELECT @error_message = STRING_AGG(message, CHAR(10)) FROM [SSISDB].[catalog].[operation_messages] WHERE operation_id = @execution_id AND message_type = 120; -- 120 = Error IF @error_message IS NULL SET @error_message = 'Unknown error occurred during package execution.'; RAISERROR ('SSIS Package failed: %s', 16, 1, @error_message); END ELSE BEGIN PRINT 'SSIS Package executed successfully.'; END END TRY BEGIN CATCH -- Handle and re-throw errors DECLARE @ErrorMessage NVARCHAR(MAX), @ErrorSeverity INT, @ErrorState INT; SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(); RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState); END CATCH END; GO

ERROR: date/time field value out of range: "1/27/2019" HINT: Perhaps you need a different "datestyle" setting. CONTEXT: COPY sales_data, line 5, column date: "1/27/2019"

Thank you.

Hi, at the moment I won't be able to make a video on this topics as I am kind of busy on some stuff. What you can do is that you can create a config_server sql table. Store the source server name information, destination server name information into this table. Now in the ssis package you will need to connect to the server\database in which this config_tbl table exists, and then write a select query to select the source server name, and dest server name like below select top 1 sourceserver, destserver from config_Server where active=1 and then set the result set option to single result set and assign these values to 2 ssis variables i.e SourceServer and DestServer at index 0 and 1. Now in your ssis package you would need to create 2 OLEDB connection managers, Source and Dest and in the expressions of the connection manager you would need to set the ServerName property for source connection from SourceServer SSIS variable and for Dest Connection string set the value to come from DestServer ssis variable. This way when the package will run it will get the source server name and dest server name from the config_server sql table and accordingly this value will be passed to the source and dest ole db connection managers using expression (using SourceServer and DestServer SSIS variables) which will make the connection dynamic and you can get and write data to appropriate server.

Sorry never tried it and not sure if we can do this with SSIS.

Uske liye aapko incremental load karna hoga, iske liye aap lookup transformation use kar sakte ho kzbin.info/www/bejne/bpW1ep2MnbCigdU

@@learnssis please video bnake daldo ?is video me nhi hai

@@prernaagupta688 isme he aap theek se dekho.

@ theek hai

@@learnssis from which time duration?