I am trying to manually re-create a DTS package in SSIS. The data source is
a Sybase database (not in my control) and the destination is my own lovely
SQL Server 2005 database. I can't figure out how to add a data flow source
object/task/icon that will connect me to the Sybase data.

I added an ODBC connection manager that works great when I do an 'execute
SQL task' on the 'Control Flow' tab. For example, I am able to do a simple
query of the Sybase database in the 'Execute SQL Task'. This query works and
populates a package variable with the return result of the query. So, I know
that the connection is valid and and I'm even able to get the ODBC connection
manager to work in the Control Flow tab.

The Problem: Then I add a 'data flow task' and go to the Data Flow tab. My
goal is to do a query of the Sybase database and copy that data into my
destination. When I look at source options, I see OLE DB, Flat File, etc.,
but no ODBC. I tried to configure some of the other sources to work with an
ODBC source, but I couldn't figure out how to do it. For example, I
remembered a long time ago seeing something to the effect that OLE DB had an
ODBC option, but I'm not seeing anything like that in the options for
configuring a new OLE DB connection manager.

I bought the SSIS book and went over quite a bit of it, but it didn't answer
this question. I know how to import from flat files, etc., but not an ODBC
database. Also, I researched this discussion group and can see that this is
a very basic question which others have already figured out. There is
another post where someone is beyond the point where I'm at. But I'm stumped
at this point. Any help would be most appreciated.

Thank you,
- JJ, Eugene OR

You could use the DataReader source or you could flip this article around
and do it in a Script Component

http://msdn2.microsoft.com/en-us/lib...7(SQL.90).aspx

--


Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com


"JJ of Eugene OR" <JJwithQuestions (AT) newsgroups (DOT) nospam> wrote

Allan,

Thank you very much! One of those should work once I figure them out.

However: both options seem very complicated to me, especially compared to
DTS. I've been so excited about SSIS and telling my co-workers how superior
I think it is while they have been bemoaning the loss of DTS. Now, I'm
pretty disappointed in SSIS, because I think of DTS/SSIS as a basic tool for
getting data from one source to another. And being able to connect easily to
Sybase seems like a no-brainer need to me. It is also a task that I have to
do all the time. I'm familiar with VBA, but not vb.net, so aside from all
the typing (I have a LOT of tables and DTS packages to do this to), I will
have yet one more learning curve besides SSIS and SQL Server 2005.

The point: Does anyone know of another, easier solution in SSIS than
writing .NET code by hand or figuring out the data reader? Or am I out of
luck? If there is no solution now, is there any hope of a better way coming
out in a future version of SSIS? Perhaps I should delay upgrading to SQL
Server 2005 given how many data transfers I have to do?? I'm not being lazy,
I just have a huge amount of work an no time to do it. I have to be
practical.

Thanks,
- JJ, Eugene OR


"Allan Mitchell" wrote:

Is there not a Sybase OLE DB Driver?

--


Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com


"JJ of Eugene OR" <JJwithQuestions (AT) newsgroups (DOT) nospam> wrote

Allan:

There is currently no listing of a Sybase OLE DB Driver in the drop down
list of choices. But if I'm understanding the intent of your query
correctly, you are suggesting that I look to see if I can get/install such
software. That is another great idea! I'm going to look into it.

As a follow up on your previous posting: I looked into both ideas. Of the
two suggestions, the data reader idea seemed like the least amount of work.
After further investigating, it has turned out to be a great lead. Here's
what I did (for anyone who might be interested in the details). I:
1) added an ADO.NET connection manager with the ODBC Data provider option
(this is NOT an ODBC connection manager).
2) added a data flow task to the control flow tab
3) added a Data Reader Source to the data flow tab
4) configured the Data Reader Source. This involved: a] entering the ADO
connection manager created in step 1) above, b] adding the SQL Command to the
component properties tab, c] doing the column mappings tab

At this point, you would think I was golden, but I ran into a problem when I
tried to connect the Data Reader Source to my local database destination
source. When I ran the package, I got an error that said something like: the
unicode string data type would not convert into a non-unicode data type.
When I went back into the properties of the data reader, it told me that the
data type property for the columns could not be changed. Not to be deterred
at this point, I added the 'Data Conversion Transformation Editor' to my data
flow between the source and destination so that I could convert the data
types. And VIOLA!, it worked.

My opinion is that the data reader approach is still way more tedious than
what it took to do the same simple thing in DTS. However, it is also a WHOLE
lot better than what I had feared would be involved when I first read your
posting. Also, now that someone else thinks there could be such a thing as
an OLE DB Sybase provider (I thought an OLE DB provider might just be a
Microsoft thing), I'm going to look into that. That would solve the problem
completely.

Thanks again,
- JJ, Eugene OR


"Allan Mitchell" wrote:

Hi,

SQL 2005 does not include built in Sybase OLEDB provider. You may need to
try some third party driver

http://www.sqlsummit.com/oledbVen.htm

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

================================================== ===


This posting is provided "AS IS" with no warranties, and confers no rights.

Allan and Peter:

Final Follow-Up:
It turns out that my third party Sybase client software does include an
OLE DB provider. I had just never installed it before, because ODBC had
always worked so well. This morning, I installed the OLE DB driver and did
the configuring for a data source. Then I did a test in SSIS. It all worked
great! (And now SSIS is as easy to move data from Sybase to SQL Server as it
was in DTS.)

It sounds like the lamest question in the world now, but at the time, I was
really stumped. Thanks for your time.

- JJ, Eugene OR


""privatenews"" wrote:

Glad it worked out

--


Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com


"JJ of Eugene OR" <JJwithQuestions (AT) newsgroups (DOT) nospam> wrote

Eugene,

Do you mind sharing what kind of third party software includes the OLE
DB driver that works with Sybase and SSIS?

Thanks,
Alex

JJ of Eugene OR wrote:

Allan, I have a similar problem and I think JJ expressed very well the
frustration some of us are feeling. With SQLServer 2000, I and several
others, used DTS to transfer data from ODBC datasources defined on our
computers into SQL Databases. One in particular that I can no longer do is
Centura SQLBase, used by ADP Payroll. I'm sure it can be done by creating a
script, but I work with people who are not programmmers, but could still use
DTS to move data. To restate the problem, when I right-click over a SQL 2005
database and select Import Data, the list of providers does not include
either A) the list of datasources defined in ODBC Datasources on my computer,
or B) an OLEDB Provider For ODBC that I have seen before. In a nutshell, I
want to be able to transfer data from datasources in ODBC into SQL server. I
want to be able to do it through the point and click interface such as
existed in DTS. Is this at all possible?
--
NEXDEV


"Allan Mitchell" wrote: