February 3, 2012 at 9:29 am
This is the first time I am using SSIS. I am unable to create a connection to my SQL 2008 server using SQL Server Import and Export Wizard.
I set the destination to: Microsoft OLE DB Provider for SQL Server, use SQL Server Authentication, provide the correct user name and password. When I attempt to expand the DB drop-down, I get the message:
Could not retrieve table list: Invalid connection string attribute (Microsoft OLE DB Provider for SQL Server)
Any help would be appreciated.
Jay
February 3, 2012 at 9:43 am
Jay
Are you specifying the server name correctly, complete with instance name if appropriate and port number if necessary? You could try either looking in the errorlog of the server (if it is configured to record failed login attempts) or run a trace against the server to see whether you're getting as far as the server itself.
John
February 3, 2012 at 9:55 am
Since you say you are new to SSIS, I want to make sure that you have first created the connection mgr successfully?
Also, are you able to connect to whatever sources you are starting from, or is it just SQL Server destination causing problems?
not sure if this will have anythign to do with it, as we always use Don't Save Sensitive, but check package encryption settings.
February 3, 2012 at 10:52 am
I am so new to SSIS that this is my 1st package using the AdventureWorks tutorial. However, I have been using DTS for several year so I am familiar with the concepts. I am authenticating using 'sa' which has all rights. I know the password is correct since I get a different message if I purposely enter an incorrect password. Here is an interesting point. If I use Windows authentication, then I can see the DB's. However, my AD account doesn't have rights to "drop and recreate a table", one of the tasks in the tutorial. How do I give my AD account sufficient rights to perform these tasks?
February 3, 2012 at 11:40 am
I resolved this challenge by using Windows authentication and adding myself as a user for the DB.
February 3, 2012 at 12:02 pm
great, glad to hear it!! We use Windows integrated security, so life is really easy for me so long as I don't accidentally encrypt a package.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply