July 22, 2008 at 12:51 am
Public Sub Main()
'
' Add your code here
Dim excelFile As String 'Excel file to be imported.
Dim connectionString1 As String 'for Connection to Excel file
Dim excelConnection As OleDbConnection
Dim connectionString2 As String 'for SQL
Dim dbconnection As SqlConnection
Dim currentTable As String
Dim intSuccess As Integer
If Dts.Variables("run").Value.ToString = "True" Then
excelFile = Dts.Variables("path").Value.ToString & Dts.Variables("excelfile").Value.ToString
'connectionString1 = ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & excelFile & ";Extended Properties=""Excel 12.0;IMEX=1;HDR=NO""")
connectionString1 = ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & excelFile & ";Extended Properties=""Excel 12.0;IMEX=1;HDR=NO""")
'Excel connection
excelConnection = New OleDbConnection(connectionString1)
excelConnection.Open()
'SQL connection
connectionString2 = "Data Source=TMUZVIDZIWA;Initial Catalog=TESTDB; Integrated Security=True;"
dbconnection = New SqlConnection(connectionString2)
dbconnection.Open()
Dim command As New OleDbCommand(Dts.Variables("querystring").Value.ToString, excelConnection)
Dim rdr As OleDbDataReader = command.ExecuteReader
Dim BulkCopy As New SqlBulkCopy(dbconnection)
BulkCopy.DestinationTableName = Dts.Variables("destinationtable").Value.ToString
BulkCopy.WriteToServer(rdr)
End If
'
Dts.TaskResult = Dts.Results.Success
End Sub
AND THE ERROR IS:
The timeout period elapsed prior to completion of the operation or the server is not responding.
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParserStateObject.ReadPacket(Int32 bytesExpected)
at System.Data.SqlClient.TdsParserStateObject.ReadBuffer()
at System.Data.SqlClient.TdsParserStateObject.ReadByte()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.ProcessAttention(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.ProcessPendingAck(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParserStateObject.WriteSni()
at System.Data.SqlClient.TdsParserStateObject.WritePacket(Byte flushMode)
at System.Data.SqlClient.TdsParser.WriteByteArray(Byte[] b, Int32 len, Int32 offsetBuffer, TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.WriteString(String s, Int32 length, Int32 offset, TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.WriteValue(Object value, MetaType type, Byte scale, Int32 actualLength, Int32 encodingByteSize, Int32 offset, TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.WriteBulkCopyValue(Object value, SqlMetaDataPriv metadata, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternal()
at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount)
at System.Data.SqlClient.SqlBulkCopy.WriteToServer(IDataReader reader)
at ScriptTask_0d3e9f888adb421bb7b33fd01a39f453.ScriptMain.Main() in dts://Scripts/ScriptTask_0d3e9f888adb421bb7b33fd01a39f453/ScriptMain:line 54
July 22, 2008 at 5:38 am
Why are you using a script task rather than just using an OLEDB Source and destination?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply