> The short of it:
> If you needed to import a CSV file of a certain structure on a regular
> basis(say 32 csv files, each to one a table in 32 databases), what
> would be your first instinct on how to set this up so as to do it
> reliably and minimize overhead?  There are currently no constraints on
> the destination table.  Assume the user or some configuration specifies
> the database name, server name, and filename+fullpath.   The server is
> SQL Express, but there is the possibility that the databases may be
> moved to Oracle in the future, so any solution that would be easily
> ported would be ideal, but is not required.
>
> Mainly I want to know conceptually in terms of tools or APIs how this
> would be done, so I know what I should focus on learning.  e.g. I
> create a DTS package to import one file, use dynamic properties to set
> database+server+filename from global properties, and load the DTS via
> VB code and pass the database and filenames to the package's global
> variables.  I would prefer as little dependance on tools beyond what is
> available in VB.NET and comes standard on the db server.
>
> I'd also like to know your opinion's in regards to exporting to excel
> files, and also exporting to CSV files.
>
> The long of it:
> I have taken over some disorganized legacy tools that basically take
> sets of CSV files, import them into 32 databases(identical schema,
> different data in each), process the data, and then export to CSV
> files.  In the processing however, there are many steps of exporting
> the data back out to excel files, then importing again, etc.  and
> everything from DTS packages to Crystal Reports are used.  I would like
> to eliminate dependancy first on the crystal reports, and then possibly
> elimnate dependancy on the DTS packages as well.
>
> Nothing very complex occurs in the Crystal Reports, and it appears that
> my predecessor was using it to build queries as a crutch for his lack
> of knowledge with SQL and set theory.  The only advantages as I see it
> is that the Crystal Reports can be used for the 32 different databases
> simply by specifying new connection information, and Crystal reports
> has a built in function to export to excel.
>
> The DTS packages mostly do a straight import of CSV files to existing
> tables, and again, the main advantage is reusing the same DTS package
> for 32 databases and specifying parameters.
>
> My strength is in C#.net, but here at work I only have the VB.net dev
> environment, and existing tools are written in VB.  And I have never
> done database applications development, but I have done applications
> development, and managed databases a good bit in the last year.  So all
> the different acronyms floating around are overwhelming, I'm not sure
> what I should focus on learning.
>
> My goal is to narrow down these tools into a single package, try to
> eliminate dependance on DTS and Crystal Reports, eliminate the
> intermediate file creations so that all processing is done in the
> software or database, and only import at the beginning and export at
> the end of the process.  Still, there are alot of files imported and
> exported, so I want to find out what is the most common technique of
> doing importing and exporting of CSV and Excel files.
>
> DTS packages aren't completely out of the question, it would just seem
> more natural to not rely on them if there is a commonly used progmatic
> alternative.

> The short of it:
> If you needed to import a CSV file of a certain structure on a regular
> basis(say 32 csv files, each to one a table in 32 databases), what
> would be your first instinct on how to set this up so as to do it
> reliably and minimize overhead?  There are currently no constraints on
> the destination table.  Assume the user or some configuration specifies
> the database name, server name, and filename+fullpath.   The server is
> SQL Express, but there is the possibility that the databases may be
> moved to Oracle in the future, so any solution that would be easily
> ported would be ideal, but is not required.
>
> Mainly I want to know conceptually in terms of tools or APIs how this
> would be done, so I know what I should focus on learning.  e.g. I
> create a DTS package to import one file, use dynamic properties to set
> database+server+filename from global properties, and load the DTS via
> VB code and pass the database and filenames to the package's global
> variables.  I would prefer as little dependance on tools beyond what is
> available in VB.NET and comes standard on the db server.
>
> I'd also like to know your opinion's in regards to exporting to excel
> files, and also exporting to CSV files.
>
> The long of it:
> I have taken over some disorganized legacy tools that basically take
> sets of CSV files, import them into 32 databases(identical schema,
> different data in each), process the data, and then export to CSV
> files.  In the processing however, there are many steps of exporting
> the data back out to excel files, then importing again, etc.  and
> everything from DTS packages to Crystal Reports are used.  I would like
> to eliminate dependancy first on the crystal reports, and then possibly
> elimnate dependancy on the DTS packages as well.
>
> Nothing very complex occurs in the Crystal Reports, and it appears that
> my predecessor was using it to build queries as a crutch for his lack
> of knowledge with SQL and set theory.  The only advantages as I see it
> is that the Crystal Reports can be used for the 32 different databases
> simply by specifying new connection information, and Crystal reports
> has a built in function to export to excel.
>
> The DTS packages mostly do a straight import of CSV files to existing
> tables, and again, the main advantage is reusing the same DTS package
> for 32 databases and specifying parameters.
>
> My strength is in C#.net, but here at work I only have the VB.net dev
> environment, and existing tools are written in VB.  And I have never
> done database applications development, but I have done applications
> development, and managed databases a good bit in the last year.  So all
> the different acronyms floating around are overwhelming, I'm not sure
> what I should focus on learning.
>
> My goal is to narrow down these tools into a single package, try to
> eliminate dependance on DTS and Crystal Reports, eliminate the
> intermediate file creations so that all processing is done in the
> software or database, and only import at the beginning and export at
> the end of the process.  Still, there are alot of files imported and
> exported, so I want to find out what is the most common technique of
> doing importing and exporting of CSV and Excel files.
>
> DTS packages aren't completely out of the question, it would just seem
> more natural to not rely on them if there is a commonly used progmatic
> alternative.