October 28, 2016 at 12:36 pm
# Create SSIS package for Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64)
# PSVersion 5.0.10586.117 32 bit
$ErrorActionPreference="Stop"
Set-PSDebug -Strict
Add-Type -AssemblyName "Microsoft.SQLServer.ManagedDTS, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
Add-Type -AssemblyName "Microsoft.SqlServer.DTSPipelineWrap, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
[string]$srcConnStr = "Provider=SQLNCLI11;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Test;Data Source=XYZ;"
[Microsoft.SqlServer.Dts.Runtime.Package]$Package = New-Object Microsoft.SqlServer.Dts.Runtime.Package
[Microsoft.SqlServer.Dts.Runtime.Application]$App = New-Object Microsoft.SqlServer.Dts.Runtime.Application
[Microsoft.SQLServer.Dts.Runtime.Executable]$exec = $Package.Executables.Add("STOCK:PipelineTask")
[Microsoft.SqlServer.Dts.Runtime.TaskHost]$th = [Microsoft.SqlServer.Dts.Runtime.TaskHost]$exec
$th.Name = "Data Flow Task"
[Microsoft.SqlServer.Dts.Pipeline.Wrapper.MainPipe]$DataPipe = [System.Runtime.InteropServices.Marshal]::CreateWrapperOfType($th.InnerObject, [Microsoft.SQLServer.DTS.pipeline.Wrapper.MainPipeClass])
[Microsoft.SqlServer.Dts.Runtime.ConnectionManager]$SourceConn = $package.Connections.Add("OLEDB") -As [Microsoft.SqlServer.Dts.Runtime.ConnectionManager]
$SourceConn.Name = "OLEDBConnectionSource"
$SourceConn.set_ConnectionString($srcConnStr)
$oledbSource = $DataPipe.ComponentMetaDataCollection.New()
$oledbSource.Name = "OLE DB Source"
$oledbSource.ComponentClassID = "DTSAdapter.OLEDBSource.1";
[Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapper]$InstanceSource = [System.Runtime.InteropServices.Marshal]::CreateWrapperOfType($oledbSource.Instantiate(),[Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass])
$instanceSource.ProvideComponentProperties()
Exception calling "ProvideComponentProperties" with "0" argument(s): "Exception from HRESULT: 0xC0048021"At N:\Psh\SSIS\LoadTest1.ps1:27 char:1
+ $instanceSource.ProvideComponentProperties()
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], ParentContainsErrorRecordException
+ FullyQualifiedErrorId : COMException
[p]If I change DTSAdapter.OLEDBSource.1 -> DTSAdapter.OLEDBSource.4[/p]
Element not found. (Exception from HRESULT: 0x8002802B (TYPE_E_ELEMENTNOTFOUND))At N:\Psh\SSIS\LoadTest1.ps1:27 char:1
+ $instanceSource.ProvideComponentProperties()
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : OperationStopped: (:) [], COMException
+ FullyQualifiedErrorId : System.Runtime.InteropServices.COMException
[p]Thank you[/p]
November 9, 2016 at 1:31 pm
I have to call C# from PowerShell as a workaround.
# version 1
# usage .\LoadTest "N:\DirName\LoadTest2.csv" "SERVER01.TestDB..LoadTest" ","
Param(
[Parameter(Mandatory=$True,Position=1)]
[string]$FromFileName,
[Parameter(Mandatory=$True,Position=2)]
[string]$ToTableName,
[Parameter(Mandatory=$True,Position=3)]
[string]$terminator
)
$ErrorActionPreference="Stop"
Set-PSDebug -Strict
[bool]$b = [System.IO.File]::Exists($FromFileName)
if (-Not $b)
{
Write-Output "File not found"
exit 1
}
# get sql info
[string[]]$ftname = $ToTableName.Split(".")
[string]$ServerName = $ftname[0]
[string]$dbName = $ftname[1]
[string]$schema = $ftname[2]
[string]$TableName = ''
if (-Not $schema)
{
$TableName = $ftname[3]
}
else
{
$TableName = $schema + "." + $ftname[3]
}
#[Reflection.Assembly]::LoadWithPartialName("Microsoft.SQLServer.ManagedDTS") #| out-null
#[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Dts.Runtime")
#[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.DTSPipelineWrap")
# C# code from Integration Services samples, sqlsrvintegrationsrv-114171.zip, PackageGeneration
Add-Type -AssemblyName "Microsoft.SQLServer.ManagedDTS, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
Add-Type -AssemblyName "Microsoft.SqlServer.DTSPipelineWrap, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
Add-Type -AssemblyName "Microsoft.SqlServer.PipelineHost, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
$Assem = (
"Microsoft.SQLServer.ManagedDTS, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91",
"Microsoft.SqlServer.DTSPipelineWrap, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91",
"Microsoft.SqlServer.PipelineHost, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91",
"Microsoft.SqlServer.DTSRuntimeWrap, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91")
$Source = @"
using System;
using System.Text;
using System.Collections;
using System.Globalization;
using Microsoft.SqlServer.Dts.Runtime;
using wrapper = Microsoft.SqlServer.Dts.Runtime.Wrapper;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
namespace St.Tools
{
public static class SourceAdapter
{
#pragma warning disable 0169
private static IDTSExternalMetadataColumnCollection100 extCols;
#pragma warning disable 0169
private static IDTSPipeline100 pipeline;
static Hashtable colPairTable;
static string columnDelimiter = "$terminator";
static wrapper.DataType[] colTypes;
public static void foo (Microsoft.SqlServer.Dts.Runtime.Package package)
{
Console.WriteLine("Start");
// Set the IDTSComponentEvent handler to capture the details from any
// COMExceptions raised during package generation
//ComponentEventHandler events = new ComponentEventHandler();
//pipeline.Events = DtsConvert.GetExtendedInterface(events as IDTSComponentEvents);
Executable dataFlowTask = package.Executables.Add("STOCK:PipelineTask");
Microsoft.SqlServer.Dts.Runtime.TaskHost taskhost = dataFlowTask as Microsoft.SqlServer.Dts.Runtime.TaskHost;
taskhost.Name = "Data Flow Task";
pipeline = taskhost.InnerObject as MainPipe;
// AddConnectionManager
ConnectionManager srcConnMgr = package.Connections.Add("FLATFILE");
srcConnMgr.Name = "FlatFileSource";
srcConnMgr.ConnectionString = @"$FromFileName"; //"N:\\Psh\\SSIS\\LoadTest2.csv";
///Set the custom properties for flat file connection mgr
wrapper.IDTSConnectionManagerFlatFile100 ffConMgr = srcConnMgr.InnerObject as wrapper.IDTSConnectionManagerFlatFile100;
ffConMgr.Format = "Delimited"; // can be parameterized, use "Delimited" for simplicity
ffConMgr.ColumnNamesInFirstDataRow = false;
ffConMgr.RowDelimiter = "\r"; // can be parameterized, use "\r" for simplicity
wrapper.IDTSConnectionManagerFlatFileColumns100 ffCols = ffConMgr.Columns; ///////////////////////
// AddConnectionManager dest
ConnectionManager destConnMgr = package.Connections.Add("OLEDB") as ConnectionManager;
destConnMgr.Name = "OLEDBConnectionDestination";
destConnMgr.ConnectionString = "Provider=SQLNCLI11;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=$dbName;Data Source=$ServerName;PacketSize=32767;AutoTranslate=False;";
//dect AddDestAdapter
IDTSComponentMetaData100 destComp = pipeline.ComponentMetaDataCollection.New();
destComp.ComponentClassID = "{63F60893-F24D-421A-A481-86C53A0ADBA8}";
destComp.ValidateExternalMetadata = true;
IDTSDesigntimeComponent100 destDesignTimeComp = destComp.Instantiate();
destDesignTimeComp.ProvideComponentProperties();
destComp.Name = "OleDB Destination - Sql Server";
destDesignTimeComp.SetComponentProperty("AccessMode", 3);
destDesignTimeComp.SetComponentProperty("OpenRowset", "$TableName");
destDesignTimeComp.SetComponentProperty("FastLoadOptions","TABLOCK,ROWS_PER_BATCH=100000");
// set connection
destComp.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(destConnMgr);
destComp.RuntimeConnectionCollection[0].ConnectionManagerID = destConnMgr.ID;
// get metadata
destDesignTimeComp.AcquireConnections(null);
destDesignTimeComp.ReinitializeMetaData();
destDesignTimeComp.ReleaseConnections();
extCols = destComp.InputCollection[0].ExternalMetadataColumnCollection;
//extCols.Count = numCols !!!
wrapper.DataType[] colTypes = new wrapper.DataType[extCols.Count];
int numCols = colTypes.Length;
for (int i = 0; i < numCols; i++)
{
colTypes = extCols.DataType;
}
String [] destColNames = new String[extCols.Count];
for (int i = 0; i < extCols.Count; i++)
{
destColNames = extCols.Name;
// src
wrapper.IDTSConnectionManagerFlatFileColumn100 ffCol = ffCols.Add();
ffCol.ColumnType = "Delimited";
// last col use row delimiter
if (i == (extCols.Count - 1))
{
ffCol.ColumnDelimiter = ffConMgr.RowDelimiter;
}
else
{
ffCol.ColumnDelimiter = columnDelimiter;
}
ffCol.DataType = extCols.DataType;
ffCol.MaximumWidth = extCols.Length;
ffCol.DataScale = extCols.Scale;
ffCol.DataPrecision = extCols.Precision;
wrapper.IDTSName100 colName = ffCol as wrapper.IDTSName100;
colName.Name = extCols.Name;
}
// AddSourceAdapter
IDTSComponentMetaData100 srcComp = pipeline.ComponentMetaDataCollection.New();
//srcComp.ComponentClassID = @"{D1AA3237-CE07-40F9-AA7D-DB75A2552BAC}";
srcComp.ComponentClassID = @"{C4D48377-EFD6-4C95-9A0B-049219453431}"; //HKEY_LOCAL_MACHINE\SOFTWARE\Classes\DTSAdapter.FlatFileSource.4
srcComp.ValidateExternalMetadata = true;
IDTSDesigntimeComponent100 srcDesignTimeComp = srcComp.Instantiate();
srcDesignTimeComp.ProvideComponentProperties();
srcComp.Name = "Flat File Source";
// set connection
srcComp.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(srcConnMgr);
srcComp.RuntimeConnectionCollection[0].ConnectionManagerID = srcConnMgr.ID;
// get metadata
srcDesignTimeComp.AcquireConnections(null);
srcDesignTimeComp.ReinitializeMetaData();
srcDesignTimeComp.ReleaseConnections();
colPairTable = new Hashtable(destColNames.Length);
foreach (String name in destColNames)
{
colPairTable[name] = name;
}
// AddPathsAndConnectColumns
IDTSOutput100 srcOutput = srcComp.OutputCollection[0];
IDTSOutputColumnCollection100 srcOutputCols = srcOutput.OutputColumnCollection;
IDTSInput100 destInput = destComp.InputCollection[0];
IDTSInputColumnCollection100 destInputCols = destInput.InputColumnCollection;
IDTSExternalMetadataColumnCollection100 destExtCols = destInput.ExternalMetadataColumnCollection;
Hashtable destColtable = new Hashtable(destExtCols.Count);
foreach (IDTSExternalMetadataColumn100 extCol in destExtCols)
{
destColtable.Add(extCol.Name, extCol);
}
// colConnectTable stores a pair of columns which dont need a type conversion and can be connected directly.
Hashtable colConnectTable = new Hashtable(srcOutputCols.Count);
foreach (IDTSOutputColumn100 outputCol in srcOutputCols)
{
String colNameToLookfor = outputCol.Name;
// Set FastParse where appropriate
switch (outputCol.DataType)
{
case wrapper.DataType.DT_I1:
case wrapper.DataType.DT_I2:
case wrapper.DataType.DT_I4:
case wrapper.DataType.DT_I8:
case wrapper.DataType.DT_UI1:
case wrapper.DataType.DT_UI2:
case wrapper.DataType.DT_UI4:
case wrapper.DataType.DT_UI8:
outputCol.CustomPropertyCollection["FastParse"].Value = true;
break;
default: break;
}
IDTSExternalMetadataColumn100 extCol = (IDTSExternalMetadataColumn100)destColtable[colNameToLookfor];
colConnectTable.Add(outputCol.ID, extCol);
}
pipeline.PathCollection.New().AttachPathAndPropagateNotifications(srcOutput, destInput);
IDTSVirtualInput100 destVirInput = destInput.GetVirtualInput();
foreach (object key in colConnectTable.Keys)
{
int colID = (int)key;
IDTSExternalMetadataColumn100 extCol = (IDTSExternalMetadataColumn100)colConnectTable;
// Create an input column from an output col of previous component.
destVirInput.SetUsageType(colID, DTSUsageType.UT_READONLY);
IDTSInputColumn100 inputCol = destInputCols.GetInputColumnByLineageID(colID);
if (inputCol != null)
{
// map the input column with an external metadata column
destDesignTimeComp.MapInputColumn(destInput.ID, inputCol.ID, extCol.ID);
}
}
if (package.Validate(package.Connections, null, null, null) != DTSExecResult.Success)
{
Application app = new Application();
app.SaveToXml("PkgnotValid.dtsx", package, null);
}
else
{
//Application app = new Application();
//app.SaveToXml("N:\\Psh\\SSIS\\GoodPkg.dtsx", package, null);
Console.WriteLine("Execute package");
DTSExecResult result = package.Execute();
StringBuilder sb = new StringBuilder();
if (result != DTSExecResult.Success)
{
foreach (DtsError err in package.Errors)
{
sb.AppendLine(err.Description);
}
Console.WriteLine(sb.ToString());
}
}
Console.WriteLine("End");
}
}
}
"@
if (-Not ([System.Management.Automation.PSTypeName]'Stifel.Tools.SourceAdapter').Type)
{
Add-Type -ReferencedAssemblies $Assem -TypeDefinition $Source -Language CSharp
}
[Microsoft.SqlServer.Dts.Runtime.Package]$Package = New-Object Microsoft.SqlServer.Dts.Runtime.Package
$Package.CreatorName = "St DB Team"
[St.Tools.SourceAdapter]::foo($Package)
November 9, 2016 at 2:02 pm
Why do you want to create an SSIS package from Powershell?
November 9, 2016 at 2:26 pm
Luis Cazares (11/9/2016)
Why do you want to create an SSIS package from Powershell?
Masochism? 😀
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 11, 2016 at 6:34 am
For production environment we prefer script solution to compiled executable.
November 11, 2016 at 6:56 am
Dmitriy Burtsev (11/11/2016)
For production environment we prefer script solution to compiled executable.
SSIS packages are not compiled executables, they're just XML files.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 11, 2016 at 7:43 am
Dmitriy Burtsev (11/11/2016)
For production environment we prefer script solution to compiled executable.
So, you'll be asking MS for the source code for DTExec? That's the compiled executable needed.
November 11, 2016 at 9:36 am
Phil Parkin (11/11/2016)
Dmitriy Burtsev (11/11/2016)
For production environment we prefer script solution to compiled executable.SSIS packages are not compiled executables, they're just XML files.
I know that. One need executable to create a package. Unless you want to type all XML by hand.
November 11, 2016 at 9:42 am
Luis Cazares (11/11/2016) So, you'll be asking MS for the source code for DTExec? That's the compiled executable needed.
No.
In my script I don't have table name, columns name, columns type and so on. All I know that file structure is the same as table structure. In this case I can create SSIS package in run-time, map input and output fields and execute it. DTExec can't create a package. You need some program like Visual Studio.
November 11, 2016 at 9:49 am
Let say you have to ETL several files to staging tables. You don't know how many tables. You don't know tables and files structure at design time.
Somebody else later will call your script and provide file and table name. The script should create the SSIS package, map input and output fields and execute the package.
November 11, 2016 at 10:21 am
Dmitriy Burtsev (11/11/2016)
Let say you have to ETL several files to staging tables. You don't know how many tables. You don't know tables and files structure at design time.Somebody else later will call your script and provide file and table name. The script should create the SSIS package, map input and output fields and execute the package.
OK, that makes more sense now that you've explained it a bit better.
You want to automate the creation of packages which will load from a flat file to a table ... one package per table in the DB. Have you considered BIML?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 11, 2016 at 12:25 pm
I may in the future. I never work wit BIML before.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply