[AccessD] using a saved SSIS with VB.Net

Eric Barro ebarro at verizon.net
Tue May 1 09:16:05 CDT 2007


John,

Based on what I have seen so far, it will not strip the spaces so I usually
create a TEMP table and BULK INSERT the data to that temp table and then
RTRIM the fields when I import to the real table.

Here's the code I used to import padded records from Peoplesoft...


-- Create a table to hold the data
CREATE TABLE #tmpEmployees
(
	suffix	varchar(5),
	last_name	varchar(30),
	first_name	varchar(20),

	[....snipped the rest of the fields in between]

	Email	varchar(50)
)

-- Read the text file into the temp table
BULK INSERT #tmpEmployees FROM 'ImportData.csv'
WITH (FORMATFILE = 'ImportData.fmt'

-- Now read it into Employees table (this assumes same number of fields as
the importdata file otherwise we need to spell out each field)

INSERT INTO Employees
SELECT
	RTRIM(last_name) as last_name,
	RTRIM(first_name) as first_name,

	[....snipped the rest of the fields in between]

	RTRIM(email) as email
FROM #tmpEmployees

-- And then clean up
DROP TABLE #tmpEmployees 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of JWColby
Sent: Tuesday, May 01, 2007 5:49 AM
To: 'Access Developers discussion and problem solving'
Cc: dba-sqlserver at databaseadvisors.com
Subject: Re: [AccessD] using a saved SSIS with VB.Net

Eric,

I have successfully generated the fmt file.  My only concern at this point
is that the import file has spaces at the end of the valid data, padding to
make up the full width of the field.  I do NOT want the spaces, and I
suspect that BCP is going to pull in all of the spaces.  Is it possible to
tell BCP to strip the spaces, or is it possible to use BCP to pull a CSV
file into an existing table, respecting the already established field types
/ sizes in the destination table?

John W. Colby
Colby Consulting
www.ColbyConsulting.com

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Eric Barro
Sent: Monday, April 30, 2007 7:31 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] using a saved SSIS with VB.Net

Have you looked into BULK INSERT in SQL? This is supposed to be a faster
data import method.


Using T-SQL you can do something like this...

CREATE TABLE #tmpEmployees (<fieldnames here>) BULK INSERT #tmpEmployees
FROM 'c:\temp\import.csv'
WITH (FORMATFILE = 'c:\temp\importCSV.fmt'

importCSV.fmt would contain the file format...in this example it's fixed
width

8.0
18
1   SQLCHAR   0   5   ""     1	suffix	SQL_Latin1_General_CP1_CI_AS
2   SQLCHAR   0   30   ""   2	last_name	SQL_Latin1_General_CP1_CI_AS
3   SQLCHAR   0   20   ""   3	first_name	SQL_Latin1_General_CP1_CI_AS



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of JWColby
Sent: Tuesday, April 24, 2007 9:07 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] using a saved SSIS with VB.Net

The CSV file is on the same machine.  It appears that the clause that pulls
the source table (csv file) into memory is taking a ton of time.  These are
large files, the smallest are a little under 200 million bytes and the
largest are up in the 3 gigabyte range.  It appears that SQL Server does not
read a few records and append them, but rather reads the whole CSV and then
starts appending all of the assembled records.

If I were a SQL Server pro I could probably speed this up considerably.
Alas, I am not.

John W. Colby
Colby Consulting
www.ColbyConsulting.com

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Robert L. Stewart
Sent: Tuesday, April 24, 2007 11:53 AM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] using a saved SSIS with VB.Net

John,

One of the keys to getting it into SQL Server faster is to have the CSV file
on the server and not on a different machine. Network traffic can kill the
process and slow it down significantly.

Robert

At 10:22 AM 4/24/2007, you wrote:
>Date: Tue, 24 Apr 2007 10:32:44 -0400
>From: "JWColby" <jwcolby at colbyconsulting.com>
>Subject: Re: [AccessD] using a saved SSIS with VB.Net
>To: "'Access Developers discussion and problem solving'"
>         <accessd at databaseadvisors.com>
>Message-ID: <003101c7867d$6cce93a0$657aa8c0 at m6805>
>Content-Type: text/plain;       charset="us-ascii"
>
>Gustav,
>
>My bigger issue here is that there 56 of these files to import into SQL 
>Server, supposedly ~100 million records.  I have done about 8 million 
>records so far.  I really must get this thing automated such that it 
>just chunks through these CSV files without my having to be around to 
>start the next one.  I am working now on setting up the append query 
>using that syntax below into a stored procedure so that I can then just
replace the file name.
>After that I will need to write something in VB.Net or whatever to 
>execute the stored procedure feeding in all of the file names from a 
>specific directory, deleting the file once the stored procedure 
>finishes the import for a given file.
>
>I have never written a stored procedure.
>Obviously, given the above, I have never called a stored procedure from 
>code.
>
>So much to learn, so little time.
>
>Once this is imported I have to turn right around and export a subset 
>of fields from the table back out as 1 - 2 million record chunks for 
>CAS / DPV / NCOA processing, then I have to import THOSE back in to a 
>new
table.
>
>And when this set of data is finished, I have another set of about the 
>same size on the way, to which I have to perform the same processes.  I 
>soooooo need to get this process automated.
>
>John W. Colby


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

No virus found in this incoming message.
Checked by AVG Free Edition. 
Version: 7.5.463 / Virus Database: 269.5.10/774 - Release Date: 4/23/2007
5:26 PM
 

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

No virus found in this incoming message.
Checked by AVG Free Edition. 
Version: 7.5.467 / Virus Database: 269.6.2/782 - Release Date: 5/1/2007 2:10
AM
 




More information about the AccessD mailing list