I'm quite new to BIML and I'm pretty stuck with a problem, for which I couldn't find a proper solution here or in any other forum.
The task at hand is to create SSIS ETL packages for something like 60 tables in order to load data from server A to server B. Since I didn't fancy the idea to create the packages manually, I decided to give BIML a try. However, I GET the desired packages (one per table), BUT each and every package throws a "The metadata of the following output column does not match the metadata of the external columns with which the output columns are associated" in the source of the data flow and with this an error/warning in the destination as well. The column which throws the error (or at least one column...) is of DataType [image] (I don't have influence on this data type - this is as it is provided by the source system). However, the OutputColumn which BIML generates receives the datatype DT_NTEXT, which of course does not match the type of the external column (image).
Following what I got so far:
<#@ template language="C#" hostspecific="true"#>
<#@ import namespace="System.Data" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Connections>
<OleDbConnection Name="Source" ConnectionString="#####"/>
<OleDbConnection Name="Target" ConnectionString="#####"/>
</Connections>
<Packages>
<#
string metadataConnectionString = "#####";
DataTable tables = ExternalDataAccess.GetDataTable(metadataConnectionString,
"SELECT s.name + '_' + t.name AS Bez, '[' + s.name + '].[' + t.name + ']' AS Col FROM sys.tables t INNER JOIN sys.schemas s on t.schema_id = s.schema_id");
foreach (DataRow row in tables.Rows)
{
#>
<Package Name="Extract_<#=row[0]#>" ConstraintMode="Parallel" AutoCreateConfigurationsType="None">
<Tasks>
<ExecuteSQL Name="Truncate <#=row[0]#>" ConnectionName="Target">
<DirectInput>
Truncate Table <#=row[1]#>
</DirectInput>
</ExecuteSQL>
<Dataflow Name="Copy Data">
<PrecedenceConstraints>
<Inputs>
<Input OutputPathName="Truncate <#=row[0]#>.Output" />
</Inputs>
</PrecedenceConstraints>
<Transformations>
<OleDbSource Name="Retrieve Data" ConnectionName="Source">
<ExternalTableInput Table="<#=row[1]#>" />
</OleDbSource>
<OleDbDestination Name="Insert Data" ConnectionName="Target">
<ExternalTableOutput Table="<#=row[1]#>"/>
</OleDbDestination>
</Transformations>
</Dataflow>
</Tasks>
</Package>
<#
}
#>
</Packages>
</Biml>
Since I read that it might be connected to the Provider used in the Connections: I am using Provider=SQLNCLI11.1 with Integrated Security=SSPI
Any help would be highly appreciated. Thanks!
Its because BIML and SSIS uses its own datatype. See this link for correct Datatypes:
https://www.cathrinewilhelmsen.net/2014/05/27/sql-server-ssis-and-biml-data-types/
You could put this in your code in a snippet and call it. Properbly you also need to change your source from ExternalTableInput to direct input, so that you write your SQL Select automatically. This is old code - Its much easier to put into a DLL Libaray.
What i have done is like this:
As you can see i call DerivedColumnDatatypeConversion which looks like this: