Data extension question?

Hi,
when you use sql server data extension and set the query to a stored
proceduere, Reporting services automatically creates report parameters based
on the stored proc parameters. I'm creating a custom data processing
extension and i would like to create parameters based on a config file. How
do i do this?
thanks
shankarIn your custom data processing extension, you should implement
IDbCommandAnalysis. In GetParameters, you can access your config file.
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"shankar" <sramasubramanian@.ozcap.com> wrote in message
news:uk7P%23hXdEHA.3132@.TK2MSFTNGP11.phx.gbl...
> Hi,
> when you use sql server data extension and set the query to a stored
> proceduere, Reporting services automatically creates report parameters
based
> on the stored proc parameters. I'm creating a custom data processing
> extension and i would like to create parameters based on a config file.
How
> do i do this?
> thanks
> shankar
>|||Thanks, Chris. I have one more question. I implemented IDbCommandAnalysis
interface and i got all the parameters from my config file. In the config
file , i also stored designer default values for all the parameter. I would
like the Reportdesigner to use the designer value when it executes the query
command. You could view the designer value as dummy value. For Example, if
you are using sql server stored proc, you would pass all the required
parameter as a part of command text.
When i implemented, IDbCommandAnalysis interface, i passed this
parameter,designer value pair. I think reporting services uses this
interface only to get the list of parameters and it simply ignores default
value.Is there any way to do this.
thanks
shankar
"Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message
news:ueGeyMadEHA.2384@.TK2MSFTNGP09.phx.gbl...
> In your custom data processing extension, you should implement
> IDbCommandAnalysis. In GetParameters, you can access your config file.
> --
> This post is provided 'AS IS' with no warranties, and confers no rights.
All
> rights reserved. Some assembly required. Batteries not included. Your
> mileage may vary. Objects in mirror may be closer than they appear. No
user
> serviceable parts inside. Opening cover voids warranty. Keep out of reach
of
> children under 3.
> "shankar" <sramasubramanian@.ozcap.com> wrote in message
> news:uk7P%23hXdEHA.3132@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> > when you use sql server data extension and set the query to a stored
> > proceduere, Reporting services automatically creates report parameters
> based
> > on the stored proc parameters. I'm creating a custom data processing
> > extension and i would like to create parameters based on a config file.
> How
> > do i do this?
> >
> > thanks
> >
> > shankar
> >
> >
>|||That is correct. The design tool uses the interface only to obtain the
parameter names, not to retrieve default values.
Using it for default values as well is a good idea. I'll forward it on to
the designer team. Thanks.
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"shankar" <sramasubramanian@.ozcap.com> wrote in message
news:OjXL2YLeEHA.3864@.TK2MSFTNGP10.phx.gbl...
> Thanks, Chris. I have one more question. I implemented IDbCommandAnalysis
> interface and i got all the parameters from my config file. In the config
> file , i also stored designer default values for all the parameter. I
would
> like the Reportdesigner to use the designer value when it executes the
query
> command. You could view the designer value as dummy value. For Example, if
> you are using sql server stored proc, you would pass all the required
> parameter as a part of command text.
> When i implemented, IDbCommandAnalysis interface, i passed this
> parameter,designer value pair. I think reporting services uses this
> interface only to get the list of parameters and it simply ignores default
> value.Is there any way to do this.
> thanks
> shankar
> "Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message
> news:ueGeyMadEHA.2384@.TK2MSFTNGP09.phx.gbl...
> > In your custom data processing extension, you should implement
> > IDbCommandAnalysis. In GetParameters, you can access your config file.
> >
> > --
> > This post is provided 'AS IS' with no warranties, and confers no rights.
> All
> > rights reserved. Some assembly required. Batteries not included. Your
> > mileage may vary. Objects in mirror may be closer than they appear. No
> user
> > serviceable parts inside. Opening cover voids warranty. Keep out of
reach
> of
> > children under 3.
> > "shankar" <sramasubramanian@.ozcap.com> wrote in message
> > news:uk7P%23hXdEHA.3132@.TK2MSFTNGP11.phx.gbl...
> > > Hi,
> > > when you use sql server data extension and set the query to a
stored
> > > proceduere, Reporting services automatically creates report parameters
> > based
> > > on the stored proc parameters. I'm creating a custom data processing
> > > extension and i would like to create parameters based on a config
file.
> > How
> > > do i do this?
> > >
> > > thanks
> > >
> > > shankar
> > >
> > >
> >
> >
>

Data Connections: Where the hell are they stored?!!

Hello all,

Does anybody know where SSIS Data Connections are stored? Whenever one creates a Connection Manager, a list of all created Data Connections appears. It's very quick and easy to create a Connection Manager from an existing Data Connection, so really the latter are in essence the Connection Managers and are thus part of the application. It is therefore important to back them up if for example one wants to migrate the application to another computer. I have looked everywhere in Documents and Settings and Program Files and I can't find any folder or file where these Data Connections are stored! It's annoying to have this mysterious black-box behaviour!

Does anybody know?

Thanks in advance,

Jerome Smith

