[dba-SQLServer] using a dtsx in .Net

JWColby jwcolby at colbyconsulting.com
Fri Apr 20 14:43:26 CDT 2007

I am looking for a learning experience here.  Using the import wizard in SQL
Server 2005 I created a .dtsx file, and successfully imported the first of
about 60 files using that from right inside of SQL Server's import wizard.
It asks if you want to save at the very end which I did, and which created
the aforementioned .dtsx file.  Unfortunately that wizard does not allow you
to use that file for another file.  Even more unfortunately, the files in
question are fixed width, and thus have no field info in the first line etc.
Thus to use the wizard, I would have to respecify the names and widths of
all 150 fields each and every time.  
Therefore... I am attempting to use the dtsx file in .Net to do the import.
If I "open" that file, Visual studio is selected as the file to do the
opening, and if I do so it opens and shows me a tabbed object.  The first
tab is a control flow, the next is a data flow, event handlers and package
I can actually execute the entire thing from inside of Visual studio and
imports that first file, creates the table, with the field names and field
widths etc.  Unfortunately, for some reason NVarChar was selected as the
default when I created this thing back in SQL Server.  I managed to change
the table inside of SQL Server to just use VarChar.  It was a few days ago
and I don't really remember how.  I do remember that the wizards that allow
you to manipulate the table / field definitions very helpfully try and
change the length from whatever value you currently have back to 50 if you
change the data type from NVarChar to VarChar.  Sometimes I think the world
is just one big IDIOT MASS.  
Be that as it may, (back in Visual Studio) if I click on the data flow tab,
it shows the source as the original file.  First task is to change that to
the name of the second file to be processed.  If I click on the "source
connection flat file" object in that tab down below the main screen, there
is a connection string property which I can change to the next file name.  
The next issue now is that the data conversion object is pulling data out of
an XML file which stubbornly insists that the destination data is NVarChar -
with the correct field lengths.  If I open the wizard, I can indeed edit
this to change the datatype, but it insists on helpfully changing the field
length from the correct values (carefully entered already) back to the
standard 50.  Sometimes I am absolutely CERTAIN that the entire world is one
So here I am, trying to edit the data conversion object for 150 fields to
change from NVarChar to VarChar, where with each field change the very
helpful wizard insists on changing my field length back to 50.  Sigh.
Idiots, all of them.  If I can get this edit done I believe I can use this
thing to import the other 60 or so files.
So am I stuck with doing this all over again?  Is there even one microscopic
particle of grey brain matter anywhere in the Microsoft campus?  Is it just
me and the rest of the world WANTS their carefully entered field lengths
changed back to 50 if they need to change from NvarChar to VarChar (and if
so why)?
John W. Colby
Colby Consulting

More information about the dba-SQLServer mailing list