Connection Problem

I created a package with two oledb connections in the connection manager....which was running perfectly...later i deleted these connections and added two new connections....and applied these to task with the new connection..

Now i run the package i come up with the following error...connection not found

Error 1 Error loading test.dtsx: The connection "SQL-DEV.sql_dev" is not found. This error is thrown by Connections collection when the specific connection element is not found. c:\documents and settings\myname\desktop\integration services project\ssis\test.dtsx 1 1

when i double click the error it goes to xml code...i dont know what need to be done...please help

Did you name the "new" connections the same as the old connections?|||No i didnt name them with the same name as the old once but the New connections are just the same as old connections...selecting server and database..|||Is "SQL-DEV.sql_dev" the name of a new connection or one of the ones you deleted?|||

Hi Sureshv,

The problem is the Connection Managers, for whatever reason, were not completely deleted. Their metadata is still stored inside the XML source for the SSIS files.

Naming the new Connection Managers the same as the old Connection Managers will not impact this error - Connection Managers are identified by GUIDs in SSIS. If you can identify the GUID of the deleted Connection Managers (perhaps by process of elimination), and comment them out inside the metadata, the package will stop throwing the error.

Hope this helps,
Andy

|||Andy,
ALL GUI screens represent a connection manager by its name, not GUID. This is precisely why I asked the question that I did. I imagined a scenario where the metadata was not cleaned up properly and when Sureshv picked the connection manager [name] in the drop down box, it was associated with a [now] defunct GUID, hence the resulting error.

In the end I think we can agree the likely culprit is bad metadata.|||

Hi Phil,

Thanks. I've seen some tasks respond differently to this scenario. Some of them pick up the changes automatically and some don't. I've also seen metadata corruption make this completely unpredicatable.

Yep - we agree this is the culprit. It's no fun to clean up a large package with corrupt metadat either - good luck Sureshv.

Andy

|||

In general when you delete a connection manager that is being used by a task; that task keeps pointing to the deleted connection manager GUID; untill you open the task and chose a diffrent one.

To the Original poster, make sure you revist all the tasks that were using those connection managers and refresh the connection metadata. If the package has logging enable and it was using one of the deleted CM; you have to refresh it as well.

|||i dont find the old connection name with in connection managers in the xml...So what do i do? i am afraid i will break my package...any more solutions?|||Did you check and correct the package logging?|||i did not set any package logging for this package..sqlsql

Connection over VPN

I have MSDE server installed on a pc at work. This PC runs Windows XP and
runs as a VPN server as well.
I have SQL Server Enterpise Manager on my laptop, and need to be able to
manage the MSDE server when connected to work from home, using a VPN.
When I try to add the server in Enterprise Manager, it says access is denied
or server is unavailable. I have read a lot of posts about using named
pipes, opening port 1723 but none of these have worked for me.
Any ideas please?
Tom
I suspect your actual error message was "sql server does not exist or
access denied". There are many potential causes as outlined in this article:
328306 INF: Potential Causes of the "SQL Server Does Not Exist or Access
http://support.microsoft.com/?id=328306
If the problem is only with VPNs I would probably concentrate on ports that
are blocked for VPN connections or permissions that are different for VPN
connections.
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.

Connection over VPN

I have MSDE server installed on a pc at work. This PC runs Windows XP and
runs as a VPN server as well.
I have SQL Server Enterpise Manager on my laptop, and need to be able to
manage the MSDE server when connected to work from home, using a VPN.
When I try to add the server in Enterprise Manager, it says access is denied
or server is unavailable. I have read a lot of posts about using named
pipes, opening port 1723 but none of these have worked for me.
Any ideas please?
TomI suspect your actual error message was "sql server does not exist or
access denied". There are many potential causes as outlined in this article:
328306 INF: Potential Causes of the "SQL Server Does Not Exist or Access
http://support.microsoft.com/?id=328306
If the problem is only with VPNs I would probably concentrate on ports that
are blocked for VPN connections or permissions that are different for VPN
connections.
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.

Connection Managers not installed?

