Is it possible to delete rows in an excel worksheet from SSIS?
是否可以从SSIS中删除Excel工作表中的行?
3 个解决方案
#1
3
From forum
- create a file system task in control flow, deleting the file.
- create a sql task, excel connection type, i used my previous excel connection for this, then used a create table script as my sql statement
- left my previous export object the same, then linked the flow of these in this order described.
在控制流中创建文件系统任务,删除该文件。
创建一个sql任务,excel连接类型,我使用我以前的excel连接,然后使用创建表脚本作为我的sql语句
将我以前的导出对象保持不变,然后按照所描述的顺序链接这些导出对象的流程。
#2
1
An arctile by microsoft claims that you cannot delete an entire row however you can delete a value in a cell. http://support.microsoft.com/kb/257819
微软的arctile声称您无法删除整行,但可以删除单元格中的值。 http://support.microsoft.com/kb/257819
This thread relates to someone with the same problem: http://www.tech-archive.net/Archive/DotNet/microsoft.public.dotnet.framework.adonet/2006-12/msg00245.html
此主题涉及具有相同问题的人:http://www.tech-archive.net/Archive/DotNet/microsoft.public.dotnet.framework.adonet/2006-12/msg00245.html
In short, it seems like the answer is 'no', you cannot delete an entire row using ado.net via SSIS. You may be able to use excel automation or vb.net in excel itself to accomplish this task.
简而言之,似乎答案是'不',你不能通过SSIS使用ado.net删除整行。您可以在excel本身中使用excel自动化或vb.net来完成此任务。
#3
1
I also did it by following the steps suggested by Gulzar but, in case anyone wants the details, this is what I did:
我也是按照Gulzar建议的步骤做到的,但是,如果有人想要细节,这就是我做的:
I originally had a task to read some information from SQL Server and export it to Excel:
我最初有一个任务是从SQL Server读取一些信息并将其导出到Excel:
I then added the suggested tasks, one to delete the destination Excel file and one to recreate it:
然后我添加了建议的任务,一个用于删除目标Excel文件,另一个用于重新创建它:
This is the task to delete the file, it's a "File System Task":
这是删除文件的任务,它是“文件系统任务”:
This is the task to re-create the Excel file, it's an "Execute SQL Task":
这是重新创建Excel文件的任务,它是一个“执行SQL任务”:
The following needs to be set for this "Execute SQL Task":
需要为此“执行SQL任务”设置以下内容:
a. Connection Type is "Excel"
一个。连接类型是“Excel”
b. As suggested, the connection is the same one used by the original Export task
湾如建议的那样,连接与原始导出任务使用的连接相同
c. The SQL statement was copied from the original Export task
C。 SQL语句是从原始导出任务复制的
That's basically it. Run it and it should work. My connection to SQL Server looks like this:
基本上就是这样。运行它,它应该工作。我与SQL Server的连接如下所示:
My Excel connection, which was originally created when the Export task was created, looks like this:
我的Excel连接最初是在创建导出任务时创建的,如下所示:
In the "Data Flow" section, using the Source Assistant, the original "OLE DB Source" has this:
在“数据流”部分,使用源助手,原始“OLE DB源”具有:
And, using the Destination Assistant, the original Excel Destination has this:
并且,使用目标助手,原始Excel目标具有:
This is the SQL Statement that I copied from the original Excel Destination:
这是我从原始Excel目标中复制的SQL语句:
#1
3
From forum
- create a file system task in control flow, deleting the file.
- create a sql task, excel connection type, i used my previous excel connection for this, then used a create table script as my sql statement
- left my previous export object the same, then linked the flow of these in this order described.
在控制流中创建文件系统任务,删除该文件。
创建一个sql任务,excel连接类型,我使用我以前的excel连接,然后使用创建表脚本作为我的sql语句
将我以前的导出对象保持不变,然后按照所描述的顺序链接这些导出对象的流程。
#2
1
An arctile by microsoft claims that you cannot delete an entire row however you can delete a value in a cell. http://support.microsoft.com/kb/257819
微软的arctile声称您无法删除整行,但可以删除单元格中的值。 http://support.microsoft.com/kb/257819
This thread relates to someone with the same problem: http://www.tech-archive.net/Archive/DotNet/microsoft.public.dotnet.framework.adonet/2006-12/msg00245.html
此主题涉及具有相同问题的人:http://www.tech-archive.net/Archive/DotNet/microsoft.public.dotnet.framework.adonet/2006-12/msg00245.html
In short, it seems like the answer is 'no', you cannot delete an entire row using ado.net via SSIS. You may be able to use excel automation or vb.net in excel itself to accomplish this task.
简而言之,似乎答案是'不',你不能通过SSIS使用ado.net删除整行。您可以在excel本身中使用excel自动化或vb.net来完成此任务。
#3
1
I also did it by following the steps suggested by Gulzar but, in case anyone wants the details, this is what I did:
我也是按照Gulzar建议的步骤做到的,但是,如果有人想要细节,这就是我做的:
I originally had a task to read some information from SQL Server and export it to Excel:
我最初有一个任务是从SQL Server读取一些信息并将其导出到Excel:
I then added the suggested tasks, one to delete the destination Excel file and one to recreate it:
然后我添加了建议的任务,一个用于删除目标Excel文件,另一个用于重新创建它:
This is the task to delete the file, it's a "File System Task":
这是删除文件的任务,它是“文件系统任务”:
This is the task to re-create the Excel file, it's an "Execute SQL Task":
这是重新创建Excel文件的任务,它是一个“执行SQL任务”:
The following needs to be set for this "Execute SQL Task":
需要为此“执行SQL任务”设置以下内容:
a. Connection Type is "Excel"
一个。连接类型是“Excel”
b. As suggested, the connection is the same one used by the original Export task
湾如建议的那样,连接与原始导出任务使用的连接相同
c. The SQL statement was copied from the original Export task
C。 SQL语句是从原始导出任务复制的
That's basically it. Run it and it should work. My connection to SQL Server looks like this:
基本上就是这样。运行它,它应该工作。我与SQL Server的连接如下所示:
My Excel connection, which was originally created when the Export task was created, looks like this:
我的Excel连接最初是在创建导出任务时创建的,如下所示:
In the "Data Flow" section, using the Source Assistant, the original "OLE DB Source" has this:
在“数据流”部分,使用源助手,原始“OLE DB源”具有:
And, using the Destination Assistant, the original Excel Destination has this:
并且,使用目标助手,原始Excel目标具有:
This is the SQL Statement that I copied from the original Excel Destination:
这是我从原始Excel目标中复制的SQL语句: