Initialization String Error in SSIS using Biml and c#

Question

I'm trying to import some Config Datatable to execute a dynamic Staging with SSIS, BIML and C#. In the end I want to initialize the table ImportTable to build me a staging environment in SSIS. It throws me the following error:

"Format of the initialization string does not conform to specification starting at index 0."

I read that it might have something to do with the ConnectionString. I tried with an empty ConnectionString but the same error occurred. Somebody has an Idea how to get rid of the error/has an alternative solution.

Thanks for your help and best regards

My Code: (The Tasks might make no greater sense)

<#@ import namespace="System.Data" #>
<#@ import namespace="System.Data.OleDb" #>
<#@ import namespace="System.Data.SqlClient" #>
<#@ template language="C#" tier="2" #>

<Biml xmlns="http://schemas.varigence.com/biml.xsd">

<Connections>
<OleDbConnection Name="Source" 
     ConnectionString="Provider=SQLOLEDB;
               Server=.\MSSQL17;
      Initial Catalog=AdventureWorksDW2014;
  Integrated Security=SSPI;" />
<OleDbConnection Name="Target_Meta" 
     ConnectionString="Provider=SQLOLEDB;
               Server=.\MSSQL17;
      Initial Catalog=BIMLMetaDaten;
  Integrated Security=SSPI;" />    
</Connections>
<#

OleDbDataAdapter ordAdapter = new OleDbDataAdapter("SELECT * FROM 
                              ImportTable", "Target_Meta");
           DataTable ImpTab = new DataTable();
           ordAdapter.Fill(ImpTab, "ImportTable");

#>

<Packages>
    <Package Name="002_Extractions" ConstraintMode="Parallel">
        <Tasks>
            <# foreach (DataRow row in ImpTab.Rows){ #>
            <Container Name="<#= ImpTab.Columns["TableName"] #>" 
             ConstraintMode="Linear">
                <Tasks>
                  <ExecuteSQL Name="truncate <#= ImpTab.Columns 
                     ["TableID"] #>_<#= ImpTab.Columns["TableName"] #>" 
                     ConnectionName="Target_Meta" >
                        <DirectInput>
                         Select 1
                        </DirectInput>
                  </ExecuteSQL>
                  <Dataflow Name="copy <#=  ImpTab.Columns["TableID"] 
                    #>_<#= ImpTab.Columns["TableName"] #>">
                  </Dataflow>
                </Tasks>
            </Container>
            <#} #>
        </Tasks>
    </Package>
    </Packages>
</Biml>

SqlKindaGuy · Answer 1 · 2017-11-09T09:54:48.897

I would write it like this:

This here is the select statement where you need data from - For your case it could be SELECT * FROM IMPORTTABLE

Change your strings to whatever you need and whatever server version you have.

If it is same Database you only need the one connectionstring else make serveral - its up to you

Look at this first script its basically the same Biml Script connection and external data access

  <# var metadataConnectionString = 
   "Provider=SQLNCLI11;Server=EGC25199;Integrated Security=SSPI;Initial 
    Catalog=MetaData"; #>

 <# var logConnectionString = "Provider=SQLNCLI11;Server=EGC25199;Integrated 
  Security=SSPI;Initial Catalog=Utility"; #>

<# var metadataSelect = @"
 SELECT * FROM ImportTable";


DataTable dt = ExternalDataAccess.GetDataTable(metadataConnectionString, metadataSelect);
#>

<Connections>
<Connection Name="UTILITY" ConnectionString="<#=logConnectionString #>" 
 CreateInProject="true" />
</Connections>

<Packages> YOUR CODE

After that use your foreach like your do.

Initialization String Error in SSIS using Biml and c#

1 Answers1