Link to home
Create AccountLog in
Avatar of KuldeepReddy
KuldeepReddy

asked on

Bulk Insert Task Stored Procedure To Load Text File wit " and |, SSIS

Hi Experts I had a Text File coming into a Shared folder every day and I have to load this data into SQlServer Table and the File (.Dat File) comes in the this Fashion( | as Column Delimiter and " as TextQualifier). I wrote a coStored procedure to Load this Data into the Table But I am getting an error. Actually one of our experts has designed an SSIS package to me but I was a bit confused in using that Package, so I developed this Code. I am here attaching the Code Which I wrote and the Table script to Which I am loading the data into and the Data Sample that I am trying to load. When I am Executing this Procedure I am getting this Error... Please give me a solution as this is very urgent.. Thanks in Advance



Msg 241, Level 16, State 1, Line 45
Conversion failed when converting datetime from character string.
 
When I click on the Error it is pointing to this line in the Code
But I dont Understand What this was..
 
 
WITH (FIELDTERMINATOR =''|'', ROWTERMINATOR = ''\n'') '

Open in new window

AdEnroll-Table-Script.txt
AdEnroll-Txt-File-Script.txt
AdEnroll-20090227-1030.txt
Avatar of aibusinesssolutions
aibusinesssolutions
Flag of United States of America image

It looks like in your temp table your data columns are declared as
      [StartDate] [nvarchar](500) NULL,
      [ExpStartDate] [nvarchar](500) NULL,
      [ReEntryDate] [nvarchar](500) NULL

And in your actual table they are declared this way
      [StartDate] [datetime] NULL,
      [ExpStartDate] [datetime] NULL,
      [ReEntryDate] [datetime] NULL,

So it is trying to put nvarchar's into a datetime field without a conversion, and it can't.
Avatar of KuldeepReddy
KuldeepReddy

ASKER

Is there a way to convert them..Pls let me know
Well you could modify your stored procedure.

CREATE  table #AdEnroll(
      [AdEnrollID] [nvarchar](500) NOT NULL,
      [SyStudentID] [nvarchar](500) NULL,
      [StuNum] [nvarchar](500) NULL,
      [AppRecDate] [nvarchar](500) NULL,
      [StartDate] [nvarchar](500) NULL,
      [ExpStartDate] [nvarchar](500) NULL,
      [ReEntryDate] [nvarchar](500) NULL,
      [GradDate] [nvarchar](500) NULL,
      [StatusDate] [nvarchar](500) NULL,
      [AmRepID] [nvarchar](500) NULL,
      [SyCampusID] [nvarchar](500) NULL,
      [SySchoolStatusID] [nvarchar](500) NULL,
      [adProgramDescrip] [nvarchar](500) NULL,
      [adProgramVersionID] [nvarchar](500) NULL,
      [DateAdded] [nvarchar](500) NULL,
      [AdProgramID] [nvarchar](500) NULL,
      [AdStartDateID] [nvarchar](500) NULL,
      [OriginalExpStartDate] [nvarchar](500) NULL,
      [DateLstMod] [nvarchar](500) NULL
) '

To

CREATE TABLE #AdEnroll(
      [AdEnrollID] [int] NOT NULL,
      [SyStudentID] [int] NULL,
      [StuNum] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [AppRecDate] [datetime] NULL,
      [StartDate] [datetime] NULL,
      [ExpStartDate] [datetime] NULL,
      [ReEntryDate] [datetime] NULL,
      [GradDate] [datetime] NULL,
      [StatusDate] [datetime] NULL,
      [AmRepID] [int] NULL,
      [SyCampusID] [int] NULL,
      [SySchoolStatusID] [int] NULL,
      [adProgramDescrip] [varchar](120) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [adProgramVersionID] [int] NULL,
      [DateAdded] [datetime] NULL,
      [AdProgramID] [int] NULL,
      [AdStartDateID] [int] NULL,
      [OriginalExpStartDate] [datetime] NULL,
      [DateLstMod] [datetime] NULL
)'
But in the Source Flat File the Data comes with a Text Qualifier (" ") For each field.
If I create the temp table like this the values will not get inserted right? This is my Data Sample and I have to Eliminate those Double Quotes(" ")

"49770"|"105500"|"LA04043694"|"2004-04-13 00:00:00"|"2004-08-05 00:00:00"|"2004-08-05 00:00:00"|""|"2010-03-27 00:00:00"|"2004-08-05 00:00:00"|"20578"|"14"|"13"|"Bachelor of Business Administration SU04"|"25614"|"2004-04-13 17:10:55.137000000"|"137"|""|"2004-08-05 00:00:00"|"2009-02-22 14:49:49.143000000"
"49774"|"105340"|"BR04043699"|"2004-04-14 00:00:00"|"2004-06-28 00:00:00"|"2004-06-28 00:00:00"|""|"2006-12-16 00:00:00"|"2006-12-16 00:00:00"|"20573"|"14"|"17"|"Bachelor of Business Administration SU04"|"25614"|"2004-04-14 15:38:13"|"137"|""|"2004-06-28 00:00:00"|"2009-02-22 14:06:14.863000000"
"49783"|"105220"|"PA04043731"|"2004-04-16 00:00:00"|"2004-06-28 00:00:00"|"2006-10-02 00:00:00"|"2006-10-02 00:00:00"|"2009-02-18 00:00:00"|"2009-02-18 00:00:00"|"20563"|"14"|"17"|"BS Information Technology"|"64160"|"2004-04-16 12:04:49.567000000"|"223"|""|"2006-10-02 00:00:00"|"2009-02-22 19:02:56.170000000"
"49811"|"105503"|"LA04043762"|"2004-04-24 00:00:00"|"2004-06-28 00:00:00"|"2004-06-28 00:00:00"|""|"2010-03-27 00:00:00"|"2006-09-20 00:00:00"|"20563"|"14"|"45"|"Bachelor of Business Administration SU04"|"25614"|"2004-04-24 17:00:05.617000000"|"137"|""|"2004-06-28 00:00:00"|"2009-02-25 05:43:34.463000000"
"49888"|"84019"|"FI04053850"|"2004-05-05 00:00:00"|"2004-07-05 00:00:00"|"2008-11-13 00:00:00"|"2008-11-13 00:00:00"|"2012-03-29 00:00:00"|"2008-11-13 00:00:00"|"20563"|"14"|"24"|"BBA Business Admin"|"25967"|"2004-05-05 11:03:23.803000000"|"137"|""|"2006-10-02 00:00:00"|"2009-02-22 13:53:20.217000000"
"49895"|"103486"|"NE04053858"|"2004-05-05 00:00:00"|"2004-06-28 00:00:00"|"2006-08-07 00:00:00"|"2006-08-07 00:00:00"|"2009-12-21 00:00:00"|"2007-05-22 00:00:00"|"20558"|"14"|"45"|"BBA Business Admin"|"25967"|"2004-05-05 14:46:57.560000000"|"137"|""|"2006-08-07 00:00:00"|"2009-02-25 05:43:34.463000000"
Ok, yeah, you will need to modify your INSERT query then, since it is trying to insert NVarChar values into a DateTime field.  You should be able to just use Cast.

set @sql = @sql + ' INSERT INTO [C2000].[dbo].[AdEnroll]
           ([AdEnrollID],[SyStudentID],[StuNum],[AppRecDate],[StartDate],[ExpStartDate],[ReEntryDate],[GradDate],[StatusDate],[AmRepID]
           ,[SyCampusID],[SySchoolStatusID],[adProgramDescrip],[adProgramVersionID],[DateAdded],[AdProgramID],[AdStartDateID]
           ,[OriginalExpStartDate],[DateLstMod])
