I'm using a INI file with my Dynamic Properties task for the data sources for my connections in the package. Say the package goes from Q/A to production and the servers change. Now assuming the ini file is changed with the correct (new) server names etc. Where do we specify the location of the INI file?
This is really frustrating. Please helpHowdy,
Assuming you have a dynamic package properties task, open steps then DTSStep_DTSDynamic..... and double click on Property Name section called "Description". You can choose the Source to be "INI File" and the location is determined by the "...." button on the right hand side.
Cheers
SG|||Originally posted by sqlguy7777
Howdy,
Assuming you have a dynamic package properties task, open steps then DTSStep_DTSDynamic..... and double click on Property Name section called "Description". You can choose the Source to be "INI File" and the location is determined by the "...." button on the right hand side.
Cheers
SG
Yeah i figured that but once i move it to a different server i dont want to go through all the packages and changes the config file location. so i was wondering if it could be done through another way where promoting the package to another server w/o actually designing the packages all over again.
Thanks.|||Howdy
Well you can read all the config values from a table instaed of an ini file. So, you could just copy a table across into a database and use that as a config "module" that can be moved around.
Beyond that, I'd suggest its not straight forward.
Cheers,
SG|||I have tired that as well but once you move the DTS package to a different server the connections in the package still point to the original server. Also, for the DT properties in the package, how to specify the source connection to use for Queries in the package?
Thanks,
V|||Howdy
the problem is that you need to define local logons for the server, so each time you copy the package to a new server, you will need to manually alter the local server logon. No way around this I know of, unless all your servers, userIDs & passwords are the same.
A DT needs 2 connections : ( asuming you are copying data around on one server ) :
1 the source connection - server name & source database
2 the destination conn. - server name & destination database
To create a DT - click the soucre, then hold the control key & clikc the destination. Then while the two conenctions highlighted, do right click & choose Transform Data Task.
Cheers
SG
Showing posts with label connections. Show all posts
Showing posts with label connections. Show all posts
Monday, March 26, 2012
Monday, March 19, 2012
dynamic killer
Hi, guys!
I want to build a scheduled job to kill any connections from server
'WS1187' built by some VB applications. The code reads something like
this:
declare @.id int
begin
set @.id = (select spid from sysprocesses where hostname='WS1187' and
program_name='Visual Basic')
kill @.id
end
GO
It doesn't compile. The error message is
"Server: Msg 170, Level 15, State 1, Line 5
Line 4: Incorrect syntax near '@.id'."
Is there any way we can kill a user process with a dynamic "spid"
built based on certain business rules?
Thanks in advance.
Gary"Gary" <rooty_hill2002@.yahoo.com.au> wrote in message
news:171bd226.0410072058.4d1b0ab0@.posting.google.c om...
> Hi, guys!
> I want to build a scheduled job to kill any connections from server
> 'WS1187' built by some VB applications. The code reads something like
> this:
> declare @.id int
> begin
> set @.id = (select spid from sysprocesses where hostname='WS1187' and
> program_name='Visual Basic')
> kill @.id
> end
> GO
> It doesn't compile. The error message is
> "Server: Msg 170, Level 15, State 1, Line 5
> Line 4: Incorrect syntax near '@.id'."
> Is there any way we can kill a user process with a dynamic "spid"
> built based on certain business rules?
> Thanks in advance.
> Gary
Check the syntax for KILL in BOL, and you'll see that it doesn't allow a
variable for the SPID, so you need to use dynamic SQL:
exec('kill ' + convert(varchar, @.id))
Simon
Sunday, February 26, 2012
Dynamic Data Connections
Hello,
I am designing a SRS integration where the data source for a report must be
changed depending on which database the user chooses. There are any number
of databases, all having the same schema. The user can select which database
they want to query in a drop-down list in the web application, and then
select the report to run.
Any ideas on the best way to dynamically set the connection at run-time?
I see that in RDL there is a tag for <ConnectionString> so I could set that.
However, I think with lots of people accessing the report this may cause
problems. Perhaps I need to make a .NET assembly that modifies the
connection string tag at run-time?
I am accessing the report via an internet shortcut to the URL, but it is
possible for me to modify the URL before it is called. Another possibility
is to include the connection string as a parameter and pass it in the URL.
This just seems a bit clunky to me, since all reports would have to have
this parameter. It would make it slightly more difficult to allow users to
design their own reports.
Any suggestions?
:-D Maliksee this:
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&selm=uaVMmQoEEHA.2600%40TK2MSFTNGP09.phx.gbl
or search for "dynamic connection" in this newsgroup
Antonio
"Abdul Malik Said" wrote:
> Hello,
> I am designing a SRS integration where the data source for a report must be
> changed depending on which database the user chooses. There are any number
> of databases, all having the same schema. The user can select which database
> they want to query in a drop-down list in the web application, and then
> select the report to run.
> Any ideas on the best way to dynamically set the connection at run-time?
> I see that in RDL there is a tag for <ConnectionString> so I could set that.
> However, I think with lots of people accessing the report this may cause
> problems. Perhaps I need to make a .NET assembly that modifies the
> connection string tag at run-time?
> I am accessing the report via an internet shortcut to the URL, but it is
> possible for me to modify the URL before it is called. Another possibility
> is to include the connection string as a parameter and pass it in the URL.
> This just seems a bit clunky to me, since all reports would have to have
> this parameter. It would make it slightly more difficult to allow users to
> design their own reports.
> Any suggestions?
> :-D Malik
>
>|||Thank you for your quick response. After doing a search through the entire
newsgroup on this issue, I can see that setting a data source dynamically is
a common problem. However, I still have one question:
Is it possible to dynamically set a connection string or data source at
run-time by developing a Data Processing Extension? For my solution, it must
be set for that user's run-time only, and must not change the data source
for other users trying to access the same report at the same time. This is a
specific requirement of the integration.
I want to avoid as much as possible resorting to multiple copies of the
report, and setting the data source dynamically in the stored procedure.
These are workarounds, where I am hoping that DPE could allow me to write a
real solution. If DPE is the way to go, could you suggest any resources for
this? I know C# and I have read some of the Books Online stuff.
Malik
"Antonio Rome" <AntonioRome@.discussions.microsoft.com> wrote in message
news:F8D54E0A-8428-43E2-941D-C5DEA489349F@.microsoft.com...
> see this:
>
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&selm=uaVMmQoEEHA.2600%40TK2MSFTNGP09.phx.gbl
> or search for "dynamic connection" in this newsgroup
> Antonio
> "Abdul Malik Said" wrote:
> > Hello,
> >
> > I am designing a SRS integration where the data source for a report must
be
> > changed depending on which database the user chooses. There are any
number
> > of databases, all having the same schema. The user can select which
database
> > they want to query in a drop-down list in the web application, and then
> > select the report to run.
> >
> > Any ideas on the best way to dynamically set the connection at run-time?
> >
> > I see that in RDL there is a tag for <ConnectionString> so I could set
that.
> > However, I think with lots of people accessing the report this may cause
> > problems. Perhaps I need to make a .NET assembly that modifies the
> > connection string tag at run-time?
> >
> > I am accessing the report via an internet shortcut to the URL, but it is
> > possible for me to modify the URL before it is called. Another
possibility
> > is to include the connection string as a parameter and pass it in the
URL.
> > This just seems a bit clunky to me, since all reports would have to have
> > this parameter. It would make it slightly more difficult to allow users
to
> > design their own reports.
> >
> > Any suggestions?
> >
> > :-D Malik
> >
> >
> >|||No experience with DPE. You could change programmatically the connection string using web services to generate the report but the problem is the user's concurrency. I think this is a big issue in reporting services.
sorry
Antonio
"Abdul Malik Said" wrote:
> Thank you for your quick response. After doing a search through the entire
> newsgroup on this issue, I can see that setting a data source dynamically is
> a common problem. However, I still have one question:
> Is it possible to dynamically set a connection string or data source at
> run-time by developing a Data Processing Extension? For my solution, it must
> be set for that user's run-time only, and must not change the data source
> for other users trying to access the same report at the same time. This is a
> specific requirement of the integration.
> I want to avoid as much as possible resorting to multiple copies of the
> report, and setting the data source dynamically in the stored procedure.
> These are workarounds, where I am hoping that DPE could allow me to write a
> real solution. If DPE is the way to go, could you suggest any resources for
> this? I know C# and I have read some of the Books Online stuff.
>
> Malik
> "Antonio Rome" <AntonioRome@.discussions.microsoft.com> wrote in message
> news:F8D54E0A-8428-43E2-941D-C5DEA489349F@.microsoft.com...
> > see this:
> >
> >
> http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&selm=uaVMmQoEEHA.2600%40TK2MSFTNGP09.phx.gbl
> >
> > or search for "dynamic connection" in this newsgroup
> >
> > Antonio
> >
> > "Abdul Malik Said" wrote:
> >
> > > Hello,
> > >
> > > I am designing a SRS integration where the data source for a report must
> be
> > > changed depending on which database the user chooses. There are any
> number
> > > of databases, all having the same schema. The user can select which
> database
> > > they want to query in a drop-down list in the web application, and then
> > > select the report to run.
> > >
> > > Any ideas on the best way to dynamically set the connection at run-time?
> > >
> > > I see that in RDL there is a tag for <ConnectionString> so I could set
> that.
> > > However, I think with lots of people accessing the report this may cause
> > > problems. Perhaps I need to make a .NET assembly that modifies the
> > > connection string tag at run-time?
> > >
> > > I am accessing the report via an internet shortcut to the URL, but it is
> > > possible for me to modify the URL before it is called. Another
> possibility
> > > is to include the connection string as a parameter and pass it in the
> URL.
> > > This just seems a bit clunky to me, since all reports would have to have
> > > this parameter. It would make it slightly more difficult to allow users
> to
> > > design their own reports.
> > >
> > > Any suggestions?
> > >
> > > :-D Malik
> > >
> > >
> > >
>
>
I am designing a SRS integration where the data source for a report must be
changed depending on which database the user chooses. There are any number
of databases, all having the same schema. The user can select which database
they want to query in a drop-down list in the web application, and then
select the report to run.
Any ideas on the best way to dynamically set the connection at run-time?
I see that in RDL there is a tag for <ConnectionString> so I could set that.
However, I think with lots of people accessing the report this may cause
problems. Perhaps I need to make a .NET assembly that modifies the
connection string tag at run-time?
I am accessing the report via an internet shortcut to the URL, but it is
possible for me to modify the URL before it is called. Another possibility
is to include the connection string as a parameter and pass it in the URL.
This just seems a bit clunky to me, since all reports would have to have
this parameter. It would make it slightly more difficult to allow users to
design their own reports.
Any suggestions?
:-D Maliksee this:
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&selm=uaVMmQoEEHA.2600%40TK2MSFTNGP09.phx.gbl
or search for "dynamic connection" in this newsgroup
Antonio
"Abdul Malik Said" wrote:
> Hello,
> I am designing a SRS integration where the data source for a report must be
> changed depending on which database the user chooses. There are any number
> of databases, all having the same schema. The user can select which database
> they want to query in a drop-down list in the web application, and then
> select the report to run.
> Any ideas on the best way to dynamically set the connection at run-time?
> I see that in RDL there is a tag for <ConnectionString> so I could set that.
> However, I think with lots of people accessing the report this may cause
> problems. Perhaps I need to make a .NET assembly that modifies the
> connection string tag at run-time?
> I am accessing the report via an internet shortcut to the URL, but it is
> possible for me to modify the URL before it is called. Another possibility
> is to include the connection string as a parameter and pass it in the URL.
> This just seems a bit clunky to me, since all reports would have to have
> this parameter. It would make it slightly more difficult to allow users to
> design their own reports.
> Any suggestions?
> :-D Malik
>
>|||Thank you for your quick response. After doing a search through the entire
newsgroup on this issue, I can see that setting a data source dynamically is
a common problem. However, I still have one question:
Is it possible to dynamically set a connection string or data source at
run-time by developing a Data Processing Extension? For my solution, it must
be set for that user's run-time only, and must not change the data source
for other users trying to access the same report at the same time. This is a
specific requirement of the integration.
I want to avoid as much as possible resorting to multiple copies of the
report, and setting the data source dynamically in the stored procedure.
These are workarounds, where I am hoping that DPE could allow me to write a
real solution. If DPE is the way to go, could you suggest any resources for
this? I know C# and I have read some of the Books Online stuff.
Malik
"Antonio Rome" <AntonioRome@.discussions.microsoft.com> wrote in message
news:F8D54E0A-8428-43E2-941D-C5DEA489349F@.microsoft.com...
> see this:
>
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&selm=uaVMmQoEEHA.2600%40TK2MSFTNGP09.phx.gbl
> or search for "dynamic connection" in this newsgroup
> Antonio
> "Abdul Malik Said" wrote:
> > Hello,
> >
> > I am designing a SRS integration where the data source for a report must
be
> > changed depending on which database the user chooses. There are any
number
> > of databases, all having the same schema. The user can select which
database
> > they want to query in a drop-down list in the web application, and then
> > select the report to run.
> >
> > Any ideas on the best way to dynamically set the connection at run-time?
> >
> > I see that in RDL there is a tag for <ConnectionString> so I could set
that.
> > However, I think with lots of people accessing the report this may cause
> > problems. Perhaps I need to make a .NET assembly that modifies the
> > connection string tag at run-time?
> >
> > I am accessing the report via an internet shortcut to the URL, but it is
> > possible for me to modify the URL before it is called. Another
possibility
> > is to include the connection string as a parameter and pass it in the
URL.
> > This just seems a bit clunky to me, since all reports would have to have
> > this parameter. It would make it slightly more difficult to allow users
to
> > design their own reports.
> >
> > Any suggestions?
> >
> > :-D Malik
> >
> >
> >|||No experience with DPE. You could change programmatically the connection string using web services to generate the report but the problem is the user's concurrency. I think this is a big issue in reporting services.
sorry
Antonio
"Abdul Malik Said" wrote:
> Thank you for your quick response. After doing a search through the entire
> newsgroup on this issue, I can see that setting a data source dynamically is
> a common problem. However, I still have one question:
> Is it possible to dynamically set a connection string or data source at
> run-time by developing a Data Processing Extension? For my solution, it must
> be set for that user's run-time only, and must not change the data source
> for other users trying to access the same report at the same time. This is a
> specific requirement of the integration.
> I want to avoid as much as possible resorting to multiple copies of the
> report, and setting the data source dynamically in the stored procedure.
> These are workarounds, where I am hoping that DPE could allow me to write a
> real solution. If DPE is the way to go, could you suggest any resources for
> this? I know C# and I have read some of the Books Online stuff.
>
> Malik
> "Antonio Rome" <AntonioRome@.discussions.microsoft.com> wrote in message
> news:F8D54E0A-8428-43E2-941D-C5DEA489349F@.microsoft.com...
> > see this:
> >
> >
> http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&selm=uaVMmQoEEHA.2600%40TK2MSFTNGP09.phx.gbl
> >
> > or search for "dynamic connection" in this newsgroup
> >
> > Antonio
> >
> > "Abdul Malik Said" wrote:
> >
> > > Hello,
> > >
> > > I am designing a SRS integration where the data source for a report must
> be
> > > changed depending on which database the user chooses. There are any
> number
> > > of databases, all having the same schema. The user can select which
> database
> > > they want to query in a drop-down list in the web application, and then
> > > select the report to run.
> > >
> > > Any ideas on the best way to dynamically set the connection at run-time?
> > >
> > > I see that in RDL there is a tag for <ConnectionString> so I could set
> that.
> > > However, I think with lots of people accessing the report this may cause
> > > problems. Perhaps I need to make a .NET assembly that modifies the
> > > connection string tag at run-time?
> > >
> > > I am accessing the report via an internet shortcut to the URL, but it is
> > > possible for me to modify the URL before it is called. Another
> possibility
> > > is to include the connection string as a parameter and pass it in the
> URL.
> > > This just seems a bit clunky to me, since all reports would have to have
> > > this parameter. It would make it slightly more difficult to allow users
> to
> > > design their own reports.
> > >
> > > Any suggestions?
> > >
> > > :-D Malik
> > >
> > >
> > >
>
>
Dynamic connections rsInvalidDataSourceCredentialSetting
Hello,
Im having a few issues with a report this should use dynamic connections.
I have a connection based on an expresssion which uses a switch to evaluate a input and select the appropriate connection string for the item.
This all works fine when in BIDS it happily switches between servers no problems. when i then deploy the report to a SSRS server and try and view it i get the following error:
The format of connection string i am using is :
Data Source=<<server>>;Initial Catalog=staging;uid=<<username>>;pwd=<<password>>"
I have checked the report properties on the SSRS server and the connection correctly reports the conection is expression based and no credentials are required.
as a trouble shooting exersize i tryed a report which you just pass the connection string into instead of using a switch and got the same error
I have also tried it on another server and get the same problem.
Any suggestions?
Thanks
Paul
Did you find an answer for this? I have the same problem.|||No ive tried a number of different work arrounds but no luck
Just coming to terms with the fact i may have to have 10 reports instead of the 1
|||Hello,
I had a similar issue with Dynamic connections. In the Report Manager, "No credentials are required" option was set. On searching I got to know that we need an unattended execution account in such cases.
To create an unattended execution account, take Reporting Services Configuration Utility -> Execution Account.
Add an existing account. Doing the above procedure solved my issue. Hope this helps. Let me know how it goes.
Regards,
Sonu.
|||Added an execution account and the report runs like a dream!!
Cheers
Paul
Dynamic connections rsInvalidDataSourceCredentialSetting
Hello,
Im having a few issues with a report this should use dynamic connections.
I have a connection based on an expresssion which uses a switch to evaluate a input and select the appropriate connection string for the item.
This all works fine when in BIDS it happily switches between servers no problems. when i then deploy the report to a SSRS server and try and view it i get the following error:
The format of connection string i am using is :
Data Source=<<server>>;Initial Catalog=staging;uid=<<username>>;pwd=<<password>>"
I have checked the report properties on the SSRS server and the connection correctly reports the conection is expression based and no credentials are required.
as a trouble shooting exersize i tryed a report which you just pass the connection string into instead of using a switch and got the same error
I have also tried it on another server and get the same problem.
Any suggestions?
Thanks
Paul
Did you find an answer for this? I have the same problem.|||No ive tried a number of different work arrounds but no luck
Just coming to terms with the fact i may have to have 10 reports instead of the 1
|||Hello,
I had a similar issue with Dynamic connections. In the Report Manager, "No credentials are required" option was set. On searching I got to know that we need an unattended execution account in such cases.
To create an unattended execution account, take Reporting Services Configuration Utility -> Execution Account.
Add an existing account. Doing the above procedure solved my issue. Hope this helps. Let me know how it goes.
Regards,
Sonu.
|||Added an execution account and the report runs like a dream!!
Cheers
Paul
Dynamic connections rsInvalidDataSourceCredentialSetting
Hello,
Im having a few issues with a report this should use dynamic connections.
I have a connection based on an expresssion which uses a switch to evaluate a input and select the appropriate connection string for the item.
This all works fine when in BIDS it happily switches between servers no problems. when i then deploy the report to a SSRS server and try and view it i get the following error:
The format of connection string i am using is :
Data Source=<<server>>;Initial Catalog=staging;uid=<<username>>;pwd=<<password>>"
I have checked the report properties on the SSRS server and the connection correctly reports the conection is expression based and no credentials are required.
as a trouble shooting exersize i tryed a report which you just pass the connection string into instead of using a switch and got the same error
I have also tried it on another server and get the same problem.
Any suggestions?
Thanks
Paul
Did you find an answer for this? I have the same problem.|||
No ive tried a number of different work arrounds but no luck
Just coming to terms with the fact i may have to have 10 reports instead of the 1
|||Hello,
I had a similar issue with Dynamic connections. In the Report Manager, "No credentials are required" option was set. On searching I got to know that we need an unattended execution account in such cases.
To create an unattended execution account, take Reporting Services Configuration Utility -> Execution Account.
Add an existing account. Doing the above procedure solved my issue. Hope this helps. Let me know how it goes.
Regards,
Sonu.
|||Added an execution account and the report runs like a dream!!
Cheers
Paul
Dynamic Connections
I have read the posts on dynamic connections and still am not able to get it to work. Sorry, but I need specific instructions. I know it has something to do with the For Each Loop container, and something with the package variable, but I need more instruction.
What I am trying to do is transfer data from several (86) servers to a central server (all SQL Servers). The many servers will have the same code run against them to gather data and transfer it to the central server. I have written a package with those two objects for each server, but I know that there has to be a way to create just those two objects with the connection of the first object being dynamic and just looping through some type of list.
I hope my explaination is clear.
Thanks,
Peter Cwik
This is a fairly step-by-step guide: http://blogs.conchango.com/jamiethomson/archive/2005/05/30/1489.aspx
If you're getting errors - let us know the error messages.
-Jamie
|||I've tried that, but it uses a flat file source file. I have a SQL Server source. There is enough difference that I cannot transfer the knowledge from one to the other. The settings and what not are too different. I need specific instructions, just once.
Pete
Subscribe to:
Posts (Atom)