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 10 months, 1 weeks ago by Tav29.
• This reply was modified 10 months, 1 weeks 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 10 months, 1 weeks ago by Tav29.
• This reply was modified 10 months, 1 weeks ago by Tav29.
• This reply was modified 10 months, 1 weeks ago by Tav29.
• This reply was modified 10 months, 1 weeks ago by Tav29.
• This reply was modified 10 months, 1 weeks ago by Tav29.
• This reply was modified 10 months, 1 weeks 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 10 months, 1 weeks 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 10 months, 1 weeks 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, 11 months ago by Tav29.

Fixed. Absolute Value (ABS)

Phil,

I'm getting this error. Here is my sql statement:

SELECT Notes, NoteId

,TotalPaid = SUBSTRING(Notes, c.Pos1, c.Pos2 - c.Pos1)

FROM dbo.NoteReceive

CROSS APPLY

(

SELECT Pos1 = CHARINDEX('Total Paid amount...

Never mind Phil, I got it. Thank you for your help.  also thank you Jonah.

Thanks Phil,

I'm sorry for such a novice question(New to SQL) I have this information in a Notes Field along with an ID Field in a table. How do I reference...

Hi Phil,

I'm trying to exact the Payment information (In Red). My problem is they all have different starting positions,  I did the Charindex recommend by Johan and I get 42,43,44,45,46...

Thank you Johan,

I tried the Charindex and I'm able to find the starting position. I have multiple positions 42,43,44,45,46.  What's the best way to go about this? a Case statement...