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 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 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

Connection Manager - No available servers

Hi

Windows XP Pro, SQL Server 2005 (Developer) & Visual Studio 2005 (Pro)

New SSIS project (Visual Studio)

New OLE DB/SQL Native client connection.

There's no servers available in the drop down box.

All the services that need to be running are running.

Guess I've missed the obvious here

Cheers

Dave

Hi,

did you try to enter the nae manually ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Yes I did. Will re-try and then post the error.|||

Just tried entering the name manually and it worked...

Any ideas why I can't browse to the instance/server?

|||Do you use another port than the default one 1433 ? If so, you will have to make sure that SQL Server browser is started. What for ? See the link here for more information about the SQL Browser.

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

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

connection management

hi.. i have like 50 ssis packages,, most of all have the samne conexion to my target server.,, the user an pwd i'm using is the same for all of them... but the pwd has been changed because of security rules,., the result: these packages will no longer execute correctly .. is there anyway i can change this conexion for all the packages without doing it manually? and if so.. how?

thanks!!!!!

You should have used package configuration from the really beginning. If you didn't and you are planning to go over all the packages again; do that for adding the required package configurations to set the connection string of your connection manager at run time; that way next time a change of this nature happens you will have to update the connection string in a single place. Serach for package configurations to learn more about it.

Rafael Salas

Connection from SQL 2005 to Informix 2000

Hi All,

I'm trying to set up a SSIS package to pull data from a data sourse on an Informix 2000 server. I'm currently using an OpenLink driver to access the data through ODBC, however when I try to query it through BIDS or using the SQL management Studio's import feature it says that the objects don't exist for what I'm querying.. I can view the tables in the query builder, just not query them?

Has anyone any suggestions as to how I might be able to resolve this? The Informix server is not one that I can modify, so anything that I do will have to be done from the SQL server (running 2005 ent edition)

I'm able to query the tables and fields from another program, however can't output the results from this easily for SQL to use.

If you have any suggestions I'd greatly appreciate them. Let me know if you need any more information

Regards,
Steve

PS, I'm using SQL 2005 Ent Ed, OpenLink Multi Tier ODBC Version 4.2, Informix 2000 connected via TCP/IP, and have a username and password to connect (not a trusted connection)

Hallo,

I have the same Problem.

I think the problem is the sql-syntax.

SQL2005 generate this query: select * from [tablename]

informix need this: select * from tablename

bye

hardy

|||

Hey Steve,

I have the exact same error. Has anyone found the answer?

Mrlace2

Connection from SQL 2005 to Informix 2000

Hi All,

I'm trying to set up a SSIS package to pull data from a data sourse on an Informix 2000 server. I'm currently using an OpenLink driver to access the data through ODBC, however when I try to query it through BIDS or using the SQL management Studio's import feature it says that the objects don't exist for what I'm querying.. I can view the tables in the query builder, just not query them?

Has anyone any suggestions as to how I might be able to resolve this? The Informix server is not one that I can modify, so anything that I do will have to be done from the SQL server (running 2005 ent edition)

I'm able to query the tables and fields from another program, however can't output the results from this easily for SQL to use.

If you have any suggestions I'd greatly appreciate them. Let me know if you need any more information

Regards,
Steve

PS, I'm using SQL 2005 Ent Ed, OpenLink Multi Tier ODBC Version 4.2, Informix 2000 connected via TCP/IP, and have a username and password to connect (not a trusted connection)

Hallo,

I have the same Problem.

I think the problem is the sql-syntax.

SQL2005 generate this query: select * from [tablename]

informix need this: select * from tablename

bye

hardy

|||

Hey Steve,

I have the exact same error. Has anyone found the answer?

Mrlace2

Connecting to Sybase

Hi,

I am assuming someone has had success connecting to Sybase from SSIS. I am having trouble. And I freely admit naivety when it comes to something like this. :-)

I installed dbConnector 6.01 which has a Sybase connector as a part of the package. I then tried to create a connection manager for Sybase using OleDb, but it was not an option in the list of OleDb connections. So, am I correct in assuming that I need to use ODBC? If so, is there an OleDb Sybase driver that can be used with SSIS?

Any help in using SSIS to connect to Sybase would be appreciated.

Thanks,

- Joel

Joel,
I've had success connecting to Sybase ASE 12.5.X using OLEDB. The question that needs to be answered, is what type/version of Sybase are you running. Is it Sybase Adaptive Server Anywhere, Sybase Adaptive Server Enterprise, or Sybase SQL Anywhere and what version is it? Depending on the product/version you may be able to get OLE DB drivers from Sybase itself.
I got the OLE DB drivers from the PC Client software that was included in my installation of ASE.
Larry Pope
|||Thank you Larry for your reply. The Sybase database that I want to connect to is version 11.5.1.2.|||Joel,
I've searched the Sybase site and they no longer have any downloads for your version of the software. That isn't really surprising since it is over 5 years old.
If I may ask what type of system is using it? Is it hosting some custom application or is it supporting some commercial application?
If it is supporting a commercial product you might want to ask the vendor to see about OLE DB providers. If it is a standalone install supporting a custom application then you may need to check the original software media for the PC Client tools. I know in my case they were included on the same CD as the server software.
Larry Pope

