create table dynamically ssis

coool sweet 61 Reputation points
2022-01-31T21:08:36.507+00:00

hi

in my ssis package, before loading into final table. i need to take backup of that table.
the issue is every time i need to create new table with previous date attached in the name dynamically.

example : select * into temp_20220130 from temp

here 20220130 should be yesterday's date which should come dynamically.

how to do it

thanks

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,451 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. ZoeHui-MSFT 32,506 Reputation points
    2022-02-01T02:23:21.55+00:00

    Hi @coool sweet ,

    You may use Execute SQL task and use the below script before loading the data.

     DECLARE @Str NVARCHAR(MAX);  
      
     SET @Str = 'select * into [temp_'   
    		+  CONVERT(VARCHAR, GETDATE(), 10)  
    		+ '] from temp'  
      
    EXEC SP_EXECUTESQL @Str  
    

    Regards,

    Zoe


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.