Acquiring Connection manager Programmatically in SSIS

hi all,

I m new to this forum and hope i ll get warm welcome from all of you.... thank you

I m Praveen kumar Dayanithi... a master student doin my assistantship in a Company. Kindly help me with this...

Here is my issue.... i would like to know how can i pass arguments to connection manager programmatically. In other words how can i acquire connection manger through SCRIPT task(Vb script). I know it is very easy to manually select n specify database name, table name by right clicking in dataflow task but in my company if i do that it will be very cumbersome for production people to change all the database and table names manually when the project is moved to production. Rather if i programmatically acquire connection manager using global variables it will make production people's job very easy. They have to just change the value of the variables. So can any one help me with this issue.

Thanks n regards

For dynamically changing configurations have you tried Package Configurations

Check out

http://msdn2.microsoft.com/en-us/library/ms141682.aspx
http://sqljunkies.com/WebLog/knight_reign/archive/2004/12/07/5445.aspx
http://www.sqlis.com/default.aspx?26

Thanks,
Loonysan

accessing report server programmatically

Hi friends
I have .net app ,where i display reports from my report server in a screen .,which works fine.
I added a web reference to my report server ,to my solution to get it work.

since i know my report server name on machine i added webreference to my project. but when i deploy at site ,as you can guess, the server name can be different.

is it possible add web reference programmaically so that i dont need to hard code report server name ?

am using sql server standard 2005,VS2005 standard edition.
Thank you very much for your help

in the place where you instantiate the webservice client:

MyReportServer rs = new MyReportServer();
rs.Url = "http://myservername/reportserver/blabla.asmx";

|||

Alexandre

Thanks for the post.

do you happen to know any sample code i can refer to.

Thanks for your help again.

|||Alexandre
i cant find "MyReportServer" namespace!
what library i need to give reference to?
Thanks for your help
|||try web.config file... i think u got it... define it in web.config file and change it whenever the server name changes|||

When you add a web reference VS will add a C# file to your project with web service endpoint definition. Then in some place in your program you make a call to the web service. the web service will be represented by an object. I refered to the type of the object MyReportServer, mostlikely it will be called differently in your app. This object has Url property that you may assign any new report server URL you want. Just find the place where the object is created.

By default, "MyReportServer" class pulls webservice URL from your config file. You can reset it by assigning new url via Url property

|||

You can use the following in your web.config:

<appSettings>
<add key="ServerURL" value=http://someServer/path/method.aspx />

</appSettings>

And in your code:

reportService.Url = ConfigurationSettings.AppSettings["ServerURL"];

|||Alexandre
if i understood you correctly, i just need to add a web reference to one of in-house report servers ,to our solution but when using it at client sites i need update "URL" property of the web service object by reading either from a config file or from a database field.
is this correct ?
Thanks for your help|||Hi Jay
its a windows app and i dont have a web.config.|||

>> if i understood you correctly

yes.

|||Thanks Alexandre
i already tried and it works nicely.
Thank you very much for your help :)

accessing report programmatically

I need to run a report, output it in a CSV format, and store it in a
specified directory. I am able to do it using VS.net report designer.
How can I programmatically specify the output format and the
destination directory?
I'm new to RS, thanks for you help.Yes you can. There is a book by WROX called SQL Server Reporting Services.
It has a chapter on programmatic rendering. The book is a must have for
those learning SQL Server Report Services. It's very straight forward and
easy to implement.
David
"Mark" <maratg3@.hotmail.com> wrote in message
news:ffcd6d4.0410121336.5c27434e@.posting.google.com...
>I need to run a report, output it in a CSV format, and store it in a
> specified directory. I am able to do it using VS.net report designer.
> How can I programmatically specify the output format and the
> destination directory?
> I'm new to RS, thanks for you help.

Accessing query execution plan results programmatically

Does anyone know if it is possible to access the execution plan results programmatically through a stored procedure or .NET assembly? I have the code sample

SET SHOWPLAN_XML ON; query... SET SHOWPLAN_XML OFF;

but it can only be run from the interface. I have tried a couple of solutions including dynamic sql to try to capture the results in a variable or file with no luck.

Does anyone know of a way to programmatically capture this information? We are doing some research with distributed query processing of dynamically generated queries using multiple processing nodes, and it would be helpful to know an estimate of how large the query is before sending it away to be processed.

I have looked at the dm_exec_query_stats view; but it can only be run on a query that has already been executed. I need to know the execution plan before the query is executed. If there is a way to get a query to show up in this view without being executed, then that would work as well.

Thanks -- MT

C# sample

SqlConnectionStringBuilder connString = new SqlConnectionStringBuilder();
connString.ApplicationName = "TryIt";
connString.DataSource = @."(local)";
connString.IntegratedSecurity = true;
connString.InitialCatalog = "tempdb";
using (SqlConnection conn = new SqlConnection(connString.ToString()))
{
conn.Open();
SqlCommand showplan_on_cmd = new SqlCommand("set showplan_xml on", conn);
showplan_on_cmd.ExecuteNonQuery();
SqlCommand cmd = new SqlCommand("Select count(*) From sys.tables", conn);
using (SqlDataReader showplan_results = cmd.ExecuteReader())
{
if (showplan_results.Read())
{
Console.WriteLine(showplan_results[0].ToString());
}
}
SqlCommand showplan_off_cmd = new SqlCommand("set showplan_xml off", conn);
showplan_on_cmd.ExecuteNonQuery();
}

Hope that helps.

PS: You can use this sample inside a .NET assembly with slight modifications (use a context-connection) to do the same thing inside a stored procedure.

|||That worked great. I had tried an ExecuteDataSet operation, but I had not thought to try a data reader. Thanks!

Accessing Query analyzer Messages programmatically

I would like to access the results posted to the Messages tab of Query Analyzer via code. I had assumed this was possible using ADO, but I am not having any luck.

Here is the example that I would like to use:

-- This T-SQL returns a simple report
DECLARE @.Foo Datetime

SELECT @.Foo = GetDate()

Print @.Foo
Print ''
Print 'Another line of text'
Print ''
Print 'End of report'

When I try opening this as an ADODB recordset, the recordset come back closed.

I'm scratching my head trying to think of other ways to do this, and I keep coming up blank.

Any ideas?see ADODB.Connection.Errors collection

?ADODB.Connection.Errors.Item(0).Description