The data connections list is stored in the registry under HKCU\Software\Microsoft\VisualStudio\8.0\Packages\{4A0C6509-BF90-43DA-ABEE-0ABA3A8527F1}\Settings\Data\Connections. Its a BIDS specific setting, not specific to SSIS, but nevertheless used for GUI based package development under BIDS.

If one were to migrate to a different machine for package development, I could see where it would be useful to copy those registry entries over, true enough, in the same sense that you can export/import favorites from IE, or server listings from management studio.

One note though, the data connections are a measure of convenience (a memory bank of previous connections) to individuals, and are not a deployment/migration artifact. Connection managers are persisted in the IS packages (which you probably already knew), and when migrated to different environments, configurations are used to mesh IS packages into the new environment.

|||

Hi,

Thanks for your reply.

Forgive my ignorance, but what is BIDS?

Now OK, connection managers are persisted in the IS packages, but what use are they if they don't store connection information (Server, Authentication, Database)? I thought that's what they were for but it now appears that this connection information is stored in the Windows registry, which is not persisted in the IS packages.

Is there any way to retain the connection information in the connection managers?

Cheers,

Jerome

|||

Connection managers do store connection information.

You thought that's what they were for and that is exactly correct.

Now the confusing part is that the connection information is stored in "both" places.

However, once the connection manager is made, that information has been copied into the package itself, and that registry entry might as well have never existed and does not need to exist in the future.

Now, if you want to see the last point demonstrated rather than just asserted (that is, that a connection manager's connectivity information is persisted to the package), create a IS package in BIDS with an OLEDB connection manager used in an execute sql task and execute the package sucessfully from BIDS.

Then,export those those registry entries and delete them (you'll reimport them later), using a tool like regedit.exe.

If you don't want to to mess with the registry, the following will demonstrate the point as well; double-click on the connection manager and point it to a different database.

Now, run the package . What happens? Runs as before. Which database is hit? The one the connection manager was changed too. Is the registry updated to point to the connection manager's current database? No, it is not.

The connection information is persisted to the package.

Now, if you deleted the registry entries, re-import them.

BIDS is an acryonym for Business Intelligence development studio, which is the design-time environment hosted by Visual Studio 2005 for building BI projects ( Integration Services, Analysis Services, Reporting Services).

|||

Thank you very much. That was very useful.

Best regards,

Jerome Smith

Data Connections: Where the hell are they stored?!!

Hello all,

Does anybody know where SSIS Data Connections are stored? Whenever one creates a Connection Manager, a list of all created Data Connections appears. It's very quick and easy to create a Connection Manager from an existing Data Connection, so really the latter are in essence the Connection Managers and are thus part of the application. It is therefore important to back them up if for example one wants to migrate the application to another computer. I have looked everywhere in Documents and Settings and Program Files and I can't find any folder or file where these Data Connections are stored! It's annoying to have this mysterious black-box behaviour!

Does anybody know?

Thanks in advance,

Jerome Smith

The data connections list is stored in the registry under HKCU\Software\Microsoft\VisualStudio\8.0\Packages\{4A0C6509-BF90-43DA-ABEE-0ABA3A8527F1}\Settings\Data\Connections. Its a BIDS specific setting, not specific to SSIS, but nevertheless used for GUI based package development under BIDS.

If one were to migrate to a different machine for package development, I could see where it would be useful to copy those registry entries over, true enough, in the same sense that you can export/import favorites from IE, or server listings from management studio.

One note though, the data connections are a measure of convenience (a memory bank of previous connections) to individuals, and are not a deployment/migration artifact. Connection managers are persisted in the IS packages (which you probably already knew), and when migrated to different environments, configurations are used to mesh IS packages into the new environment.

|||

Hi,

Thanks for your reply.

Forgive my ignorance, but what is BIDS?

Now OK, connection managers are persisted in the IS packages, but what use are they if they don't store connection information (Server, Authentication, Database)? I thought that's what they were for but it now appears that this connection information is stored in the Windows registry, which is not persisted in the IS packages.

Is there any way to retain the connection information in the connection managers?

Cheers,

Jerome

|||

Connection managers do store connection information.

You thought that's what they were for and that is exactly correct.

Now the confusing part is that the connection information is stored in "both" places.

However, once the connection manager is made, that information has been copied into the package itself, and that registry entry might as well have never existed and does not need to exist in the future.

Now, if you want to see the last point demonstrated rather than just asserted (that is, that a connection manager's connectivity information is persisted to the package), create a IS package in BIDS with an OLEDB connection manager used in an execute sql task and execute the package sucessfully from BIDS.

Then,export those those registry entries and delete them (you'll reimport them later), using a tool like regedit.exe.

If you don't want to to mess with the registry, the following will demonstrate the point as well; double-click on the connection manager and point it to a different database.

Now, run the package . What happens? Runs as before. Which database is hit? The one the connection manager was changed too. Is the registry updated to point to the connection manager's current database? No, it is not.

The connection information is persisted to the package.

Now, if you deleted the registry entries, re-import them.

BIDS is an acryonym for Business Intelligence development studio, which is the design-time environment hosted by Visual Studio 2005 for building BI projects ( Integration Services, Analysis Services, Reporting Services).

|||

Thank you very much. That was very useful.

Best regards,

Jerome Smith