Migrate data from multiple tables from source db to destinatin db

Hi friends,

After searching the web for couple of days i managed to do this along with one of my friend, so thought i should make it available for all the stragglers.

The scenario is- I have a source DB (i have SQL server, you may have different) and a destination DB (again SQL server). I wanna copy data from all the tables in the source to the destination DB which has identical tables.

The main issue is when we have multiple tables to be copied from source to destination and we put this in the simple data flow task under for each loop, the metadata does not get refreshed so it gives error of column mappings.

And another issue is we may not have SQL server always as source and destination.

so this has to be done programatically using script task, or if you want you may build a custom component also, but i found this approach easier.

I took a user variable to store table names and mapped it with the for each loop container.

Assigned the values to the variable (table names), you can do it with a separate task which supplies the table names from the source DB.


add a script task inside the for each loop container.


The most important part is the programming / code inside the script task.

For this you need to take reference of these

DTSRuntimeWrap DTSPipelineWrap

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
using System.Text;
using System.Collections.Generic;
using Microsoft.SqlServer.Dts.Pipeline;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;



namespace ST_5c5e95d7b5ce4b3c91c2a82c79477980.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{

#region VSTA generated code
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion



public void Main()
{
// TODO: Add your code here
String tablename = Dts.Variables["User::TableName"].Value.ToString();

Package package = new Package();
MainPipe dataFlow = ((TaskHost)package.Executables.Add("SSIS.Pipeline.2")).InnerObject as MainPipe;

//Add a SQL Server connection manager that will be used later.
ConnectionManager cm = package.Connections.Add("OLEDB");
cm.Name = "Source ConnectionManager";
cm.ConnectionString = "Data Source=SQL source;Initial Catalog=DBName;User ID=USER;Password=PWD;provider=SQLNCLI10.1;";

//Add a SQL Server connection manager that will be used later.
ConnectionManager cm1 = package.Connections.Add("OLEDB");
cm1.Name = "Destination ConnectionManager";
cm1.ConnectionString = "Data Source=SQL source;Initial Catalog=DBName;User ID=USER;Password=PWD;provider=SQLNCLI10.1;";

//Adding source component for Cache Database.
IDTSComponentMetaData100 component = dataFlow.ComponentMetaDataCollection.New();
//component.Name = "ADONETSource";
//component.ComponentClassID = "Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter, Microsoft.SqlServer.ADONETSrc, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91";
component.Name = "SQL Server Source";
component.ComponentClassID = "DTSAdapter.OLEDBSource.2";
CManagedComponentWrapper instance = component.Instantiate();
instance.ProvideComponentProperties();
if (component.RuntimeConnectionCollection.Count > 0)
{
//component.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections["Source ConnectionManager"]);
component.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(cm);
component.RuntimeConnectionCollection[0].ConnectionManagerID = cm.ID;


}
instance.SetComponentProperty("AccessMode", 0);
instance.SetComponentProperty("OpenRowset", tablename);

// Reinitialize the metadata.
instance.AcquireConnections(null);
instance.ReinitializeMetaData();
instance.ReleaseConnections();

// Adding destination component for SQL Server
IDTSComponentMetaData100 component1 = dataFlow.ComponentMetaDataCollection.New();
component1.Name = "SQL Server Destination";
component1.ComponentClassID = "DTSAdapter.OLEDBDestination.2";
CManagedComponentWrapper instance1 = component1.Instantiate();
instance1.ProvideComponentProperties();
if (component1.RuntimeConnectionCollection.Count > 0)
{
//component1.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections["Destination ConnectionManager"]);
component1.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(cm1);
component1.RuntimeConnectionCollection[0].ConnectionManagerID = cm1.ID;

}

instance1.SetComponentProperty("AccessMode", 0);
instance1.SetComponentProperty("OpenRowset", tablename);

//instance1.SetComponentProperty("BulkInsertTableName","[DimAccount]");
//instance1.SetComponentProperty("BulkInsertKeepIdentity", true);
//instance1.SetComponentProperty("BulkInsertKeepNulls", true);

// Reinitialize the metadata.
instance1.AcquireConnections(null);
instance1.ReinitializeMetaData(); //Throws exception. Message: "Exception from HRESULT: 0xC0202072" . Even if I reinitialize metadata after iterating through inputs of the component, the same exception is thrown at this statement.
instance1.ReleaseConnections();

//set path between components
IDTSPath100 path = dataFlow.PathCollection.New();
path.AttachPathAndPropagateNotifications(component.OutputCollection[0], component1.InputCollection[0]); //Assuming this is correct

// Iterate through the inputs of the component.
foreach (IDTSInput100 input in component1.InputCollection)
{
// Get the virtual input column collection for the input.
IDTSVirtualInput100 vInput = input.GetVirtualInput();

// Iterate through the virtual column collection.
foreach (IDTSVirtualInputColumn100 vColumn in vInput.VirtualInputColumnCollection)
{
// Call the SetUsageType method of the design time instance of the component.
IDTSInputColumn100 vCol = instance1.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
instance1.MapInputColumn(input.ID, vCol.ID, input.ExternalMetadataColumnCollection[vColumn.Name].ID);

}
}

// Save the package
string pkgPath = @"C:\My Documents\Visual Studio 2008\Projects\package.dtsx";

Microsoft.SqlServer.Dts.Runtime.Application appl = new Microsoft.SqlServer.Dts.Runtime.Application();

appl.SaveToXml(pkgPath, package, null);

package.Execute();


Dts.TaskResult = (int)ScriptResults.Success;
}
}
}

