Custom task database connection

I'm developing a custom error task for SSIS. I've got a couple of existing OLE DB connections that exist in the Connection Manager. I want a drop down to be displayed in the Custom Task dialogue properties where the user can select one of the databases from the Connection manager.

2 questions:
1. How can i get the list of OLE DB databases in the properties dialogue?
2. Does anyone have a code sample on accessing a database like that within the custom component?Try this post http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=122993&SiteID=1

I don't think you can use an OLE-DB connection directly in managed code. You may (I haven't tried) be able to read off the connection string and use that to create your own ADO.net connection. I have been using ADO.Net connections myself, normally SQL, which gives you a SqlClient.SqlConnection object.

|||Hi Darren,

I don't understand how the IDtsTaskUI.Initialize one ties in here? Also, do you have to tie in the connections within the Acquire Connection method?|||If you have a custom UI for your task then you will have implemented IDtsTaskUI.Initialize in the class that implements IDtsTaskUI.

You call AcquireConnection on the connection you want to get the real connection object. For a file connection that is just a string of the filename, for an ADO.NET connection type SQL, that is a SqlConnection. You would call this in your UI, and also at run-time in the Execute method.

Custom Reports using Reporting Services

I have a couple of quetsions regarding the Reporting Services. Any response
will be greatly appreciated.
I am looking to see if the user has the capability to design thier own
reports using the reporting services..
Is it possible to provide the report design capability to the user so that
they can build custom reports using SQL Server Reporting Services ?
If yes, how can that be achived? If no, is there any third party tool that
can provide this capability to the user.
Thanks in advance,
--
snbatHi snbat:
Currently an end user needs Visual Studio installed to design reports.
Even the most basic (cheapest) version of the 2003 IDE can be used to
design reports.
There are also a slew of 3rd parties providing solutions, including
report designers (look at Cizer and SoftArtisans):
http://www.microsoft.com/sql/reporting/partners/softwareapps.asp
--
Scott
http://www.OdeToCode.com/
On Wed, 6 Oct 2004 08:05:03 -0700, "snbat"
<snbat@.discussions.microsoft.com> wrote:
>I have a couple of quetsions regarding the Reporting Services. Any response
>will be greatly appreciated.
>I am looking to see if the user has the capability to design thier own
>reports using the reporting services..
>Is it possible to provide the report design capability to the user so that
>they can build custom reports using SQL Server Reporting Services ?
>If yes, how can that be achived? If no, is there any third party tool that
>can provide this capability to the user.
>Thanks in advance,|||The next version of RS will include ad-hoc reporting functionality.
Currently, you can create your own ad-hoc report generator (it is not that
difficult) or use third-party tools as Scott mentioned.
--
Hope this helps.
----
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
----
"snbat" <snbat@.discussions.microsoft.com> wrote in message
news:6C9CAFF3-E415-4E7A-8B25-246C5F64B762@.microsoft.com...
> I have a couple of quetsions regarding the Reporting Services. Any
response
> will be greatly appreciated.
> I am looking to see if the user has the capability to design thier own
> reports using the reporting services..
> Is it possible to provide the report design capability to the user so that
> they can build custom reports using SQL Server Reporting Services ?
> If yes, how can that be achived? If no, is there any third party tool that
> can provide this capability to the user.
> Thanks in advance,
>
> --
> snbat

custom parameters screen

I've developed a couple of reports using SQL Server 2005 Reporting Services.
However, the parameters interface doesn't suite my requirements, i need to
design a custom screen where users can specify the criteria for the data
(parameters).
How can i do the above using .NET?
(I need to do anway with the standard filter screens, & display my own
filters screens & then generate the report)you can use asp.net to create all your screens and call using render method
using Reporting Services web services, or embed report viewer control to
display all your reports.
Amarnath
"Paul" wrote:
> I've developed a couple of reports using SQL Server 2005 Reporting Services.
> However, the parameters interface doesn't suite my requirements, i need to
> design a custom screen where users can specify the criteria for the data
> (parameters).
> How can i do the above using .NET?
> (I need to do anway with the standard filter screens, & display my own
> filters screens & then generate the report)
>
>

Custom Object in SSIS

I wrote a C# class that has a couple of methods that can be called to grab data out of a flat file and import it to a DB. This class was written for a web applicaiton originally. (class has many complex business rules in it)

Now I would like to use this same class in a SSIS package.

Is it possible in a script task to create a new object with my class and use it's methods?

In the end all I want SSIS to do is create my object, call a method in that object and be done.

If I was doing this in DTS, I would have to create an .Net EXE that used my class but I'm hoping to avoid that in SSIS.

Frenchy

The simplest way would be to use the class as-is, and just reference it from a Script Task, which gives you a VB.Net environment to create your class and call your methods as required. You can also leverage connections and variables for a more integrated and dynamic approach if required. You will need to place your assembly in a defined folder to get the Add Reference to work on VSA, the script environment used by SSIS - http://msdn2.microsoft.com/en-us/library/ms136007.aspx|||

I also need the same. My code is in C# and that could not be converted in VB.NET. Did you find any way to invoke C# class ? Please let me know also If you find any solution.

Thanks,

Pooja

|||

Pooja B wrote:

I also need the same. My code is in C# and that could not be converted in VB.NET. Did you find any way to invoke C# class ? Please let me know also If you find any solution.

Thanks,

Pooja

Darren answered this question already (above). Is anythig not clear?

-Jamie

|||

Thanks Jamie. No its clear but I am totally new to SSIS so dont have that much understanding. Can you send me some sample code that is accessing already existing classes in Script task.

Thank you so much...

Pooja

|||If you follow the steps in Darren's post (adding a reference to the .dll that holds your custom code), then you just create a new instance of the object and begin using it - just like you would in any .NET code.|||

For adding a reference to required dlls we need to make them strongly typed. I made them strongly typed and added them to ../windows/microsoft.net/framework/2.0.., then I am able to run my code sucessfully but could not access these dlls in SSIS package. When I add these dlls in GAC then I could access the dlls in SSIS packages but then my application starts giving errors as it gould not refer some of the dlls because of making it strongly named. Can you please suggest what could be the problem. I am using microsoft enterprise libraries in my code.

Thanks,

Pooja

|||This is really a question for a different forum - you might try one of the groups under .NET Development.|||

PLEASE help us to help you-

What application gives these errors?

What are the errors?

An existing application that referenced non-strong named assemblies should keep working fine, assuming you have left the old assemblies in place. It would however make senses to updae your code to use the new strong named version so that all code, SSIS and other applictions, share the same common assemblies.

|||

Thanks for your reply. I am able to fix that problem. In my appplication as I strongly named the assemblies, it was not able to find that in GAC so i need to change that in my app.config file.

I am new to SSIS, Can you please tell me how to specify configuration settings for SSIS. Like my cofiguration settings are like this.

<configuration>

<configSections>

<section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, MSEntLib.Data, Version = 2.0.0.0, Culture = neutral, PublicKeyToken=1791fdccf49453e7" />

</configSections>

<connectionStrings>

<add

name="abc_Dev"

providerName="System.Data.SqlClient"

connectionString="Data Source=;Initial Catalog=;User ID=; Password=" />

<add

name="abc_Prod"

providerName="System.Data.SqlClient"

connectionString="Data Source=;Initial Catalog=;User ID=; Password=;" />

</connectionStrings>

<dataConfiguration defaultDatabase="abc_Dev"/>

</configuration>

Thanks,

Pooja

|||Take a look at "Package Configurations" in SQL Server Books Online.|||

I have a similar scenario - EntLib and SSIS. I'm afraid I haven't found a way to use the same configuration file - both SSIS and EntLib have their own ideas about file format.

What I do is to manage the EntLib configuration files in the usual way (as in your code example above); and for the SSIS packages I use XML package configurations. You can find out more about these at http://msdn2.microsoft.com/en-us/library/ms141682.aspx.

|||

I specified the database configuration like this but how to specify the configsections for EntLib in XML configuration secion.

<DTSConfiguration>
<Configuration
ConfiguredType="Property"
Path="\Package.Connections[ConnectionName].Properties[ConnectionString]"
ValueType="String">
<ConfiguredValue>

