Ralf Eisenreich

When Excel data is not coming into SSIS right, then do the following:

1. use option “IMEX=1” in ConnectionString (Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\..\MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1")
2. modify registry setting “TypeGuessRows=8” to a much higher value
3. use a sql statement to retrieve data (e.g. “SELECT * FROM [sheet1$A5:M2000]“)

NOTE: The setting IMEX=1 tells the Excel driver to use Import mode. This mode reads the registry setting “ImportMixedTypes=Text” which forces mixed data to be converted to text. To achieve a more reliable column type recognition, the registry setting “TypeGuessRows=8” should be increased. By default the ISAM driver analyzes  the first eight rows and determines the column datatypes from this sampling. If the first analysed rows contain only numeric values, then setting IMEX=1 will not convert this column datatype to Text – it will remain numeric.

ATTENTION: The IMEX setting has some other modes:

• 0 is Export mode
• 1 is Import mode
• 2 is Linked mode (full update capabilities)

Registry Setting Location:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\X.X\Engines\Excel


supplement
If you experience following error the above described solution could fix it.
1. An OLE DB error has occurred. Error code: 0x80040E21.
2. Failed to retrieve long data for column “XYZ”.
3. There was an error with output column “XYZ” (55488) on output “Excel Source Output” (109). The column status returned was: “DBSTATUS_UNAVAILABLE”.
4. The “output column “XYZ” (55488)” failed because error code 0xC0209071 occurred, and the error row disposition on “output column “XYZ” (55488)” specifies failure on error. An error occurred on the specified object of the specified component.
5. [DTS.Pipeline] Error: The PrimeOutput method on component “Info Source” (101) returned error code 0xC0209029. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
6. [DTS.Pipeline] Error: Thread “SourceThread0″ has exited with error code 0xC0047038.

Here is an example using dynamic Pivot:

The Pivot columns (country short names) are dynamically generated from a query.
Single Quotations within Single Quotations can be realized by using 2 singe Quotations at once.

DECLARE @wave_id int
SET @wave_id = 2

– Populate table variable with list of MPC_SHORT_NAMES
SET NOCOUNT ON
DECLARE @T AS TABLE(y nvarchar(10) NOT NULL PRIMARY KEY)
INSERT INTO @T SELECT DISTINCT MPC_NAME_SHORT_EN_US FROM VL8C_DWH.VL8RS_DIM_MPCS WHERE WAVE_ID = @wave_id

– Construct the column list for the IN clause
– e.g., [D],[E],[F]
DECLARE @cols AS nvarchar(MAX), @y AS nvarchar(10)
SET @y = (SELECT MIN(y) FROM @T)
SET @cols = N”
WHILE @y IS NOT NULL
BEGIN
SET @cols = @cols + N’,[' + @y + N']‘
SET @y = (SELECT MIN(y) FROM @T WHERE y > @y)
END
SET @cols = SUBSTRING(@cols, 2, LEN(@cols))
–SELECT @cols

– Construct the full T-SQL statement and execute it dynamically.
DECLARE @sql AS nvarchar(MAX)
SET @sql = N’
SELECT
*
FROM
(
SELECT
KEY_TYPES.KEY_TYPE_NAME,
KEY_TYPES.KT_SORT_ORDER,
DATA.MPC_NAME_SHORT_EN_US,
DATA.K29*100 AS K29
FROM
(
SELECT
KC.KEY_TYPE_CLUSTER,
KC.KT_NAME_EU AS KEY_TYPE_NAME,
KC.KT_SORT_ORDER
FROM
VL8C_DWH.VL8RS_DIM_KEY_TYPE_CLUSTERS KC
WHERE
WAVE_ID = ‘ + CAST(@wave_id AS nvarchar) + N’
) KEY_TYPES

LEFT JOIN
(
SELECT
M.MPC_NAME_SHORT_EN_US,
KC.KEY_TYPE_CLUSTER,
FM.K29_CNP_INDEX_TOTAL_BUSINESS AS K29
FROM

VL8C_DWH.VL8RS_FACTS_MAIN FM

INNER JOIN
VL8C_DWH.VL8RS_DIM_KEY_TYPES K
ON FM.KEY_TYPE_ID = K.KEY_TYPE_ID

INNER JOIN
VL8C_DWH.VL8RS_DIM_KEY_TYPE_CLUSTERS KC
ON K.KEY_TYPE_CLUSTER_ID = KC.KEY_TYPE_CLUSTER_ID

INNER JOIN
VL8C_DWH.VL8RS_DIM_WAVES W
ON FM.WAVE_ID = W.WAVE_ID

INNER JOIN
VL8C_DWH.VL8RS_DIM_COMPETITORS C
ON FM.COMPETITOR_ID = C.COMPETITOR_ID

INNER JOIN
VL8C_DWH.VL8RS_DIM_MPCS M
ON FM.MPC_ID = M.MPC_ID

WHERE
W.WAVE_ID = ‘ + CAST(@wave_id AS nvarchar) + N’
AND C.COMPETITOR_NAME = ”MB” AND
M.EUX_MEMBER = 1 AND
(K.REPORT_RELEVANCY = ”B” OR K.REPORT_RELEVANCY = ”E”)
) DATA
ON KEY_TYPES.KEY_TYPE_CLUSTER = DATA.KEY_TYPE_CLUSTER

WHERE
DATA.K29 IS NOT NULL AND
DATA.MPC_NAME_SHORT_EN_US <> ”D”
) p
pivot
(
sum(K29)
for MPC_NAME_SHORT_EN_US
in (‘ + @cols + N’)
) as p’
PRINT @sql — for debugging
EXEC sp_executesql @sql

“Meine Leude” ist eine kostenlose Internetplattform zur Bildung von sozialen Netzwerken im deutschsprachigen Raum. Sie wurde im Oktober 2006 von Stefan Maischner entwickelt und liegt mittlerweile in der beta5 vor.

Auf jeden Fall ist MeineLeu.de ein absoluter Surftipp!

Ein sehr guter PHP Editor inklusive Debugging-, Deployment- und (Smarty)-Template-Unterstützung heißt VS.PHP von Jcx.Software.

Außerdem gibt es 4 verschiedene Versionen:

• VS.Php Standalone Edition
  VS.Php for Visual Studio 2005
  VS.Php for Visual Studio .Net 2003
  VS.Php for Visual Studio .Net

Besonders die Integration in Visual Studio ist sehr interessant.

Das sagt Jcx.Software dazu:

If you are a .Net developer you may wonder why VS.Php? Php is one of the most popular languages for developing web applications. In fact, the Php community has the largest pool of rich open source applications, frameworks and resources to make your development life easier. VS.Php lets those who enjoy using Visual Studio use their favorite IDE for Php development. VS.Php also provides many unique features by leveraging the Visual Studio IDE like Php/Javascript debugging.

[Quelle: Jcx.Software]

Über die Sicherheit der Daten innerhalb einer Webanwendung sollte man sich stets Gedanken machen. übergibt man POST-Variablen ungeprüft der Anwendung kann die Sicherheit bereits gefährdet sein.

Angreifer können unter Umständen mittels SQL-Injektion Anfragen verändern oder neue erstellen.

Meine Schutzmaßnahme in einer PHP-Anwendung mit MySQL besteht in einer kleinen Hilfsfunktion, die alle Variablen, die in einer Anwendung verarbeitet werden für den SQL-Syntax valide terminiert:


// sanitize strings for db queries
function sanitize_sql($value) {
if (get_magic_quotes_gpc()) {
$value = stripslashes($value);
}
return mysql_real_escape_string($value);
}


In jeder Datenbankanfrage, der Variablen übergeben werden, bereinigt diese Funktion eventuellen Schadcode mittels Terminierung:


// get userID
function check_user($username, $userpass) {
$sql="SELECT userid
FROM mars_users
WHERE username='".$this->sanitize_sql($username)."' AND
userpass='".$this->sanitize_sql(md5($userpass))."' LIMIT 1";
$result=mysql_query($sql) or $this->errorLog($this->page, mysql_error());
if (mysql_num_rows($result)==1):
$resultarray=mysql_fetch_assoc($result);
return $resultarray['userid'];
else:
return false;
endif;
}


In PHP selbst kann man auch Variablen mittels Regular Expressions prüfen. Eine einfache Funktion zur Überprüfung eines Strings könnte so aussehen:


// sanitize string
function sanitize_string($value) {
$value=preg_replace("/[^\w\040\-_.!?]/","",$value);
$value=trim($value);
return $value;
}


Dazu bietet sich noch die Validierung von Formulareingaben mittels JavaScript an, wie im Beitrag vorher erwähnt.

Unter http://regexlib.com findet man eine sehr nette Sammlung an Regular Expressions.

Möchte man beispielsweise ein Eingabefeld eines Formulars mit Regular Expressions validieren kann das mit JavaScript so aussehen:


<script type="text/javascript"><!--
function allow_alpha(obj) {
if (/[^a-z]/i.test(obj.value))
obj.value=obj.value.replace(/[^a-z\040\-_.!?]/gi,'');
obj.value+='';
obj.focus();
return;
}
function allow_numeric(obj) {
if (/[^0-9]/i.test(obj.value))
obj.value=obj.value.replace(/[^0-9\040\-]/g,'');
obj.value+='';
obj.focus();
return;
}
function allow_alpha_numeric(obj) {
if (/[^\w]/i.test(obj.value))
obj.value=obj.value.replace(/[^\w\040\-_.!?]/gi,'');
obj.value+='';
obj.focus();
return;
}
// --></script>
<input onkeyup="allow_alpha(this);" size="20" type="text" />alpha
<input onkeyup="allow_numeric(this);" size="20" type="text" />numeric
<input onkeyup="allow_alpha_numeric(this);" size="20" type="text" />alpha numeric


Ein beeindruckendes und leistungsstarkes Framework ist das PRADO PHP Framework. Es ist besonders für die Entwicklung großer PHP-Projekte geeignet und besitzt ein ereignisgesteuertes sowie komponentenbasiertes Konzept.

Hier noch ein paar Infos:

* Open Source
* Object-oriented and highly reusable code
* Event-driven programming
* Separation of presentation and logic
* Configurable and pluggable modular architecture
* Feature-rich Web components: HTML input controls, validators, datagrid, wizard…
* Built-in support of internationalization (I18N) and localization (L10N)
* Seamless Ajax support
* Customizable and localizable error/exception handling
* Multiway message logging with filters
* Generic caching modules and selective output caching
* Extensible authentication and authorization framework
* Security measures: cross-site script (XSS) prevention, cookie protection…
* XHTML compliance
* Rich documentation and strong userbase

Quelle: [PRADOSOFT]

Neulich erst entdeckt:
Es gibt einige PHP-Patterns-Seiten, die gute Informationen bereitstellen.

• PatternsForPHP.com
• IBM Developers (PHP)

Also unbedingt in die Bookmarks aufnehmen!