Hi, When I import an Excel file using the DTS Wizard all
text columns are automatically set to the 'nvarchar'
datatype, is there are any way to change that to 'varchar'
without going through every column(have the DTS Wizard set
all text columns to 'varchar', is what I want)?
Thanks!

In article <0be701c3c3ed$ef481550$a401280a (AT) phx (DOT) gbl>, mike <mb (AT) mbltd (DOT) com>
writes
There is no way to override this default behaviour of the Wizard. You
could just create the table beforehand. If you don't know what to
create, try building the package by hand. You will click the New button
which pops up a dialog with the suggested CREATE TABLE statement. You
can do some a bit of copy and paste, find and replace, on this code
which should be a quicker way of fixing the types.


--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

Thanks for the reply Darren, this will not work for me
since the excel files will have different columns and data
all the time. I've tried creating DTS packages
dynamically using COM but was not very successful, just
could not get it to work. I am now looking at distibuting
SQL-NS dll's and let the user import with the DTS wizard.
I know this is far from a good solution but I'm stumped.
If you have any ideas or some good examples for creating
dynamic DTS packages I would be very greatful. Thanks!

P.S. nvarchar to varchar is an implicit conversion right?
If my SQL stmnts are for varchar will they still work?

In article <040901c3c422$fad9b650$a301280a (AT) phx (DOT) gbl>, mike <mb (AT) mbltd (DOT) com>
writes
To get a start try creating a package as normal and then use the Save As
Visual Basic option to generate some sample code. This should give you a
nice working package. If you wanted to take this further you would need
to write some code to determine the structure of the input file and then
generate the transformations and destination CREATE TABLE statement.
Since this is Excel I assume you can query the Jet provider for the
required meta-data, but it is not something I have ever tried.

rights of this component, but I assume it is the same as SQL-NS which is
not free. Have a look in Books Online and also the redist.txt file found
on the root of the install media. Simplest option would be to licence as
normal and install EM, but only use what you require, either via your
own SQL-NS code wrapper or DTSWiz.




--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

I have succesfully tested distributing/registering the
dll's required to run dtswiz.exe /i (import only) so will
probably go with this solution(just wish DTSWiz would set
excel text columns to varchar rather than nvarchar, but I
could probably just Alter Column datatypes after import if
I really need to). I don't think it's worth the coding
effort to create dynamic dts packages since i can't
reuse/rarely reuse them anyway(excel import files will
always be different), would you agree with this
logic/would you use it? Do you think it's a bad idea to
expose the dts wizard to the user?
Thanks for the input Darren!

In article <008c01c3c58e$d8ab51c0$a401280a (AT) phx (DOT) gbl>, mike <mb (AT) mbltd (DOT) com>
writes
I agree, building your own wizard is hard work, and if the effort is not
justified I see no problem with letting users loose with the wizard. It
is easy enough to use and at the end of the day you can control their
permissions in a the Sql Server level so they can only import into the
Db you allow etc.


--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org