I created the tables and results returned. Re-enter the refnum in both tables, you might have a space in there.

SELECT DISTINCT refnum FROM RefSched;
SELECT DISTINCT s.refnum FROM supplier;

Check if values match. Maybe Data Type Mismatch? Since DESC is a reserved keyword in SQL Server, you should...

• This reply was modified 5 months ago by Tav29.

--Check The Credential setup

SELECT name, credential_identity FROM sys.credentials WHERE name = 'YourCredentialName';


--Validate the Key Vault Key
SELECT name, key_algorithm FROM sys.symmetric_keys WHERE name = 'YourTDEKeyName';

https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/setup-steps-for-extensible-key-management-using-the-azure-key-vault?view=sql-server-ver16&tabs=portal

run both.

SELECT * 
FROM sys.objects 
WHERE name = 'fn_syspolicy_is_automation_enabled' AND type = 'FN';


SELECT msdb.dbo.fn_syspolicy_is_automation_enabled() AS IsAutomationEnabled;

• This reply was modified 5 months ago by Tav29.

launch Sql Server Configuration Manager, Do you see SQL Agent running?

Your are correct regarding "Data for 2024-02-29 is left in place until 2027-03-01."

Good point Phil. I do believe DATEADD(year, -3, GETDATE()) Takes leap year into account, I'm too lazy to load test data. also I would not delete data from a table...

• This reply was modified 5 months, 1 week ago by Tav29.
• This reply was modified 5 months, 1 week ago by Tav29.

You can add to your SP after records are created or execute another SP that deletes the records over 3 years old. You need to have a date for when...

a simpler way is to run Powershell Script:CheckforSameFileNames.ps1 before processing files

Script Code below

Set-ExecutionPolicy -ExecutionPolicy Unrestricted -Scope CurrentUser
Write-Host "Execution policy set to Unrestricted for...

• This reply was modified 5 months, 1 week ago by Tav29.
• This reply was modified 5 months, 1 week ago by Tav29.
• This reply was modified 5 months, 1 week ago by Tav29.
• This reply was modified 5 months, 1 week ago by Tav29.
• This reply was modified 5 months, 1 week ago by Tav29.
• This reply was modified 5 months, 1 week ago by Tav29.

https://stackoverflow.com/questions/6598139/how-to-loop-only-through-files-that-dont-exist-in-destination-using-an-ssis-pac?rq=4

What helps me, I would flowchart the entire process and  then create your SSIS Package.

• This reply was modified 5 months, 1 week ago by Tav29.

Couldn't you check if the file exists In the destination folder first and  if so move to unproccessed folder and then continue processing the next file?

• This reply was modified 5 months, 1 week ago by Tav29.

This might help: https://stackoverflow.com/questions/30498478/ssis-change-date-add-local-timezone-in-data-flow

Yes, I searched and came across "Jeff Moden's infamous delimitedSplit8K function". I was looking for an alternative way.  I will implement Jeff Moden's infamous delimitedSplit8K function.  Thanks for the reply.

Correction I need help with also the first name because I'm getting the middle Initial in my first name results;Example First Name Results= John J

Hey Phil,

You are correct had 30,000+ records with the word "Responsibility" misspelled and 400+ spelled correctly.  Ran replace function to correct the misspelling.

SELECT Pos1 = CHARINDEX('Total Paid amount is $',...

• This reply was modified 3 years, 6 months ago by Tav29.