Check Not Null constraint in all the columns of all tables in the database

CREATE PROC SearchAllTablesForNULLValues
AS
BEGIN
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @TableName = ''
WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)
WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
AND IS_NULLABLE='NO'
)
IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName +'Is Null OR '+@ColumnName+' = '''' '
)
END
END
END
SELECT ColumnName, ColumnValue,count(*)as 'No Of Occurance' FROM #Results
group by ColumnName,ColumnValue
END

CTE to delete duplicate rows from a table

Common-table expressions are a very useful new feature in SQL Server 2005. You can use them for recursive queries,

removing duplicates, and even simple looping procedures.

With some crafty TSQL, this is a relatively easy task to do when a primary key defined on the table. Luckily, the

new CTE feature in SQL Server 2005 makes it very easy to remove these duplicates, with or without a primary key.

The script below defines my CTE. I am using a windowing function named DENSE_RANK to group the records together

based on the Product, SaleDate, and SalePrice fields, and assign them a sequential value randomly. This means that

if I have two records with the exact same Product, SaleDate, and SalePrice values, the first record will be ranked

as 1, the second as 2, and so on.

WITH SalesCTE(Product, SaleDate, SalePrice, Ranking)
AS
(

SELECT Product, SaleDate, SalePrice,Ranking = DENSE_RANK() OVER(PARTITION BY Product, SaleDate, SalePrice ORDER BY

NEWID() ASC)
FROM SalesHistory

)

DELETE FROM SalesCTE WHERE Ranking > 1
Because a CTE acts as a virtual table, I am able to process data modification statements against it, and the

underlying table will be affected. In this case, I am removing any record from the SalesCTE that is ranked higher

than 1. This will remove all of my duplicate records.

To verify my duplicates have been removed, I can review the data in the table, which should now contain 8 records,

rather than the previous 10.

SELECT *FROM SalesHistory

SQL Server 2008 Business Intelligence

Summary
SQL Server 2008 makes business intelligence available to everyone through deep integration with Microsoft Office, providing the right tool, to the right user, at the right price. Employees at all levels of an organization can see and help to influence the performance of the business by working with tools that are both easy to use and powerful. Integration with the 2007 Microsoft Office System enables users to view business performance in a way that they are familiar with. The introduction of PerformancePoint® Server 2007, helps customers gain actionable insight into the entire organization so they can monitor, analyze, and plan their businesses, as well as drive alignment, accountability, and actionable insight across the entire organization.
Download Complete white paper.

SQL Server 2008 Interview questions

General Questions asked about SQL Server

What is RDBMS?
What is Normalization?
What is De-normalization where is it used?
What are different normalization forms?
What is Stored Procedure?
What is Trigger?
What are different types of triggers?
What is View?
What is an Indexed View?
What is “Rollup” clause?

NEW Questions added..
How to decide the sequence of fields in a compound index?
Is it possible to declare a clustured index on non-primary field?
What are the commands to compare two result sets in 2005?
What are the pre-requisits to create an indexed view?
What are the advantages and disadvantages of NO LOCK option in select statement?
Can we insert/update rows in system tables?
CTE for deleting duplicate rows from a table
What are 3 different ways to execute a dymnamic SQL?
How to sychronize data with foxpro DB from SQL 2005?
How to read excel data directly into SQL server 2005?
ANS:
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Source\Addresses.xls',
'SELECT * FROM [Sheet1$]')


What are magic tables or what are inserted and deleted tables?
How to update multiple tables from a view?
What is the use of instead of trigger?
How to shrink DB files?
How to truncate transaction log?
What are DBCC commands?
How to reset value of identity column in a table?
How to create a linked server in SQL Server?
How to import data from MySQL db to SQL Server?
What are full text indexes?
What are advantages and disadvantages of Dynamic Query in a stored procedure?
How to get value of last inserted identity column?
What is the difference between a table type variable and temp table, which is better?
Can we update data in the parent table using view?



What is the need of re-indexing?
What is BCP?
What is Index?
What is a Linked Server?
What is Cursor?
What is Collation?
What is Difference between Function and Stored Procedure?
What is sub-query? Explain properties of sub-query?
What are different types of Join?
What are primary keys and foreign keys?
What is the difference between primary and unique key?
What is User Defined Functions? What kind of User‐Defined Functions can be created?
What is difference between DELETE & TRUNCATE commands?
What is difference between UNION & UNION ALL commands?
What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?
What is SQL Profiler?
What is SQL Server Agent?
What is an execution plan? When would you use it? How to you view the execution plan?




Questions specific to SQL Server 2008

What is Policy Management?
What are Sparse Columns?
What is MERGE Statement?
What is Filtered Index?
Which are new data types introduced in SQL SERVER 2008?
What are synonyms?
What is EXCEPT clause?
How to handle errors in SQL Server 2008?
What is data compression?