apologies if this is the wrong group ..

whenever I create an Integrated Services project and attempt to add a Connection Manager I get the error

Connection Manager 'blah' is not properly installed on this computer.

(Microsoft.DataTransformationServices.Design)

This happens with all menu options except New Connection..

I have uninstalled and reinstalled both Visual Studio 2005 and SQL Server 2005 and made sure I have selected everything in each case

what am I missing?

Thanks

Chris

seems this has happened to others

see Jason Lodice thread for solution

Chris

Connection Managers are recognized after migrating to another server

Hi,

My database admin just migrated my packages from development to user testing server.

My packages configured to read the connection manager properties from the .dtsconfig.

But I am getting "The connection "CRPRDNMSQEZ.CIReporting2" is not found"

CRPRDNMSQEZ.CIReporting2 is being my connection manager.

I did find and replace server name in the dtsconfig but I guess it didn't work.

Any help is appreciated.

At a guess your connection manager name contained the orginal server name. The connection manager's connection string property ( you may have used other properties such as ServerName) would also have included the server name. When you did the find and replace you change both the connection name and the property values. The connection name should not have changed as that is the object in the package that you are trying to address.

For example take this config file snippet -

<Configuration ConfiguredType="Property" Path="\Package.Connections[ConnName].Properties[ConnectionString]" ValueType="String">
<ConfiguredValue>Data Source=MachineNameInstance;User ID=FunnyName;Password=somethingsecure;</ConfiguredValue>
</Configuration>

ConnName is the connection name, what you see in the Connections tray in the package.

MachineNameInstance is the bit to change. It is probably if you just accepted the defaults the designer gave you that your config file looked a bit like this-

<Configuration ConfiguredType="Property" Path="\Package.Connections[CRPRDNMSQEZ.CIReporting2].Properties[ConnectionString]" ValueType="String">
<ConfiguredValue>Data Source=CRPRDNMSQEZ;Initial Catalog=CIReporting2</ConfiguredValue>
</Configuration>

Doing a find and replace on CRPRDNMSQEZ would have found two hits, one of them being incorrect from your point of view. I strongly recomend when building packages you name things such as connections with functional or logical names, not physical names. It means the name continues to sensible over entire the package lifetime, e.g.

<Configuration ConfiguredType="Property" Path="\Package.Connections[DWReportServer].Properties[ConnectionString]" ValueType="String">
<ConfiguredValue>Data Source=CRPRDNMSQEZ;Initial Catalog=CIReporting2</ConfiguredValue>
</Configuration>

|||Sounds like the name of the connection manager in the .dtsconfig file does not match the name of a connection manager inside the package. Double check that it does.

Connection Managers are NOT recognized after migrating to another server

Hi,

My database admin just migrated my packages from development to user testing server.

My packages configured to read the connection manager properties from the .dtsconfig.

But I am getting "The connection "CRPRDNMSQEZ.CIReporting2" is not found"

CRPRDNMSQEZ.CIReporting2 is being my connection manager.

I did find and replace server name in the dtsconfig but I guess it didn't work.

Any help is appreciated.

At a guess your connection manager name contained the orginal server name. The connection manager's connection string property ( you may have used other properties such as ServerName) would also have included the server name. When you did the find and replace you change both the connection name and the property values. The connection name should not have changed as that is the object in the package that you are trying to address.

For example take this config file snippet -

<Configuration ConfiguredType="Property" Path="\Package.Connections[ConnName].Properties[ConnectionString]" ValueType="String">
<ConfiguredValue>Data Source=MachineNameInstance;User ID=FunnyName;Password=somethingsecure;</ConfiguredValue>
</Configuration>

ConnName is the connection name, what you see in the Connections tray in the package.

MachineNameInstance is the bit to change. It is probably if you just accepted the defaults the designer gave you that your config file looked a bit like this-

<Configuration ConfiguredType="Property" Path="\Package.Connections[CRPRDNMSQEZ.CIReporting2].Properties[ConnectionString]" ValueType="String">
<ConfiguredValue>Data Source=CRPRDNMSQEZ;Initial Catalog=CIReporting2</ConfiguredValue>
</Configuration>

Doing a find and replace on CRPRDNMSQEZ would have found two hits, one of them being incorrect from your point of view. I strongly recomend when building packages you name things such as connections with functional or logical names, not physical names. It means the name continues to sensible over entire the package lifetime, e.g.

<Configuration ConfiguredType="Property" Path="\Package.Connections[DWReportServer].Properties[ConnectionString]" ValueType="String">
<ConfiguredValue>Data Source=CRPRDNMSQEZ;Initial Catalog=CIReporting2</ConfiguredValue>
</Configuration>

|||Sounds like the name of the connection manager in the .dtsconfig file does not match the name of a connection manager inside the package. Double check that it does.sqlsql

Connection manager validation within UI

Hi all,

I need to validate a custom connection manager within its custom user interface, like the FlatFileConnectionManager does it, by showing errors and warnings at the bottom of the window.

How can I do that since SSIS provides only a ConnectionManager object with the IDtsConnectionManagerUI::Initialize method, and this object doesn't contain any Validate method?

Note that for a custom data flow component, SSIS provides a ComponentMetaData object which supports a Validate method.

Thanks.

Pascal

The UI class is just the stub that allows SSIS to find your UI implementation. You need to add a form that displays controls for setting properties and such like. Within that form you can call the Validate method on your connection class, capture the messages and display them as you wish. There is a connection manager sample with UI in the updated samples available from the MS site, Downloads area. For more samples, look at a task, as this follows exactly the same pattern as for a connection. Components are quite a bit different.

|||I can't call the Validate method of my custom connection manager because SSIS doesn't give me my object but a ConnectionManager which is not a ConnectionManagerBase type...

Connection Manager to connect ot Access mdb

How do you setup a conntection in ssis to connect to an access mdb? I have used DTS to upload access tables to sql and want to do the same in the new ssis services. I migrated the DTS package but the connection in created for the access mdb does not work in ssis and is shown to be be illegal. The migration did not post any errors when it migrated the package.

Use a OLE DB ConnectionManager.

What is yourmigrated package using?

Why is it illegal? What error message do you get?

-Jamie

|||

When you double click on the connection you get the error:

The specified provider is not supported. Please choose different provider in connection manager.

When you do get in it is all greyed out and when you test connection you get the follow error in this screen shot:

http://216.247.79.34/public/ssiserror.jpg

Here is the DTS 2000 input setup for the access file using DSN

http://216.247.79.34/public/ssiserror1.jpg

|||

Try using the "Native OLE DB\microsoft jet 4.0 OLE DB Provider" Provider.

I've just installed SSIS on a clean O/S and this is working fine.

-Jamie

Connection Manager Questino

I know this is a dumb question, but what exactly does connection manager do in SQL Server 2005? If it works like I think it does, then it sets up BI packages on the servers that it creates a connection to. However, if my computer goes down, are these packages lost to the server?

-Thank you in advance.

A connection manager acquires connections to a database. flat file, or other data source so that the SSIS package can retrieve from or send information to the source. It doesn't have anything to do with setting up the package on a server.|||Exactly. It's essentially a wrapper around the connection, be it a database connection, file connection or whatever, that "manages" the connection for SSIS.

Connection Manager Property Expression Editor

Is it possible to use a property, say name, of an object ( say the connection object) in the "Property Expression" of that object? I would like to modify the Connection String property of a flat file connection manager to append date to it. To do this I need to be able to use the Name property of the connection manager in the Property Expression editor. How ever I get an error that it does not recognize name, it almost seems to suggest I can only use variables. I find it hard to believe since it seems like common requirement to be able to use properties of an object (connection manager) in modifying other properties of the object. Any help would be greatly appreciated.
Thanks.

I do not think it is possible. However, I guess you could base both properties on the same variable that will contain the name.

Thanks.

sqlsql

Connection Manager Property Expression Editor

