Looking for some general feedback on working with SQL, SSIS and SAP

This is less of a specific question and more of a request for for some advice as to possibilities and directions. Here's the current situation. My company is using SAP for its purchasing, inventory, etc. This system is pretty much opaque to me - it's managed by another group within the company, and changes to it go through a complicated approval process. At the same time, the majority of our users, internal and external, are looking at this same data through a more accesible and more user friendly collection of web applications - done in classic ASP, up through ASP.NET 1.1 and 2 - and stored in an assortment of MS-SQL 2000 databases. Data is exchanged between SQL and SAP via DTS packages, some nightly, some run more frequently.

There's some issues here - data is never quite synchronized between the two sides, sometimes the same data must be updated twice, leading to possible data integrity issues, etc. Given that, we're going to be moving to SQL 2005 within the next year or so. From everything I've understood, within that context, there are vastly better ways of dealing with out situation than the way we're currently doing it.

So what I'm looking for is just a general impression of what can be done, with SSIS and SAP. Any approaches that might prove more fruitful, an y pitfalls to watch out for, that sort of thing.

How are you pulling data from SAP to SQL using DTS?

With SQL 2005, we have a preview version of the SAP .NET Data Provider for use within SSIS. You can check it out http://msdn2.microsoft.com/en-us/library/ms141761(SQL.90).aspx

SSIS will also be supported by the upcoming Biztalk R2 Adapter pack - which has adapters for SAP, Oracle & Siebel.

Go to https://connect.microsoft.com/ and look for this adapter pack - you can try out Beta2 around end-July.

|||

Currently we're not exactly pulling data. We're exporting data from SAP to a collection of flat files. Then we have a number of DTS packages that run - most nightly, one every 4 hours - and import the data into our database. Which seems damn clumsy.

Thanks for pointing me towards the .NET Data Provider, which I had a vague notion of, and to the Biztalk beta, which is something I hadn't heard of. I'll have plenty of things to research.

Looking for some examples link server, excel file

I have excel file and need to create a link server so I can read excel file
from sql2k.
Server where file is residing is SERVER1
Excel file name is ABC
User name is XYZ
password is PASSWORD
and the column that I want to retreive is Price.
I would appreciate if someone please show me an example for Link server
Thanks
msnews.microsoft.com wrote:
> I have excel file and need to create a link server so I can read excel file
> from sql2k.
> Server where file is residing is SERVER1
> Excel file name is ABC
> User name is XYZ
> password is PASSWORD
>
> and the column that I want to retreive is Price.
> I would appreciate if someone please show me an example for Link server
> Thanks
>
>
Hi
Try to look up "sp_addlinkedserver" in Books On Line. Here you have the
syntax for adding an Excel file as a linked server.
Regards
Steen

Looking for some examples link server, excel file

I have excel file and need to create a link server so I can read excel file
from sql2k.
Server where file is residing is SERVER1
Excel file name is ABC
User name is XYZ
password is PASSWORD
and the column that I want to retreive is Price.
I would appreciate if someone please show me an example for Link server
Thanksmsnews.microsoft.com wrote:
> I have excel file and need to create a link server so I can read excel fil
e
> from sql2k.
> Server where file is residing is SERVER1
> Excel file name is ABC
> User name is XYZ
> password is PASSWORD
>
> and the column that I want to retreive is Price.
> I would appreciate if someone please show me an example for Link server
> Thanks
>
>
Hi
Try to look up "sp_addlinkedserver" in Books On Line. Here you have the
syntax for adding an Excel file as a linked server.
Regards
Steen

Looking for some advice.....

I have a couple of files that I have that are comma seperated, and am looking for the best way to take those files, but them in a temp location to run some sql up against, and than update or insert a production sql database based on a SP i have written that takes a number of variable. I have played around with using the recordset destination and defining all the variables and than using a for each loop but seem to be stuck. Somewhat new to the whole programming field, and to be honest, seems a little intimidating with the little I know of the programming side. Just looking for some thoughts.You could use a data flow to read the flat file, and use an OLEDB Destination to save it to a "temp" table. Use an Execute SQL to update the temp table, then use a second data flow to retrieve the data and send it to an OLE DB Command to call your stored procedure.

Looking for Solution to Remote Data Entry

