Databases
StackOverflow2013
Postsdbo
PO
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    text
    copied!<p>I've become a fan of using biml to solve these sorts of problems. </p> <ol> <li>Download and install <a href="http://bidshelper.codeplex.com/" rel="nofollow">BIDS Helper</a></li> <li>Add a new biml file to an existing SSIS project</li> <li>Disable Visual Studio's auto-fix for XML files. See <a href="http://bimlscript.com/Walkthrough/Details/45" rel="nofollow">Overcoming BimlScript Copy and Paste Issues in Visual Studio</a></li> <li>Paste this code into that Bimlscript.biml file</li> <li>Fix your source and destination connection strings (lines 8 &amp; 9) to point to the correct servers as well as change the Provider type if SQLNCLI11.1 is not correct for your version of SQL Server</li> <li>Right-Click on the biml file and select "Generate SSIS Packages"</li> </ol> <p>Assuming everything is lined up, you'd end up with 29 packages that have a single data flow task in them pulling from source to destination (based on an SSIS Variable). </p> <pre><code>&lt;#@ template language="C#" hostspecific="true" #&gt; &lt;#@ import namespace="System.Data" #&gt; &lt;#@ import namespace="System.Data.SqlClient" #&gt; &lt;#@ import namespace="System.IO" #&gt; &lt;Biml xmlns="http://schemas.varigence.com/biml.xsd"&gt; &lt;!-- &lt;# string connectionStringSource = @"Server=localhost\dev2012;Initial Catalog=AdventureWorksDW2012;Integrated Security=SSPI;Provider=SQLNCLI11.1"; string connectionStringDestination = @"Server=localhost\dev2012;Initial Catalog=AdventureWorksDW2012_DEST;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 &lt;&gt; 'sysdiagrams'; "; DataTable dt = null; dt = ExternalDataAccess.GetDataTable(connectionStringSource, SrcTableQuery); #&gt; --&gt; &lt;Connections&gt; &lt;OleDbConnection Name="SRC" CreateInProject="false" ConnectionString="&lt;#=connectionStringSource#&gt;" RetainSameConnection="false"&gt; &lt;/OleDbConnection&gt; &lt;OleDbConnection Name="DST" CreateInProject="false" ConnectionString="&lt;#=connectionStringDestination#&gt;" RetainSameConnection="false"&gt; &lt;/OleDbConnection&gt; &lt;/Connections&gt; &lt;Packages&gt; &lt;# foreach (DataRow dr in dt.Rows) { #&gt; &lt;Package ConstraintMode="Linear" Name="&lt;#=dr[1].ToString()#&gt;" &gt; &lt;Variables&gt; &lt;Variable Name="SchemaName" DataType="String"&gt;&lt;#=dr[0].ToString()#&gt;&lt;/Variable&gt; &lt;Variable Name="TableName" DataType="String"&gt;&lt;#=dr[1].ToString()#&gt;&lt;/Variable&gt; &lt;Variable Name="QualifiedTableSchema" DataType="String" EvaluateAsExpression="true"&gt;"[" + @[User::SchemaName] + "].[" + @[User::TableName] + "]"&lt;/Variable&gt; &lt;/Variables&gt; &lt;Tasks&gt; &lt;Dataflow Name="DFT" &gt; &lt;Transformations&gt; &lt;OleDbSource Name="OLE_SRC &lt;#=dr[0].ToString()#&gt;_&lt;#=dr[1].ToString()#&gt;" ConnectionName="SRC" &gt; &lt;TableFromVariableInput VariableName="User.QualifiedTableSchema"/&gt; &lt;/OleDbSource&gt; &lt;OleDbDestination Name="OLE_DST &lt;#=dr[0].ToString()#&gt;_&lt;#=dr[1].ToString()#&gt;" ConnectionName="DST" KeepIdentity="true" TableLock="true" UseFastLoadIfAvailable="true" KeepNulls="true" &gt; &lt;TableFromVariableOutput VariableName="User.QualifiedTableSchema" /&gt; &lt;/OleDbDestination&gt; &lt;/Transformations&gt; &lt;/Dataflow&gt; &lt;/Tasks&gt; &lt;/Package&gt; &lt;# } #&gt; &lt;/Packages&gt; &lt;/Biml&gt; </code></pre> <p>At this point, you simply need to figure out how you want to coordinate the execution of the packages.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload