Sp_OACreate With Excel

Jan 3, 2002

I'm having problems creating and saving an Excel object.

I can create a Word document, but not an Excel file

It fails and Excel is in the task manager but nothing else happens.

I have to kill the processes, from my Exe/Dll viewer "ProcExp"
(It's a cool app, that saves me from rebooting to free up my system)

Anyway, here is both my VBS and TSQL code.

TIA

JeffP...

/*
'vbs code......saved in Notepad
Dim myexcel
set myexcel = createobject("Excel.Application")
myExcel.Application.UserName = "JeffP"
myExcel.Application.Workbooks.Add
myExcel.Application.ActiveWorkbook.SaveAs ("Me.xls")
myExcel.Application.ActiveWorkbook.Save
myExcel.Workbooks.Close
myExcel.Application.Quit
'end vbs code
*/
--begin tsql
declare @Obj int ,@file varchar(100) ,@user varchar(100) ,@res int
,@sheet int ,@saveas varchar(255) ,@save varchar(255)

set @user = 'Jackx'
set @file = 'me.xls'

set @saveas = 'ActiveWorkbook.SaveAs("C:Documents and SettingsjeffMy Documents")'
set @save = 'ActiveWorkbook.Save'

exec @res = sp_oacreate 'excel.application' ,@obj output
print 'Obj'
print @res
print @obj
print '-------'

exec @res = sp_oamethod @obj ,'workbooks.add' ,@sheet output
print 'add'
print @res
print @sheet
print '-------'

exec @res = sp_oamethod @obj ,@saveas
print 'saveas'
print @res
print @file
print '-------'

exec @res = sp_oamethod @obj ,'ActiveWorkbook.Save'
print 'save'
print @res
print @file
print '-------'

exec @res = sp_oamethod @obj ,'Workbooks.Close'
print 'close'
print @res
print @file
print '-------'

exec @res = sp_oamethod @obj ,'quit'
print 'quit'
print @res
print @sheet
print '-------'

exec @res = sp_oadestroy @obj
print 'end'
print @res
--end tsql

View 1 Replies


ADVERTISEMENT

SP_oacreate

Mar 31, 2008

pls send me some code samples to import a html/txt file using the sp_oacreate procedure
am getting the error number -2147221005


please let me know

View 5 Replies View Related

Sql Server : Sys.sp_OACreate Error

May 21, 2008

I tried to  send the html format email alert  through sql server. Its working fine in sql server 2000 (Dev Server). then i
moved to production server (sql server 2005).
when i run this email program (USP_SendHolidayAlert) in sql server 2005, i got the folloing errors,
Msg 15281, Level 16, State 1, Procedure sp_OACreate, Line 1SQL Server blocked access to procedure 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this component is
turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole
Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface
Area Configuration" in SQL Server Books Online.
Msg 15281, Level 16, State 1, Procedure sp_OASetProperty, Line 1SQL Server blocked access to procedure 'sys.sp_OASetProperty' of component 'Ole Automation Procedures' because this component
is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole
Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface
Area Configuration" in SQL Server Books Online.
Msg 15281, Level 16, State 1, Procedure sp_OASetProperty, Line 1SQL Server blocked access to procedure 'sys.sp_OASetProperty' of component 'Ole Automation Procedures' because this component
is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole
Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface
Area Configuration" in SQL Server Books Online.
Msg 15281, Level 16, State 1, Procedure sp_OAMethod, Line 1SQL Server blocked access to procedure 'sys.sp_OAMethod' of component 'Ole Automation Procedures' because this component is
turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole
Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface
Area Configuration" in SQL Server Books Online.
Msg 15281, Level 16, State 1, Procedure sp_OADestroy, Line 1SQL Server blocked access to procedure 'sys.sp_OADestroy' of component 'Ole Automation Procedures' because this component is
turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole
Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface
Area Configuration" in SQL Server Books Online.
Stored Procedure----------------
CREATE Procedure USP_SendHolidayAlert           /*                        Name:  USP_SendHolidayAlert                     Description:  Send Email holiday Alert  Author:  Gopalan Mani                    Modification Log: Create                                            Description                  Date         Changed By                        Created procedure            13-05-2008    Gopalan Mani                    */                 @SenderName varchar(250),      @SenderAddress  varchar(250),      @RecipientName  varchar(250),      @RecipientAddress  varchar(250),      @Subject   varchar(250) ,     @Title  varchar(350)  ,    @HolidayDate   varchar(350)         as                  DECLARE @HtmlContent varchar(8000)                DECLARE @object int        DECLARE @executable int              DECLARE @RecCount int                        EXEC @executable = sp_OACreate 'CDONTS.NewMail', @object OUT        EXEC @executable = sp_OASetProperty @object, 'BodyFormat', 0        EXEC @executable = sp_OASetProperty @object, 'MailFormat', 0                       select @HtmlContent =MailText from Tbl_EmailContent where RecID=2         select @HtmlContent= REPLACE(@HtmlContent,'Holiday_Title',@Title)  select @HtmlContent= REPLACE(@HtmlContent,'Holiday_Date',@HolidayDate)      EXEC sp_OAMethod @object, 'Send', NULL,@SenderAddress ,@RecipientAddress,@Subject,@HtmlContent        EXEC sp_OADestroy @object        
please do some necessery steps to solve this problems.
 

View 2 Replies View Related

Sp_OACreate And Sp_OAMethed Heelllllpppp...

Mar 23, 2008

I have a question and I hoping to not sound to lame. I am trying to use the sp_oacreate and the sp_oamethed to run my dll from sql sever 2005
It looks some thing like this

EXEC @hr = sp_OACreate 'MyApplication.Adcom', @object OUT,5
IF @hr <> 0
BEGIN --Not able to create the object
Return @hr
END --Not able to create the object

EXEC @hr = sp_OAMethod @object, 'Authenticate', @FunctionReturn OUT, strUser, strPassword, strDoman
IF @hr <> 0
BEGIN --Not able to execute Com function
Return @hr
END --Not able to execute Com function

I have created my dll in .net and placed it in the root drive of the server. How does OACreate know where to locate my dll?
One executing this fucntion I am returning -2147221005
-214721148 error code.

Have I created my dll incorrectly? The name of the dll is ActiveDirectoryCom and the Class with in the dll is called Adcom and the function that need to run is called AuthenticateUserFullPath. Have I correctly Written the sp_Oa's

View 3 Replies View Related

Sp_OACreate And Sp_OAMethed Heelllllpppp...

Mar 23, 2008

I have a question and I hoping to not sound to lame. I am trying to use the sp_oacreate and the sp_oamethed to run my dll from sql sever 2005
It looks some thing like this

EXEC @hr = sp_OACreate 'MyApplication.Adcom', @object OUT,5
IF @hr <> 0
BEGIN --Not able to create the object
Return @hr
END --Not able to create the object

EXEC @hr = sp_OAMethod @object, 'Authenticate', @FunctionReturn OUT, strUser, strPassword, strDoman
IF @hr <> 0
BEGIN --Not able to execute Com function
Return @hr
END --Not able to execute Com function

I have created my dll in .net and placed it in the root drive of the server. How does OACreate know where to locate my dll?
One executing this fucntion I am returning -2147221005
-214721148 error code.

Have I created my dll incorrectly? The name of the dll is ActiveDirectoryCom and the Class with in the dll is called Adcom and the function that need to run is called AuthenticateUserFullPath. Have I correctly Written the sp_Oa's

View 4 Replies View Related

Need To Call Com Object Using Sp_OACreate

Apr 11, 2008

Hi,
I have com object on another machine, and I want to call from diffetent machine,Is It possible to use sp_oACreate to creating object of com object that resides on another computer.

Thanks


View 1 Replies View Related

.Net COM Interop Class Not Working Via Sp_OACreate

Aug 2, 2006

this thread is actually to help another guy out in a seperate thread lol...I am not a COM expert but I do have a basic understanding of it from a dev perspective. I am trying to get COM interop via VS2005 to work from a sql2005 sp_OACreate call. below is the code. I can replace my custom object with say a sqldmo reference and IT WORKS! So I was thinking maybe security issues, but even when I run sql under an admin account it does not work. and i am connecting via sa for the code.

 

Also, I have tested the COM object via windows script host/.vbs file: dim oOjbect set oObject = CreateObject(myCOMObject.Math) msgbox oObject.Add(1,1). And the object appears to be listed correctly in the registry under HKEY_Classes

Imports System

Imports System.IO

Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Imports Microsoft.SqlServer.Server

Imports Microsoft.VisualBasic

Imports System.Diagnostics

 

Public Class Math

Public Function Add(ByVal iFirstNum As Integer, ByVal iSecondNum As Integer)

Return (iFirstNum + iSecondNum)

End Function

End Class

------------------------------------------------------------------------------------------

DECLARE @object int

DECLARE @hr int

DECLARE @src varchar(255), @desc varchar(255)

EXEC @hr = sp_OACreate 'myCOMObject.Math', @object OUT,5

IF @hr <> 0

BEGIN

EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT

SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc

RETURN

END

View 6 Replies View Related

Sp_OAcreate, Access.Application Can Not OpenCurrentDatabase

Jan 19, 2007

Hi all,

trying to use MS Access Automation from within SQL Server 2000 .

Seems that OpenCurrentDatabase (Access Application property) just hangs...

Following, the code that is used:

--BOF--

--object vars
DECLARE @w_object INT,
@s_object INT

--error handling vars
DECLARE @error INT
DECLARE @src VARCHAR(255),
@desc VARCHAR(255)

--execution vars
DECLARE @command NVARCHAR(100),
@property NVARCHAR(20)

--create an Access application
EXEC @error = sp_OACreate 'Access.Application', @w_object OUT
IF @error <> 0
BEGIN
EXEC sp_OAGetErrorInfo @w_object, @src OUT, @desc OUT
SELECT CONVERT(VARBINARY(4),@error) AS Error, @src AS Source, @desc AS [Description]
RETURN
END

--get default security level
EXEC sp_OAGetProperty @w_object, 'AutomationSecurity', @property OUT
SELECT @property AS default_security_level

--set application security level to low (no sandbox)
--thought this would help, but no luck
EXEC @error = sp_OASetProperty @w_object, 'AutomationSecurity', '1' --Low Security
IF @error <> 0
BEGIN
EXEC sp_OAGetErrorInfo @w_object
GOTO Cleanup
END

--Test security level
EXEC sp_OAGetProperty @w_object, 'AutomationSecurity', @property OUT
SELECT @property AS my_security_level

--check current user
EXEC sp_OAGetProperty @w_object, 'CurrentUser', @property OUT --should be Admin
SELECT @property AS current_application_user --it is Admin

--Open non password protected database,
SET @command = 'OpenCurrentDatabase("C:mikros.mdb")'

--unfortunately this hangs...
EXEC @error = sp_OAMethod @w_object, @command
IF @error <> 0
BEGIN
EXEC sp_OAGetErrorInfo @w_object, @src OUT, @desc OUT
SELECT 'Failed to Open database', CONVERT(VARBINARY(4),@error) AS Error, @src AS Source, @desc AS [Description]
GOTO Cleanup
END

--Close current database
EXEC @error = sp_OAMethod @w_object, 'CloseCurrentDatabase'
IF @error <> 0
BEGIN
EXEC sp_OAGetErrorInfo @w_object, @src OUT, @desc OUT
SELECT 'Failed to Close database', CONVERT(VARBINARY(4),@error) AS Error, @src AS Source, @desc AS [Description]
END

Cleanup:
--close Application
EXEC @error = sp_OAMethod @w_object, 'Quit'
IF @error <> 0
BEGIN
EXEC sp_OAGetErrorInfo @w_object
END

--destroy Application Object
EXEC @error = sp_OADestroy @w_object
IF @error <> 0
BEGIN
EXEC sp_OAGetErrorInfo @w_object
END

--EOF--



Any help would be appreciated.

Thanks in advance

View 1 Replies View Related

EXECUTE Permission Denied On Object 'sp_OACreate', Database 'mssqlsystemresource'

Oct 5, 2006

HI There

I have a user login that is executing an sp. It gets the follwoing error:
Msg 229, Level 14, State 5, Procedure sp_OACreate, Line 1
EXECUTE permission denied on object 'sp_OACreate', database 'mssqlsystemresource', schema 'sys'.
Msg 229, Level 14, State 5, Procedure sp_OAMethod, Line 1
EXECUTE permission denied on object 'sp_OAMethod', database 'mssqlsystemresource', schema 'sys'.
Msg 229, Level 14, State 5, Procedure sp_OAMethod, Line 1
EXECUTE permission denied on object 'sp_OAMethod', database 'mssqlsystemresource', schema 'sys'.
Msg 229, Level 14, State 5, Procedure sp_OAMethod, Line 1
EXECUTE permission denied on object 'sp_OAMethod', database 'mssqlsystemresource', schema 'sys'.
Msg 229, Level 14, State 5, Procedure sp_OAGetProperty, Line 1
EXECUTE permission denied on object 'sp_OAGetProperty', database 'mssqlsystemresource', schema 'sys'.
Msg 229, Level 14, State 5, Procedure sp_OAGetProperty, Line 1
EXECUTE permission denied on object 'sp_OAGetProperty', database 'mssqlsystemresource', schema 'sys'.
Msg 229, Level 14, State 5, Procedure sp_OADestroy, Line 1
EXECUTE permission denied on object 'sp_OADestroy', database 'mssqlsystemresource', schema 'sys'.

Everything ic an find on the net refers to Sql Server 2000 but this is 2005, all the resolutions say you must grant exec permissions to the user account for these sp's in the master database.

BUT is SS2005 they are in the mysqlsystemresource database.

WHen i try the following

grant exec on mssqlsystemresource.sys.sp_OACreate to UserLogin:

I get this error:

Cannot find the object sp_OACreate, becuase the object does not exist or you do not have permission.

I am logged in as sysadmin so i doubt it is permission.

How do i get a user login to be able to exec these sp's?

Thanx

View 11 Replies View Related

Sp_OACreate In SQL Server 2005, Fails With 'Not Enough Storage Is Available To Complete This Operation'

Jan 20, 2007

Hi all,

I have created a small COM in C# so that I can programatically create and execute stored procedures with SMO. At this point the COM has nothing in it but just a test prototype.
But when I tried to create the object as follows, I get the error indicated below.
It is not a memory issue because I have adequate storage and RAM.

Please Help!

DECLARE @object int
DECLARE @hr int
DECLARE @property varchar(255)
DECLARE @return varchar(255)
DECLARE @src varchar(255), @desc varchar(255)

-- Create an object.
EXEC @hr = sp_OACreate 'SQLInterop.CsharpHelper', @object OUT
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END

This is the error I am getting:

Error Code: 0x8007000E
Description: Not enough storage is available to complete this operation.
Source: ODSOLE Extended Procedure


This is the C# code for the COM:

using System;
using System.Runtime.InteropServices;
using System.Reflection;
using System.Runtime.CompilerServices;
using System.EnterpriseServices;

// General Information about an assembly is controlled through the following
// set of attributes. Change these attribute values to modify the information
// associated with an assembly.
[assembly: AssemblyTitle("CSServer")]
[assembly: AssemblyDescription("Test SQL .NET interop")]
[assembly: AssemblyDelaySign(false)]
[assembly: AssemblyKeyFile("MyKey.snk")]

// Setting ComVisible to false makes the types in this assembly not visible
// to COM components. If you need to access a type in this assembly from
// COM, set the ComVisible attribute to true on that type.
[assembly: ComVisible(true)]

// The following GUID is for the ID of the typelib if this project is exposed to COM
[assembly: Guid("ff35c6b4-81bf-47dd-9290-fcbbb49008d9")]

// Version information for an assembly consists of the following four values:
//
// Major Version
// Minor Version
// Build Number
// Revision
//
// You can specify all the values or you can default the Revision and Build Numbers
// by using the '*' as shown below:
[assembly: AssemblyVersion("1.0.0.1")]
[assembly: AssemblyFileVersion("1.0.0.1")]

// the ApplicationName attribute specifies the name of the
// COM+ Application which will hold assembly components
[assembly: ApplicationName("SQLInterop")]

// the ApplicationActivation.ActivationOption attribute specifies
// where assembly components are loaded on activation
// Library : components run in the creator's process
// Server : components run in a system process, dllhost.exe
[assembly: ApplicationActivation(ActivationOption.Server)]
namespace SQLInterop
{
public interface ITest
{
string SayHello();
string SayIt(String strMessage);
}

//[SecurityRole("RBSecurityDemoRole", SetEveryoneAccess = true)]
[ComVisible(true)]
[CLSCompliant(false)]
[ClassInterface(ClassInterfaceType.None)]
public class CSharpHelper : ITest
{
public string SayHello()
{
return "Hello from CSharp";
}

public string SayIt(String strMessage)
{
return strMessage + ": from CSharp";
}
}
}

View 8 Replies View Related

EXECUTE Permission Denied On Object 'sp_OACreate', Database 'master', Owner 'dbo'.

Sep 21, 2005

I want to send email using sql stored procedure.my code is work fine in my local sqlserver account. when I use my online sql server it display this error.
EXECUTE permission denied on object 'sp_OACreate', database 'master', owner 'dbo'.
EXECUTE permission denied on object 'sp_OASetProperty', database 'master', owner 'dbo'.
EXECUTE permission denied on object 'sp_OAMethod', database 'master', owner 'dbo'.
EXECUTE permission denied on object 'sp_OADestroy', database 'master', owner 'dbo'.
How canI solove this problem?

View 1 Replies View Related

EXECUTE Permission Denied On Object Sp_OACreate/method/GetProperty/Destroy

Aug 3, 2002

Hi friends

I copy script that use SQL proc to call DTS package from this site.

When I run this procedure in SQL query alalyzer. I got an error messages:
EXECUTE permission denied on object sp_OACreate/method/GetProperty/Destroy.
I did not change anything in spExecutePKG.
Even I used sa as ServerPWD. How to fix this problem? my code is
exec spExecutePKG 'myserver','execl_DTS','sa','0',''
Thanks
jimmy

------proc original code
CREATE PROC spExecutePKG
@Server varchar(255),
@PkgName varchar(255), -- Package Name (Defaults to most recent version)
@ServerPWD varchar(255) = Null,-- Server Password if using SQL Security to load Package (UID is SUSER_NAME())
@IntSecurity bit = 0,-- 0 = SQL Server Security, 1 = Integrated Security
@PkgPWD varchar(255) = ''-- Package Password
AS
SET NOCOUNT ON
/*
Return Values
- 0 Successfull execution of Package
- 1 OLE Error
- 9 Failure of Package
*/
DECLARE @hr int, @ret int, @oPKG int, @Cmd varchar(1000)

-- Create a Pkg Object
EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUTPUT
IF @hr <> 0
BEGIN
PRINT '*** Create Package object failed'
EXEC sp_displayoaerrorinfo @oPKG, @hr
RETURN 1
END

-- Evaluate Security and Build LoadFromSQLServer Statement
IF @IntSecurity = 0
SET @Cmd = 'LoadFromSQLServer("' + @Server +'", "' + SUSER_SNAME() + '", "' + @ServerPWD + '", 0, "' + @PkgPWD + '", , , "' + @PkgName + '")'
ELSE
SET @Cmd = 'LoadFromSQLServer("' + @Server +'", "", "", 256, "' + @PkgPWD + '", , , "' + @PkgName + '")'

EXEC @hr = sp_OAMethod @oPKG, @Cmd, NULL

IF @hr <> 0
BEGIN
PRINT '*** LoadFromSQLServer failed'
EXEC sp_displayoaerrorinfo @oPKG , @hr
RETURN 1
END

-- Execute Pkg
EXEC @hr = sp_OAMethod @oPKG, 'Execute'
IF @hr <> 0
BEGIN
PRINT '*** Execute failed'
EXEC sp_displayoaerrorinfo @oPKG , @hr
RETURN 1
END

-- Check Pkg Errors
EXEC @ret=spDisplayPkgErrors @oPKG

-- Unitialize the Pkg
EXEC @hr = sp_OAMethod @oPKG, 'UnInitialize'
IF @hr <> 0
BEGIN
PRINT '*** UnInitialize failed'
EXEC sp_displayoaerrorinfo @oPKG , @hr
RETURN 1
END

-- Clean Up
EXEC @hr = sp_OADestroy @oPKG
IF @hr <> 0
BEGIN
EXEC sp_displayoaerrorinfo @oPKG , @hr
RETURN 1
END

RETURN @ret
GO

View 1 Replies View Related

EXECUTE Permission Denied On Object 'sp_OACreate', Database 'master', Owner 'dbo'.

Sep 21, 2005

I want to send email using sql stored procedure.my code is work fine in my local sqlserver account. when I use my online sql server it display this error.

EXECUTE permission denied on object 'sp_OACreate', database 'master', owner 'dbo'.

EXECUTE permission denied on object 'sp_OASetProperty', database 'master', owner 'dbo'.

EXECUTE permission denied on object 'sp_OAMethod', database 'master', owner 'dbo'.

EXECUTE permission denied on object 'sp_OADestroy', database 'master', owner 'dbo'.

How canI solove this problem?

View 2 Replies View Related

Sp_OACreate 'SQLXMLBulkLoad.SQLXMLBulkload' In 2005

Jan 31, 2006

With my Sql server I install SQLxml3.0 and run this with problem.
DECLARE @ObjectError INT
, @Object INT
, @ErrMsg VARCHAR(50)

EXEC @ObjectError = sp_OACreate 'SQLXMLBulkLoad.SQLXMLBulkload', @Object OUT,4I plan to go with the Sql server 2005 but this code give me a error...:eek: Msg 15281, Level 16, State 1, Procedure sp_OACreate, Line 1

SQL Server blocked access to procedure 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface Area Configuration" in SQL Server Books Online.
My user right not change. And I dont see some "SQLXML" in the "Surface Area Configuration"???

Can someone can give my suggestion?

View 1 Replies View Related

Integration Services :: How To Upload Excel File Using SSIS With Out Excel Installed On Server

Jul 25, 2015

Trying 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

Using A Excel Source To Get The Data From An Excel File Gets Null Values For A Couple Columns

Nov 19, 2007

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

View 4 Replies View Related

Integration Services :: SSIS - Read Multiple Excel Sheets From One Excel File

Sep 13, 2015

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 Related

How To Pass The Excel Sheet Names To The Excel Source Control Through Variables

Feb 22, 2006

I 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

View 3 Replies View Related

How Do Create Table From Excel (based On Excel Column Name) And Import Data From It?

Jun 14, 2006

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.

View 3 Replies View Related

Excel Destination Appends The Excel File Everytime A Package Is Executed

Dec 18, 2006

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

Problem With Retreving A Excel Data Through Excel Source Component.

Sep 18, 2007



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.

View 6 Replies View Related

Excel Report Export Causes Excel 2000 To Crash On Print

Dec 5, 2007

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.

View 3 Replies View Related

Integration Services :: Excel Sheet Not Visible In Excel Destination

Sep 14, 2015

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

SSIS - DataFlowTask - Excel Source - Dynamic Excel Template

Mar 13, 2008



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

View 8 Replies View Related

Integration Services :: Excel Column Turns To Blank / NULL While Import Using SSIS Excel Source 2008

Jul 6, 2015

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.

View 2 Replies View Related

Excel Add-in Failed To Load After Reinstalling Excel 2007

Mar 15, 2008



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.

View 3 Replies View Related

Reports Exported As Excel Cannot Be Read By Mac Version Of Excel

Jan 31, 2007

I searched the list but did not see anything related.

I cannot open a file directly exported from RS in the Excel format. If I open the file in the Windows version and then close it, no need to even save it, I can then open it with the Mac version of Excel. The file size after closing is about 2K less so the RS program is adding something onto the file that the Mac version does not like. It will crash Excel when trying to open.

I cannot export the report as CSV as it will not correctly import into Excel on the Mac.

Is there a work around for this?

Thanks.

--sean

View 5 Replies View Related

DTS Export To Excel (How To Format Results In Excel)

Nov 22, 2005

Hi All

I've been googling this for a while now and can't seem to find any elegant answers.

I'm looking for an automated way to present a FORMATED Excel Spreadsheet to the Customer from a stored procedure output.

Can anyone advise me the best method of doing this - should I / can I assign an Excel Template to the DTS Task output ?

His mind is set on Excel and the formatting is basic and easy to write in a Macro which I've done, but this requires human interaction to finish the task (Automated Run Once on opening etc).

In an ideal world an individual would send an email to the Server with two formated parameters (@FromDate & @ToDate) and would be emailed back a ready formatted S/Sheet. But I believe he would be willing to just select the relevant SpreadSheet for the Daily / Weekly / Monthly periods dumped.

Thanks

GW

View 2 Replies View Related

DTS From Excel File (excel Filename Is Different Everyday)

Sep 25, 2006

Good Day to all,
Hope you could help me w/ my project.
Im creating a DTS Package. The source data will be coming from an excel file going to my SQL table. The DTS package is scheduled to execute daily, but the source data will be coming from different excel filename.
Example, today the DTS will get data from Data092506.xls. Then tomorrow, the data will be coming from Data092606.xls.
How can I do this? The DTS I've already done has a fixed source data file.
Please help.
Thank you so much.
God Bless.

View 9 Replies View Related

Excel 2003 To Excel 2007 Problem

Jan 2, 2008

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.

View 3 Replies View Related

TSQL + VBA Excel 2003 - Importing Data From MS Excel 2003 To SQL SERVER 2000 Using Multi - Batch Processing

Sep 11, 2007

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






View 7 Replies View Related

Import Excel Via Excel Command

Jan 24, 2008



Hi
I want to import excel in SSIS via sql command and change the data types. can anyone guide me on this or give me some sample command on how to change datatypes explicitly.

View 1 Replies View Related

Genearating The Excel Statment From EXCEL

May 29, 2008

Hi,

I want to generate the INSERT statement for the sql server.

I have 300 rows with 3 columns which are in EXCEL.

So I am writing the formula and copying it but it is not giving me the right lines.


This is what I am doing..

="INSERT INTO SalesRep (RepID,RepName )VALUES ('"& A2 &"', '" & B2 & "' );"

it is keep copying the same values into the next cell so how i can fix it , so it copy
the values from its current row.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved