February 10, 2009 at 2:24 pm
Hi,
I've googled like crazy but seem to be running into the same roadblock.
I'm running SQL Server 2000.
I'm using Visual Studio.NET which calls the DTS and Stored Procedure that I'm working with.
The Problem I'm having is with the Sheet Name. The Data Transformation Task requires a sheet name of "Sheet$". Because the sheet names are different each time, I have to rename the sheets on a daily basis. After which the the DTS works with no problem.
Is there a way to code this in ActiveX?
I've looked around and if this is answered somewhere else in the forum, I apologize.
Thanks,
Johnny
February 10, 2009 at 10:16 pm
The sheet name is the table name. A CREATE TABLE statement in an Execute SQL task creates a new sheet.
February 11, 2009 at 8:08 am
Thanks,
I apologize, I mispoke what I was trying to accomplish. I'm actually using this on an Import Procedure. I have to rename the Excel Sheet I'm trying to import to "Sheet1" for the package to execute. I do however use the Create Function to Export from DTS and it works great.
February 11, 2009 at 8:55 am
On an import, I'm not sure. You'd have to know the sheet, or write ActiveX to connect to the Excel and read the list of tables. You might check here: http://www.sqldts.com/, or email Allen/Darren and see if they remember how to do this.
February 19, 2009 at 9:19 am
Well the bad news is...I wasn't able to correct in the DTS ActiveX or Store Procedure. But the good news, I was able to research and found a way around it that was pretty simple in VB using an OPENROWSET. It looks something like this in case someone else may need this answer in the future. Keep in mind that this procedure is calling 2 SQL strings which is necesary if you are calling a secondary stored procedure.
Friend Function ImportExcel(ByVal FilePath As String) As Integer
Dim conn As New SqlClient.SqlConnection(connString)
Dim cn As New ADODB.Connection
Dim rsT As ADODB.Recordset
Dim strTbl As String
Dim strSQL As String
Dim strSQL2 As String
Dim lngRecsAff As Long
cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & FilePath & _
";Extended Properties=Excel 8.0")
rsT = cn.OpenSchema(ADODB.SchemaEnum.adSchemaTables)
strTbl = rsT.Fields("TABLE_NAME").Value
strTbl = strTbl.Replace(Chr(39), "")
cn.Close()
'Import by using OPENROWSET and SELECT query
strSQL = "insert INTO table_name select * from " & _
"OPENROWSET('Microsoft.Jet.OLEDB.4.0', " & _
"'Excel 8.0;Database=" & FilePath & "'," & _
"'Select * from [" & strTbl & "]')"
Try
Dim cmd As New SqlClient.SqlCommand(strSQL, conn)
conn.Open()
cmd.CommandTimeout() = 0
cmd.ExecuteNonQuery()
'MsgBox("Records Imported: " & cmd.ExecuteNonQuery())
Catch ex As SqlClient.SqlException
MsgBox(ex.Message)
Finally
conn.Close()
End Try
strSQL2 = "exec sp"
Try
Dim cmd2 As New SqlClient.SqlCommand(strSQL2, conn)
conn.Open()
cmd2.CommandTimeout() = 0
MsgBox("Records Count: " & cmd2.ExecuteNonQuery())
Catch ex As SqlClient.SqlException
MsgBox(ex.Message)
Finally
conn.Close()
End Try
End Function
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply