Thursday 29 December 2011

Moving an SSIS Package from a 32 bit machine to a 64 bit machine

I have an SSIS package that works on a 32 bit server but fails on a 64 bit machine with a number of errors, the most interesting being:


[Excel Destination [511]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC00F9304. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

  
Surfing I found these sites...

http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/289e29ad-26dc-4f90-bad4-ffb86c76e5f9

http://toddmcdermid.blogspot.com/2009/10/quick-reference-ssis-in-32-and-64-bits.html

It turns out that there are a number of drivers that are available in 32 bit but are not available in 64 bit. It is possible to run an SSIS package in 32 bit on a 64 bit machine. These URLs suggest

The first thing is to make the Visual Studio 2008 DTS package run in 32 bit mode in the designer. This can be done by:

  1. Goto the properties of the SSIS project 
  2. In the left tree view select Configuration Properties
  3. Set Run64BitRuntime = false

This enables you to debug the SSIS package. The next step is to execute the package in 32 bit. The easiest approach is to use

DTExec /f "D:\MyPath\MySSISPackage.dtsx" /SET \package.Variables[Variable1].Value;"Value1" /SET \package.Variables[Variable2].Value;"Value2" /X86

This works well if the total length of the command line is less than 255 characters. In my case I had a lot of parameters.

To get arround this 255 caharacter limitation I think the best solution is to make a command line executable that is compiled in 32 bit. I thought about making a power shell command for this but decided it would be simpler to keep it to a command line and search the standard output for "Success" or "Failure".

To get this to work I included the following DLLs:
D:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.ConnectionInfo.dll
D:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Dts.Design.dll

D:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.DTSPipelineWrap.dll

D:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SQLServer.DTSRuntimeWrap.dll

D:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SQLServer.ManagedDTS.dll
The code looked like:


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using Microsoft.SqlServer.Dts.Runtime;
using System.IO;
using System.Threading;

namespace Ssis32BitExec
{
class Program
{
static void Main(string[] args)
{

if (args.Count() != 2)
{
Console.WriteLine("Ssis32BitExec ");
Console.WriteLine("===========================================");
Console.WriteLine("");
Console.WriteLine("Utility to execute an SSIS package in 32 bit mode thus enabling 32 bit drivers");
Console.WriteLine("that may not be available in 64 bit");
Console.WriteLine("");
Console.WriteLine("");

Console.WriteLine(" UNC path to .dtsx file (SSIS package)");
Console.WriteLine(" Path to parameter name and value pair file (This file");
Console.WriteLine(" contains lines like ParameterName;ParameterValue)");

Console.ReadLine();
Environment.Exit(0);
}

string packagePath = args[0];
string parameterValuePairsPath = args[1];

Console.WriteLine("SizeOf IntPtr is: {0}", IntPtr.Size);

if (!File.Exists(packagePath))
{
Console.WriteLine("Failed: The following file does not exist {0}", packagePath);
Environment.Exit(0);
}

if (!File.Exists(parameterValuePairsPath))
{
Console.WriteLine("Failed: The following file does not exist {0}", parameterValuePairsPath);
Environment.Exit(0);
}


XmlDocument packageDoc = new XmlDocument();
packageDoc.Load(packagePath);

Package package = new Package();
package.LoadFromXML(packageDoc.DocumentElement, null);


string[] lines= File.ReadAllLines(parameterValuePairsPath);
string[] Fields;
foreach(string line in lines)
{
Fields = line.Split(';');
if (Fields.Count() != 2)
{
Console.WriteLine("Failed: The following line is not correctly formated {0}", line);
Thread.CurrentThread.Abort();
}
if (package.Variables.Contains(Fields[0]))
{
package.Variables[Fields[0]].Value=Fields[1];
}
}

//This is a fudge factor that allows a badly constructed package to throw some errors and run through
//package.MaximumErrorCount = 2;


DTSExecResult result = package.Execute();

if (result == DTSExecResult.Success)
{
Console.WriteLine("Success");
Environment.Exit(0);

}
else
{

Console.WriteLine("Failed");
for (int i = 0; i < package.Errors.Count; i++) { Console.WriteLine(package.Errors[i].Description); } Environment.Exit(1); } } } }



To know for sure that this is running in 32 bit the Console.WriteLine("SizeOf IntPtr is: {0}", IntPtr.Size); equals 4 for 32 bit and 8 for 64 bit.

I call this from within VB.NET in:


Dim TempFilePath As String = IO.Path.GetTempFileName
Dim Sw As New StreamWriter(TempFilePath)
Sw.WriteLine("Param1;{0}", Value1)
Sw.WriteLine("Param2;{0}", Value2)
Sw.Close()

Dim SSIS32BitProcess As New ProcessStartInfo()
SSIS32BitProcess.WorkingDirectory = Environment.CurrentDirectory
SSIS32BitProcess.FileName = "Ssis32BitExec.exe"
SSIS32BitProcess.UseShellExecute = False
SSIS32BitProcess.RedirectStandardOutput = True
SSIS32BitProcess.RedirectStandardError = True
SSIS32BitProcess.Arguments = packagePath + " " + TempFilePath

Dim Proc As Process = Process.Start(SSIS32BitProcess)

Dim Out As String = Proc.StandardOutput.ReadToEnd()
Proc.WaitForExit()

Dim Err As String = Proc.StandardError.ReadToEnd()
Proc.WaitForExit()

If Out.Contains("Success") Then
Return True
Else
Message = Out + vbCr + Err
Return False
End If
File.Delete(TempFilePath)
Return True

Monday 5 December 2011

How to version control databases using TFS and Visual Studio Database Projects

We have some databases that contain certain reference data. We would like to keep track of changes to both the schema and the reference data and store both in one central place. Another challenge was to allow everyone to develop on a local version of the database thus avoiding colliding and breaking changes. This was solved in the following way:

1. Open Visual Studio 2010
2. File/New Project
3. Go to the tree view of the New Project Dialog select
Database/SQL Server
4. In the list select SQL Server 2008 Database Project
- Choose an appropriate Solution Name
- The Name should be the same as the database you are putting into source control
5. [OK]
6. Right mouse click on the database project and select "Import Database Objects and Settings"
7. Click New Connection
8. Server Name = (local)
Database Name = the local database you are trying to version
[OK]
9. Select the following check boxs:
Script the collation only if it is different from the database collation
Import extended properties
10. [Start]

If you have multiple instances of SQL Server (eg 2005, 2008 etc) Then you need to create an Alias for the database instance that you are targeting

Aliases are created by:
1. Open Sql Server Configuration Manager
2. Click on the SQL Server Network Configuration and enable Named Pipes
3. Click on all versions of SQL Native Cient 10.0 Configuration
Add a new alias
- Alias Name: MyDB_LOCAL
- Pipe Name: \\localhost\pipe\MSSQL2008R2
- Protocol: Named Pipes
- Server: localhost\SQL2008R2
4. Repeat 3 for SQL Native Cient 10.0 Configuration (32bit)

The Database may be documented using extended properties

Extended properties are access by
1. Open the Microsoft SQL Server Management Studio ...
2. Right Mouse click and select properties
3. In the tree view select properties
4. Add the combination of Name and Value
Extended properties are available on the database level down to the table field level

After this you have imported the database schema into a Visual Studio Database project. The next step is to insert data. Depending on what you want to achieve there are several mechanisms for importing data.

The PostDeployment script is intended to be used for populating reference data. This can be found in the following way:
In the VS2010 database projects under the solution directory Scripts\PostDeployment\Script.PostDeployment.sql
In the file system under DatabaseProject\Scripts\Post-Deployment

There are several possibilities for inserting reference data:

1. If it's small you can use INSERT INTO eg
INSERT INTO [MyDatabase].[dbo].[DATABASE_VERSION] ([DATE],[MODEL_VERSION],[DATABASE_VERSION],[COMMENTS])
VALUES ('2011-01-07','4.1.0','1.0.0','A comment about what is new in this version')
GO

2. If it's big and in multiple tables any you are not looking to compare changes in reference data you can include a bakup of a database.

Then the script would look something like:

USE [master]
go

if (exists(select * from sys.databases where name=N'Testdata') )
begin
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'Testdata'

ALTER DATABASE [Testdata] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DROP DATABASE [Testdata]
end
go

-- Note that SolutionRoot is an environmental variable that can either be set in the control panel or via a batch file that opens the database project

RESTORE DATABASE [Testdata] FROM DISK = N'$(SolutionRoot)\MyDatabase\Scripts\Post-Deployment\Testdata.bak' WITH FILE = 1, MOVE N'Testdata' TO N'$(DefaultSqlFileLocation)\Testdata.mdf', MOVE N'Testdata_log' TO N'$(DefaultSqlFileLocation)\Testdata_1.ldf', NOUNLOAD, REPLACE, STATS = 10
GO

INSERT [MyDatabase].[dbo].[StudentList] (StFName,StLName,StEmail,OrderID)
SELECT StFName,StLName,StEmail,OrderID FROM [Testdata].[dbo].[StudentList]

3. Here is another method using BCP and a binary file.
This can be useful when you don't want the inconvenience of creating a backup etc.

To create the bcp file (adjust the table in the from-clause, and possibly the server after the -S param):
bcp "select field1,field2,field3 from MyDatabase.dbo.MyTable order by Id asc" queryout MyTable.bcp -S localhost -T -E -N


The script looks something like:

:setvar EstimatedRowCount 5000000

ALTER TABLE MyDatabase.dbo.MyTable NOCHECK CONSTRAINT ALL

bulk insert MyDatabase.dbo.MyTable from '$(SolutionRoot)\MyDatabase\Scripts\Post-Deployment\MyTable.bcp' with (DATAFILETYPE = 'widenative', order(ModelEventId asc), keepidentity, KEEPNULLS, ROWS_PER_BATCH = $(EstimatedRowCount))

ALTER TABLE MyDatabase.dbo.MyTable WITH CHECK CHECK CONSTRAINT ALL


4. Here is another method using BCP and a text file.
With this it is possible to compare versions of reference data directly out of TFS
To create the text file

i. Right mouse click on the database name and select All Tasks/Export Data
ii. Select Next and then in the Choose a source dialog select
- Database source : SQL Server Native Client 10.0
- Server Name : The Server Name
- Database : The database
[Next]
iii In the Choose a destination dialog choose:
- Destination : Flat File Destination
- File name : path to tablename.txt
- Locale : English (United states)
- Code Page 1252 (NASI Latin1)
- Unicode is not checked
- Format : Delimted
- Text qualifier :
- Column names in the first data row is not checked
Next
iv - Copy data from one or more tables or views
Next
v . Source taböle or view : the table to be exported
- Row delimiter : {CR}{LF}
- Column delimiter : Semicolon {;}
Next
vi - Run immediately
Finish
Finish


Then to import script looks like:

BULK INSERT MyTable FROM '$(TFSROOT)\DataBases\MyDatabase\Scripts\Post-Deployment\MyTable.txt' WITH (FIELDTERMINATOR = ';', ROWTERMINATOR = '\n' )


So now we have a Visual Studio 2010 Database project and data. Now suppose that you have a view that references another table in another database. You then create a sub database project within the database solution but when you want to deploy the database you get an error like

Error 2 SQL03006: View: [dbo].[MyView] has an unresolved reference to object [MyOtherDatabase].[dbo].[MyOtherTable]. D:\SOURCE\DataBases\MyDatabase\Schema Objects\Schemas\dbo\Views\MyView.view.sql 12 23 MyDatabase

Looking at the SQL
CREATE VIEW dbo.MyView
AS
SELECT dbo.MyTable.field1, MyOtherDatabase.dbo.MyOtherTable.Field
FROM dbo.MyTable INNER JOIN
MyOtherDatabase.dbo.MyOtherTable ON dbo.MyTable.Id = MyDatabase.dbo.MyTable.FKId

The problem is MyOtherDatabase.dbo.MyOtherTable.Field to a field in a table in another database. This is causing the deployment to fail. The solution to this can be found in an article on how to defining Cross-Database References at http://msdn.microsoft.com/en-us/library/bb386242.aspx
When adding the database reference you must define a database variable.
Fill out the Add Database Reference as follows:
- Database projects in the current solution: YourRefDB
- Database Reference Variables
- Name
- Uncheck Literal
Name $(IO_DB) Value YourRefDB
- Check Update the existing schema object definitions and scripts to use the database variables
- Check Suppress errors caused by unresolved references in the reference project
[OK]
Then you get to an opportunity to edit the modifications to the scripts
This resolves the compilation errors.

The next problem comes when we want to change the Deployment function from generating a script to deploying the script
Going to the solution view and looking at the properties of the database project and changing the Deploy action to "Create a deployment script (.sql) and deploy to database

Message 1 The deployment script was generated, but was not deployed. You can change the deploy action on the Deploy tab of the project properties. D:\SOURCE\DataBases\DBProj\DBName\sql\debug\DbName.sql 0 0 DBName

The solution to this is:
1 Right mouse click on the database project and select properties
2 Goto the Deployment tab
3 Edit Target connection
Server :(local)
Database name :MyDatabase

After this the deployment of the database project works…

I found some other curious effects. If you try and open this database project on a server where you have uninstalled SQL2008 R2 and installed SQL2008 SP3 you will get the following error

D:\SOURCE\DataBases\MyDBProject\DBName\DBName.dbproj : error : Could not load file or assembly 'Microsoft.SqlServer.Management.SqlParser, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.

This problem is caused because the uninstall process removed some assemblies needed by Visual Studio to correctly interpret the database project. There are 2 ways to solve this. Either re-install VS2010 or run the following commands

Open a command line and goto: D:\Software\VS2010\DVD\WCU\SMO
Then execute
msiexec /i SQLSysClrTypes_amd64_enu.msi
msiexec /i SharedManagementObjects_amd64_enu.msi

Next change dir to D:\Software\VS2010\DVD\WCU\DAC and execute:
msiexec /i DACFramework_enu.msi
msiexec /i DACProjectSystemSetup_enu.msi
msiexec /i TSqlLanguageService_enu.msi

Another interesting thing happens when the MS SQL2008 Server has been setup with different paths.
To fix this problem go to the database project and then open the following solution folder path Schema Objects/Database Level Objects/Storage/Files. Here you will find 2 files with names like:

MyDatabase.sqlfile.sql
MyDatabase_log.sqlfile.sql

Within these files you have

ALTER DATABASE [$(DatabaseName)]
ADD FILE (NAME = [MyDatabaseName], FILENAME = 'G:\Data\MyDatabaseName.mdf', FILEGROWTH = 1024 KB) TO FILEGROUP [PRIMARY];

To make the database project run on any setup you need to modify the path using the environmental variables DefaultDataPath and DefaultLogPath. For example:

ALTER DATABASE [$(DatabaseName)]
ADD FILE (NAME = [$(DatabaseName)], FILENAME = '$(DefaultDataPath)$(DatabaseName).mdf', FILEGROWTH = 1024 KB) TO FILEGROUP [PRIMARY];

By the way if you want to use environmental variables within a Query in MS SQL Server Manager you need to:

1. Goto the Query drop down menu
2. Select SQLCMD Mode
3. Cut and paist script....

Then you can use commands like

:setvar somevariable somevalue