C:\DW Local Stuff\SQL Data\

This SSIS_Configuration setting contains the name of the directory where the data files for the SQL Server DWStaging database resides.

DWStaging

This SSIS_Configuration contains the name of the DWStaging database.

DWStaging_ETL_20170309_172829

@[User::DWStagingDBName] + "_ETL_" + (DT_WSTR,8) ( (YEAR( @[System::ContainerStartTime] ) * 10000) + (MONTH( @[System::ContainerStartTime] ) * 100) + DAY( @[System::ContainerStartTime] ) ) + "_" + RIGHT("0" + (DT_WSTR,2)DATEPART("hh", @[System::ContainerStartTime] ), 2) + RIGHT("0" + (DT_WSTR,2)DATEPART("mi", @[System::ContainerStartTime] ), 2) + RIGHT("0" + (DT_WSTR,2)DATEPART("ss", @[System::ContainerStartTime] ), 2)

This variable will contain the name of the Snaphot that is created. It is a concatenation of the DWStagingDBName and the date and time of the package execution.

EXEC(' SET NOCOUNT ON IF 1=2 BEGIN SELECT CAST('''' AS varchar(8000)) AS CreateSnapshot END DECLARE @SourceDBName SYSNAME DECLARE @SourceDBSnapShotName SYSNAME DECLARE @FileName SYSNAME DECLARE @SQL VARCHAR(max) DECLARE @SQL_FILE VARCHAR(4000) DECLARE @DBFileFolder SYSNAME DECLARE @DataSpaceId INT SET @DBFileFolder = ''C:\DW Local Stuff\SQL Data\'' SET @SourceDBSnapShotName = ''DWStaging_ETL_20170309_172829'' SET @SourceDBName = ''DWStaging'' SET @SQL = ''CREATE DATABASE ['' + @SourceDBSnapShotName + ''] ON'' DECLARE curFileName CURSOR FAST_FORWARD FOR SELECT f.data_space_id, f.name AS DBFileName FROM sys.database_files f INNER JOIN sys.filegroups fg ON (f.data_space_id = fg.data_space_id) WHERE fg.type = ''FG'' ORDER BY f.data_space_id OPEN curFileName FETCH NEXT FROM curFileName INTO @DataSpaceId, @FileName WHILE @@fetch_status = 0 BEGIN IF @SQL_FILE IS NULL BEGIN SET @SQL_FILE = '' '' END ELSE BEGIN SET @SQL_FILE = '', '' END SET @SQL_FILE = @SQL_FILE + '' ( NAME = '''''' + @FileName + '''''', FILENAME = '''''' + @DBFileFolder + @SourceDBSnapShotName + ''_'' + @FileName + CASE WHEN @DataSpaceId = 1 THEN ''.mdf'' ELSE ''.ndf'' END + '''''' ) '' SET @SQL = @SQL + @SQL_FILE FETCH NEXT FROM curFileName INTO @DataSpaceId,@FileName END SET @SQL = @SQL + '' AS SNAPSHOT OF ['' + @SourceDBName + ''];'' CLOSE curFileName DEALLOCATE curFileName SELECT CAST(@SQL AS varchar(8000)) AS CreateSnapshot ')

" EXEC(' SET NOCOUNT ON IF 1=2 BEGIN SELECT CAST('''' AS varchar(8000)) AS CreateSnapshot END DECLARE @SourceDBName SYSNAME DECLARE @SourceDBSnapShotName SYSNAME DECLARE @FileName SYSNAME DECLARE @SQL VARCHAR(max) DECLARE @SQL_FILE VARCHAR(4000) DECLARE @DBFileFolder SYSNAME DECLARE @DataSpaceId INT SET @DBFileFolder = ''" + @[User::DWStagingDBFileFolder] + "'' SET @SourceDBSnapShotName = ''" + @[User::DWStagingSnapShotName] + "'' SET @SourceDBName = ''" + @[User::DWStagingDBName] + "'' SET @SQL = ''CREATE DATABASE ['' + @SourceDBSnapShotName + ''] ON'' DECLARE curFileName CURSOR FAST_FORWARD FOR SELECT f.data_space_id, f.name AS DBFileName FROM sys.database_files f INNER JOIN sys.filegroups fg ON (f.data_space_id = fg.data_space_id) WHERE fg.type = ''FG'' ORDER BY f.data_space_id OPEN curFileName FETCH NEXT FROM curFileName INTO @DataSpaceId, @FileName WHILE @@fetch_status = 0 BEGIN IF @SQL_FILE IS NULL BEGIN SET @SQL_FILE = '' '' END ELSE

UPDATE [dbo].[SSIS Configurations] SET ConfiguredValue = 'DWStaging_ETL_20170309_172829' WHERE ConfigurationFilter = 'ConnMgr_DWStaging' AND PackagePath = '\Package.Connections[DWStaging].Properties[InitialCatalog]'

"UPDATE [dbo].[SSIS Configurations] SET ConfiguredValue = '" + @[User::DWStagingSnapShotName] + "' WHERE ConfigurationFilter = 'ConnMgr_DWStaging' AND PackagePath = '\\Package.Connections[DWStaging].Properties[InitialCatalog]' "

This variable contains the SQL statement that will update the DWStagingDBName variabl ewith that of the Staging Snapshot so that it can be used in the rest of the packages that will be executed.