Parent / Child Package - Connections, Variables Etc.,

In respect to Parent / Child packages, can some one correct me if I'm wrong.

Even if connection managers are created in Parent package, the same needs to be created in child packages if they need to connect to database. On the other hand I can just create connection strings in variable in parent package(instead of connection managers itself) and use parent package variable configuration to configure the connection string of child package with the variable value.

Sorry If I'm confusing

The same with variables, the parent variable needs to be mapped to a child variable(using parent package variable config) to be used in child package, it cannot be used as it is.

Thanks

I think everything you've stated is correct.|||

Thanks for the confirmation Phil. Let me see how my experiment with Parent / Child packages goes.

Just a thought, It would be good to just specify the parent package in the child package and if it can pick up the variables, connections from the parent package.

Thanks

Parameterizing SQL connection

I'd like to know what's the best practice in parameterizing SQL connection in SSIS so that we can move from Dev to QA and to Production easily.

Thanks,

Tommy

Hi Tommy,

I'm not sure if it's a "Best Practice" but I'm using Indirect configuration files to store connection information on different servers.

Kirk Haselden has an article called "Keep your packages in the dark" in the November 2005 edition of SQL Server Magazine, which I found extremely helpful.

|||

Using configurations is certainly best practice, whether indirect or not. You can find more information on configurations here: http://msdn2.microsoft.com/en-us/library/ms141682.aspx

When using Books Online, please remember that you can score and comment on the content at the foot of each page. This helps us to improve the content continuously.

There's also a whitepaper here which may help ... www.microsoft.com/technet/prodtechnol/sql/2005/mgngssis.mspx

Donald

Parameterized connection strings for report parameters

Hi,
I'm posting this again - it is in fact the same issue all the time but
I'm just trying to tighten up the scope of question as I working on
the problem myself. Does anybody have used parameterized connection
strings for stored procedures used to populate report parameter
dropdown? It's just doesn't work! Even if I supply default parameter
values! Any ideas?
TommyIt doesn't work for drop downs, and why it should work? because for drop down
populating if you are passing a parameterized query then no meaning of drop
down instead it will be a main program for working as entry screen ofcourse
unless you have parameter as user id then it is different.
Amarnath, MCTS
"Tomaszek" wrote:
> Hi,
> I'm posting this again - it is in fact the same issue all the time but
> I'm just trying to tighten up the scope of question as I working on
> the problem myself. Does anybody have used parameterized connection
> strings for stored procedures used to populate report parameter
> dropdown? It's just doesn't work! Even if I supply default parameter
> values! Any ideas?
>
> Tommy
>|||Query (stored procedure) is not parameterized. What is parameterized
is connection string in data source used to run this stored procedure.
Report parameters. So why it shouldn't work? Especially that the same
parameterized connection string is working fine for stored procedure
used to fill ontent of the report.
Tommy
On Mar 21, 10:54 am, Amarnath <Amarn...@.discussions.microsoft.com>
wrote:
> It doesn't work for drop downs, and why it should work? because for drop down
> populating if you are passing a parameterized query then no meaning of drop
> down instead it will be a main program for working as entry screen ofcourse
> unless you have parameter as user id then it is different.
> Amarnath, MCTS
>
> "Tomaszek" wrote:
> > Hi,
> > I'm posting this again - it is in fact the same issue all the time but
> > I'm just trying to tighten up the scope of question as I working on
> > the problem myself. Does anybody have used parameterized connection
> > strings for stored procedures used to populate report parameter
> > dropdown? It's just doesn't work! Even if I supply default parameter
> > values! Any ideas?
> > Tommy- Hide quoted text -
> - Show quoted text -|||Sorry, I thought you are parameterizing the stored proc for drop down. can
you let me know what you have given in your parametereized connection
string.. For e.g. It should be something like this.
="data source=" & Parameters!ServerName.Value & ";initial
catalog=AdventureWorks
But some restrictions are there in creating this. I would suggest you go
through this link before writing one. search string like "Data Source
Expressions" in this link.
http://msdn2.microsoft.com/en-us/library/ms156450.aspx
Amarnath, MCTS
"Tomaszek" wrote:
> Query (stored procedure) is not parameterized. What is parameterized
> is connection string in data source used to run this stored procedure.
> Report parameters. So why it shouldn't work? Especially that the same
> parameterized connection string is working fine for stored procedure
> used to fill ontent of the report.
> Tommy
> On Mar 21, 10:54 am, Amarnath <Amarn...@.discussions.microsoft.com>
> wrote:
> > It doesn't work for drop downs, and why it should work? because for drop down
> > populating if you are passing a parameterized query then no meaning of drop
> > down instead it will be a main program for working as entry screen ofcourse
> > unless you have parameter as user id then it is different.
> >
> > Amarnath, MCTS
> >
> >
> >
> > "Tomaszek" wrote:
> > > Hi,
> > > I'm posting this again - it is in fact the same issue all the time but
> > > I'm just trying to tighten up the scope of question as I working on
> > > the problem myself. Does anybody have used parameterized connection
> > > strings for stored procedures used to populate report parameter
> > > dropdown? It's just doesn't work! Even if I supply default parameter
> > > values! Any ideas?
> >
> > > Tommy- Hide quoted text -
> >
> > - Show quoted text -
>
>|||Hi,
I have already visited mentioned link as lika a dozen others.
Connection string looks fairly simple
="Data Source=" & Parameters!SvrName.Value & ";Initial Catalog=" &
Parameters!DBName.Value
And as I write previously it works fine for filling the report
content. Alas when I'm using it for stored procedure that wills
parameter dropdown it fails. It faile even if I supply default values
for parameters.
It looks like parameters are avaliable after hitting 'view report
button' I mean after user will supply it's own report parameters - if
so this is the reason why they are unavaliable for connection string
that is needed actually for displaying parameters. But this are only
my guesses. Hope I described clearly what is happening. If you have
any suggestions please post them.
Thank you in advance.
Tommy.
On Mar 21, 11:55 am, Amarnath <Amarn...@.discussions.microsoft.com>
wrote:
> Sorry, I thought you are parameterizing the stored proc for drop down. can
> you let me know what you have given in your parametereized connection
> string.. For e.g. It should be something like this.
> ="data source=" & Parameters!ServerName.Value & ";initial
> catalog=AdventureWorks
> But some restrictions are there in creating this. I would suggest you go
> through this link before writing one. search string like "Data Source
> Expressions" in this link.
> http://msdn2.microsoft.com/en-us/library/ms156450.aspx
> Amarnath, MCTS
>
> "Tomaszek" wrote:
> > Query (stored procedure) is not parameterized. What is parameterized
> > is connection string in data source used to run this stored procedure.
> > Report parameters. So why it shouldn't work? Especially that the same
> > parameterized connection string is working fine for stored procedure
> > used to fill ontent of the report.
> > Tommy
> > On Mar 21, 10:54 am, Amarnath <Amarn...@.discussions.microsoft.com>
> > wrote:
> > > It doesn't work for drop downs, and why it should work? because for drop down
> > > populating if you are passing a parameterized query then no meaning of drop
> > > down instead it will be a main program for working as entry screen ofcourse
> > > unless you have parameter as user id then it is different.
> > > Amarnath, MCTS
> > > "Tomaszek" wrote:
> > > > Hi,
> > > > I'm posting this again - it is in fact the same issue all the time but
> > > > I'm just trying to tighten up the scope of question as I working on
> > > > the problem myself. Does anybody have used parameterized connection
> > > > strings for stored procedures used to populate report parameter
> > > > dropdown? It's just doesn't work! Even if I supply default parameter
> > > > values! Any ideas?
> > > > Tommy- Hide quoted text -
> > > - Show quoted text -- Hide quoted text -
> - Show quoted text -

Parameterized Connect String

Is it possible to define a datasource which has a dynamic connection string based on a report parameter?

Specifically I'm interested in using the XML data source, but I would like to use one data source for an unlimited number of files (instead of having a separate data source for each xml file). I was thinking that maybe I could just pass a parameter into the Render() web service method and have the connection string substitute the correct filename into the connection string.

Is this possible?

Yes, you can make your connection string an expression (including using parameters) in RS2005. There were some bugs in this area in the June CTP but it should be OK in the final build.

|||How would you write a xml connection string using report parameters?|||

This topic in RS BOL talks about connection strings for xml datasets: http://msdn2.microsoft.com/en-us/library/ms159741.aspx

-- Robert

Parameterized Connect String

Is it possible to define a datasource which has a dynamic connection string based on a report parameter?

Specifically I'm interested in using the XML data source, but I would like to use one data source for an unlimited number of files (instead of having a separate data source for each xml file). I was thinking that maybe I could just pass a parameter into the Render() web service method and have the connection string substitute the correct filename into the connection string.

Is this possible?

Yes, you can make your connection string an expression (including using parameters) in RS2005. There were some bugs in this area in the June CTP but it should be OK in the final build.

|||How would you write a xml connection string using report parameters?|||

This topic in RS BOL talks about connection strings for xml datasets: http://msdn2.microsoft.com/en-us/library/ms159741.aspx

-- Robert