SSIS/DTSx Error: The specified DSN contains an architecture mismatch between the Driver and Application

Categories: Database; Tagged with: ; @ July 11th, 2013 11:11


Error message:

     Name: OnError
     Computer: DVSSIS
     Operator: DVSSIS\siu
     Source Name: Populate TR Data
     Message: System.Data.Odbc.OdbcException: ERROR [IM014] [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application
   at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
   at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection connection, OdbcConnectionString constr, OdbcEnvironmentHandle environmentHandle)
   at System.Data.Odbc.OdbcConnectionOpen..ctor(OdbcConnection outerConnection, OdbcConnectionString connectionOptions)
   at System.Data.Odbc.OdbcConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.Odbc.OdbcConnection.Open()
   at Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetManagedConnection(String assemblyQualifiedName, String connStr, Object transaction)
   at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager100.AcquireConnection(Object pTransaction)
   at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction)
   at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper100 wrapper, Object transaction)
     Start Time: 2013-06-27 07:44:51
     End Time: 2013-06-27 07:44:51
End Log



This Error is caused by different version of ‘DTEXEC’, the DTS was developed in a 64-bit PC, that’s means the package using some 64-bit components.  in this case, when the package invoked by a 32-bit ‘DTEXEC’, the above error will be triggered.

The solution is check the system path or any batch file to make sure the 64-bit version path(e.g.  C:\Program Files\Microsoft SQL Server\100\DTS\Binn\)is before 32-bit version(if existing, e.g. C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn)

Modify SSIS package’s ProtectionLevel and password using C#

Categories: Database; Tagged with: ; @ December 9th, 2012 13:24


Modify SSIS DTSx package production level and password.


It’s terrible to open and edit each package, luckily, we got the interface: Microsoft.SqlServer.Dts.Runtime. (How to add the lib)


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

    class DtsUtils
         * Change DTSX package password
        public static void changePassword(String pkgLocation, String oldPassword, DTSProtectionLevel dtsPrdLevel, String newPassword)
            Application app = new Application();

            if (oldPassword != null && oldPassword.Trim() != "")
                app.PackagePassword = oldPassword;

            Package pkg = app.LoadPackage(pkgLocation, null);

            // Modify the password
            pkg.ProtectionLevel = dtsPrdLevel;
            pkg.PackagePassword = newPassword;

            // Save dts pacakge
            app.SaveToXml(pkgLocation, pkg, null);

Get source code from GitHub:

Besides password, there’re many interfaces, like logging, Connections, variables,.


1. SaveToXml:

2.  Building Packages Programmatically

在SSIS/DTSx中使用事务管理 Using Transaction in SSIS DTSx package

Categories: Database; Tagged with: ; @ December 9th, 2012 11:32




需求:  DataFlow转移数据时非0即1, 一旦遇到失败, 回滚到操作前的状态.

解决: 使用Transaction.



表1 Source: tbUser: userID, userName, emai, age(varchar)

表2, Destination: 结构与表1相似, 但age 类型为 int.

(用for loop insert一堆数据:

DECLARE @i int
SET @i = 0
WHILE @i < 1000000
    SET @i = @i + 1
    INSERT INTO guoliangDB.dbo.tbUser (userName, email) values (‘name’ + CAST(@i as varchar(8)), ’email@’ + CAST(@i as varchar(8)))



update tbUser set age = ‘fakeage’ where userID = 980000;






运行结束, 错误信息:

OnError,,9/12/2012 11:17:58 AM,9/12/2012 11:17:58 AM,-1071636471,0x,SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: “Microsoft SQL Server Native Client 10.0”  Hresult: 0x80004005  Description: “Invalid character value for cast specification”.

OnError,,9/12/2012 11:17:58 AM,9/12/2012 11:17:58 AM,-1071607780,0x,There was an error with input column “age” (54) on input “OLE DB Destination Input” (38). The column status returned was: “The value could not be converted because of a potential loss of data.”.

此时Select Destination的数量为: 979998. 剩余的数据不会再被导入.

修改Dataflow transaction属性为: required

在遇到错误之后, Select destination数量, 逐渐减少, 直至rollback到0.

结论: 如需要事务支持, require transaction.

SSIS Designer(Visual Studio 2005) cannot open package: Exception from HRESULT: 0x8007007E (System.Windows.Forms)

Categories: Database; Tagged with: ; @ December 8th, 2012 17:31

I try to apply SQL Server 2005 in my PC(Win 7 64bits), failed, After that, when I try to open SSIS package in BIDS, I got this Error every time:

TITLE: Microsoft Visual Studio

An error prevented the view from loading.
The specified module could not be found. (Exception from HRESULT: 0x8007007E) (System.Windows.Forms)

—————————— BUTTONS: OK ——————————


It’s kind of ridiculous. because I need to finish my DTSx package design,  so I re-install SQL Server sp1. but seems no change.

Then I got this link:

2008 SSIS Designer stops working after installing VS11 Dev Preview

the chosen answer seems not for me, and I got the solution from one of the reply:

I solved the problem by deleting the whole folder “C:\Program Files (x86)\Common Files\microsoft shared\MSDesigners8“, and copying it from a machine that was working. Not the smoothest solution, I know, but it did work!


Get DTSx/SSIS Connections From C#

Categories: DatabaseDevelopment Notes; Tagged with: ; @ December 5th, 2012 23:49

Requirement:  Get all connections from dtsx packages using C#.


Using Microsoft.SqlServer.Dts.Runtime to load the package, and then get all connections.

Step1 Add reference

In case you are totally new to .Net/C#, you may need this screen capture:


In the pop-up window, click “Browse” tab, and select: C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Microsoft.SQLServer.ManagedDTS.dll

About the dll path:

ref 1:

ref 2:

Step 2 Coding

import: using Microsoft.SqlServer.Dts.Runtime


        public void testDts()
            string pkgLocation;
            Package pkg;
            Application app;
            DTSExecResult pkgResults;

            pkgLocation =
              @"C:\Users\x\Documents\Visual Studio 2008\Projects\Integration Services Project1\Integration Services Project1\" +
            app = new Application();
            pkg = app.LoadPackage(pkgLocation, null);

            Connections conns = pkg.Connections;
            foreach (ConnectionManager cm in conns)
                Console.WriteLine("Name = " + cm.Name + ", HostType = " + cm.HostType + "; ConnectionString=" + cm.ConnectionString);



Name = Flat File Connection Manager, HostType = ConnectionManager; ConnectionString=C:\Users\x\Desktop\ssisTest.txt
Name = x_instan, HostType = ConnectionManager; ConnectionString=Data Source=x-PC\x_INSTAN;Integrated Security=SSPI;Connect Timeout=30;

Older Posts

// Proudly powered by Apache, PHP, MySQL, WordPress, Bootstrap, etc,.