Data reader Source

Question

Data reader Source

Gangadhara MS

Hall of Fame

Points: 3377
More actions
October 9, 2009 at 8:08 am

#142407

Dear All,
I am new to SSIS,i do need to change the Data reader SQLCommand property.
Here I am doing simple select statement with where condition from the table in data source reader as a data source and to Excel file as destination.
I am using this query in the data reader source in sqlcommand
select * from empoyee where jdate='2009-10-09'
The problem here is every month when this get executed i need to modify the query in solution.How do i change this without modifying the package.
Pls suggest.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

COZYROC One Orange Chip Points: 28499 More actions · Answer 1

gangadhara.ms (10/9/2009)
Dear All,
I am new to SSIS,i do need to change the Data reader SQLCommand property.
Here I am doing simple select statement with where condition from the table in data source reader as a data source and to Excel file as destination.
I am using this query in the data reader source in sqlcommand
select * from empoyee where jdate='2009-10-09'
The problem here is every month when this get executed i need to modify the query in solution.How do i change this without modifying the package.
Pls suggest.

Go to your data flow right-click and select Properties. Look for [DataReader Source].[SqlCommand] property or something similar. This is the Data Source component SqlCommand property. Now in the same Properties window look for Expressions property. Click on ... and then for property select the SqlCommand property. Click ... on expression. Here you have to setup expression, which will dynamically modify your SqlCommand.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Gangadhara MS Hall of Fame Points: 3377 More actions · Answer 2

Hi,

Thanks for your immidiate reply.

Here i am thinking to store that value that is date in XML file which i need to modify out side the development environment(in every month)

How should i specify that in my package.

If you give soem clue about expression it willbe very helpful..

Thanks in advance

COZYROC One Orange Chip Points: 28499 More actions · Answer 3

gangadhara.ms (10/9/2009)
Hi,
Thanks for your immidiate reply.
Here i am thinking to store that value that is date in XML file which i need to modify out side the development environment(in every month)
How should i specify that in my package.
If you give soem clue about expression it willbe very helpful..
Thanks in advance

I would load the date in a variable and then use in expression like this:

"select * from empoyee where jdate='" + @[User::ProcessDate] + "'"

where ProcessDate is the variable containing the date.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Gangadhara MS Hall of Fame Points: 3377 More actions · Answer 4

Hi Thanks for reply as i am very new to SSIS can u pls help me.

I am getting below syntax error when i parsed the expression

I am using this expression :

" select * from Employee where jdate=' " + @[User::date] + " ' "

TITLE: Expression Builder

------------------------------

Expression cannot be evaluated.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.DataTransformationServices.Controls.TaskUIFramework.TaskUIFrameworkSR&EvtID=FailToEvaluateExpression&LinkId=20476

------------------------------

ADDITIONAL INFORMATION:

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.

Attempt to set the result type of binary operation "" select * from ganga1 where jdate=' " + @[User::date]" failed with error code 0xC0047080.

(Microsoft.DataTransformationServices.Controls)

------------------------------

BUTTONS:

OK

------------------------------

COZYROC One Orange Chip Points: 28499 More actions · Answer 5

gangadhara.ms (10/9/2009)
Hi Thanks for reply as i am very new to SSIS can u pls help me.
I am getting below syntax error when i parsed the expression
I am using this expression :
" select * from Employee where jdate=' " + @[User::date] + " ' "
TITLE: Expression Builder
------------------------------
Expression cannot be evaluated.
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.DataTransformationServices.Controls.TaskUIFramework.TaskUIFrameworkSR&EvtID=FailToEvaluateExpression&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
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.
Attempt to set the result type of binary operation "" select * from ganga1 where jdate=' " + @[User::date]" failed with error code 0xC0047080.
(Microsoft.DataTransformationServices.Controls)
------------------------------
BUTTONS:
OK
------------------------------

You have to cast the date variable to string like this:

"select * from empoyee where jdate='" + (DT_WSTR, 30)@[User::date] + "'"

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Gangadhara MS Hall of Fame Points: 3377 More actions · Answer 6

Thanks i made the correction and its working.

But the problem is again when i run the package i am getting this error

TITLE: Package Validation Error

------------------------------

Package Validation Error

------------------------------

ADDITIONAL INFORMATION:

Error at Data Flow Task [DataReader Source [113]]: An error occurred executing the provided SQL command.

Error at Data Flow Task [DTS.Pipeline]: "component "DataReader Source" (113)" failed validation and returned validation status "VS_ISBROKEN".

Error at Data Flow Task [DTS.Pipeline]: One or more component failed validation.

Error at Data Flow Task: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

------------------------------

BUTTONS:

OK

------------------------------

Pls help

COZYROC One Orange Chip Points: 28499 More actions · Answer 7

Go to the data reader source component and set ValidateExternalMetadata property to false. Try again to execute your package.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Gangadhara MS Hall of Fame Points: 3377 More actions · Answer 8

Thank you very much its now working correctly..

One more thing if i execute the query in Query analyzer its returning the data.But After executing the package no rows are getting inserted into

destination table.

Do we need to any configuration in the data source reader ??

COZYROC One Orange Chip Points: 28499 More actions · Answer 9

gangadhara.ms (10/9/2009)
Thank you very much its now working correctly..
One more thing if i execute the query in Query analyzer its returning the data.But After executing the package no rows are getting inserted into
destination table.
Do we need to any configuration in the data source reader ??

What you have to do is format the input date in the expression to have the correct format. Right now the format is general. I will leave this to you as an exercise for designing expressions.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Gangadhara MS Hall of Fame Points: 3377 More actions · Answer 10

Ok thank you very much for your help it helps a lot..Let me try this myself..Thanks for your guidance

Gangadhara MS Hall of Fame Points: 3377 More actions · Answer 11

Thanks for your help..it helps me a lot..I will try this myself thank you very much for your guidance ..

Gangadhara MS Hall of Fame Points: 3377 More actions · Answer 12

HI,

I am not getting exactly how to convert this ls help me.