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 enter image description here

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.

  • 3
    Your expression seems to be missing the closing quote for the last setting literal, ",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. Sep 7, 2021 at 7:11
  • Thanks on both counts David, will check out the expression ..noted on the question.
    – Stockburn
    Sep 7, 2021 at 21:45
  • David, spot on mate .. works now, thank you for this I am annoyed at myself! If you want to make this a a full answer I can assign points, admit my shame etc .
    – Stockburn
    Sep 7, 2021 at 22:25


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.