BIML and C# SQL connection and command to SSIS issue

Question

I have 2 tables on my Database, one called Sales.Salesperson which is the source, and the target database is dbo.Salesperson. I made a BIML that uses C# code to get a list of existing tables from the source, then import all the data from source to target, for my example, i just have the 1 table, which i listed above. On the C#, i am using SQL connection and SQL command to get the table and schema of all the tables (again only 1 so it can be dynamic),

THE PROBLEM, because i have a period, to sepearte schema and table, in my SQLcommand, when i go from BIML to Generate SSIS package, it gives me an error message from SSIS step EXECUTESQL DirectInput, when i try and truncate table <#=table #> error says "Sales.SalesPerson in package DynamicDataLoad includes invalid characters (/:[].=) for SSIS. the invalid characters will be replaced with_" AND BECAUSE OF THIS, i get an error on my SSIS package

Here is the Code below to help:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Packages>
        <Package Name="DynamicDataLoad" ConstraintMode ="Linear"          ProtectionLevel="DontSaveSensitive">
        <Tasks>
            <# foreach(var table in GetTables()) { #>
            <ExecuteSQL Name="Truncate Table Dest <#=table#>" ConnectionName="Target">
                **<DirectInput>Truncate Table <#=table#></DirectInput>          
            </ExecuteSQL>**
            <Dataflow Name="Load Table <#=table#>" >
                <Transformations>
                    <OleDbSource Name="Source Table" ConnectionName="Source">
                        <ExternalTableInput Table="<#=table#>" />
                    </OleDbSource>
                    <OleDbDestination Name="Destination Table" ConnectionName="Target">
                        <ExternalTableOutput Table="<#=table#>" />
                    </OleDbDestination>
                </Transformations>
            </Dataflow>
          <# } #>
        </Tasks>
    </Package>
 </Packages>
</Biml>

and below is the C# part, if you look at the SQLcommand, i am bringing back Schema then table in a list called "table, which gets used in the above BIML to list all tables to be converted into SSIS package, this is used in the BIML part called truncation, which is where the error is coming from in the BIML.

<#+
List<string> GetTables() {
    List<string> tables = new List<string>();

        SqlConnection cn = new SqlConnection("Data Source=mdsdqsdev;Initial Catalog=Test;Persist Security Info=False;Integrated Security=SSPI;");
        SqlDataAdapter da = new SqlDataAdapter();
        DataSet ds = new DataSet();
        DataTable dt = new DataTable();

        string q = "Select TABLE_SCHEMA+'.'+TABLE_NAME as name from INFORMATION_SCHEMA.TABLES";

        var cmd = new SqlCommand(q);
        cmd.Connection = cn;

    try 
    {
            da.SelectCommand = cmd;

            ds.Tables.Add(new DataTable("Results"));

            if(cn.State != ConnectionState.Open)
            {
                cn.Open();
            }

            ds.Tables[0].BeginLoadData();
            da.Fill(ds,"Results");
            ds.Tables[0].EndLoadData();

            dt = ds.Tables[0];

            if(cn.State != ConnectionState.Closed)
            {
                cn.Close();
            }
    }
    catch (Exception ex)
    {
        throw ex;
    }
    finally
    {
        if (cn.State != ConnectionState.Closed)
        {
            cn.Close();
        }
        cn.Dispose();
    }

    foreach (DataRow row in dt.Rows)
    {
        tables.Add(row["name"].ToString());
    }

    return tables;
}

#>

I'm not quite tracking what the problem is. Could you take a moment and click the edit button to clarify the question? Perhaps show some of the code that you are using — billinkc
added code, sorry about that, thanks for input, any help would be great, its a difficult one, trying to mix technologies together :( — SteveB
Is there a reason you've chosen this route for pulling metadata versus the native Biml extension of GetDatabaseSchema — billinkc

billinkc billinkc · Accepted Answer · 2017-02-17T17:59:50

The error is generated by <ExecuteSQL Name="Truncate Table Dest <#=table#>" ConnectionName="Target"> and <Dataflow Name="Load Table <#=table#>" > Since your table variable will be in the form of schema.table, the name of your data flow would be something like "Dataflow Foo.Bar" which if you tried to type manually, SSDT would balk specifying that you can't name it as such.

The resolution to this specific issue to to do something like "safe" the string. A low-rent approach would be just apply a Replace call to swap an underscore for the period.

<ExecuteSQL Name="Truncate Table Dest <#=table.Replace(".", "_")#>" ConnectionName="Target">

A bigger problem will be tables or schemas that have invalid characters in them or spaces. That would require you to wrap them with []. Instead of blindly applying them, I would push that work to SQL Server with QUOTENAME()

Select QUOTENAME(TABLE_SCHEMA) +'.' + QUOTENAME(TABLE_NAME) as name

I have found that I'm usually better served by having the schema and the table name served back as discrete parts, as I find concatenation much easier than splitting. That makes the signature of your GetTables() different. Maybe List<KeyValuePair<string, string>>, I don't know.

Even then, you're still doing more work than I'd want to do when the native Biml way is about 4 lines of code. I have an example where I used Biml to reverse engineer a specific schema of a database but searching on Biml + GetDatabaseSchema will net you many examples of how do so.

BIML and C# SQL connection and command to SSIS issue

1 Answers