Data Source=[Server Name];

User ID=[user name];

Initial Catalog=[database name];

</ConfiguredValue>
</Configuration>

</DTSConfiguration>

Can you please show me the dtsconfig xml file that you used in your application.

Thanks,

Pooja

|||

SSIS configurations and Enterprise Library configurations are clearly not the same, SSIS has no concept of .Net configurations. You will never be able to get SSIS to use Ent Lib config sections.

The best you can ever do is get .Net code hosted in SSIS to use Ent Lib config. This is not the same as making SSIS use Ent Lib or it's config, this is using .Net code. This is important if you have managed code, that uses Ent Lib functions. As covered above, to use external (external to SSIS I mean) assemblies, you need to ensure they are in the GAC, a challenge for first time Ent Lib builds.

For the configuration, think about how any .Net config works, the host process is responsible for loading it. So if you write an assembly which calls Microsoft.Practices.EnterpriseLibrary.ExceptionHandling stuff, and you want the exceptionHandling\exceptionPolicies config stuff to be available, then you need to add that to the config file for the calling application. If you use this assembly in a Script Task, and execute your package through DTExec, then you need to create a DTExec.config file, with suitable entries.

Other hosts of interest will be DtsDebugHost.exe , DtsHost.exe, DtExecUI.exe

|||

I have done in the same way. I added the assemblies in GAC that are using Enterprise libraries and then I am refering those dlls in my SSIS package.

Now you are saying that for configuration I need to create a DTExec.Config file. I am not able to understand how i should define my configuration setttings in this config file.

What I did is as my configuration file is like this

<configuration>

<configSections>

<section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, MSEntLib.Data, Version = 2.0.0.0, Culture = neutral, PublicKeyToken=1791fdccf49453e7" />

</configSections>

<connectionStrings>

<add name="abc" providerName="System.Data.SqlClient" connectionString="Data Source=[Server Name];Initial Catalog=[Database Name];User ID=; password=" />

</connectionStrings>

<dataConfiguration defaultDatabase="abc"/>

</configuration>

So I created one Connection manager named "abc" for that package and then in package configuration I made the configuration settings like

<DTSConfiguration

<Configuration ConfiguredType="Property" Path="\Package.Connections[abc].Properties[ConnectionString]" ValueType="String">

<ConfiguredValue>Data Source=[server name];User ID=;Initial Catalog=[database name];</ConfiguredValue>

</Configuration>

</DTSConfiguration>

I have config file named app.config in which i have added only configuration settings for EnLib like this

<configuration>

<configSections>

<section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, MSEntLib.Data, Version = 2.0.0.0, Culture = neutral, PublicKeyToken=1791fdccf49453e7" />

</configSections>

<dataConfiguration defaultDatabase="abc"/>

</configuration>

then I executed it through dtExec like this

EXEC xp_cmdshell 'dtexec /conf "C:\App.Config" /f "C:\Package1.dtsx"'

On executing this I get the exception in code. This exception is coming because it is not able to access the data layer. If I comment the code in which it is accessing database then it work ok.

Please help me. Problem is in configuration file only.

Thanks,

Pooja

Custom Object in SSIS

I wrote a C# class that has a couple of methods that can be called to grab data out of a flat file and import it to a DB. This class was written for a web applicaiton originally. (class has many complex business rules in it)

Now I would like to use this same class in a SSIS package.

Is it possible in a script task to create a new object with my class and use it's methods?

In the end all I want SSIS to do is create my object, call a method in that object and be done.

If I was doing this in DTS, I would have to create an .Net EXE that used my class but I'm hoping to avoid that in SSIS.

Frenchy

The simplest way would be to use the class as-is, and just reference it from a Script Task, which gives you a VB.Net environment to create your class and call your methods as required. You can also leverage connections and variables for a more integrated and dynamic approach if required. You will need to place your assembly in a defined folder to get the Add Reference to work on VSA, the script environment used by SSIS - http://msdn2.microsoft.com/en-us/library/ms136007.aspx|||

I also need the same. My code is in C# and that could not be converted in VB.NET. Did you find any way to invoke C# class ? Please let me know also If you find any solution.

Thanks,

Pooja

|||

Pooja B wrote:

I also need the same. My code is in C# and that could not be converted in VB.NET. Did you find any way to invoke C# class ? Please let me know also If you find any solution.

Thanks,

Pooja

Darren answered this question already (above). Is anythig not clear?

-Jamie

|||

Thanks Jamie. No its clear but I am totally new to SSIS so dont have that much understanding. Can you send me some sample code that is accessing already existing classes in Script task.

Thank you so much...

Pooja

|||If you follow the steps in Darren's post (adding a reference to the .dll that holds your custom code), then you just create a new instance of the object and begin using it - just like you would in any .NET code.|||

For adding a reference to required dlls we need to make them strongly typed. I made them strongly typed and added them to ../windows/microsoft.net/framework/2.0.., then I am able to run my code sucessfully but could not access these dlls in SSIS package. When I add these dlls in GAC then I could access the dlls in SSIS packages but then my application starts giving errors as it gould not refer some of the dlls because of making it strongly named. Can you please suggest what could be the problem. I am using microsoft enterprise libraries in my code.

Thanks,

Pooja

|||This is really a question for a different forum - you might try one of the groups under .NET Development.|||

PLEASE help us to help you-

What application gives these errors?

What are the errors?

An existing application that referenced non-strong named assemblies should keep working fine, assuming you have left the old assemblies in place. It would however make senses to updae your code to use the new strong named version so that all code, SSIS and other applictions, share the same common assemblies.

|||

Thanks for your reply. I am able to fix that problem. In my appplication as I strongly named the assemblies, it was not able to find that in GAC so i need to change that in my app.config file.

I am new to SSIS, Can you please tell me how to specify configuration settings for SSIS. Like my cofiguration settings are like this.

<configuration>

<configSections>

<section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, MSEntLib.Data, Version = 2.0.0.0, Culture = neutral, PublicKeyToken=1791fdccf49453e7" />

</configSections>

<connectionStrings>

<add

name="abc_Dev"

providerName="System.Data.SqlClient"

connectionString="Data Source=;Initial Catalog=;User ID=; Password=" />

<add

name="abc_Prod"

providerName="System.Data.SqlClient"

connectionString="Data Source=;Initial Catalog=;User ID=; Password=;" />

</connectionStrings>

<dataConfiguration defaultDatabase="abc_Dev"/>

</configuration>

Thanks,

Pooja

|||Take a look at "Package Configurations" in SQL Server Books Online.|||

I have a similar scenario - EntLib and SSIS. I'm afraid I haven't found a way to use the same configuration file - both SSIS and EntLib have their own ideas about file format.

What I do is to manage the EntLib configuration files in the usual way (as in your code example above); and for the SSIS packages I use XML package configurations. You can find out more about these at http://msdn2.microsoft.com/en-us/library/ms141682.aspx.

|||

I specified the database configuration like this but how to specify the configsections for EntLib in XML configuration secion.

<DTSConfiguration>
<Configuration
ConfiguredType="Property"
Path="\Package.Connections[ConnectionName].Properties[ConnectionString]"
ValueType="String">
<ConfiguredValue>

Data Source=[Server Name];

User ID=[user name];

Initial Catalog=[database name];

</ConfiguredValue>
</Configuration>

</DTSConfiguration>

Can you please show me the dtsconfig xml file that you used in your application.

Thanks,

Pooja

|||

SSIS configurations and Enterprise Library configurations are clearly not the same, SSIS has no concept of .Net configurations. You will never be able to get SSIS to use Ent Lib config sections.

The best you can ever do is get .Net code hosted in SSIS to use Ent Lib config. This is not the same as making SSIS use Ent Lib or it's config, this is using .Net code. This is important if you have managed code, that uses Ent Lib functions. As covered above, to use external (external to SSIS I mean) assemblies, you need to ensure they are in the GAC, a challenge for first time Ent Lib builds.

For the configuration, think about how any .Net config works, the host process is responsible for loading it. So if you write an assembly which calls Microsoft.Practices.EnterpriseLibrary.ExceptionHandling stuff, and you want the exceptionHandling\exceptionPolicies config stuff to be available, then you need to add that to the config file for the calling application. If you use this assembly in a Script Task, and execute your package through DTExec, then you need to create a DTExec.config file, with suitable entries.

Other hosts of interest will be DtsDebugHost.exe , DtsHost.exe, DtExecUI.exe

|||

I have done in the same way. I added the assemblies in GAC that are using Enterprise libraries and then I am refering those dlls in my SSIS package.

Now you are saying that for configuration I need to create a DTExec.Config file. I am not able to understand how i should define my configuration setttings in this config file.

What I did is as my configuration file is like this

<configuration>

<configSections>

<section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, MSEntLib.Data, Version = 2.0.0.0, Culture = neutral, PublicKeyToken=1791fdccf49453e7" />

</configSections>

<connectionStrings>

<add name="abc" providerName="System.Data.SqlClient" connectionString="Data Source=[Server Name];Initial Catalog=[Database Name];User ID=; password=" />

</connectionStrings>

<dataConfiguration defaultDatabase="abc"/>

</configuration>

So I created one Connection manager named "abc" for that package and then in package configuration I made the configuration settings like

<DTSConfiguration

<Configuration ConfiguredType="Property" Path="\Package.Connections[abc].Properties[ConnectionString]" ValueType="String">

<ConfiguredValue>Data Source=[server name];User ID=;Initial Catalog=[database name];</ConfiguredValue>

</Configuration>

</DTSConfiguration>

I have config file named app.config in which i have added only configuration settings for EnLib like this

<configuration>

<configSections>

<section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, MSEntLib.Data, Version = 2.0.0.0, Culture = neutral, PublicKeyToken=1791fdccf49453e7" />

</configSections>

<dataConfiguration defaultDatabase="abc"/>

</configuration>

then I executed it through dtExec like this

EXEC xp_cmdshell 'dtexec /conf "C:\App.Config" /f "C:\Package1.dtsx"'

On executing this I get the exception in code. This exception is coming because it is not able to access the data layer. If I comment the code in which it is accessing database then it work ok.

Please help me. Problem is in configuration file only.

Thanks,

Pooja

Custom Object in SSIS

I wrote a C# class that has a couple of methods that can be called to grab data out of a flat file and import it to a DB. This class was written for a web applicaiton originally. (class has many complex business rules in it)

Now I would like to use this same class in a SSIS package.

Is it possible in a script task to create a new object with my class and use it's methods?

In the end all I want SSIS to do is create my object, call a method in that object and be done.

If I was doing this in DTS, I would have to create an .Net EXE that used my class but I'm hoping to avoid that in SSIS.

Frenchy

The simplest way would be to use the class as-is, and just reference it from a Script Task, which gives you a VB.Net environment to create your class and call your methods as required. You can also leverage connections and variables for a more integrated and dynamic approach if required. You will need to place your assembly in a defined folder to get the Add Reference to work on VSA, the script environment used by SSIS - http://msdn2.microsoft.com/en-us/library/ms136007.aspx|||

I also need the same. My code is in C# and that could not be converted in VB.NET. Did you find any way to invoke C# class ? Please let me know also If you find any solution.

Thanks,

Pooja

|||

Pooja B wrote:

I also need the same. My code is in C# and that could not be converted in VB.NET. Did you find any way to invoke C# class ? Please let me know also If you find any solution.

Thanks,

Pooja

Darren answered this question already (above). Is anythig not clear?

-Jamie

|||

Thanks Jamie. No its clear but I am totally new to SSIS so dont have that much understanding. Can you send me some sample code that is accessing already existing classes in Script task.

Thank you so much...

Pooja

|||If you follow the steps in Darren's post (adding a reference to the .dll that holds your custom code), then you just create a new instance of the object and begin using it - just like you would in any .NET code.|||

For adding a reference to required dlls we need to make them strongly typed. I made them strongly typed and added them to ../windows/microsoft.net/framework/2.0.., then I am able to run my code sucessfully but could not access these dlls in SSIS package. When I add these dlls in GAC then I could access the dlls in SSIS packages but then my application starts giving errors as it gould not refer some of the dlls because of making it strongly named. Can you please suggest what could be the problem. I am using microsoft enterprise libraries in my code.

Thanks,

Pooja

|||This is really a question for a different forum - you might try one of the groups under .NET Development.|||

PLEASE help us to help you-

What application gives these errors?

What are the errors?

An existing application that referenced non-strong named assemblies should keep working fine, assuming you have left the old assemblies in place. It would however make senses to updae your code to use the new strong named version so that all code, SSIS and other applictions, share the same common assemblies.

|||

Thanks for your reply. I am able to fix that problem. In my appplication as I strongly named the assemblies, it was not able to find that in GAC so i need to change that in my app.config file.

I am new to SSIS, Can you please tell me how to specify configuration settings for SSIS. Like my cofiguration settings are like this.

<configuration>

<configSections>

<section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, MSEntLib.Data, Version = 2.0.0.0, Culture = neutral, PublicKeyToken=1791fdccf49453e7" />

</configSections>

<connectionStrings>

<add

name="abc_Dev"

providerName="System.Data.SqlClient"

connectionString="Data Source=;Initial Catalog=;User ID=; Password=" />

<add

name="abc_Prod"

providerName="System.Data.SqlClient"

connectionString="Data Source=;Initial Catalog=;User ID=; Password=;" />

</connectionStrings>

<dataConfiguration defaultDatabase="abc_Dev"/>

</configuration>

Thanks,

Pooja

|||Take a look at "Package Configurations" in SQL Server Books Online.|||

I have a similar scenario - EntLib and SSIS. I'm afraid I haven't found a way to use the same configuration file - both SSIS and EntLib have their own ideas about file format.

What I do is to manage the EntLib configuration files in the usual way (as in your code example above); and for the SSIS packages I use XML package configurations. You can find out more about these at http://msdn2.microsoft.com/en-us/library/ms141682.aspx.

|||

I specified the database configuration like this but how to specify the configsections for EntLib in XML configuration secion.

<DTSConfiguration>
<Configuration
ConfiguredType="Property"
Path="\Package.Connections[ConnectionName].Properties[ConnectionString]"
ValueType="String">
<ConfiguredValue>

Data Source=[Server Name];

User ID=[user name];

Initial Catalog=[database name];

</ConfiguredValue>
</Configuration>

</DTSConfiguration>

Can you please show me the dtsconfig xml file that you used in your application.

Thanks,

Pooja

|||

SSIS configurations and Enterprise Library configurations are clearly not the same, SSIS has no concept of .Net configurations. You will never be able to get SSIS to use Ent Lib config sections.

The best you can ever do is get .Net code hosted in SSIS to use Ent Lib config. This is not the same as making SSIS use Ent Lib or it's config, this is using .Net code. This is important if you have managed code, that uses Ent Lib functions. As covered above, to use external (external to SSIS I mean) assemblies, you need to ensure they are in the GAC, a challenge for first time Ent Lib builds.

For the configuration, think about how any .Net config works, the host process is responsible for loading it. So if you write an assembly which calls Microsoft.Practices.EnterpriseLibrary.ExceptionHandling stuff, and you want the exceptionHandling\exceptionPolicies config stuff to be available, then you need to add that to the config file for the calling application. If you use this assembly in a Script Task, and execute your package through DTExec, then you need to create a DTExec.config file, with suitable entries.

Other hosts of interest will be DtsDebugHost.exe , DtsHost.exe, DtExecUI.exe

|||

I have done in the same way. I added the assemblies in GAC that are using Enterprise libraries and then I am refering those dlls in my SSIS package.

Now you are saying that for configuration I need to create a DTExec.Config file. I am not able to understand how i should define my configuration setttings in this config file.

What I did is as my configuration file is like this

<configuration>

<configSections>

<section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, MSEntLib.Data, Version = 2.0.0.0, Culture = neutral, PublicKeyToken=1791fdccf49453e7" />

</configSections>

<connectionStrings>

<add name="abc" providerName="System.Data.SqlClient" connectionString="Data Source=[Server Name];Initial Catalog=[Database Name];User ID=; password=" />

</connectionStrings>

<dataConfiguration defaultDatabase="abc"/>

</configuration>

So I created one Connection manager named "abc" for that package and then in package configuration I made the configuration settings like

<DTSConfiguration

<Configuration ConfiguredType="Property" Path="\Package.Connections[abc].Properties[ConnectionString]" ValueType="String">

<ConfiguredValue>Data Source=[server name];User ID=;Initial Catalog=[database name];</ConfiguredValue>

</Configuration>

</DTSConfiguration>

I have config file named app.config in which i have added only configuration settings for EnLib like this

<configuration>

<configSections>

<section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, MSEntLib.Data, Version = 2.0.0.0, Culture = neutral, PublicKeyToken=1791fdccf49453e7" />

</configSections>

<dataConfiguration defaultDatabase="abc"/>

</configuration>

then I executed it through dtExec like this

EXEC xp_cmdshell 'dtexec /conf "C:\App.Config" /f "C:\Package1.dtsx"'

On executing this I get the exception in code. This exception is coming because it is not able to access the data layer. If I comment the code in which it is accessing database then it work ok.

Please help me. Problem is in configuration file only.

Thanks,

Pooja

Custom Formatting SQLXML Result

Here's what am doing:
I have a couple of SPs that can only return data in the format i want when i use FOR XML EXPLICIT.
The problem is if am using SQLXML then the data cannot be returned as XML since formatting is done by the client (which only gives me the option of raw/nested) and therefore the result i get is not formatted as i need it.
Is there a way i can format the result from the SP in the same way as if i were just using FOR XML EXPLICIT on the server side.

Example of output formatting expected:
<Patients_List>
<patient>
<Field name="forename"/>
<Field name="surname">Patience</Field>
</patient>
<patient>
<Field name="forename">Adam</Field>

<Field name="surname">Sandler</Field>
</patient>
...
</Patients_List>

My problem is, the raw/nested options cannot format the output as above (using FOR XML EXPLICIT could achieve this).

Any suggestions?

Are you using SQL2005? If so please give me your table structure. I might help you to write FOR XML RAW/nested query.|||Am actually using SQL 2000.

sample table structure.

Table Name: pat_info

id int
surname varchar(35)
forename varchar(35)
dob datetime
occupation varchar(100)

not sure if the structure helps (its just part of table).

|||

Hello,

You can use XPath query over annotated xsd schema (which internally translates into FOR XML EXPLICIT queries) to get the desired results.

Hope this helps,

Monica

|||

Hello,

You can use XPath query over annotated xsd schema (which internally translates into FOR XML EXPLICIT queries) to get the desired results.

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

Hope this helps,

Monica

Custom Assembly

Having problems with the <CustomAssemblies> attribute in my config files. It
doesn't exist!?!. I have a couple books for examples, Enterprise and Dev
versions running here onsite. I cannot find this element in the 2 defined
configs that are supposed to have this element. Since they don't seem to
exist, am I supposed to add it? and Where in the config files?. Any ideas
or a point in the right direction. Even the help files say the config files
are supposed to have this element, and to remove the comment. but it doesn't
seem to exist. Help:)
Frustrated Reporter:)Hi Clint,
I've been through rather step learning curve with Custom Assemblies over the
last week and although I'm definately not a guru, I hope to be able to
return some advice in this area - I hope !
OK, I expect the 2 config files are the Report Designer and the RS config
files yes ?
In order to make custom assemblies work, it depends on what they do - this
governs what code security policy directives you need to implment into youe
RS policy config file.
RS will "trust" your custom assemblies to a point (simply static expressions
require no special privelidges) . . . . if however you try and access
protected resources outside your custom assemblies (eg. call a web Service,
get environment vars, read files etc) you'll need to add specific details
and evidence info. into the RS Policy Server PemissionSet and Code group
sections
I can give specific help on these issues if you need more direction.
If you have corrupted (or badly formatted XML in your config files, you may
need to recover from backup (of in worse case just re-install RS)
regards,
- peteZ
"Clint Jennings" <cjennings@.fusionAlliance.com> wrote in message
news:O5hmffGkEHA.2664@.TK2MSFTNGP11.phx.gbl...
> Having problems with the <CustomAssemblies> attribute in my config files.
> It
> doesn't exist!?!. I have a couple books for examples, Enterprise and Dev
> versions running here onsite. I cannot find this element in the 2 defined
> configs that are supposed to have this element. Since they don't seem to
> exist, am I supposed to add it? and Where in the config files?. Any ideas
> or a point in the right direction. Even the help files say the config
> files
> are supposed to have this element, and to remove the comment. but it
> doesn't
> seem to exist. Help:)
> Frustrated Reporter:)
>|||It took a few tries but success. Thanx PeteZ.
"PeteZ" wrote:
> Hi Clint,
> I've been through rather step learning curve with Custom Assemblies over the
> last week and although I'm definately not a guru, I hope to be able to
> return some advice in this area - I hope !
> OK, I expect the 2 config files are the Report Designer and the RS config
> files yes ?
> In order to make custom assemblies work, it depends on what they do - this
> governs what code security policy directives you need to implment into youe
> RS policy config file.
> RS will "trust" your custom assemblies to a point (simply static expressions
> require no special privelidges) . . . . if however you try and access
> protected resources outside your custom assemblies (eg. call a web Service,
> get environment vars, read files etc) you'll need to add specific details
> and evidence info. into the RS Policy Server PemissionSet and Code group
> sections
> I can give specific help on these issues if you need more direction.
> If you have corrupted (or badly formatted XML in your config files, you may
> need to recover from backup (of in worse case just re-install RS)
> regards,
> - peteZ
>
> "Clint Jennings" <cjennings@.fusionAlliance.com> wrote in message
> news:O5hmffGkEHA.2664@.TK2MSFTNGP11.phx.gbl...
> > Having problems with the <CustomAssemblies> attribute in my config files.
> > It
> > doesn't exist!?!. I have a couple books for examples, Enterprise and Dev
> > versions running here onsite. I cannot find this element in the 2 defined
> > configs that are supposed to have this element. Since they don't seem to
> > exist, am I supposed to add it? and Where in the config files?. Any ideas
> > or a point in the right direction. Even the help files say the config
> > files
> > are supposed to have this element, and to remove the comment. but it
> > doesn't
> > seem to exist. Help:)
> >
> > Frustrated Reporter:)
> >
> >
>
>|||peteZ, I an having this same problem. What config file and where should the
<CustomAssemblies> attribute be added?
Thanks
"PeteZ" wrote:
> Hi Clint,
> I've been through rather step learning curve with Custom Assemblies over the
> last week and although I'm definately not a guru, I hope to be able to
> return some advice in this area - I hope !
> OK, I expect the 2 config files are the Report Designer and the RS config
> files yes ?
> In order to make custom assemblies work, it depends on what they do - this
> governs what code security policy directives you need to implment into youe
> RS policy config file.
> RS will "trust" your custom assemblies to a point (simply static expressions
> require no special privelidges) . . . . if however you try and access
> protected resources outside your custom assemblies (eg. call a web Service,
> get environment vars, read files etc) you'll need to add specific details
> and evidence info. into the RS Policy Server PemissionSet and Code group
> sections
> I can give specific help on these issues if you need more direction.
> If you have corrupted (or badly formatted XML in your config files, you may
> need to recover from backup (of in worse case just re-install RS)
> regards,
> - peteZ
>
> "Clint Jennings" <cjennings@.fusionAlliance.com> wrote in message
> news:O5hmffGkEHA.2664@.TK2MSFTNGP11.phx.gbl...
> > Having problems with the <CustomAssemblies> attribute in my config files.
> > It
> > doesn't exist!?!. I have a couple books for examples, Enterprise and Dev
> > versions running here onsite. I cannot find this element in the 2 defined
> > configs that are supposed to have this element. Since they don't seem to
> > exist, am I supposed to add it? and Where in the config files?. Any ideas
> > or a point in the right direction. Even the help files say the config
> > files
> > are supposed to have this element, and to remove the comment. but it
> > doesn't
> > seem to exist. Help:)
> >
> > Frustrated Reporter:)
> >
> >
>
>