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.

Improve this question
3
  • 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 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

0

Reset to default

Your Answer

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