Is it possible to use a property, say name, of an object ( say the connection object) in the "Property Expression" of that object? I would like to modify the Connection String property of a flat file connection manager to append date to it. To do this I need to be able to use the Name property of the connection manager in the Property Expression editor. How ever I get an error that it does not recognize name, it almost seems to suggest I can only use variables. I find it hard to believe since it seems like common requirement to be able to use properties of an object (connection manager) in modifying other properties of the object. Any help would be greatly appreciated.
Thanks.

Your suspicion is correct -- only variables and literals can be used in property expressions. In dataflow expressions, you also have access to columns in expressions. However, there is no support for object properties.

You might be able to use a script task to populate a variable with the value you require, and then use that in the expression.

Thanks

Mark

|||

Mark:

Is this (being able to use a property in the expression) something that can be considered in the future releases of SSIS? I am currently using a script to iterate thru my connections and changing the location, but since it can be done much easily using property expressions would like to get feedback if this is a reasonable enhancement request. How do I go about to request this feature?

Thanks.

|||Yeah, I don't understand your desires for this feature... Perhaps an example of what you would like to do is in order here...|||

Toofan wrote:

How do I go about to request this feature?

Try http://connect.microsoft.com/, and find the SQL area. You can post feedback, bugs and suggestions on there.

Name certainly would be a nice property to have acces to (read-only).

|||

DarrenSQLIS :

Thank you for the link and the feedback.

Phil:
My original post had an example where this feature would be great. Here is the bit more explanation, consider a package that exports data from several different flat files (for loading different targets). The source files are created daily with creation date appended to them (file_name_YYMMDD). The connection names are named so that just adding the date would give us the file name. If I can use the "Name" property in the Property Expression editor, then I would be able to set the Connection String property to the file name without requiring a script. Right now I use a script to iterate thru the connections and change the connection string in every run. Hope that helps and any feedback or workarounds (besides scripting, since I am already doing that) are welcome.

I added a feature request:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=282481

Thanks.

Connection Manager Property Expression Editor

Is it possible to use a property, say name, of an object ( say the connection object) in the "Property Expression" of that object? I would like to modify the Connection String property of a flat file connection manager to append date to it. To do this I need to be able to use the Name property of the connection manager in the Property Expression editor. How ever I get an error that it does not recognize name, it almost seems to suggest I can only use variables. I find it hard to believe since it seems like common requirement to be able to use properties of an object (connection manager) in modifying other properties of the object. Any help would be greatly appreciated.
Thanks.

Your suspicion is correct -- only variables and literals can be used in property expressions. In dataflow expressions, you also have access to columns in expressions. However, there is no support for object properties.

You might be able to use a script task to populate a variable with the value you require, and then use that in the expression.

Thanks

Mark

|||

Mark:

Is this (being able to use a property in the expression) something that can be considered in the future releases of SSIS? I am currently using a script to iterate thru my connections and changing the location, but since it can be done much easily using property expressions would like to get feedback if this is a reasonable enhancement request. How do I go about to request this feature?

Thanks.

|||Yeah, I don't understand your desires for this feature... Perhaps an example of what you would like to do is in order here...|||

Toofan wrote:

How do I go about to request this feature?

Try http://connect.microsoft.com/, and find the SQL area. You can post feedback, bugs and suggestions on there.

Name certainly would be a nice property to have acces to (read-only).

|||

DarrenSQLIS :

Thank you for the link and the feedback.

Phil:
My original post had an example where this feature would be great. Here is the bit more explanation, consider a package that exports data from several different flat files (for loading different targets). The source files are created daily with creation date appended to them (file_name_YYMMDD). The connection names are named so that just adding the date would give us the file name. If I can use the "Name" property in the Property Expression editor, then I would be able to set the Connection String property to the file name without requiring a script. Right now I use a script to iterate thru the connections and change the connection string in every run. Hope that helps and any feedback or workarounds (besides scripting, since I am already doing that) are welcome.

I added a feature request:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=282481

Thanks.

Connection Manager problem in ScriptComponent

Hi,

