SSIS - Import Package from ODBC Drivers

We have a C-Tree Database from Faircom.

I am able  to connect the database through their ODBC Drivers. I am trying to import the data from C-Tree Database to SQL 2008.

At present, how i am working is ..! I am able to connect MS Access 2007 - Using External ODBC Drivers - I am connecting to C-Tree Database.

Then from MS Access 2007, i am importing records to SQL 2008 using SSIS.

This is actually 2 steps and it is a disconnected architecture.

We want Import to be connected mode and directly from C-Tree Database to SQL 2008.

                        ( Or )

How to import record to SQL 2008 Database through ODBC Drivers?
ODBC.JPG
chokkaStudentAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
"How to import record to SQL 2008 Database through ODBC Drivers"
You can add a SQL Linked server using that ODBC driver then run SQL Statement like the one below toy import your data:

--create a SQL linked server called MY_LINKED_SERVER

--create destination table in SQL database if not there already and match source table columns/datatype
CREATE TABLE myschema.mytable
(
  id integer,
  name nvarchar(20),
  total numeric,
  arc_date datetime
)

--populate it
insert into  myschema.mytable
SELECT * FROM OPENQUERY(MY_LINKED_SERVER, 'select * from  CTree_table;')

--check inserted data
select count(*) from myschema.mytable

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lcohanDatabase AnalystCommented:
I believe you could use "c-treeACE SQL ODBC" to directly connect to your C-tree db and bypass Access if not mandatory, the create a ODBC data source on the SQL Server, then use steps above to add a SQL Linked server.

c-treeACE SQL ODBC
http://www.faircom.com/ace/ace_api_odbc_t.php

sql linked server:
http://msdn.microsoft.com/en-us/library/ms188279.aspx
chokkaStudentAuthor Commented:
While trying to created MySchema.MyTable

i am receiving following errors

Msg 2760, Level 16, State 1, Line 1
The specified schema name "myschema" either does not exist or you do not have permission to use it.
Expert Spotlight: Joe Anderson (DatabaseMX)

We’ve posted a new Expert Spotlight!  Joe Anderson (DatabaseMX) has been on Experts Exchange since 2006. Learn more about this database architect, guitar aficionado, and Microsoft MVP.

lcohanDatabase AnalystCommented:
What I posted is a pseudo code where you must replace al the "my" stuff with your actual names - "myschema" for instance would become "dbo"
chokkaStudentAuthor Commented:

What i am trying to do is..!

1)  I have created a new database.    LinkedDB

2) Then, i executed this script ..

CREATE TABLE myschema.mytable
(
  id integer,
  name nvarchar(20),
  total numeric,
  arc_date datetime
)

3) I already have created CTree connection in the Linked Server

lcohanDatabase AnalystCommented:
So all is left to populate the table in SQL database by running a command like the one below:

assuming your SQL db name is LinkedDB
assuming the linked server name is CTree
CTree_table will be the source table name from the linked setver CTree and it must have the same structure as destination table in SQL called mytable

in SQL SSMS query you need to execute statements like below:

use LinkedDB

--check data - should return 0 rows of the newly created table
select count(*) from myschema.mytable

--populate it
insert into  myschema.mytable
SELECT * FROM OPENQUERY(CTree, 'select * from  CTree_table')

--check inserted data
select count(*) from myschema.mytable

chokkaStudentAuthor Commented:

1) I have created a Table Name - Patient.  This table Patient has the same schema as per C-Tree Database.

2) On executing this query : select count(*) from Patient
I received - 0

3) On executing this query : SELECT * FROM OPENQUERY(CTree, 'select * from  Patient')

I received this error

OLE DB provider "MSDASQL" for linked server "CTree" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "CTree".
lcohanDatabase AnalystCommented:
What is the name of the Linked server? is it CTree?
Please start SQL Server Management studion, connect to your server and expand Server Objects->Linked servers. Use the name of the linked server in that list in the above statement instead of CTree if is not CTree.

You should be also able to expand that Linked server right there and see the Catalogs(databases) and tables if the connection is ok.
chokkaStudentAuthor Commented:
1) i have created two linked server connection for the same database (ODBC Driver)

CTREE
CTREESQL

             
LinkedServer.JPG
lcohanDatabase AnalystCommented:
Ok but is the source table name called Patient as well in CTREE or CTREESQL? you could check that by expanding the linked server in the above view and you should be able to see them all.
chokkaStudentAuthor Commented:
Icohan .. i am thankful for helping me.

There is a big mistake on my side which i noticed.

Linked Server connection has not been established properly.

I have attached the screen shot.
LinkedServer.JPG
lcohanDatabase AnalystCommented:
Sure - no problem. My suggestion is that whenever you create a new ODBC or any other connection you should always "test connection" before saveing it as this will save some headaches in the future.

Good luck!
chokkaStudentAuthor Commented:


I am recreating the Linked Server Connection.

How to identify the Server Name, User Name, Password for the ODBC Driver which i have created in my computer ?
chokkaStudentAuthor Commented:

I am not able to provide User Name / Password for ODBC Driver which i have created on my computer which is communicating with a Server Machine.


LinkedServer1.JPG
lcohanDatabase AnalystCommented:
if you look at the picture you see "Be made using this security context" and check that box then provide the userid and password for your destination linked server.
chokkaStudentAuthor Commented:
I dont know what userid and password for my destination linked server.


The database is set of flat files located in a different server,

File path of that server is ..

\\192.168.1.***\Rx30\MedEx

username / password for this server machine is
Rx30 / Rx30.

I am not sure, how to give and what i have to give in that window..!
chokkaStudentAuthor Commented:

I am also infront of their website
http://www.faircom.com/ace/enl_35_mssqllink_t.php
chokkaStudentAuthor Commented:
i face challenges on setting - Linked Server Connection between SQL 2008 Database and C- Tree Database.

I am looking for assistance to establish Linked Server Connection.
lcohanDatabase AnalystCommented:
OK - lest start from scrach again...:

1. You must create create a ODBC data source connection on the SQL Server using c-treeACE SQL ODBC:       http://www.faircom.com/ace/ace_api_odbc_t.php
Here you MUST know your credentials to connect to the "C-Tree Database " and test/save them with that ODBC data source.


2. Now that you have a ODBC connection the ServerName, Login and Password are saved in the ODBC connection you created on that server right? So next step is: Under the "General" page on the New Linked Server put your linked server name in there, select(check box) "Other Data Source", and use the ODBC data source name you just created above as "Data Source", and select "Microsoft OLE DB Provider for ODBC  Drivers" from the Provider drop down list.

That should be all. then you must be able to run a query like below in your SQL Server:

SELECT * FROM OPENQUERY(CTree, 'select * from  Patient')

chokkaStudentAuthor Commented:

1) I have created ODBC Connection using ODBC Drivers

2) From SQL 2008, I tried to establish Linked Connection - But still its not working.

Is there anyway for you to come remotely to help me

chokkaStudentAuthor Commented:
Thank you
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.