OLE DB Destination Editor VS OLE DB connection VS ADO NET

Question

OLE DB Destination Editor VS OLE DB connection VS ADO NET

yrstruly

Hall of Fame

Points: 3440
More actions
May 6, 2019 at 6:51 pm

#3636600

I am having trouble displaying table as set out in the book "Microsoft SQL Server 2012 Integration Services: An Expert Cookbook" page:96. The Book states i must return to OLE DB Source Editor. In the OLE DB Source Editor, i am not able to view the table that has bee created. I can view the table with select * from user, but cant find it in the drop down. Please see screenshots? Is thi s an error in the book

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99126 More actions · Answer 1

I'm surprised that select * from user is even working. USER is a Reserved Keyword in SQL Server, and (Ideally) shouldn't be used for object names. If you have the reference the object, you have to reference an object with a name that is a Reserved Keyword, you have to quote the object name (I can't actually show you how, as it breaks the post see ). Not doing so would generate an error ("Incorrect syntax near the keyword 'user'").

Perhaps the table user wasn't created on the dbo schema but on a different default schema and you're looking in the wrong place for it? I can't say I've ever seen that behaviour. Otherwise, it could be the fact that SSIS doesn't like that you've used a Reserved Keyword for the object's name, so changing it to one that isn't may fix the problem.

This reply was modified 4 years, 11 months ago by Thom A.
This reply was modified 4 years, 11 months ago by Thom A.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

yrstruly Hall of Fame Points: 3440 More actions · Answer 2

Are you familiar with the book mentioned? I need to import data from MySQL db. Database n table is created as you can view in ssis screenshot. I cant view the table in ssis, thats the problem?

Thom A SSC Guru Points: 99126 More actions · Answer 3

yrstruly wrote:

Are you familiar with the book mentioned? I need to import data from MySQL db. Database n table is created as you can view in ssis screenshot. I cant view the table in ssis, thats the problem?

I'm not familiar with it all.

You didn't mention MySQL in your post, only SQL Server, so I assumed that that's where your pulling the data from. I can't comment for how SSIS reacts with MySQL, but that picture with points 13 and 14 appears to be referencing a SQL Server database, not a MySQL database. It's using a trusted connection which, from my Googling, only MySQL Enterprise supports and using the name . for the host/instance name (which would connect to the local SQL Server instance running on port 1433 with the default instance name MSSQLSERVER).

Edit: That picture, from Point 14, is of the OLEDB connection to SQL Server, your picture is from an ADO.NET connection. They aren't the same. Reading between the lines here, the ADO.NET is your source, and the OLEDB your destination. You can't compare those pictures as they aren't the same.

This reply was modified 4 years, 11 months ago by Thom A.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

yrstruly Hall of Fame Points: 3440 More actions · Answer 4

I downloaded a connection manager for MySQL and i can connect from SSIS to MySQL. I can use the Select query from SSIS as seen in above sql result set, but i cant view the table. Why is that?