Dynamically change the DataFlow Queries

Hi Guys,

This is Ravi. I'm working on SSIS 2005 version. I have created the DTSX file from the SQL Server and executed it successfully from my .NET 2005 code.

Now I have a requirement that I need to dynamically change the Source database query. ie., based on the user selection I need to get the data from different tables of SQL and put it into an Excel file.

Can anyone help me in this..

Regards,
Ravi K. Kalyan
Mascon Global Limited.

You can only change the query if the metadata of the data-flow is unchanged thereafter.

If this is the case then read this: http://blogs.conchango.com/jamiethomson/archive/2006/03/11/3063.aspx

It tells you how to dynamically alter your SQL queries.

-Jamie

|||

Thanks for the reply.

I tried to modify my code using the dataflow, but cud't do it exactly the way I wanted.

I have a requirement of exporting the data into excel file using the SSIS. For that I have used Application & Package class of DTS Namespace.

I can able to load and execute the package. Now according to the user selection I need to export the data from different tables. Can I pass the source query to the package object from my .NET 2005 code? If Yes, can u please give me some sample code or any reference links.

--Kalyan

Dynamically Adding DataFields to Chart

I am in need of some advice on my first project.
I have a chart which displays as I would expect when a static query is
executed. I need to somehow dynamically add the datafields to the chart at
runtime which is where I am coming unstuck. The number and name of
datafields could change each time the report is run so they absolutely need
to be dynamic.
Any advice on the best way to approach thsi problem would be much
appreciated.
Thanks, SimonDoes anyone have experience in creating Reports with Dynamic fields like
this. The only solution I can think of is to programatically build the RDL
each time, save it to the server then call the report to render it for the
client. It seems very inefficient which is why I wanted to ask others with
more experience in this field '?
Simon
"Simon Dingley" <newsgroups@.nospam-creativenrg.co.uk> wrote
> I am in need of some advice on my first project.
> I have a chart which displays as I would expect when a static query is
> executed. I need to somehow dynamically add the datafields to the chart at
> runtime which is where I am coming unstuck. The number and name of
> datafields could change each time the report is run so they absolutely
need
> to be dynamic.
> Any advice on the best way to approach thsi problem would be much
> appreciated.
> Thanks, Simon
>

dynamic SQL security context

Does a query thats executed as a dynamic SQL run under a different security
context other than the caller ?No, that will be possible in sql 2005.
HTH, Jens Suessmeyer.
"Hassan" <fatima_ja@.hotmail.com> schrieb im Newsbeitrag
news:OhZ6a11SFHA.3140@.TK2MSFTNGP14.phx.gbl...
> Does a query thats executed as a dynamic SQL run under a different
> security
> context other than the caller ?
>|||Hassan wrote:
> Does a query thats executed as a dynamic SQL run under a different
> security context other than the caller ?
No. Same security context. But the caller must have rights to the
underlying objects. If, for example, you execute a dynamic SQL command
from a stored procedure that updates the Customer table, the caller must
have update rights on the Customer table. Simply granting execute rights
to the proc is not enough.
David Gugick
Imceda Software
www.imceda.com|||Depend. When you execute a stored procedure, the sp is executed under the
security context of the sp owner (first sql server check if the caller has
right to execute the sp), but if this sp use dynamic sql, then the batch
being executed using EXEC() or sp_executesql is executed under the security
context of the caller.
AMB
"Hassan" wrote:

> Does a query thats executed as a dynamic SQL run under a different securit
y
> context other than the caller ?
>
>

dynamic SQL security context

Does a query thats executed as a dynamic SQL run under a different security
context other than the caller ?
No, that will be possible in sql 2005.
HTH, Jens Suessmeyer.
"Hassan" <fatima_ja@.hotmail.com> schrieb im Newsbeitrag
news:OhZ6a11SFHA.3140@.TK2MSFTNGP14.phx.gbl...
> Does a query thats executed as a dynamic SQL run under a different
> security
> context other than the caller ?
>
|||Hassan wrote:
> Does a query thats executed as a dynamic SQL run under a different
> security context other than the caller ?
No. Same security context. But the caller must have rights to the
underlying objects. If, for example, you execute a dynamic SQL command
from a stored procedure that updates the Customer table, the caller must
have update rights on the Customer table. Simply granting execute rights
to the proc is not enough.
David Gugick
Imceda Software
www.imceda.com
|||Depend. When you execute a stored procedure, the sp is executed under the
security context of the sp owner (first sql server check if the caller has
right to execute the sp), but if this sp use dynamic sql, then the batch
being executed using EXEC() or sp_executesql is executed under the security
context of the caller.
AMB
"Hassan" wrote:

> Does a query thats executed as a dynamic SQL run under a different security
> context other than the caller ?
>
>

dynamic SQL security context

Does a query thats executed as a dynamic SQL run under a different security
context other than the caller ?No, that will be possible in sql 2005.
HTH, Jens Suessmeyer.
"Hassan" <fatima_ja@.hotmail.com> schrieb im Newsbeitrag
news:OhZ6a11SFHA.3140@.TK2MSFTNGP14.phx.gbl...
> Does a query thats executed as a dynamic SQL run under a different
> security
> context other than the caller ?
>|||Hassan wrote:
> Does a query thats executed as a dynamic SQL run under a different
> security context other than the caller ?
No. Same security context. But the caller must have rights to the
underlying objects. If, for example, you execute a dynamic SQL command
from a stored procedure that updates the Customer table, the caller must
have update rights on the Customer table. Simply granting execute rights
to the proc is not enough.
--
David Gugick
Imceda Software
www.imceda.com|||Depend. When you execute a stored procedure, the sp is executed under the
security context of the sp owner (first sql server check if the caller has
right to execute the sp), but if this sp use dynamic sql, then the batch
being executed using EXEC() or sp_executesql is executed under the security
context of the caller.
AMB
"Hassan" wrote:
> Does a query thats executed as a dynamic SQL run under a different security
> context other than the caller ?
>
>

Dynamic SQL in Stored Procedures

A small percentage of my client's stored procedures use dynamic SQL
statements that are executed using sp_executesql. I am planning on
removing access to everything except stored procedure execution.
However, I have heard, that to be able to execute dynamic sql, a user
must have access to more than just stored procedure execution (i.e.,
SELECT, UPDATE, etc.). Can anyone clarify this for me, or lead me to a
site that explains it in better detail? Thank you in advance, Jeremy.On SQL Server 2000, permissions would be needed on the
underlying tables. There are other options with execute as
and certificates in SQL Server 2005.
You can find a lot of details on dynamic sql in the
following article on Erland's site:
The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
-Sue
On 5 Jun 2006 06:51:31 -0700, "jbiros" <jbiros@.sppinc.net>
wrote:

>A small percentage of my client's stored procedures use dynamic SQL
>statements that are executed using sp_executesql. I am planning on
>removing access to everything except stored procedure execution.
>However, I have heard, that to be able to execute dynamic sql, a user
>must have access to more than just stored procedure execution (i.e.,
>SELECT, UPDATE, etc.). Can anyone clarify this for me, or lead me to a
>site that explains it in better detail? Thank you in advance, Jeremy.|||Sue,
Thank you for the site reference. That is exactly the type of
information I was looking for.
Jeremy

Dynamic SQL help needed

I have some dynamic SQL code that is executed using the following:

SET @.SQLSTRING = @.SELECT+@.FROM+@.WHEREPARM+@.ORDER

EXEC sp_executesql @.SQLSTRING

and this works fine if my @.WHEREPARM is

SET @.WHEREPARM = ' Where srm.EVENT_HISTORY.EVENT_DATE <> ampfm.rpt_Abstract.AbsCompleteDate'

However, it fails if my @.WHEREPARM is

SET @.WHEREPARM = ' Where srm.EVENT_HISTORY.EVENT_DATE <> ampfm.rpt_Abstract.AbsCompleteDate

and srm.EVENT_HISTORY.EVENT_TYPE_KEY = '460''

I am getting the following error: Incorrect syntax near '460'

I believe I need to change the number of apostrophes around the 460 value but can't quite figure this out. Can someone provide me a correct example?

Thanks!

Try 3 quotes at the end: '460'''

|||

You are correct channge:

Code Snippet

and srm.EVENT_HISTORY.EVENT_TYPE_KEY = '460''

to

Code Snippet

and srm.EVENT_HISTORY.EVENT_TYPE_KEY = ''460'''

|||

Thank you both for the quick response. The ''460''' did the trick.