Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

.csv file data load into SQL Server (same date file multiple times and figure out inside no of column and based on insert particular table.) Expand / Collapse
Author
Message
Posted Wednesday, April 24, 2013 1:19 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, February 7, 2014 1:45 PM
Points: 26, Visits: 266


/****** Object: Table [dbo].[Paychex_t_ClockID] Script Date: 04/23/2013 14:59:07 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Paychex_t_ClockID]') AND type in (N'U'))
DROP TABLE [dbo].[Paychex_t_ClockID]
GO



/****** Object: Table [dbo].[Paychex_t_ClockID] Script Date: 04/23/2013 14:59:07 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Paychex_t_ClockID](
[Employee] [varchar](50) NULL,
[First Name] [varchar](100) NULL,
[Last Name] [varchar](100) NULL,
[Date] Datetime null,
[ClockID] int NULL,

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

---------------------------------


/****** Object: Table [dbo].[Paychex_t_ClockData] Script Date: 04/23/2013 14:59:07 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Paychex_t_ClockData]') AND type in (N'U'))
DROP TABLE [dbo].[Paychex_t_ClockData]
GO



/****** Object: Table [dbo].[Paychex_t_ClockData] Script Date: 04/23/2013 14:59:07 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Paychex_t_ClockData](
[Employee] [varchar](50) NULL,
[Title] [varchar](1000) NULL,
[First Name] [varchar](100) NULL,
[Last Name] [varchar](100) NULL,
[Start Date] Datetime NULL,
[Start Time ] time NULL,
[End Time] time NULL,
[Total Hours ] float NULL,
[OT1] [varchar](50) NULL,
[Pay Type Code ] [varchar](50) NULL,
[Pay Type Description] [varchar](50) NULL,
[Salary Type] [varchar](50) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

---------------------------------------



IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__FileContr__Proce__2355CBBA]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[FileControl] DROP CONSTRAINT [DF__FileContr__Proce__2355CBBA]
END

GO


/****** Object: Table [dbo].[FileControl] Script Date: 04/24/2013 14:56:14 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FileControl]') AND type in (N'U'))
DROP TABLE [dbo].[FileControl]
GO


/****** Object: Table [dbo].[FileControl] Script Date: 04/24/2013 14:56:14 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[FileControl](
[ImportedFileName] [varchar](1000) NOT NULL,
[RecordsImported] [int] NOT NULL,
[ProcessDate] [datetime] NOT NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[FileControl] ADD DEFAULT (getdate()) FOR [ProcessDate]
GO


Above I created 3 tables for loading data from .csv file.
In post, I convert my .csv file into .xls format because not permitted .csv file attachment.
also added my package also.

---> Now getting error when importing data from same location and even how to verify which file going from which table.
--> one more question also if same date file comes twice time then before inserting want to delete those records which have same startdate.

And I am new with SSIS.......

In Advance appreciate your help.
Please advice me.


  Post Attachments 
Paychex.png (4 views, 18.46 KB)
Paychex1.png (2 views, 26.75 KB)
Custom-4232013114030.xls (1 view, 28.00 KB)
Custom-4232013114227.xls (1 view, 28.83 KB)
Custom-4232013114312.xls (1 view, 196.86 KB)
Custom-4232013123150.xls (1 view, 98.50 KB)
Paychex.rar (2 views, 56.36 KB)
Post #1446203
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse