Sql Server 2005 Import Wizard - keep NULLS

[Database Administrator]

Hi,

I have to import data from a flat file. There are some empty values in the
flat file.
When I import it using SQL 2000 Import Wizard, null values are correctly
inserted in the table.

However, using SQL Server 2005 Import wizard, empty strings (len(colName) is
zero) instead of nulls are being inserted.

How can I achieve the same results (keeping nulls) using the SQL Server 2005
Import Wizard?

[Database Administrator]

On Nov 6, 1:34 am, Mike Caceres Cace...@discussions.microsoft.com> wrote:
> Hi,
>
> I have to import data from a flat file. There are some empty values in the
> flat file.
> When I import it using SQL 2000 Import Wizard, null values are correctly
> inserted in the table.
>
> However, using SQL Server 2005 Import wizard, empty strings (len(colName) is
> zero) instead of nulls are being inserted.
>
> How can I achieve the same results (keeping nulls) using the SQL Server 2005
> Import Wizard?

Hi Mike,

Check the "RetainNulls" property on your File Source connection in
your data flow is set to True.

Good luck!
J

[Database Administrator]

J. thanks for the answer. Unfortunately I do not see that option in the
"Microsoft SQL Server Management Studio - Import and Export Wizard" when
setting the Data source to "Flat File".

Just to clarify, the empty values are delimited by, let's say two
consecutive TABS. The SQL 2000 import wizard will insert null in these cases.
The SQL 2005 import wizard will insert an empty string in these cases.

The reason for using this wizard (as oppossed to Bulk Insert) is because the
table columns could be in different order than the ones in the source text
files, or some columns in the text file needs to be skipped. I know I could
create a bcp format file, but I would like to avoid that route and keep
working with the Import wizard, if that is possible.

Thanks!

"jhofmeyr@googlemail.com" wrote:

> On Nov 6, 1:34 am, Mike Caceres > Cace...@discussions.microsoft.com> wrote:
> > Hi,
> >
> > I have to import data from a flat file. There are some empty values in the
> > flat file.
> > When I import it using SQL 2000 Import Wizard, null values are correctly
> > inserted in the table.
> >
> > However, using SQL Server 2005 Import wizard, empty strings (len(colName) is
> > zero) instead of nulls are being inserted.
> >
> > How can I achieve the same results (keeping nulls) using the SQL Server 2005
> > Import Wizard?
>
> Hi Mike,
>
> Check the "RetainNulls" property on your File Source connection in
> your data flow is set to True.
>
> Good luck!
> J
>
>

[Database Administrator]

On Nov 6, 6:01 pm, Mike Caceres
wrote:
> J. thanks for the answer. Unfortunately I do not see that option in the
> "Microsoft SQL Server Management Studio - Import and Export Wizard" when
> setting the Data source to "Flat File".
>
> Just to clarify, the empty values are delimited by, let's say two
> consecutive TABS. The SQL 2000 import wizard will insert null in these cases.
> The SQL 2005 import wizard will insert an empty string in these cases.
>
> The reason for using this wizard (as oppossed to Bulk Insert) is because the
> table columns could be in different order than the ones in the source text
> files, or some columns in the text file needs to be skipped. I know I could
> create a bcp format file, but I would like to avoid that route and keep
> working with the Import wizard, if that is possible.
>
> Thanks!
>
>
>
> "jhofm...@googlemail.com" wrote:
> > On Nov 6, 1:34 am, Mike Caceres > > Cace...@discussions.microsoft.com> wrote:
> > > Hi,
>
> > > I have to import data from a flat file. There are some empty values in the
> > > flat file.
> > > When I import it using SQL 2000 Import Wizard, null values are correctly
> > > inserted in the table.
>
> > > However, using SQL Server 2005 Import wizard, empty strings (len(colName) is
> > > zero) instead of nulls are being inserted.
>
> > > How can I achieve the same results (keeping nulls) using the SQL Server 2005
> > > Import Wizard?
>
> > Hi Mike,
>
> > Check the "RetainNulls" property on your File Source connection in
> > your data flow is set to True.
>
> > Good luck!
> > J- Hide quoted text -
>
> - Show quoted text -

Hi Mike,

I haven't really used the import wizard much, but I know that behind
the scenes it creates a SSIS package which is executed and discarded.
Presumably the wizard is hiding some of the functionality of the
package to make it easier to use, and in your case it is preventing
you from correctly setting the connection.

If I remember correctly, at the end of the wizard it asks if you want
to save the package instead of simply executing it immediately. I
would suggest you do this and then manually edit the package using
BIDS to set the file connection properties manually.

A little more leg-work, but well worth it as a basic introduction to
SSIS.
Good luck!
J

[Database Administrator]

Thank you J.

I tested this option (saving the package in the server, modifying it in BIDS
and executing the modified package) and it works well: it keeps the nulls.

I also found a simple workaround: keep using the old dtswiz.exe which also
keeps the nulls from the original text file.

Mike

"jhofmeyr@googlemail.com" wrote:

> On Nov 6, 6:01 pm, Mike Caceres
> wrote:
> > J. thanks for the answer. Unfortunately I do not see that option in the
> > "Microsoft SQL Server Management Studio - Import and Export Wizard" when
> > setting the Data source to "Flat File".
> >
> > Just to clarify, the empty values are delimited by, let's say two
> > consecutive TABS. The SQL 2000 import wizard will insert null in these cases.
> > The SQL 2005 import wizard will insert an empty string in these cases.
> >
> > The reason for using this wizard (as oppossed to Bulk Insert) is because the
> > table columns could be in different order than the ones in the source text
> > files, or some columns in the text file needs to be skipped. I know I could
> > create a bcp format file, but I would like to avoid that route and keep
> > working with the Import wizard, if that is possible.
> >
> > Thanks!
> >
> >
> >
> > "jhofm...@googlemail.com" wrote:
> > > On Nov 6, 1:34 am, Mike Caceres > > > Cace...@discussions.microsoft.com> wrote:
> > > > Hi,
> >
> > > > I have to import data from a flat file. There are some empty values in the
> > > > flat file.
> > > > When I import it using SQL 2000 Import Wizard, null values are correctly
> > > > inserted in the table.
> >
> > > > However, using SQL Server 2005 Import wizard, empty strings (len(colName) is
> > > > zero) instead of nulls are being inserted.
> >
> > > > How can I achieve the same results (keeping nulls) using the SQL Server 2005
> > > > Import Wizard?
> >
> > > Hi Mike,
> >
> > > Check the "RetainNulls" property on your File Source connection in
> > > your data flow is set to True.
> >
> > > Good luck!
> > > J- Hide quoted text -
> >
> > - Show quoted text -
>
> Hi Mike,
>
> I haven't really used the import wizard much, but I know that behind
> the scenes it creates a SSIS package which is executed and discarded.
> Presumably the wizard is hiding some of the functionality of the
> package to make it easier to use, and in your case it is preventing
> you from correctly setting the connection.
>
> If I remember correctly, at the end of the wizard it asks if you want
> to save the package instead of simply executing it immediately. I
> would suggest you do this and then manually edit the package using
> BIDS to set the file connection properties manually.
>
> A little more leg-work, but well worth it as a basic introduction to
> SSIS.
> Good luck!
> J
>
>