I have been fighting with an SSIS package for sometime now.
I did successfully build a package that will extract data from a specific table by creating a date list from a script task, use a forEachLoop container to loop through the date list variable and create daily files. Package then uploads them to S3 and deletes the data from the SQL table.
I am now trying to build this dynamically that will loop through a list of audit tables.
Tables will have different retention periods and different column structures.
So far I have created a SQL archive maintenance table that has a list of the tables and retention days, idea being we won’t need to alter the SSIS package if we want to change the retention periods +/-
Script task that selects the list from the archive maint table and drops the output into a variable that is connected to a ForEachLooper container. I then planned to have another execute SQL task within the ForEachLooper container using the ? in my SQL statement to identify the user::tablename variable. After reading I understand I cannot just add the user::tablename variable as ? and I need to create it as an expression, or at least this was one suggestion.
I have tried the expression below, the variables are strings, I made he mistake originally where they were objects.
"SELECT DISTINCT CONVERT(VARCHAR, DateTimeModified, 23) AS ShortDate FROM " + @(User::ArchTableName) + " WHERE DateTimeModified <= DATEADD(MONTH, " + @(User::ArchTableRetentionDays) + ",GETDATE())
Trying to evaluate this I get an error
I am sure I have missed a step or have made a glaring error.
It may also be I am way off track with this as an idea, I also realise I need dynamically build the column lists of the tables as they are not the same structures but I am ignoring that until this is working.
If anyone has any advice or guidance I would appreciate it.
If there are ways to improve the question I am also open to suggestions.