Sample Header Ad - 728x90

SSIS Dynamically Loop through SQL Tables

0 votes
0 answers
760 views
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.
Asked by Stockburn (501 rep)
Sep 7, 2021, 06:15 AM