|||

Hi Larry,

Thanks again for th reply. I think we got it figured out. We installed a more recent driver (ASE) and was able to connect to Sybase through ODBC and OleDb.

- Joel

|||

So we are trying to do something similar. SYBASE ASE 12.5.3

We downloaded the 12.5.1 SDK and installed. Our choices via SSIS as far providers was the same.

We downloaded the 15.0 SDK and installed. Our choices via SSIS as far providers changed to include Sybase OLEDB Provider.

We are still not having any luck connecting., Any help would be appreciated.

TIA

Connecting to Sybase

Hi,

I am assuming someone has had success connecting to Sybase from SSIS. I am having trouble. And I freely admit naivety when it comes to something like this. :-)

I installed dbConnector 6.01 which has a Sybase connector as a part of the package. I then tried to create a connection manager for Sybase using OleDb, but it was not an option in the list of OleDb connections. So, am I correct in assuming that I need to use ODBC? If so, is there an OleDb Sybase driver that can be used with SSIS?

Any help in using SSIS to connect to Sybase would be appreciated.

Thanks,

- Joel

Joel,

I've had success connecting to Sybase ASE 12.5.X using OLEDB. The

question that needs to be answered, is what type/version of Sybase are

you running. Is it Sybase Adaptive Server Anywhere, Sybase Adaptive Server Enterprise, or Sybase

SQL Anywhere and what version is it? Depending on the

product/version you may be able to get OLE DB drivers from Sybase

itself.

I got the OLE DB drivers from the PC Client software that was included in my installation of ASE.

Larry Pope

|||Thank you Larry for your reply. The Sybase database that I want to connect to is version 11.5.1.2.|||Joel,

I've searched the Sybase site and they no longer have any downloads for

your version of the software. That isn't really surprising since

it is over 5 years old.

If I may ask what type of system is using it? Is it hosting some

custom application or is it supporting some commercial

application?

If it is supporting a commercial product you might want to ask the

vendor to see about OLE DB providers. If it is a standalone

install supporting a custom application then you may need to check the

original software media for the PC Client tools. I know in my

case they were included on the same CD as the server software.

Larry Pope|||

Hi Larry,

Thanks again for th reply. I think we got it figured out. We installed a more recent driver (ASE) and was able to connect to Sybase through ODBC and OleDb.

- Joel

|||

So we are trying to do something similar. SYBASE ASE 12.5.3

We downloaded the 12.5.1 SDK and installed. Our choices via SSIS as far providers was the same.

We downloaded the 15.0 SDK and installed. Our choices via SSIS as far providers changed to include Sybase OLEDB Provider.

We are still not having any luck connecting., Any help would be appreciated.

TIA

Connecting to SSIS in Management Studio.

Hi:

I have 4 named instances of SQL 2005 running on one of our sales server (dont ask me why I have 4 instances.Its a beefy box btw). All the instances have SSIS Packages (around 6-7 in each instance) saved to the SQL server and not to the file system. The issue is every time i need to look at the packages or export the packages from SSMS I have to edit the MsDtsSrvr.ini.xml and type in the named instance name within the <ServerName></ServerName> tag . I then have to restart my SSIS. I dont see an easier approach to this method.

This is causing me a lot of unnecessary time waste. is there anyway this can be automated where in i can pass the instance name dynamically to the ini file or even more best, can I have all the instance names in the ini file and some how look at the packages in each Instance. I am not sure how having all the instance names in the ini file woud resolve the issue though.

I know I can use BIDS which is much more flexible and a recommended approach but need a solution for looking at SSIS packages through SSMS in all of the 4 instances. I look forward to recommendations from anyone who have better ideas and suggestions.

Thank you

AK

you could create a console app that edits the xml file and re-starts ssis.|||

Thanks Duane. Do you happen to have an example code sample on how to do that?. I can build looking from that.

Thanks again

AK

|||

Ankith wrote:

Thanks Duane. Do you happen to have an example code sample on how to do that?. I can build looking from that.

Thanks again

AK

unfortunately, i don't have a code example to share with you. however, writing such an application should be fairly straightforward (provided that you know .NET). the .NET system.xml namespace provides a number of classes to manipulate xml data. also, the system.serviceprocess namespace provides classes to manipulate windows services.

