Edit connection manager connection string at runtime with c#

This is the first time I have used SSIS, so please bear with the ignorance.

I have a super simple package that inserts x000's of rows into a temporary table. The data source is a file that the user will upload. I need to be able to tell the package what file to upload. I'm thinking the simplest thing would be to edit the connectionString property of the SourceConnectionFlatFile at runtime. Is this possible? What form should the file path be in (UNC, other)? And, are there any other considerations I should be aware of?

Thanks!

Package configurations were designed for just this purpose.

Easy way to find connection leaks?

I, just like others, am experiencing problems with data leaks. I was wondering if anyone knows of a tool out there that can help pinpoint the pages with problems? I basically want to find the culprits!

Thanks,
--
YonahBack in the old days I usedNumega's BoundsChecker but it's for C++.

In principal with managed code (i.e., C# and VB.Net) you shouldn't have problems with memory leaks, though I'm sure there are ways to generate them.

There may be some tricks you can use to recover memory faster. The articles below may help:

Garbage Collector Basics and Performance Hints

Garbage Collection: Automatic Memory Management in the Microsoft .NET Framework|||::In principal with managed code (i.e., C# and VB.Net) you shouldn't have problems with
::memory leaks.

Interesting statement given that the question is for CONNECTION LEAKS, not memory leaks. The articles are of similar relevance :-(

I, just like others, am experiencing problems with data leaks.

No, not really.

What you can do is get a profiler (there are some good free ones around) that helps you to figur out what objects are in memory and why. If you experience a "forgot to close the connection" issue, though - you are stuck here.

What I do in general (in our O/R mapper) is handle all database connection open/close from one class - so I actually never have this issue, simply because my code is very sure things are easy here. Consider going this appraoch, concentrating this in one location.

Besides this:

You an set for SQL Server an application "name" that shows up in SQL Server's enterprise manager when you look at open connections. You COULD set this name differently for every page (like the page name), then you would know WHERE you forget to close the connection.

Dynamically switching report data connection at run time

We have been presented with the problem of using Reporting Services to run
the same report against one of two databases.
We are currently using SQL Server 2000, and while 2005 has support for
dynamically building connection strings with parameters, 2000 apparently does
not.
Thus far, we have found the following solutions:
- A custom data processing extension, which wraps up a SqlConnection, and
switches database context at run-time based on an expected query parameter
- Reporting against a front-end query on the database, which in turn calls
the query from the desired database
- Installing two versions of the same report on the report server, and
having the application choose which to execute at run-time.
Each of these options has various drawbacks, the first brings with it a mess
of support and deployment issues, the second leans on the database harder
than it needs to, and the third is basically redundant.
Although the solution we need now is to switch between one of two databases,
the ideal solution would be able to manage 1-n databases.
We would appreciate your input as to which solution is the best, or if there
is functionality which would better suit our needs that we havenâ't discovered
yet.On Nov 16, 4:07 pm, breedReed <br...@.community.nospam> wrote:
> We have been presented with the problem of using Reporting Services to run
> the same report against one of two databases.
> We are currently using SQL Server 2000, and while 2005 has support for
> dynamically building connection strings with parameters, 2000 apparently does
> not.
> Thus far, we have found the following solutions:
> - A custom data processing extension, which wraps up a SqlConnection, and
> switches database context at run-time based on an expected query parameter
> - Reporting against a front-end query on the database, which in turn calls
> the query from the desired database
> - Installing two versions of the same report on the report server, and
> having the application choose which to execute at run-time.
> Each of these options has various drawbacks, the first brings with it a mess
> of support and deployment issues, the second leans on the database harder
> than it needs to, and the third is basically redundant.
> Although the solution we need now is to switch between one of two databases,
> the ideal solution would be able to manage 1-n databases.
> We would appreciate your input as to which solution is the best, or if there
> is functionality which would better suit our needs that we haven't discovered
> yet.
I would personally create a SQL Server instance that has Linked
Servers to your two other databases, then in reporting services pass
the SELECT * FROM OPENQUERY( @.ServerName, 'SELECT real SQL here' )
-- Scott

Dynamically Create Connection Managers @ Run time

Is there a way to dynamically create a connection manager @. run time? I would like to do this from a data set of connection strings so I can link them into a union all component.

No. You cannot change package structure at run-time. You can dynamically read information in at run-time, so you can change your connection string for example. The best method is the build in Configuration support. You can also drive most properties through Expressions and supply a variable to set the property. Variables can be set in several ways, including other expressions and script tasks.

To load data from multiple sources, try using the For Each Loop, and drive this off your list of "connections". This can contain a data flow task, the connection of which can be updated on each loop iteration.

Try this article to give you an idea of looping with recordsets.

Shredding a Recordset
(http://www.sqlis.com/default.aspx?59)

dynamically changing the connection properties

I want to transfer data from one server to another by using SSIS. i want the connection string to be dynamic and also according to the some other variable, the transforming data is changing.

Could you provide me the solution thet how i am able to change my connetction string dynamically and the other variable too.

i am using VS 2003 as front end and SQL server 2005 as a backhand.

Due to VS.NET 2003 i am able to create DTS packages but i have to migrate it and then anly i am able to use it in JOB in SQL server agent of SQL server 2005.

is that any code or any stored procedure from which i am able to migrate DTS packages to SSIS packages.

Thank you

You need Business Intelligence Developers Studio or Visual Studio 2005 to edit SSIS packages. You can use configurations to change connection strings or variables, or you can set them by using the /SET switch for DTEXEC.|||You can reset the connection string using Script task in you SSIS and you can acess the conneciton variable as DTS.Connection|||

I suggest that you use configurations, or variables and expressions before you start with Script Tasks. The latter are harder to support. Using one of the more structured options should be easier, and also more manageable going forward, particularly when looking to future versions.

Dynamic Windows Authentication

Hi

The Parent Package has a Loop Container. The Loop Container calls the Child Package and in the process the connection manager properties are changed using package configurations.

When the packages are tested in BIDS both Windows and SQL authentication work.

When the packages are scheduled on SQL authentication works and Windows authentication fails. (The account which executes the schedule has permissions to all the data sources required. The SQL Agent Service Account has permissions. In addition a crendential was created using a different account which also has permissions and this also failed.) If the connection managers are made static then the schedule executes.

Any ideas would be appreciated.

Thanks

Try this technique: http://blogs.conchango.com/jamiethomson/archive/2005/10/10/2253.aspx

-Jamie

|||

The error seems to be:

An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Communication link failure". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "TCP Provider: An existing connection was forcibly closed by the remote host. ". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.".

The dynamic windows authentication works interactively in BIDS but when scheduled it fails in the parent and child packages. An expression is used in the parent package and configurations are used in the child package. If the connections are made static the packages execute succesfully.

The KB on Troubleshooting Kerberos Delegation (http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/security/tkerbdel.mspx) alludes to NTLM authentication as being the source for null user problems but why would the static package work and not the dynamic one?

Thanks

Dynamic Windows Authentication

Hi

The Parent Package has a Loop Container. The Loop Container calls the Child Package and in the process the connection manager properties are changed using package configurations.

When the packages are tested in BIDS both Windows and SQL authentication work.

When the packages are scheduled on SQL authentication works and Windows authentication fails. (The account which executes the schedule has permissions to all the data sources required. The SQL Agent Service Account has permissions. In addition a crendential was created using a different account which also has permissions and this also failed.) If the connection managers are made static then the schedule executes.

Any ideas would be appreciated.

Thanks

Try this technique: http://blogs.conchango.com/jamiethomson/archive/2005/10/10/2253.aspx

-Jamie

|||

The error seems to be:

An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Communication link failure". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "TCP Provider: An existing connection was forcibly closed by the remote host. ". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.".

The dynamic windows authentication works interactively in BIDS but when scheduled it fails in the parent and child packages. An expression is used in the parent package and configurations are used in the child package. If the connections are made static the packages execute succesfully.

The KB on Troubleshooting Kerberos Delegation (http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/security/tkerbdel.mspx) alludes to NTLM authentication as being the source for null user problems but why would the static package work and not the dynamic one?

Thanks

Dynamic SQL task

I want to make my SQL task dynamic i.e I want to accept the Connection
String through variable i pass to the package I dont intend on using a
configuration file.How do i go about doing this and what all variables do i pass
to do this.
Thanks
Clayton
P.S How can we assign Connections dynamically to a OLEDB Source by passing values from a list of variables.

Running the package using dtexec you can update the value of the connection string by using the Set command. The Books Online topic dtexec utility contains information about using the set option. You cannot update property expression by using the set option.

Another option is to use the Script task to access the connection strings which are stored outside the package. For example, a database table. The script could retrieve the string and update the connection string.

Marianne
SQL Server User Education
This posting is provided "AS IS" with no warranties, and confers no rights.