计划的 SSIS 包创建以日期作为文件名的 Excel 文件

发布于 2024-08-23 20:23:39 字数 406 浏览 3 评论 0原文

我需要一些关于做我想做的事情的最佳方式的反馈,请允许我定位。

我有一个巨大的数据库,不断接收新的输入。现在我需要一个 Excel 文件来显示过去一天的所有输入列。所以我认为最好的方法是使用 SSIS 包。我从来没有真正使用过 SSIS,所以我不太确定它的可能性有多大。因此,目前我只能创建一个包含我的所有输入的 SSIS 包。但我每天都需要一个新文件,而且文件名中包含数据,因为旧文件不会被删除等。所以我需要能够创建一个文件名中包含日期的 .xls 文件。有没有简单的方法可以做到这一点?

我还想知道是否可以自动修改这个excel文件的布局,特别是列宽,因为它们都是默认宽度,这对客户来说不是很有用。

我听说也可以使用 Visual Studio 创建 SSIS 包,这会有所不同还是可能性相同(请记住,我是 ac# 程序员)?

多谢

I need some feedback on what is the best way to do what I want to do, allow me to situate.

I have a huge database which receives new inputs constantly. Now I need to have an excel file which shows all the columns of inputs of the past day. So I thought the best way to do this is to use SSIS packages. I never really used SSIS that much so I'm not very sure how wide the possibilities are. So at the moment I'm only capable of creating an SSIS package of ALL my inputs. But I need a new file every day, but also where the filename has the data in it, because the old files will not be deleted etc. So I need to be able to create an .xls file with the date in the filename. Is there an easy way to do this?

I was also wondering if it's possible to automaticly modify the layout for this excel file, especially the column widths, since they are all the default width, which is not very usefull for clients.

I heard it's also possible to create SSIS packages with visual studio, would this make a difference or are the possibilities the same (Keep in mind that I'm a c# programmer)?

Thanks a lot

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

深居我梦 2024-08-30 20:23:39

可以通过 Visual Studio 创建 SSIS 包。在 Visual Studio 中单击新项目时,选择“商业智能项目”->“集成服务项目”。

尽管与您的情况(即 Excel 文件输出)不同,但在我的情况下,对于平面 .TXT 文件输出,我将日期作为平面文件名的一部分。为此,在我的平面文件连接管理器的“属性”-“表达式”->“连接字符串(属性)”中,我输入了以下表达式。类似的方法也适用于您的情况,特别是如果您需要 .csv 文件输出。

"[Folder Destination]_" + 
(DT_WSTR,4)YEAR(GETDATE()) + 
RIGHT("0" + DT_WSTR,2)MONTH(GETDATE()),2) +
RIGHT("0" + (DT_WSTR,2)DAY(GETDATE()),2) + 
RIGHT("0" + (DT_WSTR,2)DATEPART("hh", GETDATE()),2) + ".TXT"

It is possible to create SSIS packages through Visual Studio. When click on new project in Visual Studio, select Business Intelligence Projects->Integration Services Project.

Though different from your situation (i.e., Excel File output), in my situation, for a flat .TXT file output, I had the date as part of flat file name. For this, in my Flat File Connection Manager, in Properties-Expressions->Connection String (Property), I put in the following expression. Similar approach could work in your case as well, specially if you go for a .csv file output.

"[Folder Destination]_" + 
(DT_WSTR,4)YEAR(GETDATE()) + 
RIGHT("0" + DT_WSTR,2)MONTH(GETDATE()),2) +
RIGHT("0" + (DT_WSTR,2)DAY(GETDATE()),2) + 
RIGHT("0" + (DT_WSTR,2)DATEPART("hh", GETDATE()),2) + ".TXT"
只是在用心讲痛 2024-08-30 20:23:39

@Kashif - 谢谢!!

你的帖子中有一个小错字。
注意第三行缺少左括号:

RIGHT("0" + DT_WSTR,2)MONTH(GETDATE()),2) +

需要是:

RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()),2) +

全部在一起:

"[Folder Destination]_" +
(DT_WSTR,4)YEAR(GETDATE()) +
RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()),2) +
RIGHT("0" + (DT_WSTR,2)DAY(GETDATE()),2) +
RIGHT("0" + (DT_WSTR,2)DATEPART("hh", GETDATE()),2) + ".TXT"

另外,只是给找到这篇文章的人一个注释,您可以将上面的“[文件夹目标]_”替换为如下路径:

"C:\\Some\\Path\\to\\File.txt"

注意双反斜杠! !

@Kashif - Thank you!!

One small typo in your post.
Notice missing open parenthesis on 3rd line:

RIGHT("0" + DT_WSTR,2)MONTH(GETDATE()),2) +

Needs to be:

RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()),2) +

All together:

"[Folder Destination]_" +
(DT_WSTR,4)YEAR(GETDATE()) +
RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()),2) +
RIGHT("0" + (DT_WSTR,2)DAY(GETDATE()),2) +
RIGHT("0" + (DT_WSTR,2)DATEPART("hh", GETDATE()),2) + ".TXT"

Also, just a note for anyone finding this post, you can replace "[Folder Destination]_" in the above with a path like so:

"C:\\Some\\Path\\to\\File.txt"

Notice the double backslash!!

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文