Creation of "global" variables in runtime.

Is it possible to create "global" (package scope) variables in runtime in SSIS packages? If yes, please give some examples.

Hi Yuriy:

Not sure I understand your question. Are you asking how to create variables that are consumed at runtime, or to have on-the-fly variables created as a package runs?

The first type is easy -- go to the Control Flow tab, make sure nothing is selected, and right-click anywhere in the white space outside of an object, and select "Variables" from the shortcut menu. Alternatively, select "Variables" from the SSIS menu at the top of the main window.

The dialog you see is pretty self-explanatory. Click on the blue "x" button in the toolbar to add a new variable. Give it a name, set its data type and initial value. Delete a variable by selecting it in the list and clicking the X button with the red "x" over it.

Once you create a variable it can be referenced through code in a Script Task or expressions in other tasks. Most often you must reference the variable as @.[User::<VariableNameHere>]. Notice the two colons (::) between "User" and the variable name, and the @. sign in front of the expression.

If an object in the Control Flow tab is selected when you invoke the Variables command, the variable is local to that object. I do this now and then on containers when I need a variable that is used to share data among the tasks within a container.

I don't believe it's possible to create entirely new variables at runtime.

|||No object can be created at runtime inside the packge.

Creating trigger at runtime

Hi,
I am using VS 2005 and SQL server 2005.
I want to create a trigger at runtime. The trigger has to be created
when one of the rows in user-specified table is deleted. User will
specify the name of the table at runtime. Hence I cant create trigger
at development time. Can I create such a trigger from my C# code?
ManaMana wrote:
> Hi,
> I am using VS 2005 and SQL server 2005.
> I want to create a trigger at runtime. The trigger has to be created
> when one of the rows in user-specified table is deleted. User will
> specify the name of the table at runtime. Hence I cant create trigger
> at development time. Can I create such a trigger from my C# code?
> Mana
Create triggers when your tables are created. Do you really create
tables at runtime? This sounds like a strange requirement and perhaps
not an optimal design.
Use the CREATE TRIGGER statement to create a trigger. Bear in mind that
you'll have to give ddl admin rights to your users, which is not
usually recommended. Maybe if you explain a bit more about why you want
to do this then someone can suggest a better alternative.
David Portas
SQL Server MVP
--|||Mana
Can I ask you , why an user should specify a table name when he/she deletes
a row?
"Mana" <DearManasi@.gmail.com> wrote in message
news:1136290048.321123.94070@.o13g2000cwo.googlegroups.com...
> Hi,
> I am using VS 2005 and SQL server 2005.
> I want to create a trigger at runtime. The trigger has to be created
> when one of the rows in user-specified table is deleted. User will
> specify the name of the table at runtime. Hence I cant create trigger
> at development time. Can I create such a trigger from my C# code?
> Mana
>|||Triggers can be created in C# code if you have the proper permissions... But
I would warn against doing this... IF there is something you wish to do
permanently, just add the trigger and leave it there... Otherwise put the
code directly in the C# program, instead of creating a trigger...
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Mana" wrote:

> Hi,
> I am using VS 2005 and SQL server 2005.
> I want to create a trigger at runtime. The trigger has to be created
> when one of the rows in user-specified table is deleted. User will
> specify the name of the table at runtime. Hence I cant create trigger
> at development time. Can I create such a trigger from my C# code?
> Mana
>|||Hi Uri n David,
Let me explain my requirement elaborately.
I am building a web application which allows subscribers to subscribe
for the event of thier interest and receive notification when the event
occurs.
The event can be inserting or deleting a record from one of the tables
in database. (This is equirement of client. Cant help)The names of the
tables are exposed to user. And the user decides at runtime - first,
the name of the table and second, whether he should be notified on
insertion of record or deletion.
Since I dont not know table name and action (insert / delete) in
advance I cant write trigger in advance. I need to do it runtime.
I dont want to create triggers for all the tables existing in database.
Only for those that user is interested in.
Hope this explains my problem.
Mana|||Mana
I'd create one general table for auditing. When inserting/deletion is
occured so insert a row into this table which may have for instance a name
of the table , type of the operation and etc
"Mana" <DearManasi@.gmail.com> wrote in message
news:1136354013.575930.55610@.g14g2000cwa.googlegroups.com...
> Hi Uri n David,
> Let me explain my requirement elaborately.
> I am building a web application which allows subscribers to subscribe
> for the event of thier interest and receive notification when the event
> occurs.
> The event can be inserting or deleting a record from one of the tables
> in database. (This is equirement of client. Cant help)The names of the
> tables are exposed to user. And the user decides at runtime - first,
> the name of the table and second, whether he should be notified on
> insertion of record or deletion.
> Since I dont not know table name and action (insert / delete) in
> advance I cant write trigger in advance. I need to do it runtime.
> I dont want to create triggers for all the tables existing in database.
> Only for those that user is interested in.
> Hope this explains my problem.
> Mana
>|||Mana wrote:
> Hi Uri n David,
> Let me explain my requirement elaborately.
> I am building a web application which allows subscribers to subscribe
> for the event of thier interest and receive notification when the event
> occurs.
> The event can be inserting or deleting a record from one of the tables
> in database. (This is equirement of client. Cant help)The names of the
> tables are exposed to user. And the user decides at runtime - first,
> the name of the table and second, whether he should be notified on
> insertion of record or deletion.
> Since I dont not know table name and action (insert / delete) in
> advance I cant write trigger in advance. I need to do it runtime.
> I dont want to create triggers for all the tables existing in database.
> Only for those that user is interested in.
> Hope this explains my problem.
> Mana
Don't send notifications from a trigger. I explained why not in the
following thread:
http://groups.google.co.uk/group/mi...4dd2078d0df5312
Uri's suggestion is a better one: Log your changes to a table. Send out
notifications at suitable intervals based on that table. Have you
considered using SQL Server Notification Services? Both 2000 and 2005
versions are available. See:
http://www.microsoft.com/sql/techno...on/default.mspx
Hope this helps.
David Portas
SQL Server MVP
--|||Thanks, I think Uri's sugestion to keep an audit table is good.
Will implement that.
Thanks to all :)
Mana

