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

aibusinesssolutions

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.

KuldeepReddy

ASKER

Is there a way to convert them..Pls let me know

aibusinesssolutions

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
)'

KuldeepReddy

ASKER

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"

aibusinesssolutions

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])'

KuldeepReddy

ASKER

Hi I have Changed the Select Statement, But still getting the same error

aibusinesssolutions

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.

aibusinesssolutions

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)

KuldeepReddy

ASKER

This is getting executed finely and the records are getting inserted

aibusinesssolutions

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.

KuldeepReddy

ASKER

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])

aibusinesssolutions

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])'

aibusinesssolutions

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

aibusinesssolutions

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bulk Insert Task Stored Procedure To Load Text File wit &quot; and |, SSIS

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