I'm looking for different solution people have implemented to solve this
problem.
Problem: People need to work remotely. While working remotely they want to
gather information. This information might be survey info, or inspection
info, etc. This information is to be stored remotely on a handheld, or
laptop device. After they get back to the office/homeo office they want to
upload the information to our corporate database, which is SQL Server 2000.
This upload process should be very easy, like a like of a button, or done
automatically when the devices is docked into the corporate network. This
solution has to be supported over the Internet, and intranet.
What I'm looking for is what technologies and processes have people put in
places to implement a solution to handle the problem I stated above.
If you have more questions about what I am asking for please let me know.Replication is the MS SQL Server technology for this.
Quentin
"Greg Larsen" <GregLarsen@.discussions.microsoft.com> wrote in message
news:C353A7E9-0D0E-4DA4-85E8-6EE8F6D754AD@.microsoft.com...
> I'm looking for different solution people have implemented to solve this
> problem.
> Problem: People need to work remotely. While working remotely they want
> to
> gather information. This information might be survey info, or inspection
> info, etc. This information is to be stored remotely on a handheld, or
> laptop device. After they get back to the office/homeo office they want
> to
> upload the information to our corporate database, which is SQL Server
> 2000.
> This upload process should be very easy, like a like of a button, or done
> automatically when the devices is docked into the corporate network. This
> solution has to be supported over the Internet, and intranet.
> What I'm looking for is what technologies and processes have people put in
> places to implement a solution to handle the problem I stated above.
> If you have more questions about what I am asking for please let me know.
>|||Are you saying you would use replication from a CE or Laptop device back to
your corporate database?
What other technologies might be available. I'm looking for all the
different technologies so I can determine what might be best and worst
methods.
"Quentin Ran" wrote:
> Replication is the MS SQL Server technology for this.
> Quentin
>
> "Greg Larsen" <GregLarsen@.discussions.microsoft.com> wrote in message
> news:C353A7E9-0D0E-4DA4-85E8-6EE8F6D754AD@.microsoft.com...
> > I'm looking for different solution people have implemented to solve this
> > problem.
> >
> > Problem: People need to work remotely. While working remotely they want
> > to
> > gather information. This information might be survey info, or inspection
> > info, etc. This information is to be stored remotely on a handheld, or
> > laptop device. After they get back to the office/homeo office they want
> > to
> > upload the information to our corporate database, which is SQL Server
> > 2000.
> > This upload process should be very easy, like a like of a button, or done
> > automatically when the devices is docked into the corporate network. This
> > solution has to be supported over the Internet, and intranet.
> >
> > What I'm looking for is what technologies and processes have people put in
> > places to implement a solution to handle the problem I stated above.
> >
> > If you have more questions about what I am asking for please let me know.
> >
> >
>
>|||> Are you saying you would use replication from a CE or Laptop device back
> to
> your corporate database?
Yes that's what I am saying.
> What other technologies might be available. I'm looking for all the
> different technologies so I can determine what might be best and worst
> methods.
I am a SQL Server guy, not too much into other applications. One thing I
see can be useful is message queueing.
> "Quentin Ran" wrote:
>> Replication is the MS SQL Server technology for this.
>> Quentin
>>
>> "Greg Larsen" <GregLarsen@.discussions.microsoft.com> wrote in message
>> news:C353A7E9-0D0E-4DA4-85E8-6EE8F6D754AD@.microsoft.com...
>> > I'm looking for different solution people have implemented to solve
>> > this
>> > problem.
>> >
>> > Problem: People need to work remotely. While working remotely they
>> > want
>> > to
>> > gather information. This information might be survey info, or
>> > inspection
>> > info, etc. This information is to be stored remotely on a handheld, or
>> > laptop device. After they get back to the office/homeo office they
>> > want
>> > to
>> > upload the information to our corporate database, which is SQL Server
>> > 2000.
>> > This upload process should be very easy, like a like of a button, or
>> > done
>> > automatically when the devices is docked into the corporate network.
>> > This
>> > solution has to be supported over the Internet, and intranet.
>> >
>> > What I'm looking for is what technologies and processes have people put
>> > in
>> > places to implement a solution to handle the problem I stated above.
>> >
>> > If you have more questions about what I am asking for please let me
>> > know.
>> >
>> >
>>

Looking for Solution to Remote Data Entry