i hope this helps.

|||

Hi Duane:

I got this working. I am posting the code snippet for the benefit of others. I have created a SQL Agent job that changes the server name in the XML ini file and restarts SSIS. Restarting I do it through the Job step. Works great.

if (File.Exists(xmlFile))
{
XmlDocument doc = new XmlDocument();
doc.Load(xmlFile);

XmlNode node = doc.SelectSingleNode(@."//*[local-name()='ServerName']");

if (node == null)
{
Console.Write("Node does not exist");
}
else
{
node.InnerText = value;
}

node = null;

doc.Save(xmlFile);

Thanks again for your help.

AK

|||Instead of switching between the servers, you can simply add them all to the SSIS service config file! Simply copy the <FOLDER> tag (till the end - </FOLDER>) as many times as you have SQL instances, give each one unique <NAME>, save config file and restart the SSIS service:

...
<TopLevelFolders>
<Folder xsi:type="SqlServerFolder">
<Name>Sql1</Name>
<ServerName>.\Instance1</ServerName>
</Folder>
<Folder xsi:type="SqlServerFolder">
<Name>Sql2</Name>
<ServerName>.\Instance2</ServerName>
</Folder>
<Folder xsi:type="SqlServerFolder">
<Name>Sql3</Name>
<ServerName>.\Instance3</ServerName>
</Folder>
</TopLevelFolders>
...|||

WOW!!!. Thats cool. Thanks Michael.I used your solution and it works great and I dont need my code anymore. DBAs who dont have much experience with C# will find your method really helpful.

Thanks for posting it.

Best Regards

AK

Connecting to SSIS in Management Studio.

Hi:

I have 4 named instances of SQL 2005 running on one of our sales server (dont ask me why I have 4 instances.Its a beefy box btw). All the instances have SSIS Packages (around 6-7 in each instance) saved to the SQL server and not to the file system. The issue is every time i need to look at the packages or export the packages from SSMS I have to edit the MsDtsSrvr.ini.xml and type in the named instance name within the <ServerName></ServerName> tag . I then have to restart my SSIS. I dont see an easier approach to this method.

This is causing me a lot of unnecessary time waste. is there anyway this can be automated where in i can pass the instance name dynamically to the ini file or even more best, can I have all the instance names in the ini file and some how look at the packages in each Instance. I am not sure how having all the instance names in the ini file woud resolve the issue though.

I know I can use BIDS which is much more flexible and a recommended approach but need a solution for looking at SSIS packages through SSMS in all of the 4 instances. I look forward to recommendations from anyone who have better ideas and suggestions.

Thank you

AK

you could create a console app that edits the xml file and re-starts ssis.|||

Thanks Duane. Do you happen to have an example code sample on how to do that?. I can build looking from that.

Thanks again

AK

|||

Ankith wrote:

Thanks Duane. Do you happen to have an example code sample on how to do that?. I can build looking from that.

Thanks again

AK

unfortunately, i don't have a code example to share with you. however, writing such an application should be fairly straightforward (provided that you know .NET). the .NET system.xml namespace provides a number of classes to manipulate xml data. also, the system.serviceprocess namespace provides classes to manipulate windows services.

i hope this helps.

|||

Hi Duane:

I got this working. I am posting the code snippet for the benefit of others. I have created a SQL Agent job that changes the server name in the XML ini file and restarts SSIS. Restarting I do it through the Job step. Works great.

if (File.Exists(xmlFile))
{
XmlDocument doc = new XmlDocument();
doc.Load(xmlFile);

XmlNode node = doc.SelectSingleNode(@."//*[local-name()='ServerName']");

if (node == null)
{
Console.Write("Node does not exist");
}
else
{
node.InnerText = value;
}

node = null;

doc.Save(xmlFile);

Thanks again for your help.

AK

|||Instead of switching between the servers, you can simply add them all to the SSIS service config file! Simply copy the <FOLDER> tag (till the end - </FOLDER>) as many times as you have SQL instances, give each one unique <NAME>, save config file and restart the SSIS service:

...
<TopLevelFolders>
<Folder xsi:type="SqlServerFolder">
<Name>Sql1</Name>
<ServerName>.\Instance1</ServerName>
</Folder>
<Folder xsi:type="SqlServerFolder">
<Name>Sql2</Name>
<ServerName>.\Instance2</ServerName>
</Folder>
<Folder xsi:type="SqlServerFolder">
<Name>Sql3</Name>
<ServerName>.\Instance3</ServerName>
</Folder>
</TopLevelFolders>
...|||

WOW!!!. Thats cool. Thanks Michael.I used your solution and it works great and I dont need my code anymore. DBAs who dont have much experience with C# will find your method really helpful.

Thanks for posting it.

Best Regards

AK