Hello,
I am trying to write my first couple Integration Services packages using SQL 2005. My configuration is a workstation running windows xp professional, and a windows 2003 server that is running the SQL server.
Anytime I run a package that accesses the remote server from my workstation, the job fails with an error code. The workstation cannot seem to run a package to load data to the remote sql server. Why is this? Is there a service pack, or hotfix coming out soon to correct this problem?
Additionally, I also seem to be unable to update a database using excel as the data source from which information should be used. If I import my excel spreadsheet into an access table, I can update the sql database from Access using integration services. Why can't I use an excel spreadsheet as the source? Is there a a service pack or hotfix coming out soon for 2005 sql that will correct this problem?
Thanks!
Jim
I've been able to input an excel spreadsheet in the dataflow and then use an Oracle destination to update an oracle table - what errors are you getting?|||Jim R wrote:
Is there a a service pack or hotfix coming out soon for 2005 sql that will correct this problem?
It would help if you told us what the problem was before anyone answers that question.
What errors are you getting?
|||What destination transform are you using?
If you are using the SQL Server Destination, then the package must run
on the destination server. The SQL Destination inserts data much
more efficiently than the OLE Destination.
If you are using the OLE DB Destination, then please post the error message you are receiving so that we help you debug.
Larry|||
I am using an SQL destination, but I don't understand why the package must run on the destination server for SQL server? That was never a requirement before with sql 2000's DTS services... Shouldn't I be able to develop, test, and even deploy packages to other servers?
Speaking of deploying. Once I have a package developed, how can I run it on the server machine? I don't intend to install the visual studio, etc. on the server....
Jim
|||Jim,
the SQL Server desination is a special destination adapter for LOCAL SQL Servers only... If you want to connect to remote servers you have to use the OLE DB destination... This is by design...
|||
Why is the SQL server destination for LOCAL SQL servers only? Isn't there a significant performance increase in using the SQL server type versus the OLEDB one when accessing a SQL server?
I also read some articles that state that the SSIS service does not get installed in the workstation edition of SQL server 2005, unless you use the Developer Edition. Can someone also explain why that is?
Thanks!
|||Hi,
to be honest: no idea. I only know that they did some "tricks" to make accessing local servers faster...
What do you mean with "workstation edition"? I'm not aware of this edition...
|||The Service is indeed not part of Workgroup Edition, as noted in the matrix in "Features Supported by the Editions of SQL Server 2005." However unlike the SQL Server or Analysis Services services, for example, the Integration Services service is not crucial to building and running packages, but merely provides some extra services, like monitoring running packages.
-Doug
|||
Even i have taken excel spreadsheet as input in dataflow and use SQL server destination to update data. But iam not able to update data in database.
I have an excel source which has some columns with values(for ex column with values "yes" / "No"). Now i need to update particular column of a table in a sql server database depending on the column value in excel source.
There was no error in the package but i could not get the expected result could any one help me in this regard.
thanks in advance.
|||
You have not told us what unexpected results you obtained, or what errors you encountered.
You almost certainly need to add a Derived Column transformation to convert "yes" or "no" values to the appropriate Boolean values that a SQL database is probably expecting.
For relatively simple import and export scenarios, you'll usually save yourself some grief by using the Import and Export Wizard to create and save the initial package, then revise and enhance it as needed.
-Doug
|||Why is the SQL server destination for LOCAL SQL servers only?
Because of the way it works. It has a special mechanism for accessing the memory space of the SQL Server instance but of course in order to do that it needs to be on the same machine. It isn't a limitation that they have delierately put in - its just the way it is.
-Jamie
|||
The scenario goes on like this:
I have an excel source with 3 coulumns(sno,sname,status). The values for status column will be either "yes" or "no".
I have a student table in SQL Server datatase which contain some columns(stdsno,stdsname, stdstatus)
i need to update the stdstatus column of student table in sql server database if and only if the sno of excel source matches with stdsno of sqlserver table and also status column value of excel source is "no".
(for example i need to update the stdstatus column values in database only if the status column value of excel source is "no").
I have taken excel as input source and sqlserverdestination as destination. Which transformation should i use to achive the above said
output.
|||
The scenario goes on like this:
I have an excel source with 3 coulumns(sno,sname,status). The values for status column will be either "yes" or "no".
I have a student table in SQL Server datatase which contain some columns(stdsno,stdsname, stdstatus)
i need to update the stdstatus column of student table in sql server database if and only if the sno of excel source matches with stdsno of sqlserver table and also status column value of excel source is "no".
(for example i need to update the stdstatus column values in database only if the status column value of excel source is "no").
I have taken excel as input source and sqlserverdestination as destination. Which transformation should i use to achive the above said
output.
If you need to compare source with destination then Lookup is a good option. Merge Join also has cpabilities in this area.
-Jamie
No comments:
Post a Comment