Skip to content

Instantly share code, notes, and snippets.

tdmitch

Block or report user

Report or block tdmitch

Hide content and notifications from this user.

Learn more about blocking users

Contact Support about this user’s behavior.

Learn more about reporting abuse

Report abuse
View GitHub Profile
View createschema.sql
CREATE SCHEMA [etl]
/*
User-defined table to store the most recent change tracking version ID
processed via ETL.
*/
CREATE TABLE [etl].[Change_Tracking_Version]
(
[Table_Name] VARCHAR(100) NOT NULL PRIMARY KEY
, [Change_Tracking_Version] [BIGINT] NULL
View temporal_offset.sql
/* Get local time */
DECLARE @dt2 DATETIMEOFFSET = GETDATE() AT TIME ZONE 'Central Standard Time'
/* Convert to UTC */
SET @dt2 = @dt2 AT TIME ZONE 'UTC';
/* Now query the table using the offset datetime2 */
SELECT ValidFrom, ValidTo, * FROM dbo.Customers
FOR SYSTEM_TIME AS OF @dt2
WHERE (address = '' OR customerid = 13)
View select_temporal2.sql
SELECT *
FROM dbo.Customers
FOR SYSTEM_TIME AS OF '2017-10-10 19:37:02.2280376'
WHERE PostalCityID = 32887
View select_temporal.sql
SELECT *
FROM dbo.Customers
WHERE PostalCityID = 32887
View update_temporal.sql
UPDATE dbo.Customers
SET PrimaryContactPersonID = 1029
WHERE PrimaryContactPersonID = 1025
UPDATE dbo.Customers
SET Address = 'Street-level'
WHERE Address = 'Shop 13'
DELETE dbo.Customers
WHERE CustomerID IN (23, 24, 25)
View turn_on_temporal.sql
/* Turn on system versioning */
ALTER TABLE dbo.Customers
SET (SYSTEM_VERSIONING = ON
(
HISTORY_TABLE = [dbo].[CustomersHistory]
)
)
View create_temporal.sql
/* Create base table */
CREATE TABLE [dbo].[Customers](
[CustomerKey] INT NOT NULL IDENTITY(1,1),
[CustomerID] INT NOT NULL,
[CustomerName] VARCHAR(100) NOT NULL,
[CustomerCategory] VARCHAR(50) NULL,
[PrimaryContactPersonID] INT NOT NULL,
[PostalCityID] INT NOT NULL,
[CreditLimit] DECIMAL(18, 2) NULL,
[AccountOpenedDate] DATE NOT NULL,
View scriptcomponent.cs
public override void CreateNewOutputRows()
{
// Create the StreamReader object to read the input file
System.IO.StreamReader reader = new System.IO.StreamReader(this.Variables.vInputFilename);
// Loop through the file to read each line
while (!reader.EndOfStream)
{
// Read one line
string line = reader.ReadLine();
View tables.xml
<!-- Get all tables in the Sales schema -->
<# var importResult = RootNode.Connections["AdventureWorks"].GetDatabaseSchema(new List<string>{"Sales"}, null, ImportOptions.ExcludePrimaryKey|ImportOptions.ExcludeCheckConstraint|ImportOptions.ExcludeForeignKey|ImportOptions.ExcludeColumnDefault|ImportOptions.ExcludeViews|ImportOptions.ExcludeIdentity|ImportOptions.ExcludeIndex);#>
View dataflow.xml
<# foreach (var worksheet in worksheetCollection) {#>
<Dataflow Name="<#= worksheet.Replace("$""") #>" DelayValidation="true">
<Transformations>
<ExcelSource ConnectionName="Accidents Source File" Name="XLS Accidents - <#= worksheet.Replace("$""") #>">
<DirectInput>SELECT * FROM `<#= worksheet #>`</DirectInput>
</ExcelSource>
<OleDbDestination Name="OLEDST Accidents Table" ConnectionName="AccidentData DB">
<ExternalTableOutput Table="[dbo].[AccidentData]"></ExternalTableOutput>
</OleDbDestination>
</Transformations>
You can’t perform that action at this time.