sql server dts:
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,
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 [quoted text, click to view] "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@newsgroups.nospam> wrote in message > news:45A8E249-B1AB-4B0E-B80E-7FD081AC422A@microsoft.com... > >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/library/ms345157(SQL.90).aspx -- Allan Mitchell www.SQLDTS.com www.SQLIS.com www.Konesans.com [quoted text, click to view] "JJ of Eugene OR" <JJwithQuestions@newsgroups.nospam> wrote in message news:45A8E249-B1AB-4B0E-B80E-7FD081AC422A@microsoft.com... >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
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 [quoted text, click to view] "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@newsgroups.nospam> wrote in message > news:6A80D2D0-5EB5-4D0A-880C-FFB608EC2C51@microsoft.com... > > 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@newsgroups.nospam> wrote in message > >> news:45A8E249-B1AB-4B0E-B80E-7FD081AC422A@microsoft.com... > >> >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 > >> > >> > >> > >
Is there not a Sybase OLE DB Driver? -- Allan Mitchell www.SQLDTS.com www.SQLIS.com www.Konesans.com [quoted text, click to view] "JJ of Eugene OR" <JJwithQuestions@newsgroups.nospam> wrote in message news:6A80D2D0-5EB5-4D0A-880C-FFB608EC2C51@microsoft.com... > 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@newsgroups.nospam> wrote in message >> news:45A8E249-B1AB-4B0E-B80E-7FD081AC422A@microsoft.com... >> >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 >> >> >>
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 [quoted text, click to view] ""privatenews"" 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. > >
Glad it worked out -- Allan Mitchell www.SQLDTS.com www.SQLIS.com www.Konesans.com [quoted text, click to view] "JJ of Eugene OR" <JJwithQuestions@newsgroups.nospam> wrote in message news:C04DD170-1A8D-4482-9379-E7EFF3E7CDC3@microsoft.com... > 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: > >> 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. >> >> >>
Eugene, Do you mind sharing what kind of third party software includes the OLE DB driver that works with Sybase and SSIS? Thanks, Alex [quoted text, click to view] JJ of Eugene OR wrote: > 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: > > > 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, 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 [quoted text, click to view] "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@newsgroups.nospam> wrote in message > news:6A80D2D0-5EB5-4D0A-880C-FFB608EC2C51@microsoft.com... > > 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@newsgroups.nospam> wrote in message > >> news:45A8E249-B1AB-4B0E-B80E-7FD081AC422A@microsoft.com... > >> >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 > >> > >> > >> > >
Don't see what you're looking for? Try a search.
|