Error using SSIS Bids in Visual Studio 2012

Question

I have to load several tables into SQL Server 2012 from SQL Server 2000. I heard BIDS could do this and I'm pretty new to it and wanted to some help. I would really appreciate whatever help I get with it.

I have Installed BIDS helper. already and used the below code. But it gives me errors stating,

Error 1187 Illegal syntax. Expecting valid start name character. Error 1188 Character '#', hexadecimal value 0x23 is illegal in an XML name. Error 1189 The character '@', hexadecimal value 0x40 is illegal at the beginning of an XML name.

---

<#@ template language="C#" hostspecific="true" #>
<#@ import namespace="System.Data" #>
<#@ import namespace="System.Data.SqlClient" #>
<#@ import namespace="System.IO" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<!--
<#
    string connectionStringSource = @"Server=xxxxx;Initial Catalog=xxxx;Integrated Security=SSPI;Provider=sqloledb";
    string connectionStringDestination = @"Server=xxxxxx;Initial Catalog=xxxxxxx;Integrated Security=SSPI;Provider=SQLNCLI11.1";
    string SrcTableQuery =     @"
SELECT
    SCHEMA_NAME(t.schema_id) AS schemaName
,   T.name AS tableName
FROM
    sys.tables AS T
WHERE
    T.is_ms_shipped = 0
    AND T.name <> 'sysdiagrams';
";

    DataTable dt = null;
    dt = ExternalDataAccess.GetDataTable(connectionStringSource, SrcTableQuery);
#>    
-->
    <Connections>
        <OleDbConnection
            Name="SRC"
            CreateInProject="false"
            ConnectionString="<#=connectionStringSource#>"
            RetainSameConnection="false">
        </OleDbConnection>
        <OleDbConnection
            Name="DST"
            CreateInProject="false"
            ConnectionString="<#=connectionStringDestination#>"
            RetainSameConnection="false">
        </OleDbConnection>
    </Connections>

    <Packages>
        <# foreach (DataRow dr in dt.Rows) { #>
            <Package ConstraintMode="Linear"
                Name="<#=dr[1].ToString()#>"

            >
            <Variables>
                <Variable Name="SchemaName" DataType="String"><#=dr[0].ToString()#></Variable>
                <Variable Name="TableName" DataType="String"><#=dr[1].ToString()#></Variable>
                <Variable Name="QualifiedTableSchema"
                          DataType="String"
                          EvaluateAsExpression="true">"[" +  @[User::SchemaName] + "].[" +  @[User::TableName] + "]"</Variable>
            </Variables>
            <Tasks>
                <Dataflow
                    Name="DFT"
                >
                    <Transformations>
                        <OleDbSource
                            Name="OLE_SRC <#=dr[0].ToString()#>_<#=dr[1].ToString()#>"
                            ConnectionName="SRC"
                        >
                            <TableFromVariableInput VariableName="User.QualifiedTableSchema"/>
                        </OleDbSource>
                        <OleDbDestination
                            Name="OLE_DST <#=dr[0].ToString()#>_<#=dr[1].ToString()#>"
                            ConnectionName="DST"
                            KeepIdentity="true"
                            TableLock="true"
                            UseFastLoadIfAvailable="true"
                            KeepNulls="true"
                            >
                            <TableFromVariableOutput VariableName="User.QualifiedTableSchema" />                        
                        </OleDbDestination>
                    </Transformations>
                </Dataflow>

            </Tasks>
            </Package>
        <# } #>
    </Packages>
</Biml>

Answer 1

This is the maddening thing about trying to do much BimlScript in Visual Studio. The editor "knows" it's doing XML markup so all of the enhancements that make up BimlScript are "wrong" and so it's going to highlight them and put angry red squigglies and have you question whether you really have valid code here.

In my Error List, I see the same things you're seeing but this is one of the few times you can ignore Visual Studio's built in error checker.

Instead, the true test of whether the code is good is right click on the .biml file(s) and select "Check Biml for errors"

You should get a dialogue like

If so, click Generate SSIS Packages and then get some tape to attach your mind back into your head as it's just been blown ;)

Operational note

Note that the supplied code is going to copy all of the data from the source to the target. But, you've also specified that this is going to be a monthly operation so you'd either want to add a truncate step via an Execute SQL Task, or factor in a Lookup Transformation (or two) to determine new versus existing data and change detection.