SSIS/DTSx Error: The specified DSN contains an architecture mismatch between the Driver and Application
Categories: Database; Tagged with: DTSX • ETL • SSIS; @ July 11th, 2013 11:11Requirement
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: C# • DTSX • ETL • SSIS; @ December 9th, 2012 13:24Requirement:
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: DTSX • ETL • SSIS • Transaction; @ 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:
在数据跑到980,000时会因为数据转换而出现错误:
运行结束, 错误信息:
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: BIDS • DTSX • ETL • SQL Server • SSIS • Visual Studio; @ December 8th, 2012 17:31I 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: Database • Development Notes; Tagged with: C# • DTS • DTSX • ETL • SSIS; @ December 5th, 2012 23:49Requirement: 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:
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 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;