Database

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

SQL Server: Query Result Order by ‘IN’ Conditions/Filter 按照IN条件排序

Categories: Database; Tagged with: SQL • SQL Server; @ May 2nd, 2013 12:12

Requirement:

Select some data using ‘IN’ clause, the result should keep the same order as the in conditions.

Solution: Use CharIndex in Order by


SELECT SECCODE, SECSNAME FROM SECURITY 
WHERE SECCODE IN
    ('1U68',
    '1F77')
ORDER BY 
CharIndex(LTRIM(RTRIM([SECCODE])), '1U68, 1F77')

In MySQL, you can use “Order By Field()
http://www.electrictoolbox.com/mysql-order-specific-field-values/

SSIS: Trim the Join Fields for Merge Join

Categories: Database; Tagged with: ETL • SQL • SQL Server • SSIS; @ March 13th, 2013 19:37

Kindly remind you:

Do trim the join fields for your ‘MergeJoin’:

Otherwise it may take hours to debug!!

SELECT LTRIM(RTRIM(seccode)) AS seccode….

TSQL: Converting/Casting String between Unicode and Non-Unicode

Categories: Database; Tagged with: SQL • TSQL; @ January 16th, 2013 12:04

I got an Error in a SSIS package:

cannot convert between unicode and non-unicode string data types.

We can use one component called ‘DataConversion’, but I think directly convert the type in SQL will be better.

SQL:

DECLARE @str_unicode NVARCHAR(32);
SET @str_unicode = NCHAR(9734);
SELECT @str_unicode

— Using CAST
SELECT CAST(@str_unicode AS VARCHAR(32))

— Using Convert
SELECT CONVERT(VARCHAR(32), @str_unicode)

Result:

TSQL: Get the length of String including/excluding trailing spaces

Categories: Database; Tagged with: SQL • SQL Server • TSQL; @ January 16th, 2013 11:53

LEN (Transact-SQL)
Returns the number of characters of the specified string expression, excluding trailing blanks.
Note: To return the number of bytes used to represent an expression, use the DATALENGTH function.

http://msdn.microsoft.com/en-us/library/ms190329(SQL.90).aspx

DATALENGTH (Transact-SQL)

Returns the number of bytes used to represent any expression.

SQLs:

DECLARE @STR VARCHAR(32);
SET @STR = ’12 ‘
SELECT LEN(@STR) — 2
SELECT DATALENGTH(@STR); –6

Newer Posts <-> Older Posts