n********6
发帖数: 1511 | 1 objective:
load data into sqlserver 2005
data source:
10 tables in one Access, each table share the same structure, each table
represents the different data source, data source is only defined in table
name(not in column)
e.g.
table_SourceA(id, name, dateofbirth, phone)
table_SourceB(id, name, dateofbirth, phone)
table_SourceC(id, name, dateofbirth, phone)
...
table_SourceN(id, name, dateofbirth, phone)
question:
1. easy way to avoid error with data conversion?
2. easy way to add one column with the value of filename?
3. If the number of table increase, does ssis do loop and load all the files
by the naming convention? | i****a
发帖数: 36252 | 2 question:
1. easy way to avoid error with data conversion?
you mean the date fields you either have to fix it before import, or do
error handling during import.
in Access, write a query:
select id, cdate(dateofbirth) from table_SourceA
you'll get error on invalid dates
you can use data converter in SSIS and you can configure it's error handling
, if I remember right
2. easy way to add one column with the value of filename?
yes, in SSIS you can define a variable for the table name and map that as
output for your source_name column
3. If the number of table increase, does ssis do loop and load all the files
by the naming convention?
I can't think of an easy way to dynamically define data source tables inside
SSIS. maybe easier if you call the SSIS package from command line and pass
in the SSIS connection information. then you can code your table name
looping on the caller, then call dtexec.exe .
【在 n********6 的大作中提到】
: objective:
: load data into sqlserver 2005
: data source:
: 10 tables in one Access, each table share the same structure, each table
: represents the different data source, data source is only defined in table
: name(not in column)
: e.g.
: table_SourceA(id, name, dateofbirth, phone)
: table_SourceB(id, name, dateofbirth, phone)
: table_SourceC(id, name, dateofbirth, phone)
| n********6
发帖数: 1511 | 3 Thank you very much.
handling
【在 i****a 的大作中提到】
: question:
: 1. easy way to avoid error with data conversion?
: you mean the date fields you either have to fix it before import, or do
: error handling during import.
: in Access, write a query:
: select id, cdate(dateofbirth) from table_SourceA
: you'll get error on invalid dates
: you can use data converter in SSIS and you can configure it's error handling
: , if I remember right
: 2. easy way to add one column with the value of filename?
| g***l
发帖数: 18555 | 4 还ACCESS，一看就是外行，先弄到SQL SERVER里把DATA TYPE搞好了再说吧，你的
DATABASE就没设计好，用起来就费劲。SSIS可以LOOP，但你的TABLE COLUMN DATATYPE要一致，LOAD进来再去CONVERT也行，这么多数据还用ACCESS，CHEAP BUY A DOZEN了。 | a***y
发帖数: 2803 | 5 oracle 11g行吗?
不过,lz也就 10个table,不是什么大的database.
DATATYPE要一致，LOAD进来再去CONVERT也行，这么多数据还用ACCESS，CHEAP BUY A
DOZEN了。
【在 g***l 的大作中提到】
: 还ACCESS，一看就是外行，先弄到SQL SERVER里把DATA TYPE搞好了再说吧，你的
: DATABASE就没设计好，用起来就费劲。SSIS可以LOOP，但你的TABLE COLUMN DATATYPE要一致，LOAD进来再去CONVERT也行，这么多数据还用ACCESS，CHEAP BUY A DOZEN了。
| g***l
发帖数: 18555 | 6 10个TABLE，每个都100MILLTION RECORDS也不小了，关键是10个TABLE，肯定有其他的
TABLE，RELATIONSHIP不弄好，将来很难用的，哪个数据库都能管理TABLE，ACCESS就是
个PERSONAL临时用用的，不能SHARE，ORACLE当然行，你买的起么，搞数据库要有长远
眼光，将来多少USERS，怎么开发，做什么REPORT，怎么做ORDER ENTRY，走一步看一步
，步步都走得难。
【在 a***y 的大作中提到】
: oracle 11g行吗?
: 不过,lz也就 10个table,不是什么大的database.
:
: DATATYPE要一致，LOAD进来再去CONVERT也行，这么多数据还用ACCESS，CHEAP BUY A
: DOZEN了。
|
|
