Taking Backup of the table in sql server using the SMO Enumerator in Foreach Loop container in SSIS

 Suppose we want to take back up of the table in sql server using ssis, we can do it by using Foreach loop container using SMO enumerator. SMO enumerator to enumerate SQL Server Management Objects (SMO) objects. SMO enumerator is use to loop the sql server object. 

Let’s see the example

Take Foreach loop container

  

Double click on the container

Go to the collection and select Foreach SMO Enumerator.

Now we need to create the connection.

Configure the connection manager

Now we need to select the Enumerate.

Browser and select the database and table

And select Enumerator type is name and click ok.

Database[@Name='TestSSIS']/SMOEnumObj[@Name='Tables']/ SMOEnumType[@Name='Names']

  

Now we need to map the table name to a variable. Select the variable mapping.

Click ok.

Now I am taking Execute SQL task to taking the back up of the each table from the database.

Now we need to configure the required connection.

Click ok.

  

Below is the sql script

DECLARE @tblName sysname=? DECLARE @Bkptbl varchar(100) DECLARE @DtTime varchar(20) Set @DtTime= cast(DatePart(yyyy,GetDate()) as varchar(10))+'_'+ cast(DatePart(MM,GetDate()) as varchar(10))+'_'+ cast(DatePart(DD,GetDate()) as varchar(10))+'_'+ Cast(DatePart(HH,GetDate()) as varchar(10))+'_'+ cast(DatePart(MI,Getdate()) as varchar(10)) set @bkptbl=@tblName+'_BKP_'+ @DtTime exec ('select * into '+@Bkptbl+' From ' +@tblName)

Now we need to map the variable.

  

Now click.

Package ready to execute.

Now I am executing the package.

Package executed successfully. See the result