Need to generate dynamically excel file from Sql Select Statement using SSIS?

Question

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

In data flow task I have taken Oledb Source inside oledb source editor under 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]

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

created 3 variables inside ssis project

var1 datatype = datetime value=5/01/2011 8:22:10 AM

var2 datatype = datetime value=5/21/2011 8:22:10 AM

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

var 3 taken as string and added value into Expression But getting parse error :

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 "@".".

AND

The data types "DT_WSTR" and "DT_DATE" are incompatible for binary operator "+". The operand types could not be implicitly cast into compatible types for the operation. To perform this operation, one or both operands need to be explicitly cast with a cast operator.

Please advice.

enter image description here

criticalfix · Accepted Answer · 2013-05-22 13:25:37Z

1

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]

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.

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".

answered May 22, 2013 at 13:25

criticalfix

2,8701 gold badge21 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Neo Over a year ago

I want to set up @var2 dynamically to previous date how can i do so? please help

criticalfix Over a year ago

Use GETDATE in an expression for var2. You'll need to convert it to a string at some point to build your select query. Have you fixed the parse error in your Oledb Source yet?

Neo Over a year ago

No I'm getting same error again :

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 "@".".

Neo Over a year ago

Also Getting error : ` The data types "DT_WSTR" and "DT_DATE" are incompatible for binary operator "+". The operand types could not be implicitly cast into compatible types for the operation. To perform this operation, one or both operands need to be explicitly cast with a cast operator.`

criticalfix Over a year ago

You set it in static string rather than an expression. Probably best to get rid of var3, and use var1 and var2 in an OLD DB Source Parametrized SQL Statement like "SELECT * FROM Production.Product WHERE Color = ? AND Size = ?"

|

Collectives™ on Stack Overflow

Need to generate dynamically excel file from Sql Select Statement using SSIS?

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related