0

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.

Stockburn
  • 397
  • 2
  • 14
  • 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. – David Spillett Sep 07 '21 at 07:11
  • Thanks on both counts David, will check out the expression ..noted on the question. – Stockburn Sep 07 '21 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 07 '21 at 22:25

0 Answers0