Excel to SQL-Server Insert in vba apostrophe in sql

Excel to SQL-Server Insert in vba.ado.net, connectionstring & handling apostrophe 

Private Sub CommandButton1_Click()

Dim adoCN As ADODB.Connection

Dim sConnString As String

Dim sSQL As String

Dim lRow As Long, lCol As Long

 

sConnString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=visliDB;Data Source=USER-PC\SQLEXPRESS;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=USER-PC;Use Encryption for Data=False;Tag with column collation when possible=False"

Set adoCN = CreateObject("ADODB.Connection")

adoCN.Open sConnString

For lRow = 2 To 11

   'replace

   Dim MyString As String

   MyString = Replace(Sheet1.Cells(lRow, 3), "'", "''")

   MyString = Replace(MyString, """", """")

   FixMyString = MyString

   

    sSQL = "INSERT INTO YRTAB (FIELD1, FIELD2, FIELD3) " & _

            " VALUES (" & _

            "'" & Sheet1.Cells(lRow, 1) & "', " & _

            "'" & Sheet1.Cells(lRow, 2) & "', " & _

            "'" & FixMyString & "')"

    adoCN.Execute sSQL

Next lRow

adoCN.Close

Set adoCN = Nothing

End Sub

Execute SSIS package -dtexec

in sql-server

EXEC xp_cmdshell 'dtexec /FILE "C:\\Package.dtsx" /SET \Package.Variables[User::FileName].Properties[Value];"C:\\linkservertest.xlsx"'

in batch command

dtexec /FILE "C:\\Package.dtsx" /SET \Package.Variables[User::FileName].Properties[Value];"C:\\linkservertest.xlsx"

Dynamically creating Excel File, ExcelSheets and Exporting Data from SQL Server using SSIS 2005 - CodeProject®

Dynamically creating Excel File, ExcelSheets and Exporting Data from SQL Server using SSIS 2005 - CodeProject®

Setting up an SSIS package with a dymanic Excel Source

Setting up an SSIS package with a dymanic Excel Source

Excel sql SSIS with variable file name

-- exec dbo.import_data 'C:\Documents and Settings\varunj\My Documents'
ALTER       procedure dbo.import_data
@FilePath varchar(2000)
--@filename  varchar(2000)
as
declare 
 @sql nvarchar(50),
        @Bank_code numeric(35),
        @Bank_transaction numeric(35),
 @Money_trn   numeric(35),
 @Check_num varchar(25),
  
 @FilePath varchar(2000),

 @dtsname varchar(1000),

 @cmd varchar(2000)

set @dtsname = 'Package.dtsx'

select @cmd = 'DTExec /F "' + @FilePath + @dtsname + '"'

print @cmd

set @sql = ''

--insert new records
insert into dbo.import_data2 (Bank_code,Bank_transaction, Money_trn, Check_num )
select a.Bank_code,a.Bank_transaction, a.Money_trn, a.Check_num
from dbo.import_data1 a left join 
dbo.import_data2 b 
on a.bank_code=b.bank_code
where b.bank_code is null

-- update existing records
update dbo.import_data2
set money_trn = x.Money_trn, Bank_code = x.Bank_code, Bank_transaction = x.Bank_transaction, Check_num = x.Check_num
from 
(select a.Bank_code,a.Bank_transaction, a.Money_trn, a.Check_num
from dbo.import_data1 a join 
dbo.import_data2 b 
on a.bank_code=b.bank_code) x
where import_data2.bank_code = x.bank_code

--clean up import_data1 (temp table)
truncate table dbo.import_data1

exec master..xp_cmdshell @cmd

GO

http://www.bidn.com/forums/microsoft-business-intelligence/integration-services/310/passing-a-filepath-in-ssis

Excel SpreadSheet variable Filename to SSIS

Without being able to see the package you've created, I can't be certain what the problem may be. Have you confirmed that the Excel file you're using is not corrupt (trying opening it in Excel)?

I created a simple package (using BIDs on a 32-bit machine) that contains an Excel Source & OLE DB Destination, Excel Connection Manager, and an SSIS variable that stores the full path to the Excel file. The package runs successfully. Here are the steps I followed. I hope this is helpful in resolving the error you're getting:

1. Added Excel Source.
2. Added Excel Connection Manager, clicked Edit, and entered the full file path in the Excel file path box (C:\Work\Excel Folder\TestBook97.xls). Saved changes.
3. Configured the Excel Source to point to the connection manager.
4. Added a string variable to the package, confirmed that the variable scope was Package, and filled in the variable value (C:\Work\Excel Folder\TestBook97.xls).
5. Added an expression to the Excel Connection Manager, to set value of the ExcelFilePath property using the string variable.
6. Connected the Source to the Destination.

http://www.mssqltips.com/sqlservertip/1930/use-ssis-to-import-one-cell-of-an-excel-file-into-sql-server/

Connect between Excel SQL-Server update delete

Opendatasource provides the means to create adhoc connections. It uses a linked server object. You can access server locally or remotely. You can add the same kind of parameters as a linked server object including username and password. If you need to access a remote server using an ole db provider such as the "Microsoft.ACE.OLEDB.12.0" you need to set the registry option "DisallowAdhocAcces " to 0 and of course the Ad hoc Distributed

http://klanguedoc.hubpages.com/hub/How-To-Synchronize-data-Between-Excel-2007-and-SQL-Server-2008-OPENDATASOURCE