Fix text-formatted numbers

Beside the suggestion from the following Microsoft page, re-assigning the value with Macro will also resolve.

Selection.Value = Selection.Value

http://office.microsoft.com/en-us/excel-help/fix-text-formatted-numbers-by-applying-a-number-format-HA102749043.aspx

Importing data from Excel is not easy. If the formatting of a column changed (the first 100 cells is number and the second 100 cells is text), the SSIS may convert the second 100 cells to NULL values. Cleaning the formats must be done before the import.

Advertisements

notes on character encoding conversion between SQL Server and AS400

Character encoding is a difficult topic because different companies have developed different terminologies and different standard.  For the Microsoft products, the default character encoding is Windows-1252.  It is being used in the SQL Servers.  For IBM servers, it is EBCDIC encoding schema.  In some AS400 system, both CCSID 37 and CCSID 65535 are being used.  The CCSID 37 seems to be mostly used.

The IBM iSeries OLE DB provider translates the character encoding when data are being sent from SQL Server to the AS400.  In most cases, the translation of Windows 1252 to CCSID 37 works.  But, there are 26 Windows 1252 characters cannot be translated, see the table below.  The CCSID 37 does not have the matching characters.  So, the program (like SSIS package) has to ignore the error and let the iSeries driver change the unsupported characters to question mark “?”.  Or, the program has to manually convert them, for example changing the character Ž to the character Z.

 

Windows 1252

character

code

128

130

ƒ

131

132

133

134

135

ˆ

136

137

Š

138

139

Œ

140

Ž

142

145

146

147

148

149

150

151

˜

152

153

š

154

155

œ

156

ž

158

Does SSIS OLE DB Command Transformation supports multiple query statements?

SSIS OLE DB Command Transformation does support multiple query statements, but it does not fully support the query syntax.

For example, it allows multiple DML statements like the followings.

UPDATE dbo.table_abc SET x = ? WHERE y = ?;
INSERT INTO dbo.table_cbs (I) VALUES (?);

But, it return error the following statements.

DECLARE @x INT;

SET @x = ?;

INSERT INTO dbo.table_abc (x) VALUES (@x);
INSERT INTO dbo.table_cbs (I) VALUES (@x);

The error is:

Source: "Microsoft SQL Native Client"
Hresult: 0x80004005
Description: "Syntax error, permission violation, or other nonspecific error".

I could not find the documentation regarding this problem. I guess the get around solution is using the OleDb.OleDbCommand class from ADO.Net, or using stored procedures. It correctly process the multiple statements.

SQL 2005: SSIS Package List

-- sql 2005: package list
SELECT name
     , description
     , CASE
           WHEN packagetype = 0 THEN 'default client'
           WHEN packagetype = 1 THEN 'SQL Server Import and Export Wizard'
           WHEN packagetype = 2 THEN 'DTS Designer in SQL Server 2000'
           WHEN packagetype = 3 THEN 'SQL Server Replication'
           WHEN packagetype = 5 THEN 'SSIS Designer'
           WHEN packagetype = 6 THEN 'Maintenance Plan Designer'
           ELSE 'Unknown'
       END packagetype
FROM msdb.dbo.sysdtspackages90

dtutil notes

REM copy package to server
dtutil /file my_package.dtsx /copy sql;”\the_folder\my_package” /dests my_server

REM copy and encrypt package to server
dtutil /file my_package.dtsx /encrypt sql;”\the_folder\my_package”;5 /dests my_server

REM copy encrypted pacage to file system
dtutil /sources my_server /sql “\my_package” /encrypt file;”c:\tmp\my_package”;4

REM delete page
dtutil /sql “\the_folder\my_package” /sources my_server /DEL

REM rename folders
dtutil /SourceServer my_server /FRename SQL;”\;folder_a;folder_b”

REM create folder
REM The “/” means root folder.
dtutil.exe /SourceServer mySqlServer /FCreate SQL;/;NewFolder

REM list packages including subfolders
dtutil /fdirectory SQL;S /sources my_server

REM exists
dtutil /SQL \the_folder\my_package /Exists /sources my_server

reference: http://www.eggheadcafe.com/aspnet_answers/SQLServerdts/May2006/post26745426.asp
http://www.databasejournal.com/features/mssql/print.php/3600201
http://msdn.microsoft.com/en-us/library/ms162820.aspx