How To Access Data In A SQL Command In The OLE DB Destination Editor
Jul 14, 2006
Instead of blindly inserting all my data from a previous task into a table using "Table" as the Data Access Mode in the OLEDB Destination editor. I would like to join this output with a reference table and insert only qualifying rows. Question is "how do I access the data from previous task so that I can do a meaningful equijoin" ? I know I have to use the "SQL Command" data access mode, but what next ?
Thanks.
chiraj
View 12 Replies
ADVERTISEMENT
Oct 30, 2007
I have a revenue spreadsheet that I populate with data using SSIS. It has 18 named ranges, but only 12 will show up in the Excel Destination Editor. Is there a work-around to be able to get past the 12 displayed limitation? thanks.
View 5 Replies
View Related
Apr 14, 2008
Hi All
I have seen several posts on this issue but as yet no answer. So i'm hoping that somewhere out there knows what the issue is.
SSIS package queries database creates MS spreadsheets per customer and then emails each customer their list.
Anyway all works fine apart from the fact that the Excel Destination Editor converts the date from uk to us format. My routine is already using an MS Excel template (correctly formatted) which it copies as part of the package.
Any ideas anyone?
rgds
View 2 Replies
View Related
Aug 1, 2006
I can't seem to find a way to make the Data Flow Destination in a Business Intelligence Visual Studion Project output an MDB file for Microsoft Access.
View 4 Replies
View Related
Sep 27, 2007
I have a data flow and inside the data flow , i have a ole db source and ole db command task to execute an insert transaction ( SP).. i would like to save the error output if the insert didn;t happend into a error log table..
but when I darg an error output line ( red) to another ole db command to insert an error log , i can only see two columns( error code and error column) are available in OLEDB command advance editor related to errors.. this doesn;t tell you much information about the error.how can i grap the error reson(desc) as the error output and store into a erro log table? so that i can see what the problem is?
View 1 Replies
View Related
Jan 4, 2007
Hello,
In a Data Flow Task, I have an insert that occurs into a SQL Server 2000 table from a fixed width flat file. The SQL Server table that the data goes into is accessed through an OLE DB connection manager that uses the Native OLE DBMicrosoft OLE DB Provider for SQL Server.
In the OLE DB Destination, I changed the access mode from Table or View - fast load to Table or View because I needed to implement OLE DB Destination Error Output. The Error output goes to a SQL Server 2000 table that uses the same connection manager.
The OLE DB Destination Editor Error Output 'Error' option is configured to 'Redirect' the row. 'Set this value to selected cells' is set to 'Fail component'.
Was changing the access mode the simple reason why the insert from the flat file takes so much longer, or could there be other problems?
Thank you for your help!
cdun2
View 32 Replies
View Related
Feb 14, 2006
I got a package with data flow task. Within the data flow task I have flat file with Fiscal Calendar defined. I got another data source within the data flow task, which is OLE DB Source. I want to use SQL Command as data access mode. SQL similar to one the below is in there.
----------------------------------------------------------------------------------------------------
DECLARE @startdate DATETIME
DECLARE @enddate DATETIME
DECLARE @date DATETIME
DECLARE @id INT
SET @startdate = '1993-09-26' --Change these to
SET @enddate = '2010-09-25' --whatever you want
SET @id = 1
SET @date = DATEADD(dd, @id, @startdate)
WHILE @date <= @enddate
BEGIN
select @date CalendarDate,
DATEPART(dd, @date) CalendarDayMonth,
DATEPART(dy, @date) CalendarDayYear,
DATEPART(dw, @date) CalendarDayWeek,
DATENAME(dw, @date) CalendarDayName
SET @id = @id + 1
SET @date = DATEADD(dd, @id, @startdate)
END
----------------------------------------------------------------------------------------------------
This SQL works fine in SSMS and returns around 6000 rows. But when I plug the same SQL in OLE DB Source it returns only the first record. It is not going through the WHILE loop.
Has anyone came across this?
Thanks
Sutha
View 2 Replies
View Related
Aug 26, 2015
I'm trying to execute a simple VBS file from the Executable command line in the Execute Process Task Editor.
My line is this : cscript.exe "c:convertcsvssisXlsToCsv.vbs"
SSIS keeps saying there are illegal characters here. Â I've Googled and looked about 20 articles and I can't resolve it.
I have a ForEach that loops through Excel files and changes them to CSV files using code i found. This script takes an original Excel file and transfers it to a new CSV file in a new directory.
So in DOS at the CMD line I would type : XlsTocsv.vbs originalfile.xls newfile.csv
I have the original file and new file in the Arguments line so I'm assuming that after the script executes it will look at the filepaths in the loop and loop through them so I want it do to this when it runs:
XlsTocsv.vbs [User::@ExcelFile] [User::@CSVFile]
I just can't get it to execute and I keep getting illegal characters. Â
View 5 Replies
View Related
Nov 14, 2007
Hi all,
I have created a global variable and it will read the user input from a web application. How can i pass this variable into OLE DB destination SQL command so that i can retrieve the user specified table? Or is there a better way to do this?
View 4 Replies
View Related
Sep 21, 2006
Hi,
I have a data flow task that performs an "upsert" by directing successful rows from a Lookup to an OLE DB Command that updates rows and unsuccessful rows (Lookup error output) to an OLE DB Destination for insertion.
The problem is that execution hangs when both tasks update/insert into the same table (execution is still hung after 20 minutes). Modifying the OLE DB Destination to insert into a different table succeeds (execution completese within 2 minutes). Replacing the OLE DB Destination with a Row Count transformation also works.
Could this be due to a table-locking issue? Any suggestions?
Thanks
ray
View 6 Replies
View Related
Apr 21, 2006
I have a data flow that takes an OLE DB Source, transforms it and then uses an OLE DB Command as a destination. The OLE DB Command executes a call to a stored procedure and I have the proper wild cards indicated. The entire process runs great and does exactly what is intended to do.
However, I need to know when a SQL insert fails what record failed and I need to log this in a file somewhere. I added a Flat File Destination object and configured appropriately. I created 3 column names for the headers in the flat file and matched them with column names existing for output. When I run this package the flat file log is created ok, but no data is ever pumped into the file when a failure of the OLE DB Command occurs.
I checked the Advanced Editor for the OLE DB Command object and under the OLE DB Command Error Output node on the Input and Output Properties tab I notice that the ErrorCode and ErrorColumn output columns both have ErrorRowDisposition set to RD_NotUsed. I would guess this is the problem and why no data is written to my log file, but I cannot figure out how to get this changed (fields are greyed out so no access).
Any help would be greatly appreciated.
View 3 Replies
View Related
Apr 29, 2008
I'm using 2 OLE DB Commands; 1 to perform an insert the other an update. I have found that on the column mapping tab, I only have 10 parameters available to map to. The issue is I need 40 parameters. Am I doing something wrong? Is there a setting I am missing? Is there another way to do this? Am I out of luck .
Here is the sql query I am using, so you can see that I have the correct number of parameters listed:
Insert into Reqs_Data (G_COLLECTDATE, PROGRAM, PRODUCT_ID, TOTAL, ADDED, CHANGED, DELETED, NOT_ALLOCATED, NEWREQS, MODIFIED, LEGACY, UNCATEGORIZED, NOT_TRACED_DOWN, NOT_TRACED_UP, PASSED, PARTIALLY_PASSED, WAIVED, FAILED, NOT_VERIFIED, PLANNED_ADDED, RQTS_TOTAL_TBD_COUNT, RQTS_TOTAL_MODS_IN_MONTH, BUILD_ID, RTM_PRODUCT_ID, TRACED_UP, TRACED_DOWN, ALLOCATED, LASTMONTHTOTAL, CALC_ADD, CALC_DELETE, IS_TRACED_DOWN, IS_TRACED_UP, LEVEL3, LEVEL2, LEVEL1, LEVEL0, IPT1, IPT2, IPT3, IPT4 ) Values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
Update Reqs_Data SET TOTAL = ?, ADDED = ?, CHANGED = ?, DELETED = ?, NOT_ALLOCATED = ?, NEWREQS = ?, MODIFIED = ?, LEGACY = ?, UNCATEGORIZED = ?, NOT_TRACED_DOWN = ?, NOT_TRACED_UP = ?, PASSED = ?, PARTIALLY_PASSED = ?, WAIVED = ?, FAILED = ?, NOT_VERIFIED = ?, PLANNED_ADDED = ?, RQTS_TOTAL_TBD_COUNT = ?, RQTS_TOTAL_MODS_IN_MONTH = ?, BUILD_ID = ?, RTM_PRODUCT_ID = ?, TRACED_UP = ?, TRACED_DOWN = ?, ALLOCATED = ?, LASTMONTHTOTAL = ?, CALC_ADD = ?, CALC_DELETE = ?, IS_TRACED_DOWN = ?, IS_TRACED_UP = ?, LEVEL3 = ?, LEVEL2 = ?, LEVEL1 = ?, LEVEL0 = ?, IPT1 = ?, IPT2 = ?, IPT3 = ?, IPT4 = ?
WHERE G_Collectdate = ? AND Program = ? AND PRODUCT_ID = ?
View 5 Replies
View Related
Aug 24, 2007
Hi,
I am testing SSIS and have created a Flat File Destination. I defined the Flat File Connection as New for the first time and it worked fine. Now, I would like to go back and modify the Flat File Connection in the Flat File Destination Editor, but it allows only to create a New connection rather allowing me to edit the existing one. For testing, I can go back and create a new connection, but if my connection had 50-100 columns then it would be an issue to re-create it from scratch.
Did someone else faced this issue?
Thanks,
AQ
View 1 Replies
View Related
Jun 5, 2015
In my package there are 10 DFT.
Each DFT have source > Tranformation > Conditionsplit > Rowcount_Transformation >  Oledb Command
                                                                               Â
> Rowcount_Transformation1 >Â Oledb Command1
                                                                               Â
> Rowcount_Transformation2 >Â Oledb Command2
                                                                               Â
> Rowcount_Transformation3 >Â Oledb Command3
All update hapend on diffrent Table.I want to log in Audit table .
My audit table like
Table_Name  Insert_count Update_count
How can I log the package having multiple OLEDB Destination.
View 7 Replies
View Related
Dec 20, 2006
Hi All,
Is it possible for someone to open a msde data file in an editor (text or hex) and modify the data, then save the file back to the filesystem?
I would think this would not be allowed but I want to confirm or see if anyone has had any experiences of someone doing something like this?
Thanks.
Chris
View 4 Replies
View Related
Dec 21, 2006
I have a value with a float datatype in Excel that is actually a date. For example, the value displays 1272006 for January 27, 2006. I am trying to convert this value to a date. I can do this in several stages using T-SQL by converting the datatype to integer, using replicate to make it eight characters and than using substrings to make it look like 01-27-2006 which SQL Server will recognize as a date.
My problem is that there is nothing similar to a PAD (replicate) function using the Data Tranformation Editor in Integration Services. There is a replicate function but it merely functions to repeat a value. It doesn't appear to perform the same PAD function as the T-SQL replicate does. Here is my formula in T-SQL that works okay.
right(replicate('0', 8) + convert(varchar, cast([Date] as varchar)), 8)
I could do this by creating a staging table but I want to handle the entire transformation process within Integration Services. Can anyone offer me some advice? Thank you.
David
View 7 Replies
View Related
Jul 23, 2005
Hello,I am trying to update records in my database from excel data using vbaeditor within excel.In order to launch a query, I use SQL langage in ADO as follwing:------------------------------------------------------------Dim adoConn As ADODB.ConnectionDim adoRs As ADODB.RecordsetDim sConn As StringDim sSql As StringDim sOutput As StringsConn = "DSN=MS Access Database;" & _"DBQ=MyDatabasePath;" & _"DefaultDir=MyPathDirectory;" & _"DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" &_"PWD=xxxxxx;UID=admin;"ID, A, B C.. are my table fieldssSql = "SELECT ID, `A`, B, `C being a date`, D, E, `F`, `H`, I, J,`K`, L" & _" FROM MyTblName" & _" WHERE (`A`='MyA')" & _" AND (`C`>{ts '" & Format(Date, "yyyy-mm-dd hh:mm:ss") & "'})"& _" ORDER BY `C` DESC"Set adoConn = New ADODB.ConnectionadoConn.Open sConnSet adoRs = New ADODB.RecordsetadoRs.Open Source:=sSql, _ActiveConnection:=adoConnadoRs.MoveFirstSheets("Sheet1").Range("a2").CopyFromRecordset adoRsSet adoRs = NothingSet adoConn = Nothing---------------------------------------------------------------Does Anyone know How I can use the UPDATE, DELETE INSERT SQL statementsin this environement? Copying SQL statements from access does not workas I would have to reference Access Object in my project which I do notwant if I can avoid. Ideally I would like to use only ADO system andSQL approach.Thank you very muchNono
View 1 Replies
View Related
Nov 30, 2007
Hello,
For packages where an MS Access database is the destination, what are some ways to detect whether or not the file is 'in use'? I know there is a lock file associated with Access databases. Could it be as simple as using something from the FileSystem Object to discover whether or not there is a lock file with the same name as the destination database? I'd like to stop the package if the file is in use.
Any ideas?
Thank you for your help!
cdun2
View 10 Replies
View Related
Apr 23, 2015
I have a simple powershell script, that is writing single row to remote SQL server as sort of keepalive solution for branch machine. It opens System.Data.SqlClient.SqlConnection, SqlBulkCopy writes table, and closes connection.
Sometimes script throws an error in form of - Cannot access destination table.
We are not managing internet connectivity in that place, so I don't know for sure, but can this error be caused by connection timeout that comes when script tries to write ?
View 4 Replies
View Related
Jan 22, 2008
I'm fairly new to SSIS, and I'm looking for some general guidance on the best approach to take to a particular issue. Essentially, I've built a web application, running on a remote server, and I'm looking to take the contents of an Excel spreadsheet, load it into memory, run a whole mess of calculations against it, and then load the results into a table on MS-SQL 2005. The calculations are in a class library, written in C#, so my sense was that the best approach would be to a) load the data into a datareader, b) run the calculations against the data and c) load it to SQL. Of these steps, a) seems like the place where SSIS would come into play.
My question is, once I've got the data into a datareader, how can it be accessed from my code? I'd been playing around with DtsClient until having the realization that it was intended to be used locally. Is there a similar method for doing this sort of thing remotely?
View 3 Replies
View Related
Aug 17, 2015
From SQL Server 2014, using SQL Server Data Tools for Visual Studio - BI, I'm trying to edit a Script Component within an SSIS Data Flow Task. The 'Edit Script...' button is enabled and turns a nice shade of blue when moused over, but a click has no effect. Perhaps I'm missing a component of VSTA? Everything else seems to work correctly. What might I be missing?
View 2 Replies
View Related
Nov 10, 2010
I have a package which has an Excel source with the 'Data access mode' set to SQL command and then a sql select statement. Â When I try and hit the 'Preview...' button below the 'SQL command text' window I get the following error:
 "Error at Standard Data Flow Tasks [source tasks name]: No column information was returned by the SQL command"
