I had a simple SSIS flat file import going but the empty date fields were not showing up as null but "1899-12-30 00:00:00.000".

Short answer:

• Right click "flat file source" component
• Select "Show Advanced Editor" from the context menu
• On the "Component Properties" tab set "RetainNulls" to true

I knew it was hidden somewhere! This post helped - http://phil-austin.blogspot.com/2007/12/retain-nulls.html - my issue was all the same except for the date value.

We are managing lots of "lookup data" through a big fat Excel spreadsheet. We have a simple tool that reads the sheets and produces insert statements (using ADO.NET).

The problem was that some fields were coming through NULL when I knew they were not.The short answer is "IMEX=1"

Basically the provider looks at the first few rows and guesses the type from the cell values, in my case the first few were numbers followed by text codes. When the provider gets to the text values its in “number” mode so defaults to a NULL value.

You can use the "IMEX" property to make the provider look further and go into an import mode. See the KB article for more info.

http://support.microsoft.com/kb/194124

PK  :-)

 

PS: To connect to an excel file within ADO.NET use the "System.Data.OleDb" provideran a connection string similar to:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source="foo.xls";Persist Security Info=False;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1"

Go see http://elijahmanor.com/webdevdotnet/post/ASPNET-MVC-Cheat-Sheets.aspx for a nice index of ASP.NET cheat sheets...

All,

I uploaded RC1 today: http://minisqlquery.codeplex.com/Release/ProjectReleases.aspx?ReleaseId=33994

Lots of goodies including the new generic schema engine and text templating including access to the data.

One of the regular questions I get is if there is a plug-in that will convert “database A” to “database B”… answer… no – BUT – you can actually write a script connected to database A that writes out the schema for database b. Then you would run the generated SQL against database b. You could for example even create a db layout using Excel (one table per sheet etc), connect to it with Mini SQL Query and write a script that is a “create script” for the destination database. You can also get the data out with the template tool.

 

What’s Missing?

 

• Schema wise the main thing missing is procedures/functions. Not all languages support them but it would be good to have for v1.
• Another schema addition is foreign key information for other databases. This is not provided properly by the ADO.NET schema functions and so I manually provide it for the respective database driver.

 

PK  :-)

 

I have finally got around to mapping through the Primary Key (for all DB’s) and Foreign Key info (for MSSQL and SQL Compact Edition)

MSSQL:

And for SQL Compact Edition…

The tooltips have extra info too.

Go see http://www.pksoftware.net/MiniSqlQuery/Download.aspx the latest download…

PK  :-)