I'm looking for different solution people have implemented to solve this
problem.
Problem: People need to work remotely. While working remotely they want to
gather information. This information might be survey info, or inspection
info, etc. This information is to be stored remotely on a handheld, or
laptop device. After they get back to the office/homeo office they want to
upload the information to our corporate database, which is SQL Server 2000.
This upload process should be very easy, like a like of a button, or done
automatically when the devices is docked into the corporate network. This
solution has to be supported over the Internet, and intranet.
What I'm looking for is what technologies and processes have people put in
places to implement a solution to handle the problem I stated above.
If you have more questions about what I am asking for please let me know.
Replication is the MS SQL Server technology for this.
Quentin
"Greg Larsen" <GregLarsen@.discussions.microsoft.com> wrote in message
news:C353A7E9-0D0E-4DA4-85E8-6EE8F6D754AD@.microsoft.com...
> I'm looking for different solution people have implemented to solve this
> problem.
> Problem: People need to work remotely. While working remotely they want
> to
> gather information. This information might be survey info, or inspection
> info, etc. This information is to be stored remotely on a handheld, or
> laptop device. After they get back to the office/homeo office they want
> to
> upload the information to our corporate database, which is SQL Server
> 2000.
> This upload process should be very easy, like a like of a button, or done
> automatically when the devices is docked into the corporate network. This
> solution has to be supported over the Internet, and intranet.
> What I'm looking for is what technologies and processes have people put in
> places to implement a solution to handle the problem I stated above.
> If you have more questions about what I am asking for please let me know.
>
|||Are you saying you would use replication from a CE or Laptop device back to
your corporate database?
What other technologies might be available. I'm looking for all the
different technologies so I can determine what might be best and worst
methods.
"Quentin Ran" wrote:

> Replication is the MS SQL Server technology for this.
> Quentin
>
> "Greg Larsen" <GregLarsen@.discussions.microsoft.com> wrote in message
> news:C353A7E9-0D0E-4DA4-85E8-6EE8F6D754AD@.microsoft.com...
>
>
|||> Are you saying you would use replication from a CE or Laptop device back
> to
> your corporate database?
Yes that's what I am saying.

> What other technologies might be available. I'm looking for all the
> different technologies so I can determine what might be best and worst
> methods.
I am a SQL Server guy, not too much into other applications. One thing I
see can be useful is message queueing.
[vbcol=seagreen]
> "Quentin Ran" wrote:

Looking for Solution to Remote Data Entry

I'm looking for different solution people have implemented to solve this
problem.
Problem: People need to work remotely. While working remotely they want to
gather information. This information might be survey info, or inspection
info, etc. This information is to be stored remotely on a handheld, or
laptop device. After they get back to the office/homeo office they want to
upload the information to our corporate database, which is SQL Server 2000.
This upload process should be very easy, like a like of a button, or done
automatically when the devices is docked into the corporate network. This
solution has to be supported over the Internet, and intranet.
What I'm looking for is what technologies and processes have people put in
places to implement a solution to handle the problem I stated above.
If you have more questions about what I am asking for please let me know.Replication is the MS SQL Server technology for this.
Quentin
"Greg Larsen" <GregLarsen@.discussions.microsoft.com> wrote in message
news:C353A7E9-0D0E-4DA4-85E8-6EE8F6D754AD@.microsoft.com...
> I'm looking for different solution people have implemented to solve this
> problem.
> Problem: People need to work remotely. While working remotely they want
> to
> gather information. This information might be survey info, or inspection
> info, etc. This information is to be stored remotely on a handheld, or
> laptop device. After they get back to the office/homeo office they want
> to
> upload the information to our corporate database, which is SQL Server
> 2000.
> This upload process should be very easy, like a like of a button, or done
> automatically when the devices is docked into the corporate network. This
> solution has to be supported over the Internet, and intranet.
> What I'm looking for is what technologies and processes have people put in
> places to implement a solution to handle the problem I stated above.
> If you have more questions about what I am asking for please let me know.
>|||Are you saying you would use replication from a CE or Laptop device back to
your corporate database?
What other technologies might be available. I'm looking for all the
different technologies so I can determine what might be best and worst
methods.
"Quentin Ran" wrote:

> Replication is the MS SQL Server technology for this.
> Quentin
>
> "Greg Larsen" <GregLarsen@.discussions.microsoft.com> wrote in message
> news:C353A7E9-0D0E-4DA4-85E8-6EE8F6D754AD@.microsoft.com...
>
>|||> Are you saying you would use replication from a CE or Laptop device back
> to
> your corporate database?
Yes that's what I am saying.

> What other technologies might be available. I'm looking for all the
> different technologies so I can determine what might be best and worst
> methods.
I am a SQL Server guy, not too much into other applications. One thing I
see can be useful is message queueing.
[vbcol=seagreen]
> "Quentin Ran" wrote:
>