问题描述
我需要让用户将Excel/CSV文件导入到我的数据库中.
I need to have users import Excel/CSV files to my database.
当前，我有一个VB.net应用程序，它将允许我仅将CSV文件导入到我们的数据库中.我正在考虑切换到SSIS，而不是扩展此应用程序以满足我的需求，而是将其部署到用户以导入数据.
Currently, I have a VB.net application that will let me import CSV files only to our database. Rather than scaling this application to keep fitting my needs, and deploying it to users to import data, I'm considering switching to SSIS.
如何部署程序包，以便我的用户能够使用它们导入Excel/CSV文件?我知道SSIS并不是要成为前端的，所以我不应该将其用于我的需求吗?它仅用于SQL Developers导入数据吗?
How do I deploy packages so that my users are able to use them to import Excel/CSV files? I know SSIS is not intended to be a front end, so should I not use it for my needs? Is it only used for SQL Developers to import data?
我的用户也没有使用SQL或使用数据库的经验.他们习惯将其excel文件放在Sharepoint上或通过电子邮件传递给他们.我刚刚向他们介绍了SSRS，它可以作为报告服务很好地工作，但是我需要一个简单而可靠的导入过程.
Also, my users have no experience with SQL or using a database. They are used to putting their excel files on Sharepoint or pass them around via email. I just introduced them to SSRS which works wonderfully as a reporting service but I need a simple and reliable import process.
推荐答案
可能出于以下几个原因:
Probably not for a few reasons:
-
您必须部署SSIS运行时才能使程序包运行-这通常不是要做的事情.您可能需要支付许可证费用
You'd have to deploy the SSIS runtime for the package to run - this is not something that is usually done. You'd probably have to pay a licence cost
SSIS存储元数据(即源和目标中的列的类型和列数).如果此元数据发生更改，则该软件包通常会失败
SSIS stores metadata (i.e. the type and number of columns in the source and target). If this metadata changes then the package will usually fail
SSIS是服务器工具.它并不是真正为用户反馈而构建的
SSIS is a server tool. It 's not really built for user feedback
将Excel用作源很困难，原因有两个:
Excel as a source is difficult for two reasons:
-
它没有验证.用户可以在其中放入任何想要的东西，包括无效或缺失的值
It has no validation. Users can put anything they want in it, including invalid or missing values
Excel驱动程序通过动态检查行来计算元数据，有时这是不正确的(我确定您已经在程序中遇到了此问题)
Excel drivers work out metadata by inspecting rows on the fly and this is sometimes incorrect (I'm sure you've already encountered this in your program)
自定义构建的解决方案需要更多的维护，但具有更大的灵活性，并且鉴于您拥有优秀的资源，您可能需要这种灵活性.
A custom built solution requires more maintenance but has a lot more flexibility, and you probably need this flexibility given that you have excel sources.
如果保证您的excel文件每次都是干净的，并且所有用户都使用一个SQL Server(带有SSIS的一个许可安装)，那么这可能是可行的.
If your excel files are guaranteed to be clean every time, and all of your users use a single SQL Server (with a single licensed install of SSIS) then it might be practical.
在这种情况下，您有来自其他地方的一致数据文件，需要自动将其上载到数据库中.在这种情况下，SSIS可以通过以下已证明的模式来提供帮助:
In this case you have consistent data files coming from elsewhere that need to be automatically uploaded into the database. SSIS can help in this case with the following proven pattern:
- 用户(或进程)保存的文件已保存到特定的共享文件夹
- 一个计划在SQL Agent中每(例如)分钟运行一次的程序包，将导入该文件夹中的所有文件
- 如果导入成功，则文件将移至成功"文件夹
- 如果导入失败，则文件将移至失败"文件夹
这样，不需要将胖客户端应用程序部署到每个人.相反，任何用户都可以删除文件(如果他们具有共享访问权限)，它将被自动拉入
This way, a thick client app doesn't need to be deployed to everyone. Instead any user can drop the file (if they have share access), and it will be automatically pulled in
用户还可以通过检查文件夹来确认文件成功
Users can also confirm that the file was successful by checking the folder
这是一个包的示例，该包将所有文件导入文件夹中，并在完成后将其移动:
Here's an example of a package that imports all files in a folder and moves them when complete:
SSIS-如何遍历文件夹中的文件并获取路径+文件名，最后执行参数为Path + Filename的存储过程
这篇关于SSIS是否可以满足我的需求?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！