i believe you have to encrypt it again using the ssisdb original password in this way:
open master key decryption by password = 'xxxxx' –‘Password used when creating SSISDB’
Alter Master Key Add encryption by Service Master Key
Try this out:
declare @vardate varchar(100)='02121955'
set @vardate= SUBSTRING(@vardate, 1, 2)+'-'+ SUBSTRING(@vardate, 3, 2)+'-'+SUBSTRING(@vardate, 5, 4)
select CONVERT(date, @vardate) as dataconverted
This should do the job for the whole column
SET columnName = SUBSTRING(columnName, 1, 2)+'-'+ SUBSTRING(columnName, 3, 2)+'-'+SUBSTRING(...
the first obvious solution could be the adoption of a SQL Server VM hosted on azure.
You can move there databases and ssis packages in any traditional ways.
This is an Azure Iaas solution and usually is the most expensive. You can reduce cost moving your on prem sql license wiht the "Bring Your own license" solution coming from software assurance ...
Another aspect is which of the Azure SQL flavors you should go for:
SQL Database: PaaS, least "DBA-work" for you to do. But least compatibility with On-Prem SQL Server. For instance Agent isn't there and no cross-database queries.
Managed Instance: You do get an instance with close to 100% compatibility for the db engine. MS takes care of the OS, ...
How frequently are the database changing and how much downtime can your business allow?
There are a multitude of non-realtime ways to move a snapshot of the databases such as:
Full Database Backups
Scripting the Database (with Data)
3rd Party Synchronization Tools (RedGate's SQL Compare)
There's also a series of more realtime continuous ...
You can use SUBSTRING to pull apart the source. Then DATEFROMPARTS will stitch them back together.
A few notes: dates are not held internally in that format, or any other date-like format. The question says the source is 6 characters but the example shows 8. If you need to find the century you'll have to decide on rules for that.
I concur with @dan Guzman's comment. You're likely overallocated on memory for SQL Server (and probably for the box itself). Unless you just have continuous package execution and/or suboptimal design patterns in play, that smells like the box will be happy with 1/4 of the resources - just make sure it has the best network speed (assuming data comes from ...