I am using OleDB source as well as Script component in my package. Both these component are using same database. But as of now I am using two connections for each of this comp as I am unable to make use of the OLE DB connection in script component.

How can I solve this. I mean how can I use the the OLE DB conn which I used in OLEDB Source comp in the Script Component.

Any help will be greatly appreciated.

Thanks

For script component, create and use ADO.NET connection manager.

OLEDB connection is not meant to be used from .NET. If you really need to use it, you should create a custom component in C++, rather than using script component.

Connection manager permission issue

Hi there,

I apologize upfront if this is an old issue but i couldn't find the solution after searching quite a bit.

I have a OLE db connection manager and an ADO.net db connection manager and both of them fail when i run them in VS dev environment:

Connection may not be configured correctly or you may not have the right permissions on this connection

When I set up my connection managers, they do connect fine but no matter which one i try to use with an Execute sql task, the above error keeps popping up.

The connection managers are both set up to use SQL Authentication and the database is not on my local machine but instead on a server and my package security is set to 'DontSaveSensitive'. I have not yet deployed so this is all in my VS.net dev environment where it's failing.

I have also turned off the firewall on both the development server and my pc. All I want to be able to do is let the connection managers point the the dev server as the db is on there.

Any suggestions?

Regards
Mike
Hi,

I found a solution for the problem and thought it might be useful to post it for those that seek to develop using a remote dev server that contains the database.

I declared a variable that contained the connectionstring for the specific connection manager (note that the ado.Net connection string will look slightly different than oledb for instance) and in the connection manager's expression property pointed it to the variable.

I got pointed into this direction by reading the article 'Suggested best practices' by Jamie Thomson :
http://blogs.conchango.com/jamiethomson/archive/2006/01/05/2554.aspx

Thanks for that excellent article Jamie
Regards
Michael

Connection manager or Data Source

For seting up the connection should we use Connection Manager or New Connection From Data Source?

The advantage of using Data Source is that we have the connection avalilable at project level but I've studied it somewhere that Data Source is used for SSAS not SSIS.

But I don't have enough explanation to give it to my team, that why I am not using Data Source.

We are working on VSS, do we have to reset the connection managers if want to test our packages on some other machine(else than the one on which it was developed)?

I would use connection managers. (Actually, I DO use connection managers). Using package configurations, you can make the connections "dynamic" so-to-speak.|||

Paarul wrote:

For seting up the connection should we use Connection Manager or New Connection From Data Source?

The advantage of using Data Source is that we have the connection avalilable at project level but I've studied it somewhere that Data Source is used for SSAS not SSIS.

But I don't have enough explanation to give it to my team, that why I am not using Data Source.

We are working on VSS, do we have to reset the connection managers if want to test our packages on some other machine(else than the one on which it was developed)?

Both SSIS and SSAS can use data sources; but in SSIS they are optional. On my first SSIS projects I started using data sources until I realized SSIS checks the connection manager properties against the DS definition and gives annoying warnings about they being out of sync (usually the order of the parameters in the connection string)...and that happened every time you open a package. So, now I am creating the connection managers without using DS.

sqlsql

Connection Manager now showing "New Connection From Data Source..."

Hello,

I've created a SSIS Solution and have created Data Sources. I have two packages. One was created before the Data Sources, and one was created after. The package that was created after is using connections from the Data Sources. I want to change the package before the Data Soruces were created to use them, but when I right click in the Connection Managers pane "New Connection From Data Source.." is not an option.

Did I not add it to the Solution properly?

How do I get it to show?

Did I not refresh something?

Please provide the how if you figure it out.

Thanks

I Don't know why you cannot see that option. In my opinion, a connection manager based in a Data Source does not add any benefit, and it may represent an issue. If you ever need to change the connection string in the CM in the package; BIDS will generate a warning because DS and CM are out of sync....it will not sync them for you. While that won't cause the package to fail; it is certanly annoy during design time.|||if the first package is not part of the solution (you just opened a dtsx file without a solution) then it will not see the datasources from the solution.|||

