We have a DTS package that imports data from a client's Excel file. Previously our client was using Excel 2000, but recently they upgraded to Office 2003, and now suddenly when I try to view the transformation task properties I get a Jet Database Engine error: "External table is not in the expected format".
Everything else with the file appears the same (layout, formatting, etc.).
Any suggestions?
Hi,
I need to import an SQL string from MS Excel 2003 to SQL SERVER 2000.
The string I need to import is composed by 5 different several blocks and looks like:
Code Snippet
CommandLine01 = "USE mydb"
CommandLine02 = "SELECT Block ..."
CommandLine03 = "GO
ALTER TABLE Block...
GO"
CommandLine04 = "UPDATE Block..."
CommandLine05 = "SELECT Block..."
The detail of the SQL string is at:
http://forums.microsoft.com/msdn/showpost.aspx?postid=2093921&siteid=1&sb=0&d=1&at=7&ft=11&tf=0&pageid=1
I am trying to implement OJ's suggestion:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2117223&SiteID=1
to use multi - batch processing to import the string to SQL SERVER, something like:
Code Snippet
Dim SqlCnt, cmd1, cmd2, cmd3
'set the properties and open a connection
cmd1="use my_db"
cmd2="create table mytb"
cmd3="insert into mytb"
SqlCnt.execute cmd1
SqlCnt.Execute cmd2
SqlCnt.Execute cmd3
Below is the code (just partial) I have, and I need help to complete it.
Thanks in advance,
Aldo.
Code Snippet
Function TestConnection()
Dim ConnectionString As New ADODB.Connection
Dim RecordSet As New ADODB.RecordSet
ConnectionString = "Driver={SQL Server};Server=myServer;Database=myDBName;Uid=UserName;Pwd=Password"
ConnectionString.Open
CmdLine01 = " USE " & myDB
CmdLine02 = " SELECT ACCOUNTS.FULLNAME FROM ACCOUNTS" ...
CmdLine03 = "GO
ALTER TABLE Block...
GO"
CmdLine04 = "UPDATE Block..."
CmdLine05 = "SELECT Block..."
RecordSet.Open CmdLine01, ConnectionString
RecordSet.Open CmdLine02, ConnectionString
ConnectionString.Execute CmdLine01
ConnectionString.Execute CmdLine02
'Retrieve Field titles
For ColNr = 1 To RecordSet.Fields.Count
ActiveSheet.Cells(1, ColNr).Value = RecordSet.Fields(ColNr - 1).Name
Next
ActiveSheet.Cells(2, 1).CopyFromRecordset RecordSet
'Close ADO objects
RecordSet.Close
ConnectionString.Close
Set RecordSet = Nothing
Set ConnectionString = Nothing
End Function
hi,
i have one application in Microsoft excel 2003. but when i open this in Microsoft excel 2007 it gives me error saying that
error inintializing menu,
object variable or with block variable not set.
kindly guide me what i can do for this. i m not getting exactly what is happening.
thanks & regards,
Vishruti.
Hi guys and gals,
Since monitoring blocks on the server, I have noticed that when I have a whole lot of users thrown out of their apps, it's all because of one PC.
The program name is "Microsoft Office 2003". The user reckons that they haven't opened any Excel files that have any kind of link to the live database.
Is there any known problem that you are aware of where Excel can "remember" connection strings maybe from other files you have opened in the past and would that spuriously throw INSERT, UPDATE & SELECT statements to the database server and that then cause locks?
The idea seem ludicrous to me. I'm stumped.
I want to use the CopyFromRecordSet however I am using a stored procedure that returns a recordset. Here is the code, but the recordset never opens. I have commented out the Execute
Here is a code snip, any Ideas? TIA Mike
Dim Con1 As New ADODB.Connection
Dim Cmd1 As New ADODB.Command
Dim Rs As New ADODB.Recordset
Con1.Open
Cmd1.ActiveConnection = Con1
Cmd1.CommandType = adCmdText
Cmd1.CommandText = "MyStroredProc"
Cmd1.Execute
Rs.Open Cmd1
Worksheets("T1").Range("B1").CopyFromRecordset Rs
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
Hi,
I have encountered an error when attempt to open a report which is exported to Excel using SSRS2003. It looks like this:
Microsoft Office Excel File Repair Log
Errors were detected in file 'C:Documents and SettingsTocsonEIMy DocumentsRigidReports
ptRigidTransactionSummaryReport3.xls'
The following is a list of repairs:
Damage to the file was so extensive that repairs were not possible. Excel attempted to recover your formulas and values, but some data may have been lost or corrupted.
At first I thought it was because of the sheer bulk of the report since when I break down a month's report to every 5 days, it would export without error. So I thought that if I can break a whole report into several worksheets using page break, it might work. Unfortunately, although I did succeed in exporting the whole report into several worksheets, I still encounter the same error. Now I have no idea on what the root of the error is. Note that the same report can be exported in other formats and can be opened without error. Except for a case when I tried opening the same report exported in XML using Excel, an error which might be related to the error above occurs which says that there's an invalid unicode character in the xml file.
Any help in determining the root of the problem and a solution to it?
Hello,
how can I use SQL Svr 2005 Express as database engine in background through VBA code in Excel 2003?
I want to CREATE and DELETE tables and SELECT, INSERT and UPDATE data. Is it possible to use ADO or other database objects to get in contact with SQL Svr 2005 Express?
Thanks a lot.
Christian
Hi
I am trying to connect to SQL Express from Excel VBA using an user instance.
I have tried the following connection string:
Driver={SQL Native Client};Server=.SQLExpress;AttachDbFilename=full_path_to_mdf; Database=dbname;Trusted_Connection=Yes;
And I get the following error : Could not open new database 'dbname' CREATE DATABASE is aborted.
Any ideas on what I am doing wrong?
All I want is an Excel document to connect to a .mdf that is stored on a CD (The database will be readonly). I do not want the database to be stored on the users hard drive.
Many Thanks
MP
I have a C# program that sends an e-mail with an Excel attachment. The Excel file is populated with the results of a SQL stored procedure before the e-mail program is executed. The entire process is in a DTS package (populate a temporary table, export the table to Excel, then send the e-mail). It is possible that the stored procedure will return no results, in which case the Excel file will not be populated.I'd like to know whether it is possible (and if so, how) to add some code to my e-mail program that will count the number of populated rows in the Excel file and if that number is <2 (the header row will always be populated), to quit and not send the e-mail.
An alternative would be for the DTS package to terminate if the stored procedure returns 0 records.
Thanks!
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.
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 RelatedI have a stored procedure in SQL Server which needs a parameter and returns a resultset.
sp_xxx 'parameterValue'
I'd like to know if it is possible to launch this stored procedure through Excel 2003 and get the resultset in the active spreadsheet.
I have tried to do this with Microsoft Query but it doesn't allow parameters in queries that can't be graphically represented.
I have tried also through an ODC files but I get an error.
Is it possible to do this?
Hi, i´m using Excel 2002/2003 Add-in for SQL Server Analysis Services and i have the following problem.
I´ve created a report with the addin and i saved it in a share directory. I have users in differents workstations with windows 2000 sp 4, excel 2003 and mdac 2.8 the problem is that sometimes when users open the report and want to do a drill down of a dimension it isn´t any data..
Some one have any idea?
Tks!!!
Pablo
File Structure:
--**************************************************************************************************************************
USE [Test]
GO
/****** Object: Table [dbo].[tbl_Test] Script Date: 03/04/2008 09:51:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_Test](
[TestID] [int] IDENTITY(1,1) NOT NULL,
[TestValue] [numeric](18, 2) NOT NULL CONSTRAINT [DF_tbl_Test_TestValue] DEFAULT ((0)),
CONSTRAINT [PK_tbl_Test] PRIMARY KEY CLUSTERED
(
[TestID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
--**************************************************************************************************************************
Stored Procedure:
--**************************************************************************************************************************
USE [Test]
GO
/****** Object: StoredProcedure [dbo].[proc_Ins_Test] Script Date: 03/04/2008 09:52:23 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
/*
Name: proc_Ins_Test
*/
ALTER PROCEDURE [dbo].[proc_Ins_Test]
(
@TestValue numeric(18,2)=0
)
AS
BEGIN TRAN
BEGIN
INSERT INTO tbl_Test (TestValue) VALUES (@TestValue)
END
COMMIT TRAN
SELECT MAX(TestID) AS MaxValue FROM tbl_Test
RETURN 1
--**************************************************************************************************************************
(I have it returning a value through SELECT to simulate returning a key value from the newly inserted record. I also use the RETURN value to fetch any error codes.)
'***************************************************************************************************************************
Public Sub PSInsertTest(cnConn As ADODB.Connection)
Dim rst As ADODB.Recordset
Dim cmd As ADODB.Command
Dim stProcName As String 'Stored Procedure name
Set rst = New ADODB.Recordset
Set cmd = New ADODB.Command
'Defines the stored procedure commands
stProcName = "dbo.proc_Ins_Test" 'Define name of Stored Procedure to execute.
cmd.CommandType = adCmdStoredProc 'Define the ADODB command
cmd.ActiveConnection = cnConn 'Set the command connection string
cmd.CommandText = stProcName 'Define Stored Procedure to run
'Append Parameters
With cmd
.Parameters.Append .CreateParameter("@return_value", adInteger, adParamReturnValue)
.Parameters.Append .CreateParameter("@TestValue", adNumeric, adParamInput)
.Parameters.Item("@TestValue").NumericScale = 2
.Parameters.Item("@TestValue").Precision = 18
.Parameters.Item("@TestValue").Value = 123.45
End With
'Execute stored procedure and return to a recordset
Set rst = cmd.Execute
If Not rst.EOF Then
rst.MoveFirst
MsgBox (rst.Fields("MaxValue").Value)
Else
MsgBox ("No Value")
End If
'Close database connection and clean up
If CBool(rst.State And adStateOpen) = True Then rst.Close
Set rst = Nothing
End Sub
'***************************************************************************************************************************
It goes ahead and inserts the record, but gets an error when I try to retrieve the "SELECT"ed value. I get the same results with the following statement:
rst.Open cmd, , adOpenStatic, adLockReadOnly
The error is: Operation is not allowed when the object is closed
If I use the following command, I get the error below:
rst.Open cmd.Execute
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
Any insights would be helpful
Bob
I have around 600 worksheets that i need to import into sql server that are in a somewhat non-table like format. Data defractor seems to be able to do what i need, but i also need to check into doing it manually. I've seen the code to import cells and ranges into sql server, but can't seem to remember what it is anymore. I've also searched through the forums and can't seem to find any examples either. Could anyone post an example or a link with some examples or explaination code. Thanks in advance.
View 4 Replies View Related
Hola
We are getting symptoms of a 30 second timeout irrespective of what we do, but only on certain machines. Can anyone shed any light on how the connection timeout can be set, as there seems to be a factor overiding any settings we set?
We are working in Excel (2003) connecting to an Analysis Services 2005 cube through a website https://Datasource connection. When we edit the connection string that the file is using (either by forcing use of an exterior .ODC file and editing the settings in there, or by converting the xls spreadsheet into an xml spreadsheet and manually editing the connection string in there) we get no joy: after 30 seconds the error appears:
"Either a connection cannot be made to the [my server address], or Analysis services is not running on the computer specified".
This error is itself rubbish: If a smaller query (one that takes < 30 seconds) is run in the same Excel file and Pivot Table, it connects fine.
What really doesn't make sense is that the exact same file works on some machines and not others. These machines are all inside the same network (and the machines that work have been proven to work on a number of other networks). What's more there is no pattern to the machine capabilities - some newer / faster machines get the error. These machines (that work and do not) have excel 2003 SP3. (Another of the ones that works has Excel 2007).
Changing the connection string to include "Timeout=300" or "Connect Timeout=300" makes no difference, and "Command Timeout" causes a different error so that it wont even attempt to run the query (as opposed to collapsing after 30 seconds of normal progress).
Similarly in the .ODV the tags <ODC:CommandTimeout>, <CommandTimeout>, <ODC:Connect Timeout>, <ODC:Command Timeout>, <meta name=CommandTimeout content=1000> etc. have no effect (the file still gets the 30 seconds error rather than just giving up so no way of working out correct syntax)
The only pattern we can see, and we can't fix this, is that the machines that work have Retail versions of excel, and the ones that do not have corporately licensed versions of excel. We cannot see any difference between them in the Help >Abouts, but thought it might be worth mentioning.
Any help would be fantastically appreciated, we are out of ideas.
Thanks kindly
Amundo
I would like to know recommendations on automate the following:
1. I want to know how to take data from various sql server 2000 data queries and load the data onto excel spreadsheets in an automated method. Some of the data loaded into the excel spreadsheets load detail data and some load data into pivot tables.
Is there any way that the data can be taken from sql server to the excel spreadsheets using odbc connections?
2. Some of the data taken from sql server 2000 database is loaded into pdf files. Is there any way this process can be automated?
Thanks!
Hi guys,
When I thought everything is okay with this script, I got a new problem...
I have a VBA's script from Excel 2003 that builds sql script and retrieves data from SQL SERVER 2000.
in order to make the sql running, I need to use a multi - batch processing, to pass and execute every command line once a time.
Up to here, I am using a test case with Account number = '123456' and getting the desire results.
The code below is running okay with the test case, but when changing the account number (mark as yellow in the code) to include all the accounts (or just one other account), I am getting the following ERROR:
run - time error '-2147217871 (80040e31)' - [Microsoft] [ODBC SQL Server Driver] time out expired.
Now, if I take the same code, with the condition that generates the ERROR, and try it into SQL Server, I get the results without errors.
Thanks in advance,
Aldo.
Below the code:
Code Snippet
Function QuerySalesAging()
'--------------------------------------------------------------
'MUST !!! References: Microsoft ActiveX Data Object 2.1 Library
'--------------------------------------------------------------
Dim ConnString As New ADODB.Connection
Dim RecordSet As New ADODB.RecordSet
'Setting Connection String
Driver = "{SQL Server}"
ServerName = "SERVER"
DB_Name = CompanyName
ConnString = "Driver=" & Driver & ";" & "Server=" & ServerName & ";" _
& "Database=" & DB_Name & ";" & "Uid=" & SQLLoginName & ";" & "Pwd=" & SQLPassword & ";"
'Report Criterias
Criteria05 = " AND " & "Accounts.ACCOUNTKEY Between " & AccountKeyAsRange
' -- ==> With AccountKeyAsRange = '123456' AND '123456' it works okay.
' -- ==> With any other value, in example AccountKeyAsRange = '123456' AND '9999999999' it get's ERROR.
CmdLine01 = " USE " & CompanyName
' Check and drop temporary table
TemporaryTableName = "CTE" ' The table is a regular one
CmdLine02 = " if object_id('" & TemporaryTableName & "') is not null exec('DROP TABLE " & TemporaryTableName & "') "
CmdLine03 = " SELECT ..."
CmdLine03 = CmdLine03 & " INTO " & TemporaryTableName
CmdLine03 = CmdLine03 & " FROM ..."
CmdLine03 = CmdLine03 & " WHERE " & "(" & Replace(Criteria05, "AND", "") & ")"
CmdLine03 = CmdLine03 & " ORDER BY ..."
CmdLine04 = CmdLine04 & " ALTER TABLE " & TemporaryTableName ...
CmdLine05 = CmdLine05 & " UPDATE " & TemporaryTableName ...
CmdLine06 = CmdLine06 & " SELECT ..."
CmdLine06 = CmdLine06 & " FROM ..."
ConnString.Open
ConnString.Execute CmdLine01
ConnString.Execute CmdLine02
RecordSet.Open CmdLine01, ConnString
RecordSet.Open CmdLine02, ConnString
RecordSet.Open CmdLine03, ConnString
RecordSet.Open CmdLine04, ConnString
RecordSet.Open CmdLine05, ConnString
RecordSet.Open CmdLine06, ConnString
ConnString.Execute CmdLine01
ConnString.Execute CmdLine02 ' The debbuger stops here:" if object_id('CTE') is not null exec('DROP TABLE CTE') "
ConnString.Execute CmdLine03
ConnString.Execute CmdLine04
ConnString.Execute CmdLine05
ConnString.Execute CmdLine06
ConnString.Execute CmdLine02
'Retrieve Field titles
For ColNr = 1 To RecordSet.Fields.Count
ActiveSheet.Cells(1, ColNr).Value = RecordSet.Fields(ColNr - 1).Name
Next
ActiveSheet.Cells(2, 1).CopyFromRecordset RecordSet
'Cleanup & Close ADO objects
ConnString.Execute "USE master"
ConnString.Close
Set RecordSet = Nothing
Set ConnString = Nothing
End Function
Hi!
Today we moved from 32bit to 64bit computing environment. 64bit Window server 2003 and SQL 2005 are now our playing ground. And 32 bit Office 2003 is also installed. Excel in Office program plays important roll in our mission.
One of our routine task is that
step 1. get Excel data files from other websites and
step 2. extract specific data from them and
step 3. converting them into some type of data that can be compatible with SQL
Our problem occured in step 3. To convert data, we realised taht 64 bit excel driver, which we believe is not yet available, is required.
My questions are:
1. Is 64 bit excel driver (of office 2003 or office 2007) available now?
2. Is there any way OR IS IT POSSIBLE to use 32 bit excel driver with 64bit SQL 2005?
If possible, please enlighten me.
I would appreciate it very much if anybody answer my questions.
Sincerely
I may not be the best forum for this post, however I believe some onecan still help me or direct me somewhere else.I'm finding that win 2003 x64 and SQL 2005 CTP is slower than win 2003and SQL 2000.Machine A: Opteron 2.2 (248) w/ 2 cpus. 8GB memory. SCSI disk array.Windows Enterprise 2003 x64 final release. SQL Server 2005 enterpriseCTP.Machine B: Opeteron 2.2 (248) w/ 2 CPUs. 4GB Memory. IDE Drives.Windows Server 2003 (32bit) and SQL Server 2005 (standard).I'm using Cognos' DecisionStream as the ETL tool (32bit). I have theidentical job on both machines. Machine A is slower to do my nightlybuild (1h 20m) vs. Machine B (50m).I've done no tweaking of the databases. I Used SQL 2005's studio tocopy the database from Machine B to Machine A.The only major difference is the O/S and SQL, the machines are samespeed, however Machine A has twice the memory and faster disk; so Iwould expect it to be faster.Can anyone think of smoking guns I might be missing?TIA
View 2 Replies View RelatedTrying to upload excel in server where excel is not installed. BIDs was there in the server, when i am trying to craete Excel source I am not able.what the workround for this.. How to upload excel without excel installed on the server.
View 4 Replies View Related I am using a Excel Source to get the data from an excel file to sql server 2005 table. A couple columns are coming in a double precision float, but some values have characters in them, but those values are coming out as null, even though I changed the datatype from float to unicode string. Any inputs on resolving this will be much appreciated.
Thanks,
Manisha
We have 10 sheets in Excel File and 10 sheet contains errror data. How to load 9 sheets data in to 1 destination and error data in to other destination?
View 4 Replies View RelatedI am trying to get the contents of the Excel Files dynamically and dumping into the SQL Database using SSIS. Through WMI Event Watcher, I could find when one or more Excel files dumped in a particular folder and using ForEach Loop Container I was able to take all the filenames and pass it through Variables. But at the same time in the Data Flow, I have to pass each Sheet of an Excel File to the Excel Source control and export the data to my SQL Database using OLEDB Destination.
For that I need to get the names of each sheets in an Excel File and pass it to the Excel Source Control through variables. But when I give Data Access Mode as "Table name or view name variable" and provide the variable name in that, then it is giving an error message as "A destination table name has not been provided".
And at the same time, Since I was not able to provide an static Filename (as I am passing through Variables), when I tried to map the columns in the OleDB Destination, it is not allowing me to map the columns.
So all these things I should do at Run-time using Variables in SSIS. I don't want to hard-code any filenames or Sheet names. If any one of you have a solution, please share with me.
Thanks & Regards,
Prakash Srinivasan
l've the following situation,
l've some excel files controlled by Vendor which changing frequently. The only thing does not change is the header name of each column.
So my question is, is there any way to create a new table based on the excel file selected including the column name in SSIS? So that l can use the data reader as source to select those columns l am interested on and start the integration.
Thanks.
Regards,
Yong Boon, Lim
p/s : The excel header is at the row 7.
i have an SSIS package that exports to an excel file. This works fine. the problem is that it appends the data instead of overwriting the file. Is there any way to overwrite the file like you can with a flat file? I have to email the file everyweek and don't want to have to clear it out manually. Any help would be appreciated
View 2 Replies View Related
Hello,
I have a problem with retreving a excel data through excel source component.
I have source component as Excel Source which will connect to my .xls sheet.
To retrieve the values from the sheet i am using a query as,
"SELECT F14,F3 FROM [Charac Defn & Assgnment$]"
The column F14 is not formatted so that the format of the cell is "General" I have a different type of values in the F14 column such as "PE","PES",15,20,20.00,8888.9999 etc..
While i click on preview button of Excel source it shows only the text values and not the int or decimal values, its returning NULL for those cells. I tried to use convert function, its throwing an error as
TITLE: Microsoft Visual Studio
------------------------------
There was an error displaying the preview.
------------------------------
ADDITIONAL INFORMATION:
Undefined function 'Convert' in expression. (Microsoft JET Database Engine)
Is there any other function to change the format of the cell or i need to some thing else
Please help me how to solve this issue.
When I open the spreadsheet in Excel 2000, it works fine. When I try to print, it crashes Excel. In testing, I narrowed it down to the Header/Footer, because it also crashes when I go to Page Setup and click on the header/footer tab.
However, I can print the same spreasheet from Excel 2007.
Am I just dealing with a "you need to upgrade all your clients" situation, or is there a known issue with certian formatting that is passed out with reports that is not supported by older versions of Excel?
I am using Reporting Services 2005 SP2 to serve up the report that is exported to Excel.
Any assistance is appreciated.
I have ssis package where I have excel connection manager with expression pointing to a variable which has path for location and name of excel spreadsheet to be create each with date on the name.ExcelFilePath points to variable for shared location where excel file will be saved.I have File system task for copying template excel file to destination location with date in file name.I drag and drop excel destination.  Pointed to excel connection manager. Under data access mode, I have select table and view.  When I try to select name of excel sheet,  it says, no tables or views could be loaded. I should be able to see sheetname there so that I can map column. I only have option to create new spreadsheet. I want to use template to load data in excel file. I dont want to create new sheet. It was working before. But I opened the ssis package and its broken. I was able to see spreadsheet name before but I dont see it now even though I have not made any change to package. XCEL 12.0 XML;HDR=NO";
View 5 Replies View Related
Hi,
I am creating an SSIS package witha a Dataflow task, which reads from an Excel source and then uses script component to dumpt the data to multiple tables in Sql Server database
I need to some how make my Excel source dynamic, that is my excel template which i would be using to map the excel columns to script component's input columns would be dynamic..
In other words, I should be able to define the Excel Source, Column Mapping Information, Precedence constraint to the Script component dynamically
Please suggest how could i accomplish this
Regards,
Kalyan
While importing data from Excel source , some column is getting null value even though excel column has value.To Resolve the issue we tried with
HKEY_LOCAL_MACHINESOFTWAREWow6432NodeMicrosoftOffice14.0Access Connectivity EngineEnginesExcel
1.Change the Value of the Row TypeGuessRows from 8 (Default value) to 0 and ImportMixedType = text
• xls
HKEY_LOCAL_MACHINESOFTWAREMicrosoftJet4.0EnginesExcel
1.Change the Value of the Row TypeGuessRows from 8 (Default value) to 0 and ImportMixedType = text
the connection string of the excel
UPPER(REVERSE(SUBSTRING( REVERSE(@[User::VarInputExcelFile]), 1, 5) ) ) == ".XLSX" ? "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + @[User::VarInputExcelFile] + ";Extended Properties="Excel 12.0;HDR=Yes;IMEX=1";":"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" + @[User::VarInputExcelFile] + ";Extended Properties="EXCEL 8.0;HDR=Yes;IMEX=1";"
by doing the above setting also , the column is coming as null from excel source even though there is data in excel.
Hi,
I have reinstalled Office 2007 (to changre the license key)
after this, the data mining excel add-in failed to load.
the "COM add-ins" displays: "not loaded. a runtime error occurred during the loading"
reinstalling the add-in doesn't solve the problem
installing the 2008 version don't solve the issue too.
There is no other information, what can I do to solve the issue?
thanks.