每周将不同工作表中的数据提取到SQL表中 [英] Extract data from different sheets on a weekly basis into SQL table

问题描述

我是SSIS的新手，但谷歌搜索我想要达到的目标已经把我带到了这里。让我详细描述一下。 

我有一个excel工作簿，其中包含多个名为Week1，Week2的工作表，依此类推。在第1周，有一些行在本周末更新。在本周开始时，这些行仍为空，但用户实际上会在本周末的
处填满它们。我计划在本周末执行一项工作，该工作实际上会根据某些条件将行中的数据提取到特定的表中。 

表格在SQL Server的数据库中。这在SSIS中是否可以实现？如果是这样，我该如何处理？

解决方案

可实现

这种方法会如下所示

1。你需要一个基于ADO Schema Rowset枚举器的For Each循环

2。创建与Excel工作表的连接（如果Excel文件名不同并且需要在运行时传递，可以通过参数化使其动态化）

3。在内部循环中选择Tables集合，这将使包能够遍历Excel表格。您可以在循环内将变量赋值给索引2，以便每次都在变量内部获取sheetnames。 （@ [User :: SheetName]）

要确保只处理工作表而不处理任何命名范围，只有当SheetName变量包含<时，才需要检查以执行其余任务/ BLOCKQUOTE>

字符。这可以在基于Expression的Precedence Constraint和Expression中使用，看起来像
，如下所示

 InStr（@ SheetName，" 

"）！= 0

如果您只需提取当前一周或某事的数据，您会在表达式中需要一个额外的逻辑来检查sheetname（假设它对应于weekname）

所以你要创建一个新变量来保存当前周值，然后在表达式中使用它，如

 InStr（@ SheetName，" 

Hi,

I am new to SSIS, but googling for what I want to achieve has brought me here. Let me describe in detail. 

I have got an excel workbook with multiple sheets named Week1, Week2 and so on. In Week 1, there are like multiple rows that gets updated at the end of the week. At the start of the week, these rows remain empty, but user will actually fill them up at the end of the week. I am planning to get a job to execute at the end of the week, where the job would actually extract the data from the rows into specific tables based on some condition. 

The tables are in a database in SQL Server. Is this achievable in SSIS ? IF so, how do I go about it ?

解决方案

Its achievable

The approach would look like below

1. You would need a For Each loop based on ADO Schema Rowset enumerator

2. Create a connection to your excel sheet (you can make it dynamic by parameterizing it if Excel file name varies and needs to be passed at runtime)

3. Inside loop select Tables collection which will enable the package to iterate through the sheets of Excel. You can assign variable to index 2 within loop to get sheetnames inside variable each time. (@[User::SheetName])

To make sure you process only sheets and not any named ranges, you would require a check to execute rest of the tasks only when SheetName variable contains

character. This you can use inside Expression based Precedence Constraint and Expression will look like below

InStr(@SheetName,"

") != 0

If you need to only extract the data for current week or something, you would require an additional logic inside the expression to check for the sheetname as well (assuming it will correspond to weekname)

So you would create a new variable to hold current Week value and then use it in the expression like

InStr(@SheetName,"

这篇关于每周将不同工作表中的数据提取到SQL表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！