需要使用SSIS从Sql Select语句动态生成excel文件吗?

问题描述

我想使用 SSIS 包动态生成 excel 文件.我试过如下:

I want to generate dynamically excel file using SSIS package.I tried like following :

在数据流任务中，我在oledb source editor中使用了Oledb Source在 connection manager 下采用 data access mode = sql command from variable 因为我的变量有如下 sql 查询 select cusip,price,company from mytable where date 在两者之间@[var1] 和@[var2]

In data flow task I have taken Oledb Source inside oledb source editorunder connection manager taken data access mode = sql command from variable as my variable is having sql query like below select cusip,price,company from mytable where date in between @[var1] and @[var2]

但是我没有使用 var1 和 var 2 它给了我解析错误.

But i failed to use var1 and var 2 it is giving me parse error.

在 ssis 项目中创建了 3 个变量

created 3 variables inside ssis project

var1 数据类型 = 日期时间值=5/01/2011 8:22:10 AM

var2 数据类型 = 日期时间值=5/21/2011 8:22:10 AM

var3 datatype = datetime value=MY ABOVE SELECT QUERY使用上面的选择查询，我想每天生成新的 excel 文件，文件名类似于 MYFile05222013 (with todays date)

var3 datatype = datetime value=MY ABOVE SELECT QUERYUsing above select query I want to generate new excel file every day with filename like MYFile05222013 (with yesterdays date)

var 3 作为字符串并将值添加到表达式中但是得到解析错误:

Error code: 0x80040E14.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80040E14  Description: "Statement(s) could not be prepared.".
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80040E14  Description: "Must declare the scalar variable "@".".

和

数据类型DT_WSTR"和DT_DATE"与二元运算符+"不兼容.操作数类型无法隐式转换为操作的兼容类型.要执行此操作，需要使用转换运算符显式转换一个或两个操作数.

请指教.

推荐答案

尝试使 var3 成为字符串数据类型(而不是日期时间).给它一个这样的表达:

Try making var3 a string datatype (not a datetime). Give it an expression like this:

"select cusip,price,company from mytable where date in between " + @[User::var1] + " and " + @[User::var2]

也将 var1 和 var2 变量设为字符串数据类型.使用解析为有效日期时间值的默认值设置 var1 和 var2.

Make the var1 and var2 variables string datatypes, also. Set up var1 and var2 with default values that parse to valid datetime values.

查看表达式的求值结果，点击在表达式生成器中评估表达式.

To view the evaluation result of the expression, click Evaluate Expression in the Expression Builder.

对于输出文件，您需要一个 Excel 目标.目标连接管理器将具有文件名的表达式.您将在该表达式中构建文件名，例如MYFile05222013"​​.

For the output file, you will want an Excel Destination. The destination connection manager will have an expression for the filename. That expression is where you will build a filename like "MYFile05222013".

这篇关于需要使用SSIS从Sql Select语句动态生成excel文件吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！