August 24, 2011 at 1:12 am
Hi All,
I want to import data from flat file into SQL Server table using SQL CLR ..
Also, we need to do some manipulation /formating of the datetime column
Size of the flat file is 20 gb per daily job..
As of now we are using bulk insert utility
============================================================================
CREATE TABLE [dbo].[I_ERCH](
[BELNR] [varchar](12) NULL,
[BUKRS] [varchar](4) NULL,
[SPARTE] [varchar](2) NULL,
[GPARTNER] [varchar](10) NULL,
[VKONT] [varchar](12) NULL,
[VERTRAG] [varchar](10) NULL,
[BEGABRPE] [datetime] NULL,
[ENDABRPE] [datetime] NULL,
[ABRDATS] [datetime] NULL,
[STORNODAT] [datetime] NULL,
[ABRVORG] [varchar](2) NULL,
[SIMULATION] [varchar](4) NULL,
[BERGRUND] [varchar](2) NULL,
[TOBRELEASED] [varchar](1) NULL,
[HVORG] [varchar](4) NULL,
[ADATSOLL] [datetime] NULL,
[BELEGDAT] [datetime] NULL,
[ABLEINH] [varchar](8) NULL,
[BEGRU] [varchar](4) NULL,
[INSTGRTYPE] [varchar](4) NULL,
[KOFIZ] [varchar](2) NULL
) ON [PRIMARY]
GO
===============================Stored Procedure ================================
USE [SP_Reporting]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[USP_IMPORT_WEEKLY_ERCH]
AS
BEGIN
/*--Declaring variables to store file name and location*/
DECLARE @DATAFILE VARCHAR(1024)
,@ERRORFILE VARCHAR(1024)
,@FORMATFILE VARCHAR(1024)
,@STRQRY VARCHAR(2000)
,@FLAG VARCHAR(1)
,@STARTDATE DATETIME
,@ENDDATE DATETIME
,@DESCRIPTION VARCHAR(8000)
,@UPDATE DATETIME
,@CHKFLAG VARCHAR(1)
/*--Getting the location of data files, error files and format files*/
SELECT @DATAFILE = DataFileFolder + DataFileName
,@FORMATFILE = FormatFileFolder + FormatFileName
,@ERRORFILE = ErrorFileFolder + ErrorFileName
,@CHKFLAG = Flag
,@UPDATE=UpdatedOn
FROM T_Weekly_BacklogJob_Status
WHERE TableName = 'I_ERCH'
EXEC [USP_CHECK_WEEKLY_FILE_EXISTANCE] @DATAFILE,@CHKFLAG,@FLAG OUT, @STARTDATE OUT
/*Delete the previous records*/
IF @FLAG = 'A'
BEGIN
TRUNCATE TABLE I_ERCH
update [SAP_Reporting].[dbo].[T_Weekly_BacklogJob_Status]
set Flag='R',UpdatedOn=GETDATE()
where TableName='I_ERCH'
EXEC [USP_DROP_INDEX] 'I_ERCH'
/*--Create bulk insert query and execute it inside the try catch block*/
SELECT @STRQRY = 'INSERT INTO dbo.I_ERCH WITH (TABLOCK)
([BELNR]
,[BUKRS]
,[SPARTE]
,[GPARTNER]
,[VKONT]
,[VERTRAG]
,[BEGABRPE]
,[ENDABRPE]
,[ABRDATS]
,[STORNODAT]
,[ABRVORG]
,[SIMULATION]
,[BERGRUND]
,[TOBRELEASED]
,[HVORG]
,[ADATSOLL]
,[BELEGDAT]
,[ABLEINH]
,[BEGRU]
,[INSTGRTYPE]
,[KOFIZ])
SELECT document.[BELNR]
,document.[BUKRS]
,document.[SPARTE]
,document.[GPARTNER]
,document.[VKONT]
,document.[VERTRAG]
,dbo.USF_CONVERT_SAP_DATE_TO_SQL_DATE(document.[BEGABRPE])
,dbo.USF_CONVERT_SAP_DATE_TO_SQL_DATE(document.[ENDABRPE])
,dbo.USF_CONVERT_SAP_DATE_TO_SQL_DATE(document.[ABRDATS])
,dbo.USF_CONVERT_SAP_DATE_TO_SQL_DATE(document.[STORNODAT])
,document.[ABRVORG]
,document.[SIMULATION]
,document.[BERGRUND]
,document.[TOBRELEASED]
,document.[HVORG]
,dbo.USF_CONVERT_SAP_DATE_TO_SQL_DATE(document.[ADATSOLL])
,dbo.USF_CONVERT_SAP_DATE_TO_SQL_DATE(document.[BELEGDAT])
,document.[ABLEINH]
,document.[BEGRU]
,document.[INSTGRTYPE]
,document.[KOFIZ]
FROM OPENROWSET(BULK N''' + @DATAFILE +'''
,FORMATFILE = ''' + @FORMATFILE +'''
,ERRORFILE = ''' + @ERRORFILE +''') AS DOCUMENT'
EXEC [USP_CREATE_INDEX] 'I_ERCH'
BEGIN TRY
EXEC (@STRQRY)
SET @FLAG = 'C'
SET @DESCRIPTION= 'The data has been loaded successfully'
END TRY
BEGIN CATCH
SET @FLAG = 'F'
SET @DESCRIPTION= ERROR_MESSAGE()
END CATCH
SET @ENDDATE = GETDATE()
EXEC [USP_UPDATE_WEEKLY_LOG] 'I_ERCH',@STARTDATE,@ENDDATE,@FLAG,@DESCRIPTION
END
ELSE
BEGIN
IF @CHKFLAG <> 'C' AND @FLAG <> 'N'
BEGIN
SET @FLAG = @CHKFLAG
SET @DESCRIPTION= ''
EXEC [USP_UPDATE_WEEKLY_LOG] 'I_ERCH',@STARTDATE,@ENDDATE,@FLAG,@DESCRIPTION
END
END
END
GO
==============================Format_ERCH.fmt=================================
9.0
21
1 SQLCHAR 0 14 "|" 1 BELNR Latin1_General_CI_AS
2 SQLCHAR 0 6 "|" 2 BUKRS Latin1_General_CI_AS
3 SQLCHAR 0 4 "|" 3 SPARTE Latin1_General_CI_AS
4 SQLCHAR 0 12 "|" 4 GPARTNER Latin1_General_CI_AS
5 SQLCHAR 0 14 "|" 5 VKONT Latin1_General_CI_AS
6 SQLCHAR 0 12 "|" 6 VERTRAG Latin1_General_CI_AS
7 SQLCHAR 0 10 "|" 7 BEGABRPE Latin1_General_CI_AS
8 SQLCHAR 0 10 "|" 8 ENDABRPE Latin1_General_CI_AS
9 SQLCHAR 0 10 "|" 9 ABRDATS Latin1_General_CI_AS
10 SQLCHAR 0 10 "|" 10 STORNODAT Latin1_General_CI_AS
11 SQLCHAR 0 4 "|" 11 ABRVORG Latin1_General_CI_AS
12 SQLCHAR 0 6 "|" 12 SIMULATION Latin1_General_CI_AS
13 SQLCHAR 0 4 "|" 13 BERGRUND Latin1_General_CI_AS
14 SQLCHAR 0 3 "|" 14 TOBRELEASED Latin1_General_CI_AS
15 SQLCHAR 0 6 "|" 15 HVORG Latin1_General_CI_AS
16 SQLCHAR 0 10 "|" 16 ADATSOLL Latin1_General_CI_AS
17 SQLCHAR 0 10 "|" 17 BELEGDAT Latin1_General_CI_AS
18 SQLCHAR 0 8 "|" 18 ABLEINH Latin1_General_CI_AS
19 SQLCHAR 0 4 "|" 19 BEGRU Latin1_General_CI_AS
20 SQLCHAR 0 4 "|" 20 INSTGRTYPE Latin1_General_CI_AS
21 SQLCHAR 0 2 "\r" 21 KOFIZ Latin1_General_CI_AS
============================================================================
August 24, 2011 at 11:48 am
arpitsinghal.tcs (8/24/2011)
Hi All,I want to import data from flat file into SQL Server table using SQL CLR ..
Also, we need to do some manipulation /formating of the datetime column
Size of the flat file is 20 gb per daily job..
Why use SQLCLR here? Having the file available so you can manipulate some of the data before loading it into a database table will require that you have the data loaded into memory (unless you get very creative with streaming and an ODBC text file driver). With a 20GB flat-file, unless you have a massive amount of RAM to burn processing this file, you'll hammer your server into submission.
I recommend SSIS for this type of work. It's efficient because it will load the file asynchronously, allowing you to manipulate the data and pass it through to the database in batches as it is read from the file. This means the entire file will not need to be loaded into memory at one time. SSIS was built from the ground up to handle ETL tasks like this.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply