|
|
||||||
|
|
||||||
Workflow Screenshot
Properties
| Property | Value |
| Name | CreateDWStagingSnapshot |
| Description | This package will create a snaphot of the DWStaging database. This will ensure that only records updated prior to the start of this package will be processed during the load. This is especially important when processing deleted Student records. This package will also update the name of the DWStaging Database to that of the Snapshot for use in the rest of the packages. |
| Checkpoint File Name | |
| Checkpoint Usage | Never |
| Check Signature On Load | True |
| Creation Date | Wednesday, August 22, 2012 6:04 PM |
| Creator Computer Name | SQL1 |
| Creator Name | AD\clove |
| Debug Mode | False |
| Delay Validation | False |
| Disable | False |
| Disable Event Handlers | False |
| Enable Configurations | True |
| Encrypt Checkpoints | False |
| Fail Package On Failure | False |
| Fail Parent On Failure | False |
| Forced Execution Value | 0 |
| Force Execution Value | None |
| Description | False |
| ID | 86c349b4-752f-4a98-bbd3-32cea7095e93 |
| Interactive Mode | False |
| Is Default Locale ID | False |
| Isolation Level | Serializable |
| Locale ID | 1033 |
| Logging Mode | Enabled |
| Max Concurrent Executables | -1 |
| Maximum Error Count | 1 |
| Offline Mode | False |
| Package Type | DTSDesigner100 |
| Protection Level | DontSaveSensitive |
| Save Checkpoints | False |
| Suppress Configuration Warnings | False |
| Suspend Required | False |
| Transaction Option | Supported |
| Update Objects | False |
| Version Build | 41 |
| Version Comments | |
| Version Guid | 840b396f-10d9-4663-aec8-f8953125cb99 |
| Version Major | 1 |
| Version Minor | 0 |
Executables
| Name | Type | Description |
Task |
Execute SQL Task |
|
Task |
Execute SQL Task |
|
Task |
Execute SQL Task |
Connection Managers
| Name | Description |
Log Providers
| Name | Description |
User Defined Variables
| Name | Value | Expression | Description |
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. |
System Variables
| Name | Value | Expression | Description |
| Powered by BI Documenter |
