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.
",GETDATE())- is that how you have it defined, or a copy+paste error in making the question text? I suspect the former as it seems to be missing in the error message dialogue too. Also note that when asking a question like this it is probably better to use that "copy error button" to include the error text instead of a screenshot.