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

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

Requirement

Error message:

Log:
     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

 

Solution

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

Requirement:

Modify SSIS DTSx package production level and password.

Solution:

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;

namespace SSISHelper.com.liguoliang.ssis.util
{
    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: https://github.com/DavidGuoliang/SSISHelper/blob/master/SSISHelper/com/liguoliang/ssis/util/DtsUtils.cs

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

Links:

1. SaveToXml: http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.application.savetoxml.aspx

2.  Building Packages Programmatically http://msdn.microsoft.com/en-us/library/ms345167.aspx

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

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

之前有研究过一个container中某一个component失败后的事务管理:http://liguoliang.com/2012/using-transaction-in-ssis/

今天测试某一行数据出错时的事务管理.

 

需求:  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
BEGIN
    SET @i = @i + 1
    INSERT INTO guoliangDB.dbo.tbUser (userName, email) values (‘name’ + CAST(@i as varchar(8)), ’email@’ + CAST(@i as varchar(8)))
END

 

制造一条问题数据:

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

 

设计DTSx:

image

在数据跑到980,000时会因为数据转换而出现错误:

image

运行结束, 错误信息:

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.
——————————
ADDITIONAL INFORMATION:
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!

Gorgeous!

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#.

Solution:

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:

image

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: http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/c333d8e5-a4ca-470e-a2d8-4f67a5e05a10/

ref 2: http://stackoverflow.com/questions/4920591/havent-got-microsoft-sqlserver-manageddts-dll-but

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\" +
              @"Package.dtsx";
            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);
            }
        }

API: http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.package.connections(v=sql.100).aspx

Output:

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,.