Creating Reports at Runtime

Is it possible to create a report programmatically at runtime using Visual Studio 2005 based upon say a report layout specification file?There is a nifty tutorial on this I found.|||

Can you direct me to that tutorial please? It might help me in my situation.

Thanks!

|||

http://msdn2.microsoft.com/en-us/library/ms170667.aspx

It's beautiful!

|||

Hi,

Can you give link for example report preview by RDL-file ?

|||

proki wrote:

Hi,

Can you give link for example report preview by RDL-file ?

You can create any report preview that you would normally create using a report server project. I'm not sure what type of link you are wanting. RDL generator offers full flexibility for creating reports. It's just a different way to go about creating the report. The same reports can be created by using either the RDL generator method or the report server project.

There's nothing new and magical looking if that's what you're asking.

Creating Reports at Runtime

Is it possible to create a report programmatically at runtime using Visual Studio 2005 based upon say a report layout specification file?There is a nifty tutorial on this I found.|||

Can you direct me to that tutorial please? It might help me in my situation.

Thanks!

|||

http://msdn2.microsoft.com/en-us/library/ms170667.aspx

It's beautiful!

|||

Hi,

Can you give link for example report preview by RDL-file ?

|||

proki wrote:

Hi,

Can you give link for example report preview by RDL-file ?

You can create any report preview that you would normally create using a report server project. I'm not sure what type of link you are wanting. RDL generator offers full flexibility for creating reports. It's just a different way to go about creating the report. The same reports can be created by using either the RDL generator method or the report server project.

There's nothing new and magical looking if that's what you're asking.

Creating RDL files at runtime

