Automation Of Excel To SQL Server 2005
Aug 11, 2007
Hi all,
I have a task which needs to be automated.
1) Excel spreadsheet load into sql server database as a table.
2) Then , there will be some quering of data joining this table ( imported from excel) with other tables.
3) Then, output should be back into excel spreadsheet.
All these steps 1,2,3 need to be automated.
Could anybody give me the different steps(if not,, any links which guide me doing so), for doing this and any pre setup required for this.
Thanks for all your help..
View 1 Replies
ADVERTISEMENT
Mar 19, 2008
H...
I have a problem to run a package with a scrip task containing the code in this page. I try to create a new excel workbook, change some cells, and save it to the harddrive. When designing the package in BI dev studio, I can execute it without a problem. I can even run it with DTexec.exe. The problem is that when I run this package from the SQL server agent, then it breaks. It breaks on a machine running Windows server 2008 with SQL server 2008 on it and it also breaks on a machine running Vista with sql server 2005, but it runs fine on a windows server 2003 machine with sql server 2005 on it.
I can also mention that if I dim out the one line after the comment in the code, then It runs fine on any one of my machines. The problem seems to be with saving or opening an excel file. I am also making use of a proxy with a credential using my login name. I am part of the administrators on these machines.
Imports eXcel = Microsoft.Office.Interop.Excel
Public Sub Main()
Dim objExcel As New eXcel.Application
Dim objSheet As eXcel.Worksheet
Dim strFname As String = "c: empBook3.xls"
objExcel.DisplayAlerts = False
objExcel.Application.Interactive = False
objExcel.Workbooks.Add()
objExcel.Cells(1, 1) = "0"
'I dim out the next line and it works from sql server agent on any machine
objExcel.ActiveWorkbook.SaveAs(strFname)
objExcel.DisplayAlerts = True
objExcel.Application.Quit()
objSheet = Nothing
objExcel = Nothing
Dts.TaskResult = ScriptResults.Success
End Sub
The error that I receive is this one:
Executed as user: BS-PROCESS1dlangec. ...0.0.1300.13 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 10:25:16 PM Error: 2008-03-18 22:25:22.40 Code: 0x00000001 Source: Script Task Description: System.Runtime.InteropServices.COMException (0x800A03EC): Exception from HRESULT: 0x800A03EC Server stack trace: at Microsoft.VisualStudio.Tools.Applications.RemoteTypeAdapter.System.AddIn.Contract.Automation.IRemoteTypeContract.InvokeMember(String name, BindingFlags bindingFlags, IRemoteObjectContract target, IRemoteArgumentArrayContract remoteArgs, Boolean[] remoteArgModifiers, Int32 lcid) at System.Runtime.Remoting.Messaging.StackBuilderSink._PrivateProcessMessage(IntPtr md, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext, Object[]& outArgs) at System.Runtime.Remoting.Messaging.StackBuilderSink.PrivateProcessMessage(RuntimeMethodHandle md, Object[] args, Object server, Int32 metho... Process Exit Code 1. The step failed.
View 9 Replies
View Related
Jul 20, 2005
Hi,I tried to add column headers and delete all rows to excel databasewhich is a linked server to my sql server db.I got an error message, "delete data through linked server is notsupported by Isam".I tried to use FSO to write to the excel file, but seems like FSOsupport write to only text file.the last time I tried to create an excel applicaiton object and temperwith its cell, I hang my Excel application and had to reinstall it.Have any ideas,Thank you very much
View 1 Replies
View Related
Jul 8, 2006
I'm a newbie to SQL Server, trying to make the leap from Access because I'm starting out grow it for my needs. I have a lot of automation stuff in Access where it does a query/code driven process for us via a macro. Sometimes the output is a report, or a form with assembled data that we use. I'm wondering if SQL Server 2005 is capable of doing automation of processes like these as well. Even if it's not necessarily a macro, something I can program, compile and have use off of my SQL Server database to do the tasks that the Access macro did.
View 1 Replies
View Related
Nov 30, 2004
Hello Experts,
I am a New Bee in SQL.I would like to know,As a System Admin I would like to automate the task of keeping the SQL Server up to date with latest Service Packs and Patches.
Is there any procedure / Software where I can automatically download the patch / sp's from the site and install on the servers or maybe schedule the task for every month.
I would appreciate a reply from the experts
Best Regards
SunilVKulkarni
View 1 Replies
View Related
Jun 4, 2015
Overview of Automation with SQL server.
View 2 Replies
View Related
Mar 23, 2015
I have a 2 node cluster with 2 standalone 2k14 instances having alwayson setup. As per client requirement we have created a client access point with a cname alias in dns to connect to secondary replica. Now, everytime whenerver the roles switch over one has to manually move this resource from the previous secondary node to the new secondary node. This is tedious, and should not be done manually either, so I am looking for a way to automate it so that as soon as the role switches over, the resource group after some time should also switch over to the current secondary.
Env details
xa (current primary)
y2a (secondary)
client access point (resource group): aglreadtest
View 2 Replies
View Related
Oct 15, 2007
Hi friends,
I am facing a problem. I have a excel sheet with a 'single cell' displayed like this :
List of Employees FirstName
Arranged StateWise
California
97 Kathy
101 Jack
102 Suzanne
Texas
103 Gonzalles
104 Ricardo
111 Kate
Virginia
125 Jim
126 Rocky
and so on..where California is the state, 97 is empid and Kathy is empname
This has to be populate in a table
Employee
Empid
EmpFName
Stateid
State
StateId
StateName
So during populating, we need to compare "California" against the statename field of State and pick up id and then populate the Employee table in sql server 2005
EXPECTED Output
97 Kathy 01
101 Jack 01
102 Suzanne 01
103 Gonazalles 02
104 Ricardo 02
105 Kate 02
and so on....
I have populated the excel cell into a staging table containing nvarchar(100)..but what to do after that?
thanks
View 3 Replies
View Related
Aug 1, 2007
Hi friends
student table contain the 3 columns : SNO SNAME MARKS
by using these controles i can able to upload the records (which is not exist in the database)
Excel Source 1 --------------------student.xls
Data Conversion 1 --------------for destination datatype convertion
Fuzzy Lookup---------------map with database student table with sno inner join
Conditional Split ------------ (if simularity =1 then ignored the record) else inserted the database
OLE DB Destination ------------save the new records
+++++++++++++++++++++++++++++++++++NOW ++++++++++++++++++++++++++++++++++++
in student.xls containt 7 records (1-7)
in student table(server) containt 7 records (1-7)
but marks is diffrent from excel sheet NOW
i want to update the marks ,field only (may be tomorow more than 1 column i have to update)
== SIMULTANIOUSLY how to insert a new record and EXISTING RECORD update only marks ========
REGRADS
KOTI
View 1 Replies
View Related
Aug 5, 2007
I need some pointers to help me import some data from an excel sheet into a SQL Server 2005 DB. Here is the scenario:
Each row in my excel sheet maps to three tables in the DB. Lets say there are 6 columns: Col1, Col2...Col6.
Col1 and Col2 map to DBTableA.
Col3 and Col4 map to DBTableB.
Col4 and Col6 map to DBTableC.
DBTableA's primary key is used as a foreign key in DBTableB and DBTableC.
Now what would be the best way to do this using SSIS?
I have gone through transforms and data flow tasks etc, so I am not exactly a newbie in SSIS. So it will be ok to use any SSIS specific terms in your response.
Thanks.
-Faisal
View 1 Replies
View Related
Jul 31, 2007
HI Friends,
i was created on xls file in my dektop name (student) with 2 columns
sno sname marks
1 a 10
2 b 20
3 c 30
4 d 40
these records added to excel file only
Now : i created a table in sql server 2005
sno :numeric(18, 0)
sname :varchar(50)
marks :numeric(18, 0)
NOW in ssis package
1) i place excel datasource (selected the student excel sheet$1)
2) i placed a lookup controle and selected the server student table
Question : when we map the excel sno= server sno
ERROR : data type mismatch to any of the column ??????????????????
please give me the related steps
View 3 Replies
View Related
Oct 4, 2007
I have an Excel sheet that is dynamically updated (through DDE) and I want to import this data to a table in SQL Server 2005. Using SQL Server Management Studio to configure an Excel data source as a linked server.
(http://support.microsoft.com/kb/306397/EN-US/)
Following the first 5 steps should let me acces the table (but I cannot view the data in SQL Server 2005). However, I could not find how to export the data into an existing table. Does anyone know how or can give a pointer to document describing how to do this?
View 8 Replies
View Related
Apr 26, 2007
Hello,
i have made some Data Mining Model Examples in Excel 2007 (not temporarily!). They where there after leaving an re-opening Excel. I have used them several times. Then I want to look, if I can see them also via SQL Server Managment Studio in the Analysis Services. There where nothing in the DMAddInDB in Analysis Services.
And after this, in Excel my DataMining Models have disappeared and all Models i have made since this disappeared also.
Perhaps I have destroyed the database. But will this happen every time? Can I share Data Mining Models I have made with Excel with Projects in SQL Server Analysis Services?
Thanks
Berenice
View 5 Replies
View Related
Jan 25, 2008
I have been working with SQL Server 2005 EXPRESS for a while and now need to import some basic demographic data into my database from an Excel file.
I only have Management Studio EXPRESS to work with. How can I import Excel data into my database? Is there a mgic third party tool for this or can I do it with Management Studio EXPRESS?
View 5 Replies
View Related
May 19, 2008
I had been using Excel to connect to SQL server. This worked merrily for about 6 months then I had reason to reinstall Visual Studio.
Now I've reinstalled that, my connection between Excel ans SQL Server won't work. I thought the ADO code I had put in my VBA macro would be sufficient to connect, but obviously I need to do something else, but what? Please help!
Do I need to create an ODBC connection in Control Panel? Or something else?
The code is unchanged from before and the VBA project does reference the ADO object Library.
(OS : Windows Vista Ultimate)
View 4 Replies
View Related
Jun 23, 2007
I have about 50 Excel files from which I have to import data with some transformations to Sql Server. My first approach was to use Excel Source component in a data flow to read the data . However, as it turned out column X in some files was being converted to a DT_NTEXT blob and in other files it was mapped to WSTR. The reason I guess is because column X contained string of varying sizes - some greater than 255 characters while others less than 255 ( max was 3000 ) . My package used a ForEach loop to iterate over all the Excel files in a directory and feed that to the data flow task. I played around with IMEX and TypeGuessRows setting but they didnt help me . In my second approach I used 2 Excel sources ; one set up for the blob type and the other for the string type . I joined them together using a precedence contraint. This worked but I then figured out that there were 2 other columns in my data that exhibited the same behavior. I couldnt continue with the mulitple Excel source approach cause I would then have 8 Excel source components. Finally, I played around with Execute SQL Task . I selected the columns X,Y and Z , initialized 3 variables of type Object , used a ForEach to enumerate over the dataset and feed that to a script component that converted the objects to Strings. This seems to work for all types of data in the mulitple columns.
My question - has anyone encountered such problem ? What was the solution ? Just thought I would share this with the rest of the community. I cant seem to recall what the exact error I was getting ..but it was something like "cant convert long data to string " or something . I also keep getting annoying error icons in my Excel Source components used in the foreach loop. Something to do with acquire connection failed even after I set DelayValidation to "true".
Thanks
View 4 Replies
View Related
Dec 14, 2007
Dear friends i want to import excel file into sql server 2005 in asp .net using c# can anyone help me?
rgds,RK
View 7 Replies
View Related
Apr 3, 2008
hi all,
how to import data from excel to sql server 2005 using asp.net 2.0 and c#
Ashok
View 3 Replies
View Related
Mar 31, 2008
I want to export Excell 2003 to Sql Server 2005. I am having the following error while execute the query.
declare @SQL varchar(8000)
create table #temp(i int identity(1,1),SiteName varchar(100),SiteDescription varchar(5000),SiteURL varchar(500),CategoryName varchar(200))
set @SQL='Insert into #temp Select * From OPENROWSET (''Microsoft.Jet.OLEDB.4.0'',''Excel 8.0;Database=' +
'a.xls' +';HDR = YES;IMEX=0'',''SELECT * FROM [Sheet1$]'')'
exec (@SQL)
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
I need a solution urgently. help Pls
View 3 Replies
View Related
Jul 17, 2007
Hi,
Currently I am trying to connect to SQL Server 2005 via Excel VBA. I would like to create a connection to the server as I have previously done with my 2000 server. However, the diffence this time is that I am using Active Directory so there are no specific log-in's for SQL per se just Windows Users that are part of groups, any of which could use this spreadsheet. So where-as previously I included the username and password for SQL Server in the connection string I would now like to use the credentials currently logged onto the particular machine.
My previous code was this
Public Function getConnection() As ADODB.Connection
' Create a connection object.
Dim Conn As ADODB.Connection
Set Conn = New ADODB.Connection
' Provide the connection string.
Dim strConn As String
'Use the SQL Server OLE DB Provider.
strConn = "Network Library=DBMSSOCN;PROVIDER=SQLOLEDB;"
'Connect to the Pubs database on the local server.
strConn = strConn & "DATA SOURCE=SQL02,1433;INITIAL CATALOG=dbDataWareHouse;"
'Use a login.
strConn = strConn & " Uid=*******;Pwd=******;"
'Now open the connection.
Conn.ConnectionString = strConn
Conn.Open
Set getConnection = Conn
End Function
Would anyone be able to point me in the eight direction ? Your help would be much appreciated
Many Thanks
James
View 1 Replies
View Related
Jan 3, 2008
I need to run a SQL Server 2005 stored procedure by pressing a command button in excel 2003. Basically, I want stored procedures run from Excel to get the view/queries from sql.
Searched and tried many solutions here and in other web resources, read about opening the datasource, but I cannot find the exact solution for my problem.
Any help would be greatly appreciated,
Thanks
View 4 Replies
View Related
Jan 18, 2006
I'm trying to import a database. It seems my most likely route is via Excel, so I've moved my tables in there.
Then I tried setting up Excel as a linked server as described in http://support.microsoft.com/default.aspx?scid=kb;en-us;Q321686
My linked server gets created apparently with no errors, but I can't see any contents when I click on it in the object explorer.
Anyone with more clues than me?
thanks
View 6 Replies
View Related
Dec 13, 2006
I have been trying to use the dtswizard.exe in SQL Server 2005 to import Excel 07 spreadsheets with no success.
Any idea how this can be done?
As a work around I have been importing the Excel 07 data into Access 07 and saving the database as the older mdb extension, THEN importing the mdb file into SQL Server 2005. Seems silly though.
View 4 Replies
View Related
Jun 7, 2007
Dear Friends,
I am getting error when trying to make excel file as the linked server in my sql server the details are as under:
os: windows xp and also tried on windows server 2003
server: sql server 2005 sp2
excel: office 2003 and also tried with .xls file of office 2007
tried to add the remote as well as file on the same computer as linked server, but error was:
could not find installable ISAM and error no was 7303.
Please help me up.
I got the idea to make it as linked server from microsoft article and followed the neat steps and got the errors.
Thanks,
View 1 Replies
View Related
Jun 11, 2007
Hi,
I would like to import an Excel spreadsheet into SQL Server 2005. I can do this quite easily using the Import/Export wizard, and have each row in the spreadsheet transfer to a new row in the database table.
However, I want to import the first few columns of the spreadsheet row into one table (called Products), but put the remaining columns into a related, three-column table, called Product_Details. In the Product_Details table, one column would hold the spreadsheet column value, the other column would be a FK integer value linked to the PK in the Products table, and the third column the primary key as normal.
So, somehow, I would need to get hold of the primary key value when the first spreadsheet columns are inserted into the Products table and then insert the remaining columns into the Product_Details table with two values per row - one value being the spreadsheet cell value, the second being the primary key of the new product in the Products table.
TIA,
Graham.
View 1 Replies
View Related
Sep 3, 2007
Hi all. I want to export data from excel 2003 to sql server 2005. I am using the following script:
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO
Insert into Pamphlet
Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:Pamphlet.xls;HDR=YES', 'SELECT * FROM [Sheet3$]')
But it is giving the following error:
Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
The above script is working fine with Excel 2002 but not with 2003. Can someone please provide me exact script of importing data from excel 2003 to sql server 2005. I have also tried the Linked server option but it is still giving the same error.
Please help me.
View 1 Replies
View Related
Nov 29, 2007
I would like to import an Excel file into my SQL Server 2005 Express db. I'm using SQL Server Management Studio Express to manage my db.
I appreciate your suggestions,
View 3 Replies
View Related
Mar 22, 2006
I am using SQL Server 2005 Express Edition for testing and developping my website. How can i import MS Excel Data into a SQL Server table?
View 2 Replies
View Related
Oct 17, 2006
I am trying to load data from an Excel spreadsheet file into SQL Server 2005 Express. I understand that DTS is the best tool for doing this but from my research it appears that DTS is not available with the Express edition and the import wizard that does come with Express is not well suited for this type of conversion.
Does anyone have any suggestions for how to achieve this objective? Thanks for any help you can provide.
View 7 Replies
View Related
Sep 7, 2007
Hi,
i'm making a merge join on a excel source whit a Sql Server 2005 (OLE DB). In the SQL table i have a select over AdventureWorks.Sales.SalesOrderDetail , in a excel file i pasted the information of the AdventureWorks.Production.Product
when i'm doing the merge join of this tables,i hope obtain 121.000 registries, but when the work is finished I only have 27 registries.
So i must configure buffer properties in the merge?
JULIAN CASTIBLANCO P
View 3 Replies
View Related
Aug 27, 2007
Is there a way to create a SQL 2005 x64 Linked server to an Excel (or Access) 2003/2007 file? In SQL 2005 32bit this was possible. Does anyone know of a solution or a work around?
View 3 Replies
View Related
May 12, 2007
Greetings,
I'm having a tough time importing some of my legacy database into sql.
I have a number of dbase (IV) files I need to get into SQL. I have tried building a SSIS package with either an foxpro oledb connection or a jet 4.0 one, none of them work bec. of inconsistencies in the data format in my tables (e.g. date fields, etc).
I have tried to save the .dbfs as excel 2007 files, taking advantage of the larger space that comes with '07. Problem is you can't use the import/export wizard with 2007 for some reason and I haven't been able to create a package with the access 12 oledb as I have read.
I have to get some crucial data out of that old system and into the new one and I can't seem to be able to import them properly.
Any hints on what I should do ? (maybe I'm doing something awfully wrong)
Thank you for taking the time to answer my question,
Val
View 12 Replies
View Related
Dec 8, 2007
Hi all,
Posted - 12/05/2007 : 01:54:18
How to export data from SQL Serevr 2005 Express to Excel 2007 using OPENROWSET command.
I have tried the following code But getting error
sSQL.Format("insert into OPENROWSET('Microsoft.JET.OLEDB.4.0','Excel 8.0;Database=%s;','Select * from [%s$]') select * from [%s]", sExcelPath, sSheetName, sTable);
But getting the following error
The OLE DB provider "MICROSOFT.ACE.OLEDB.12.0" for linked server "(null)" reported an error
I am using automation object for creating EXcel .I have added the header also.
Any help is appreciated
Thanks
View 5 Replies
View Related
