Re: Sybase Source. What "data flow source" to use?

Tech-Archive recommends: Fix windows errors by optimizing your registry

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:

Is there not a Sybase OLE DB Driver?

--


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


"JJ of Eugene OR" <JJwithQuestions@xxxxxxxxxxxxxxxxx> wrote in message
news:6A80D2D0-5EB5-4D0A-880C-FFB608EC2C51@xxxxxxxxxxxxxxxx
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:

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/library/ms345157(SQL.90).aspx

--


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


"JJ of Eugene OR" <JJwithQuestions@xxxxxxxxxxxxxxxxx> wrote in message
news:45A8E249-B1AB-4B0E-B80E-7FD081AC422A@xxxxxxxxxxxxxxxx
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






.

Relevant Pages

  • Re: multi table multi database join?
    ... dont i have some kind of filter? ... sybase runns on one machine mssql on another, ... the data from the aggregate db could be used in a query to ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Sybase Source. What "data flow source" to use?
    ... Is there not a Sybase OLE DB Driver? ... I've been so excited about SSIS and telling my co-workers how ... Sybase seems like a no-brainer need to me. ... that the connection is valid and and I'm even able to get the ODBC ...
    (microsoft.public.sqlserver.dts)
  • How to handle a record when its lock
    ... This is the query which update records in sybase database. ... oDBUtil.OpenADORecordSet sqlquery, cnADOSQL, rsADO, 0, adCmdUnknown ... ' send update query to table dbtr in Debtmaster ...
    (microsoft.public.vb.general.discussion)
  • Re: combining inner and outer joins
    ... the query that works for you in Sybase, and the output you're looking for. ... > I've recently switched from Sybase to SQL Server, ... > of an outer join clause. ...
    (microsoft.public.sqlserver.programming)
  • Re: Result Set manipulation
    ... Sybase ASA 9 has an optional "START AT" clause as part of its TOP ... internally, though, and I believe you have to reissue the query if you ... Most, if not all, of the tables involved do not have identity keys, so ... Any thoughts or ideas on how to accomplish this would be appreciated. ...
    (comp.databases.sybase)