将分组报表数据导入数据库
我的公司从无法以任何直接格式提供数据的客户接收数据，因此我们必须导入多份呈分组布局的报告，如下所示.我们必须开发内部方法来取消对报告的分组，然后导入数据以获取我们需要的所有数据.目前，我团队的一名成员正在使用 MS Access/VBA 生成所需的详细记录，但我想将其移至基于服务器的自动化流程.我们使用 SQL Server 2008R2 进行存储，我想使用 SSIS 来完成任务.有谁知道我可以生成详细记录并将数据直接导入 SQL Server 的方法吗?
My company receives data from a client that is unable to provide data in any direct format so we have to import several reports that are in a grouped layout like the one below. We have to develop in house methods to ungroup the report and then import the data to get all of the data we need. Currently a member on my team is using MS Access / VBA to generate the needed detail records but I want to move this to a server based and automated process. We are using SQL Server 2008R2 for storage and I would like to use SSIS to accomplish the task. Does anyone know of a way I can generate the detail records and import the data directly into SQL Server?
推荐答案
我会用脚本组件来做.
总数据流:
ExcelSource --> 脚本组件(转换) --> 条件拆分 --> SQL 目标
ExcelSource --> Script Component (Tranformation) --> Conditional Split --> SQL Destination
在脚本组件中:
检查 InputColumns 上的 accountSummary
Check accountSummary on InputColumns
添加 ActivityDate 作为输出列.
Add ActivityDate as output column.
打开脚本:
在您的行处理之外.
添加:
public datetime dte;
内行处理:
if (DateTime.TryParse(Row.ActivitySummary.ToString()))
{dte=DateTime.Parse(Row.ActivitySummary.ToString());}
else
{Row.ActivityDate = dte;}
然后添加条件拆分以删除空活动日期
Then add a Conditional Split to remove null Activity Dates
相关文章