由买买提看人间百态

boards

本页内容为未名空间相应帖子的节选和存档,一周内的贴子最多显示50字,超过一周显示500字 访问原贴
Database版 - 请教ssis
相关主题
help about SQL for ACCESSTable Merge (SQL Server)
做DW的,如何估计/衡量一项任务的工作量?其实有个问题好久没有明白!
SQL multiply all values of a column in table (转载)analyze 在oracle里怎么用?
Access database 求助问个external table field definition的问题
Access 里面两个 column不一样的table 能combine 到一起吗?SQL aggregate multiple columns in ACCESS
Oracle Group and Index questionQuestions about building a database(Maybe ACCESS?)
SQL combine two columns from two different tables no shared (转载)搜索效率问题请教
用SSIS EXPORT 到 EXCEL 2010 有2000个COLUMN,可能吗sql里怎么做循环?
相关话题的讨论汇总
话题: table话题: ssis话题: data话题: name
进入Database版参与讨论
1 (共1页)
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了。

1 (共1页)
进入Database版参与讨论
相关主题
sql里怎么做循环?Access 里面两个 column不一样的table 能combine 到一起吗?
Can I create thousands table in one...Oracle Group and Index question
请教用ACCESS做REPORT(TABLE & GRAPH)SQL combine two columns from two different tables no shared (转载)
Oracle下如何能得到所有table的名字?用SSIS EXPORT 到 EXCEL 2010 有2000个COLUMN,可能吗
help about SQL for ACCESSTable Merge (SQL Server)
做DW的,如何估计/衡量一项任务的工作量?其实有个问题好久没有明白!
SQL multiply all values of a column in table (转载)analyze 在oracle里怎么用?
Access database 求助问个external table field definition的问题
相关话题的讨论汇总
话题: table话题: ssis话题: data话题: name