I create DB snapshot
as one of the tasks in SSIS
package. The package works with 20 different DBs. Thus, each DB will have a snapshot with hard-coded FILENAME
attribute.
CREATE DATABASE Sales_snap ON ( NAME = SalesData, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\data\SalesData_snap.ss' ), ( NAME = SalesData1, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\data\SalesData1_snap.ss' ), ( NAME = SalesData2, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\mssql\data\SalesData2_snap.ss' ) AS SNAPSHOT OF Sales
Is there a way to use some default path for FILENAME
instead of copy-pasting the same string 20 x 3 times? Alternatively, is there any way to specify base path only once and then reuse it in other SSIS tasks?