select [StuNum], Cast(StartDate as DateTime), Cast(ExpStartDate as Datetime)... etc. etc. from #AdEnroll where [AdEnrollID] not in (select [AdEnrollID] from [C2000].[dbo].[AdEnroll])'
Hi I have Changed the Select Statement, But still getting the same error
It's hard to debug that because you are using one long @sql string instead of using a transaction with multiple executions.

It looks like your error is coming from here:
bulk insert #AdEnroll from ''E:\SSH\get\'+@fileName+'''
WITH (FIELDTERMINATOR =''|'', ROWTERMINATOR = ''\n'') '

Which would mean that the error is being caused by the actual Bulk Insert, but I don't get why it would be trying to convert a string to a datetime in that function, since you have them declared as NVARCHAR.  I'll see if I can find anything on that.
What do you get if you just run this?

set @sql = '
CREATE  table #AdEnroll(
      [AdEnrollID] [nvarchar](500) NOT NULL,
      [SyStudentID] [nvarchar](500) NULL,
      [StuNum] [nvarchar](500) NULL,
      [AppRecDate] [nvarchar](500) NULL,
      [StartDate] [nvarchar](500) NULL,
      [ExpStartDate] [nvarchar](500) NULL,
      [ReEntryDate] [nvarchar](500) NULL,
      [GradDate] [nvarchar](500) NULL,
      [StatusDate] [nvarchar](500) NULL,
      [AmRepID] [nvarchar](500) NULL,
      [SyCampusID] [nvarchar](500) NULL,
      [SySchoolStatusID] [nvarchar](500) NULL,
      [adProgramDescrip] [nvarchar](500) NULL,
      [adProgramVersionID] [nvarchar](500) NULL,
      [DateAdded] [nvarchar](500) NULL,
      [AdProgramID] [nvarchar](500) NULL,
      [AdStartDateID] [nvarchar](500) NULL,
      [OriginalExpStartDate] [nvarchar](500) NULL,
      [DateLstMod] [nvarchar](500) NULL
) '


set @sql = @sql +'
bulk insert #AdEnroll from ''E:\SSH\get\'+@fileName+'''
WITH (FIELDTERMINATOR =''|'', ROWTERMINATOR = ''\n'') '

exec(@sql)
This is getting executed finely and the records are getting inserted
Ok, I downloaded the files and setup a test DB, it looks like some of the date fields are empty. So it's returning an empty string, which it can't convert to a DateTime.  
Should I use Is Null In the Select Statement Like
select [StuNum], ISNULL((Cast(StartDate as DateTime),''), ISNULL(Cast(ExpStartDate as Datetime),'')... etc. etc. from #AdEnroll where [AdEnrollID] not in (select [AdEnrollID] from [C2000].[dbo].[AdEnroll])
Well they aren't actually NULL, so you can't use ISNULL, they are empty strings.

You'll have to do something like this

select [AdEnrollID], [SyStudentID], [StuNum], CASE WHEN ISDATE(AppRecDate) = 1 THEN Cast(AppRecDate as DateTime) END, CASE WHEN ISDATE(StartDate) = 1 THEN Cast(StartDate as DateTime) END, etc. etc.

from #AdEnroll where [AdEnrollID] not in (select [AdEnrollID] from [dbo].[AdEnroll])'
I just tested that out on the first 6 columns, and it worked perfectly, you can break the code up to make it easier to read, like this.

select [AdEnrollID],
[SyStudentID],
[StuNum],
CASE WHEN ISDATE(AppRecDate) = 1 THEN Cast(AppRecDate as DateTime) END,
CASE WHEN ISDATE(StartDate) = 1 THEN Cast(StartDate as DateTime) END,
etc. etc.

from #AdEnroll where [AdEnrollID] not in (select [AdEnrollID] from [dbo].[AdEnroll])'
ASKER CERTIFIED SOLUTION
Avatar of aibusinesssolutions
aibusinesssolutions
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account