One thing that worked was to right click on the child package and select "Reload with Upgrade" This gave me another version of the child package that uses the shared Data sources. However, whenever I run the wrapper it runs the older version of the package. I couldn't get it to work using the child package, so to make sure everything was clean. I started over and created a wrapper package that calls the child package and they share Data Sources and a XML Config file to set the connections. Everything was working fine. The child was using the Data Sources in the Connection manager.

I relocated the packages and repointed the XML Config path to the new location. When I ran the wrapper the child package used OLEDB connections instead of the icon of using the Data Source Connection. So I was back again. I selected "Reload with Upgrade", and it generated another version of the child package, with my original connections of using the shared Data Sources. When I run the wrapper, it runs an older version of the child package.

How do I manage the versions? How do I use the new package that is created after the "Reload with Upgrade"? How do I get the wrapper to run the correction version of the child package?

Thanks

Connection Manager not showing "New Connection From Data Source..."

Hello,

I've created a SSIS Solution and have created Data Sources. I have two packages. One was created before the Data Sources, and one was created after. The package that was created after is using connections from the Data Sources. I want to change the package before the Data Soruces were created to use them, but when I right click in the Connection Managers pane "New Connection From Data Source.." is not an option.

Did I not add it to the Solution properly?

How do I get it to show?

Did I not refresh something?

Please provide the how if you figure it out.

Thanks

I Don't know why you cannot see that option. In my opinion, a connection manager based in a Data Source does not add any benefit, and it may represent an issue. If you ever need to change the connection string in the CM in the package; BIDS will generate a warning because DS and CM are out of sync....it will not sync them for you. While that won't cause the package to fail; it is certanly annoy during design time.|||if the first package is not part of the solution (you just opened a dtsx file without a solution) then it will not see the datasources from the solution.|||

One thing that worked was to right click on the child package and select "Reload with Upgrade" This gave me another version of the child package that uses the shared Data sources. However, whenever I run the wrapper it runs the older version of the package. I couldn't get it to work using the child package, so to make sure everything was clean. I started over and created a wrapper package that calls the child package and they share Data Sources and a XML Config file to set the connections. Everything was working fine. The child was using the Data Sources in the Connection manager.

I relocated the packages and repointed the XML Config path to the new location. When I ran the wrapper the child package used OLEDB connections instead of the icon of using the Data Source Connection. So I was back again. I selected "Reload with Upgrade", and it generated another version of the child package, with my original connections of using the shared Data Sources. When I run the wrapper, it runs an older version of the child package.

How do I manage the versions? How do I use the new package that is created after the "Reload with Upgrade"? How do I get the wrapper to run the correction version of the child package?

Thanks

connection manager losing credentials

On my SSIS package I have 2 connection managers, both are connecting to an Oracle db. I enter in the ID and Password then click 'test connection' I'm able to connect to the database fine. I then go to a data flow of one of my control flow task. Open up my OleDb Source and click preview. The SQL query executes with no issues. I can see the data from my connection manager source.

I then go and run the package and I get this error message:

[OLE DB Source [1]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "OracleConnection" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

I've been racking my brain on this for a few days and still no luck on getting this to work.

You probably have the 'protection level' property of the package set to 'dontsavesensitive'. You could either change this to 'encryptsensitivewithuserkey' or set the password property of the connection manager through a configuration.

|||

I've done both, changed the package property and tried a config file and I get the same results.

|||Enable package logging, or if you are using BIDS, check the progress tabs and see if you any warning about package configurations.

Package configuration should solve the problem. This KB article has some related info:

http://support.microsoft.com/kb/918760|||

I got it working actually now, well sort of.

I had to remove all of my connection managers, close down BIDs open it back up, add new connection managers, change the package property setting again and it work.

Now I'm running into another issue

I have one package (dtsx file) that calls other dtsx file (all within the same solution) and its unable to connect to the packages it needs to call, though all the packages are in the same solution and the same database

|||Please provide more details:

How are you running the packages (BIDS, DTEXEC, etc)?

Where are the packages stored?

Error messages?|||

I'm trying to run it in BIDS right now and I get this error message:

Error: Error 0xC0012050 while preparing to load the package. Package failed validation from the ExecutePackage task. The package cannot run. .

If I deploy them to my SQL server and try to execute it from there (right click, execute), I'm getting this error message:

Error: Error 0xC0012050 while preparing to load the package. Package failed validation from the ExecutePackage task. The package cannot run. .

and I list of warnings saying it can't find the connection manager and so on.

|||You have a problem here. If you remove a connection manager; you need to go to every single place where that connection manager was being referenced and provide a new/valid connection manager.

The message you are getting indicates that the package failed validation; that means SSIS won't even try to run it. You need to remove all the references to that connection manager(s) you deleted.|||

This is driven my insane.

I can now run the SSIS package in BIDS, but I get the error:

'the acquireconnection method call to the connection manager 'oracle' has failed

and I've removed all of the connection managers used and created new ones and I still get the above error on my SQL server

Connection Manager Keeps External Metadata information

We have a Main package and which is calling 2 more other packages.
The first package contains a connection and we are using a Dataflow task. The data flow task has OleDB Data source which is taking getting columns using a Stored Procedure. And the output we need to write in a Flat File.

The second Package also contains the same(The same Tasks, Database and Stored Procedure Calling)
The difference is in the stored procedure Parameters. Based on the different parameters Stored procedures returns the different Columns and Rows output.
When we are trying to Get the second package output in OleDb Data source it shows all the columns which is the output of the First Package because it stores External Meta Data.

So My understanding is the Connection to the same database keeps the External metadata information with the connection and because of that it is always getting the same output columns in Ole DB Data source task in the second Package also.

How to Get my correct output from the second package in this case?
Or If we dont want to store external Meta data with the Connection then is that possible? If yes then How?

Thanks in advance.

The metadata about the columns is not kept at the connection manager level; it is part of the OLE DB component. Have you tried to delete the OLE DB source component and adding a new one? Or , perhaps using the preview button.

|||Thanks for your reply.

We have tried by removing the OLEDB task and also Tried to create with New one but it didnt worked.

We also have tried using New connection and New OLE db task that also didnt worked.

Then we tried by adding the new package and the same thing happend.

But when we are using a other stored procedure with the same connection and same OLE DB Source task that works fine.
If we want to use the same with the same stored procedure name with different parameter then how it is possible?

If I want to clear the Meta data for the same then is there any way to do it?

Thanks.

Connection Manager in SSIS packages

I am trying to run a package outside of the Business Intelligence development Studio. It continuously fails and tell me that my username cannot logon due to a password failure. When I check my connections the password never shows in the connection string and remains blank in the connection properties although I have checked Save Password in the connection properties. Does anyone know how I can save the password in the connection string being utilized in the SSIS package?Passwords are not shown in the properties due to security. I am assuming you are using SQL Server authentication instead of Windows Authentication. When you specify the password and save password, it saves it. But if you open the connection manager again, it won't show you the password (though it is still saved). However, to be able to leave it in the current state, you should now click Cancel to exit the connection manager, instead of OK. If you press OK, it will expect you to enter the password again. I think you must have opened the connection manager, and then pressed Ok, instead of Cancel, which erased the password.|||

I don't think that's the problem. I experiencing the same problem in my development Environment (using SQL Server authentication):

We're a group of developers working on the same package. after setting the connection managers with the correct username and password (and checking the save option) and saving the package by a certain user, when a different user accessing it, he cannot run the package and have to go through all the connection managers and ser the user and passwords all over again.

Any solution?

|||

Liran,

Check the ProtectionLevel property of your package. My guess is that you have ProtectionLevel=EncryptSensitiveWithUserKey

This means that all passwords are encrypted with a user-specific value meaning that only that user can run it.

If you follow best practice (http://blogs.conchango.com/jamiethomson/archive/2006/01/05/2554.aspx) of making your packages location-independant (http://www.windowsitpro.com/SQLServer/Article/ArticleID/47688/SQLServer_47688.html) then this should not be a problem.

-Jamie

sqlsql