I created a CustomerReport.rdl from VS.NET. I previewed the report in
the SQLServer Report Manager and it shows the report. that is perfect.
Now what i did is, I opened the CustomerReport.rdl in Notedpad.
Obviously it shows me the XML format of the report design. I copied
all XML content of the report (Ctrl+A/paste) and copied into a new
notepad window and saved the new file as test.rdl.
Now when I open the test.rdl report from Report Manager, I see that
Iam asked to enter the SQLServer authentication (sa/password). Only
after I do the SQLServer authentication, Iam able to view the report
in the Report Manager. Why does it ask me so.
The thing is Iam playing with the rdl because I need to create reports
on the fly (at runtime) in my project requirement. Please share your
experiences if you've done something simialar.
thanks,
Anand SagarHello,
What kind of datasource are you using ? shared or private to the report ?
In Report Designer or Report Manager, connections to data sources are
managed by ReportDesigner or ReportingServices and credentials are stored
securily in Repository for ReportingServices. Credentials are not part of
RDL and you should not find then in Notepad. If you have a shared Datasource
you can configure it with ReportManager and reference it in your RDL, so you
will not have to authenticate again with Test.rdl
HTH
Jerome Berthaud (MCSD)
http://www.winsight.fr
"Anand Sagar" <anandsagar@.gmail.com> wrote in message
news:3f76a771.0408070018.29eb85ae@.posting.google.com...
> I created a CustomerReport.rdl from VS.NET. I previewed the report in
> the SQLServer Report Manager and it shows the report. that is perfect.
> Now what i did is, I opened the CustomerReport.rdl in Notedpad.
> Obviously it shows me the XML format of the report design. I copied
> all XML content of the report (Ctrl+A/paste) and copied into a new
> notepad window and saved the new file as test.rdl.
>
> Now when I open the test.rdl report from Report Manager, I see that
> Iam asked to enter the SQLServer authentication (sa/password). Only
> after I do the SQLServer authentication, Iam able to view the report
> in the Report Manager. Why does it ask me so.
>
> The thing is Iam playing with the rdl because I need to create reports
> on the fly (at runtime) in my project requirement. Please share your
> experiences if you've done something simialar.
> thanks,
> Anand Sagar|||Hi,
You can test it live on internet and change the parameter
ProductFamily=Food in ProductFamily=Food. Here you will find 4 ways to
start a MS Reporting Services Report as enduser:
http://128.171.208.112/ReportServer?%2fFoodmart+Sales&ProductFamily=Food
http://128.171.208.112/ReportServer?%2fFoodmart+Sales&rs%3aFormat=HTML4.0&ProductFamily=Food&rc%3aToolbar=True&rc%3aJavaScript=True&rc%3aLinkTarget=_top&rc%3aArea=Toolbar
http://128.171.208.112/ReportServer?%2fFoodmart+Sales&ProductFamily=Food&rs%3aFormat=HTML4.0&rc%3aToolbar=True&rc%3aJavaScript=True&rc%3aLinkTarget=_top&rc%3aArea=Report
Any other parameter can be used in an action from MS As 2000. For more
info: www.reportportal.com / www.gmsbv.nl
Regards,
Marco
www.gmsbv.nl - your partner for MS Analyses & Reporting Services in
Europe !
"Jerome BERTHAUD" <jerome.berthaud@.winsight.fr> wrote in message news:<uoKmx5IfEHA.3428@.TK2MSFTNGP11.phx.gbl>...
> Hello,
> What kind of datasource are you using ? shared or private to the report ?
> In Report Designer or Report Manager, connections to data sources are
> managed by ReportDesigner or ReportingServices and credentials are stored
> securily in Repository for ReportingServices. Credentials are not part of
> RDL and you should not find then in Notepad. If you have a shared Datasource
> you can configure it with ReportManager and reference it in your RDL, so you
> will not have to authenticate again with Test.rdl
> HTH
> Jerome Berthaud (MCSD)
> http://www.winsight.fr
>
> "Anand Sagar" <anandsagar@.gmail.com> wrote in message
> news:3f76a771.0408070018.29eb85ae@.posting.google.com...
> > I created a CustomerReport.rdl from VS.NET. I previewed the report in
> > the SQLServer Report Manager and it shows the report. that is perfect.
> >
> > Now what i did is, I opened the CustomerReport.rdl in Notedpad.
> > Obviously it shows me the XML format of the report design. I copied
> > all XML content of the report (Ctrl+A/paste) and copied into a new
> > notepad window and saved the new file as test.rdl.
> >
> >
> > Now when I open the test.rdl report from Report Manager, I see that
> > Iam asked to enter the SQLServer authentication (sa/password). Only
> > after I do the SQLServer authentication, Iam able to view the report
> > in the Report Manager. Why does it ask me so.
> >
> >
> > The thing is Iam playing with the rdl because I need to create reports
> > on the fly (at runtime) in my project requirement. Please share your
> > experiences if you've done something simialar.
> >
> > thanks,
> > Anand Sagar|||One additional comment to Jerome's posting: If you use the managed SQL
provider ("SQL") in the data source, you can use IntegratedSecurity for
testing purposes (and therefore no need for credentials):
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<IntegratedSecurity>true</IntegratedSecurity>
<ConnectString>data source=(local); ... </ConnectString>
</ConnectionProperties>
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jerome BERTHAUD" <jerome.berthaud@.winsight.fr> wrote in message
news:uoKmx5IfEHA.3428@.TK2MSFTNGP11.phx.gbl...
> Hello,
> What kind of datasource are you using ? shared or private to the report ?
> In Report Designer or Report Manager, connections to data sources are
> managed by ReportDesigner or ReportingServices and credentials are stored
> securily in Repository for ReportingServices. Credentials are not part of
> RDL and you should not find then in Notepad. If you have a shared
Datasource
> you can configure it with ReportManager and reference it in your RDL, so
you
> will not have to authenticate again with Test.rdl
> HTH
> Jerome Berthaud (MCSD)
> http://www.winsight.fr
>
> "Anand Sagar" <anandsagar@.gmail.com> wrote in message
> news:3f76a771.0408070018.29eb85ae@.posting.google.com...
> > I created a CustomerReport.rdl from VS.NET. I previewed the report in
> > the SQLServer Report Manager and it shows the report. that is perfect.
> >
> > Now what i did is, I opened the CustomerReport.rdl in Notedpad.
> > Obviously it shows me the XML format of the report design. I copied
> > all XML content of the report (Ctrl+A/paste) and copied into a new
> > notepad window and saved the new file as test.rdl.
> >
> >
> > Now when I open the test.rdl report from Report Manager, I see that
> > Iam asked to enter the SQLServer authentication (sa/password). Only
> > after I do the SQLServer authentication, Iam able to view the report
> > in the Report Manager. Why does it ask me so.
> >
> >
> > The thing is Iam playing with the rdl because I need to create reports
> > on the fly (at runtime) in my project requirement. Please share your
> > experiences if you've done something simialar.
> >
> > thanks,
> > Anand Sagar
>