Â
Ordinarily this would be down to the fact that my SQL is shocking, I hit the 'Preview...' button whilst the workbook the source is pointing at was open and it works fine??
Â
I can't figure this out, but needless to say the package errors with a NEEDSNEWMETADATA when I try and run it.
View 17 Replies
View Related
Dec 31, 2006
I have a raw file destination and am using a variable to store the filename. In an earlier task, I create the value in the variable. User:Filename ... set to C:Test.txt.
When I run the package, I get the "Error: 0xC0202070 at DFT Tekelec Call Events, RFD Tekelec [1365]: The file name property is not valid. The file name is a device or contains invalid characters". error. I then set a breakpoint to examine my variables on the DataFlow pre-execute event and found my variable showing the value "C:\Text.txt" ... so apparently XMLA is adding the escape character when it stores the value in the variable but not retracting it when it uses the value as the filename.
What am I missing? Can I not use pathing in the variable? And if that's the case, how do I specify a path. Went back through my Rational Guide to Scripting SSIS but did not find this addressed specifically ... my second option being build the fiilename by script and set the raw file destination property directly via script.
View 3 Replies
View Related
Mar 20, 2007
Trying to do a update/insert from SQL 2005 query to Access 2003 linked table.
In the Script Transformation I get this error.
Unable to cast COM object of type 'System.__ComObject' to class type 'System.Data.OleDb.OleDbConnection'. Instances of types that represent COM components cannot be cast to types that do not represent COM components; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface.
Destinatoin Oledb connection is Native OLEDB Jet 4 to Access 2003 database.
Private sqlConn As OleDb.OleDbConnection
Private sqlCmd As OleDb.OleDbCommand
Private sqlParam As OleDb.OleDbParameter
Private connstring As String
Public Overrides Sub AcquireConnections(ByVal Transaction As Object)
connMgr = Me.Connections.ConnectionOLE
'sqlConn = CType(connMgr.AcquireConnection(Nothing), SqlConnection)
connstring = connMgr.ConnectionString
sqlConn = CType(connMgr.AcquireConnection(Nothing), OleDb.OleDbConnection)
End Sub
Any help would be appreciated.
View 3 Replies
View Related
Jun 22, 2007
I have used an access linked table to import data from a sharePoint list hosted on SharePoint 2007 server.
I hope someone else has done this. (1)I Does anyone know of any other way to pull data from a SharePoint 2007 list.
(2)Has anyone pushed data to a SharePoint List from a SQL Server table?
I tried doing that via the linked table but the destination task of the data flow task does not allow me to push data into the table.
(3) Has anyone designed an SSIS package to write data to an AccESS table from a SQL Server table?
Any help would be appreciated
View 4 Replies
View Related
Apr 3, 2014
I need to see inside a SSIS 2012 project a new SSIS installed component, but in the SSDT 2010 I cannot see the SSIS Data Flow Items tab for adding data source/data destination respect to the choose toolbox items pane.
View 4 Replies
View Related
May 12, 2006
I am getting the following error running a data flow that splits the input data into multiple streams and writes the results of each stream to the same destination table:
"This operation conflicts with another pending operation on this transaction. The operation failed."
The flow starts with a single source table with one row per student and multiple scores for that student. It does a few lookups and then splits the stream (using Multicast) in several layers, ultimately generating 25 destinations (one for each score to be recorded), all going to the same table (like a fact table). This all is running under a transaction at the package level, which is distributed to a separate machine.
Apparently, I cannot have all of these streams inserting data into the same table at one time. I don't understand why not. In an OLTP system, many transactions are inserting records into the same table at once. Why can't I do that within the same transaction?
I suppose I can use a UnionAll to join them back together before writing to a single destination, but that seems like an unnecessary waste and clutters the flow. Can anyone offer a different solution or a reason why this fails in the first place?
Thanks in advance.
View 3 Replies
View Related
Sep 13, 2007
I've got an access table and I want to query a table that shows no duplicates postcode.
Therefore, I want to use the SELECT DISTINCT command in access. SELECT DISTINCT postcode FROM HOUSE
But it don't work.
How is it done in access?Any help is welcome!
Table:House
column1 : name
" : DOB
" : postcode
" : address
View 2 Replies
View Related
Jul 20, 2005
Here's the scenario. Using a campaigning applicaion for aneNewsletter. It writes to an Access table. I have an ASP web page thatwrites to another table. I chained 3 Access queries together into aMacro to copy that data to the campaigning table, give a numericidentifier to that record, and then delete all records with thatidentifier (in the 2nd table). This will keep the ASP > Access tableclean.The campaigning software allows for a pre-processing SQL command tobe run. I would like to automate the updating of the table byinitiating this Access Macro from the SQL command. Anyone have anyideas on the command/syntax?Thanks in advanceJasonJoin Bytes!
View 1 Replies
View Related
Sep 22, 2015
I'm trying to re-write my database to de-couple the interface (MS Access) from the SQL Backend. Â As a result, I'm going to write a number of Stored Procedures to replace the MS Access code. Â My first attempt worked on a small sample, however, trying to move this on to a real table hasn't worked (I've amended the SP and code to try and get it to work on 2 fields, rather than the full 20 plus).It works in SQL Management console (supply a Client ID, it returns all the client details), but does not return anything (recordset closed) when trying to access via VBA code.The Stored procedure is:-
USE [VMSProd]
GO
/****** Object: StoredProcedure [Clients].[vms_Get_Specified_Client] Script Date: 22/09/2015 16:29:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[code]....
View 4 Replies
View Related
Apr 16, 2014
I have an source file and i have to load it into the data base by changing datatype of the columns in ssis
View 1 Replies
View Related
Oct 18, 2007
Hi all,
In the Object Explorer of my SQL Server 2005 Management Studio Express, I do not have €˜Northwind€™ Database installed yet. I executed the following source code (that was copied from a book) in my VB 2005 Express:
/////////////////////----Form9.vb----//////////////////////////
Imports System.Data.SqlClient
Imports System.Data
Public Class Form9
Dim cnn1 As New SqlConnection
Private Sub Form5_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
'Compute top-level project folder and use it as a prefix for
'the primary data file
Dim int1 As Integer = InStr(My.Application.Info.DirectoryPath, "bin")
Dim strPath As String = Microsoft.VisualBasic.Left(My.Application.Info.DirectoryPath, int1 - 1)
Dim pdbfph As String = strPath & "northwnd.mdf"
Dim cst As String = "Data Source=.sqlexpress;" & _
"Integrated Security=SSPI;" & _
"AttachDBFileName=" & pdbfph
cnn1.ConnectionString = cst
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
'Create a command to create a table
Dim cmd1 As New SqlCommand
cmd1.CommandText = "CREATE TABLE FromExcel (" & _
"FirstName nvarchar(15), " & _
"LastName nvarchar(20), " & _
"PersonID int Not Null)"
cmd1.Connection = cnn1
'Invoke the command
Try
cnn1.Open()
cmd1.ExecuteNonQuery()
MessageBox.Show("Command succeeded.", "Outcome", _
MessageBoxButtons.OK, MessageBoxIcon.Information)
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
cnn1.Close()
End Try
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
'Create a command to drop a table
Dim cmd1 As New SqlCommand
cmd1.CommandText = "DROP TABLE FromExcel"
cmd1.Connection = cnn1
'Invoke the command
Try
cnn1.Open()
cmd1.ExecuteNonQuery()
MessageBox.Show("Command succeeded.", "Outcome", _
MessageBoxButtons.OK, MessageBoxIcon.Information)
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
cnn1.Close()
End Try
End Sub
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
'Declare FromExcel Data Table and RowForExcel DataRow
Dim FromExcel As New DataTable
Dim RowForExcel As DataRow
FromExcel.Columns.Add("FirstName", GetType(SqlTypes.SqlString))
FromExcel.Columns.Add("LastName", GetType(SqlTypes.SqlString))
FromExcel.Columns.Add("PersonID", GetType(SqlTypes.SqlInt32))
'Create TextFieldParser for CSV file from spreadsheet
Dim crd1 As Microsoft.VisualBasic.FileIO.TextFieldParser
Dim strPath As String = _
Microsoft.VisualBasic.Left( _
My.Application.Info.DirectoryPath, _
InStr(My.Application.Info.DirectoryPath, "bin") - 1)
crd1 = My.Computer.FileSystem.OpenTextFieldParser _
(My.Computer.FileSystem.CombinePath(strPath, "Book1.csv"))
crd1.TextFieldType = Microsoft.VisualBasic.FileIO.FieldType.Delimited
crd1.Delimiters = New String() {","}
'Loop through rows of CSV file and populate
'RowForExcel DataRow for adding to FromExcel
'Rows collection
Dim currentRow As String()
Do Until crd1.EndOfData
Try
currentRow = crd1.ReadFields()
Dim currentField As String
Dim int1 As Integer = 1
RowForExcel = FromExcel.NewRow
For Each currentField In currentRow
Select Case int1
Case 1
RowForExcel("FirstName") = currentField
Case 2
RowForExcel("LastName") = currentField
Case 3
RowForExcel("PersonID") = CInt(currentField)
End Select
int1 += 1
Next
int1 = 1
FromExcel.Rows.Add(RowForExcel)
RowForExcel = FromExcel.NewRow
Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
MsgBox("Line " & ex.Message & _
"is not valid and will be skipped.")
End Try
Loop
Try
cnn1.Open()
Using sqc1 As SqlBulkCopy = New SqlBulkCopy(cnn1)
sqc1.DestinationTableName = "dbo.FromExcel"
sqc1.WriteToServer(FromExcel)
End Using
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
cnn1.Close()
End Try
'Read the FromExcel table and display results in
'a message box
Dim strQuery As String = "SELECT * " & _
"FROM dbo.FromExcel "
Dim str1 As String = ""
Dim cmd1 As New SqlCommand(strQuery, cnn1)
cnn1.Open()
Dim rdr1 As SqlDataReader
rdr1 = cmd1.ExecuteReader()
Try
While rdr1.Read()
str1 += rdr1.GetString(0) & ", " & _
rdr1.GetString(1) & ", " & _
rdr1.GetSqlInt32(2).ToString & ControlChars.CrLf
End While
Finally
rdr1.Close()
cnn1.Close()
End Try
MessageBox.Show(str1, "FromExcel")
End Sub
End Class
///////////////////////////////////////////////////////////////////////
This is Part 1 (The length of input exceeds 50000 characters). Part 2 will be posted in this site shortly.
View 4 Replies
View Related
Jul 20, 2005
Hi!I' m sitting here now for days and I still get no conclusion on myproblem. I have an MS ACCESS .mdb File and I would like to export itwith dtswiz.exe (from MS SQL Utility Tools) via command line. (If youknow other tools which are able to do that via command line I wouldappreciate any suggestion)I tried to find another Provider Parameter (like MSDASQL) for Accessbut somehow it seems to me that Microsoft only got one. I' m also ableto address a Server which appears in the GUI but I don t need one formy ACCess DB (I only tried, if the command parameters have any effecton the converter).I would be glad if someone could help me on this.Thank youKlaus Baumgarten
View 1 Replies
View Related
