Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
btyukin
Starting Member
4 Posts |
 Posted - 2002-05-14 : 09:06:19
|
| I want to create dts package in MS SQL server database. It should get csv-formated file and copy data from it to MS SQL table. I have created dts-object in ColdFusion from saved package and point the source of csv-file. Csv-files are different from each other only in number of columns. The question is how to create dts-package, which can import data from csv-files with different numbers of columns. I have an idea to create VBscript, which would be able to analize connection object of source cvs-file, get columns and create after it table with that columns and corresponding transformation task. But I am not goog at VB. Is there more easy way? or have you any working sample?Help me,pleaseAll the bestEdited by - btyukin on 05/14/2002 09:09:50 |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-05-14 : 09:17:19
|
| Is there a reason why you are not using the DTS Designer in Enterprise Manager?If so, I would suggest you bag the dts idea and use BCP.exe or BULK INSERT . . . for a dynamic situation, it'll be far less complicated.<O> |
 |
|
|
btyukin
Starting Member
4 Posts |
Posted - 2002-05-14 : 10:03:30
|
quote:
If so, I would suggest you bag the dts idea and use BCP.exe or BULK INSERT . . . for a dynamic situation, it'll be far less complicated.<O>
I have to build universal engine for loading data not only from CSV, but from DBF, Excel & etc. In all files can be any number of columns. It's good, that I can consider all columns as VARCHAR.If I will prepare some DTS packages as templates for each file format. In this case I will need to patch DTS package with my script for SQL table creation before execution... |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-05-14 : 10:27:13
|
quote:
It's good, that I can consider all columns as VARCHAR.
No, that's EXTREMELY BAD! If the columns you are importing have numeric, date, or other non-character data, and you import them as varchar, you're wasting your time importing it into SQL Server.Edited by - robvolk on 05/14/2002 10:28:22 |
|
|
|
btyukin
Starting Member
4 Posts |
Posted - 2002-05-14 : 10:44:25
|
quote:
It's good, that I can consider all columns as VARCHAR.
No, that's EXTREMELY BAD! If the columns you are importing have numeric, date, or other non-character data, and you import them as varchar, you're wasting your time importing it into SQL Server.Edited by - robvolk on 05/14/2002 10:28:22[/quote]what your advise? I need import data into MS SQL from csv, excel, dbf sources. I want create dts-packages for each format. I don`t want use bcp because of that |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-05-14 : 11:29:43
|
| My point is this: the SQL Server tables you are importing into should NOT have varchar columns for non-character data, regardless of where the data is imported from. In other words, if your data has dates, make sure the SQL Server table has that column declared as a date and nothing else; if there are numbers, store them as int, numeric, decimal etc. Don't try to design a table that can accept data from multiple sources that don't have the same structure, or might have invalid data. The philosphy behind SQL Server and relational databases is that they store STRUCTURED data of specific TYPES. Creating tables as a "catch-all" completely defeats the purpose of a database.You might want to approach this from the perspective of "I have these tables in SQL Server, and I need to import data from various sources into them" Focus on the TABLES first, not the data sources. If you do that, you should have a specific DTS package for each table, with three variations for CSV, Excel, or dBase/Foxpro format. The file name would be dynamic. I think you can dynamically change the data source in DTS, but it may not be practical. You might have to have a separate DTS package for Excel transfers, CSV, and DBF for EACH table. I know that sounds like a lot of work, but no more so than trying to make them as generic as possible.Once you have this set up, it will be very easy to use; from what you've described, it sounds like a user is uploading a file to imported into a SQL Server table. There are 3 possible formats they can submit, and they'd have to specify the table in which to import the data. Based on the table and format, you can simply choose which DTS package to run. The only truly dynamic part to this is the name of the file, which is very easy to accomodate in DTS. Do a forum search on SQL Team for "dynamic DTS" or "dynamic file name" and you'll find some examples. |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-05-14 : 11:35:24
|
quote:
I have to build universal engine for loading data ...
somewhere along the line, you are going to need to have column names and datatypes for this data, correct? you may be able to build a DTS-Package-Builder, but I bet you are going to end up with create table <tablename> col001 varchar(255), col002 varchar(255)....colN varchar(255)). This data is gonna be very close to useless too you . . .Is there anyway you can handshake with your data provider to get file format before you load? You can dynamically build a bcp format file pretty easily and then your SQL table will have real/usable datatypes and column names.At a certain point, the quest for generic/universal/re-usable processes results in useless code and wasted time. Maybe your situation is different.<O> |
|
|
|
btyukin
Starting Member
4 Posts |
Posted - 2002-05-15 : 02:37:48
|
quote:
In other words, if your data has dates, make sure the SQL Server table has that column declared as a date and nothing else; if there are numbers, store them as int, numeric, decimal etc.
Speaking about my situation, all my fields IS a VARCHAR (or CHAR in some cases). So there is no huge problem with INT, DATA and etc for my task, because created table is temporary.quote:
...but I bet you are going to end up with create table <tablename> col001 varchar(255), col002 varchar(255)....colN varchar(255)). This data is gonna be very close to useless too you...
Yes, there is a problem, but I know a solution - in most cases I will be able to get columns count and names from source file. It can be done through Connection object inside DTS package.All I need is a some help in redefinind/patching/etc scripts that are already stored in my DTS package that I use as template. It would be great to receive some samples in VB or JScript. I know what it is possible using DTS object model to create dinamically sql task (for table creation) and transformation object for datacopying from one source to another.It is important to create table and transformation object on the fly because I can`t say user what number of columns he must use. |
|
|
|
|
|
|
|
|
importing data from different csv-files by one DTS