billinkc

USE SSISDB
GO

-- You must be in SQLCMD mode
-- setvar isPacPath "C:\sandbox\SSDTDeploy\TSQLDeploy\bin\Development\TSQLDeploy.ispac"
:setvar isPacPath "<isPacFilePath, nvarchar(4000), C:\sandbox\SSDTDeploy\TSQLDeploy\bin\Development\TSQLDeploy.ispac>"

DECLARE
    @folder_name nvarchar(128) = 'TSQLDeploy'
,   @folder_id bigint = NULL

billinkc

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    int newPlanCount = 0;
    public override void PostExecute()

billinkc

        static void Main(string[] args)
        {
            string connectionString = @"Data Source=localhost\dev2014;Initial Catalog=tempdb;Provider=SQLOLEDB;Integrated Security=SSPI;Auto Translate=false;";
            string queryTableMake = @"CREATE TABLE dbo.POC(i int IDENTITY(1,10) NOT NULL, txt varchar(30) NULL);";
            string queryTranCheck = "SELECT @@trancount AS tc;";
            string query = "INSERT INTO dbo.POC(txt) SELECT 'x'; SELECT SCOPE_IDENTITY() AS x;";

            using (OleDbConnection conn = new OleDbConnection(connectionString))
            {
                conn.Open();

billinkc

-- backfilled TreatySK values
CREATE TABLE #T
(
    ClaimSK int NOT NULL
,   TreatySK int NULL
,   Scenario varchar(50)
);

INSERT INTO #T

billinkc

DECLARE test_cursor CURSOR
READ_ONLY
FOR SELECT TOP 10
    SV.name
FROM
    master.dbo.spt_values AS SV;

DECLARE @name nvarchar(35)
OPEN test_cursor

billinkc

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <OleDbConnection ConnectionString="Provider=SQLNCLI10.1;Data Source=localhost\dev2014;Integrated Security=SSPI;Initial Catalog=tempdb" Name="CM_OLE_DB"></OleDbConnection>
    </Connections>
    <Packages>
        <Package Name="POC" ConstraintMode="Linear">
            <Tasks>
                <Dataflow Name="DFT POC">
                    <Transformations>
                        <OleDbSource Name="OLE_SRC Get all columns" ConnectionName="CM_OLE_DB">

billinkc

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <OleDbConnection Name="Source" ConnectionString="Data Source=localhost\dev2014;Initial Catalog=tempdb;Provider=SQLNCLI10.1;Integrated Security=SSPI;" />
    <OleDbConnection Name="Target" ConnectionString="Data Source=localhost\dev2014;Initial Catalog=tempdb;Provider=SQLNCLI10.1;Integrated Security=SSPI;" />
    <OleDbConnection Name="Reference" ConnectionString="Data Source=localhost\dev2014;Initial Catalog=tempdb;Provider=SQLNCLI10.1;Integrated Security=SSPI;" />
  </Connections>
  <Packages>
    <Package ConstraintMode="Linear" Name="GetData">
      <Annotations>
        <Annotation AnnotationType="Description">This package will pull only new data based on external observer table</Annotation>

billinkc

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Packages>
    <Package ConstraintMode="Linear" Name="ArchivePackagePattern">
      <Variables>
        <Variable DataType="String" Name="CurrentFileName">C:\ssisdata\SourceFile.txt</Variable>
        <Variable DataType="String" Name="DateString" EvaluateAsExpression="true">(DT_WSTR, 4)  YEAR(@[System::StartTime]) + "-" + RIGHT("0" + (DT_WSTR, 2)  MONTH(@[System::StartTime]), 2) + "-" + RIGHT("0" + (DT_WSTR, 2) DAY(@[System::StartTime]), 2)</Variable>
        <Variable DataType="String" Name="CurrentFileNameExtension" EvaluateAsExpression="true">RIGHT(@[User::CurrentFileName], FINDSTRING(REVERSE(@[User::CurrentFileName]), ".", 1 ))</Variable>
        <Variable DataType="String" Name="ArchiveFileName" EvaluateAsExpression="true">REPLACE( @[User::CurrentFileName], @[User::CurrentFileNameExtension], "." + @[User::DateString] + @[User::CurrentFileNameExtension])</Variable>
      </Variables>
    </Package>

billinkc

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <FlatFileConnection FilePath="C:\ssisdata\at.txt" FileFormat="FFFAT" Name="CM_FF"></FlatFileConnection>
    </Connections>
    <FileFormats>
        <FlatFileFormat Name="FFFAT" IsUnicode="false" ColumnNamesInFirstDataRow="true">
            <Columns>
                <Column Name="MyField" DataType="AnsiString" Length="15" Delimiter="CRLF"/>
            </Columns>
        </FlatFileFormat>

billinkc

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <OleDbConnection Name="CM_OLE" ConnectionString="Data Source=localhost\dev2014;Initial Catalog=tempdb;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;" />
    </Connections>

    <Packages>
        <Package ConstraintMode="Linear" Name="v_4495">
            <Variables>
                <Variable DataType="Int32" Name="ExecSQLResult">0</Variable>
                <Variable DataType="Int32" Name="NumOfJobsToRun">-1</Variable>