[AccessD] using a dtsx in .Net

...no question at all JC, its just you ...hth :)

William Hindman

----- Original Message ----- 
From: "JWColby" <jwcolby at colbyconsulting.com>
To: "'Access Developers discussion and problem solving'" 
<accessd at databaseadvisors.com>; <dba-sqlserver at databaseadvisors.com>; 
"'Discussion of Hardware and Software issues'" 
<dba-tech at databaseadvisors.com>
Sent: Friday, April 20, 2007 3:43 PM
Subject: [AccessD] using a dtsx in .Net


> Guys,
>
> 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
> explorer.
>
> 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
> big IDIOT MASS.
>
> 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
> www.ColbyConsulting.com
>
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>