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 12»»

Bulk Insert using UNC path of a Filetable Directory Expand / Collapse
Author
Message
Posted Monday, July 22, 2013 10:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 24, 2013 8:17 AM
Points: 6, Visits: 37
Overall goal: Write a Bulk Insert statement using the UNC path of a filetable directory.

Issue: When using the UNC path of the filetable directory in a Bulk Insert Statement, receiving "Operating system error code 50(The request is not supported.)" Looking for confirmation as to whether this is truly not supported or if anyone has seen this issue and resolved it.

Environment: SQL Server 2012 Standard. Windows Server 2008 R2 Standard
Post #1476134
Posted Monday, July 22, 2013 4:00 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, December 12, 2014 1:02 AM
Points: 823, Visits: 753
Is that the Filetable directory on the same server on a different one?

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1476296
Posted Monday, July 22, 2013 4:16 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 24, 2013 8:17 AM
Points: 6, Visits: 37
Same server.
Post #1476300
Posted Monday, July 22, 2013 4:17 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, December 12, 2014 1:02 AM
Points: 823, Visits: 753
And same database?

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1476302
Posted Monday, July 22, 2013 4:47 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 24, 2013 8:17 AM
Points: 6, Visits: 37
Same database. Running T-SQL statement directly from SSMS. Run as Windows Login and as 'sa'. Same result.
Post #1476306
Posted Monday, July 22, 2013 4:58 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 5:40 PM
Points: 868, Visits: 7,580
Can you post the code you are using? And if you are using Dynamic SQL, please post the generated SQL.



And then again, I might be wrong ...
David Webb
Post #1476308
Posted Monday, July 22, 2013 8:04 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 24, 2013 8:17 AM
Points: 6, Visits: 37
-----Create Schema------
CREATE SCHEMA Master_Data;

------Create Table---
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [Master_Data].[File_Load](
[Load_ID] [bigint] IDENTITY(1,1) NOT NULL,
[File_Record] [varchar](max) NOT NULL,
[IsError] [bit] NOT NULL,
CONSTRAINT [PK_MMR_File_Load] PRIMARY KEY CLUSTERED
(
[Load_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [Master_Data].[File_Load] ADD CONSTRAINT [DF_File_Load_IsError] DEFAULT ((0)) FOR [IsError]
GO

-----Create view for handling of identity field------

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[vw_File_Load]
AS
SELECT File_Record
FROM Master_Data.File_Load

GO

-----Sample File Data for file TestFile_20130731.txt-------

Claus|Santa
Fairy|Tooth
Bunny|Easter

-----Bulk Insert Script-------

BULK INSERT vw_File_Load
FROM '\\Vebasqlmmrdev01\trust\File_Table\FileTableTb\TestFile_20130731.txt'
WITH
(
ROWTERMINATOR = '0x0a'
)

------------------------------------
NOTE*** If I map the UNC path on the OS side and use the mapped drive in the BULK INSERT
statement then it works when using a windows login in SSMS. I then get into issues when trying to schedule in an SP using Dynamic SQL, mapped drives, etc... Thus why I am trying to keep the question as simple as possible: Is it possible to use the UNC path of a FileTable in a BULK INSERT statement?
Post #1476326
Posted Tuesday, July 23, 2013 4:24 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, December 12, 2014 1:02 AM
Points: 823, Visits: 753
I am able to reproduce the issue, and I note that it works if I attempt to bulk load the file from another SQL Server instance.

But what are you trying to achieve? It appears that you want to load the entire contents of a file into a table. In that case, wouldn't it be easier to do:

SELECT list_to_table(convert(varchar(MAX), file_stream), char(10))
FROM yourfiletable

Where list_to_table is a function that cracks a string into rows. See this article on my web site a for a heap of such functions: http://www.sommarskog.se/arrays-in-sql-2005.html.



Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1476471
Posted Tuesday, July 23, 2013 7:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 24, 2013 8:17 AM
Points: 6, Visits: 37
Thank you Erland. I will take a look at the approach you recommended. My concern as you mention in your article is performance. Some of the files I load are larger in nature. I had tried using something similar but found the performance lacking for the large files.

I am currently developing a process that will load each file's record into one field. (gets the file in SQL without any fuss)

Then based on a user defined set of layouts (Using a Web UI I developed) parse the field into multiple fields (in another table). A poor man's ETL so to speak.

Obviously I can write SSIS packages to do the same thing, but the method I have developed is:

1. More dynamic and requires less knowledge from the end user perspective.
2. Faster than SSIS based on what I have read and experienced.
3. More portable, having all my code in SP's instead of SSIS packages.

SQL Server 2012's FileTable functionality allows me to drop files in the UNC directory from an ftp process. Use T-SQL to loop through the files in the directory (FileTable) and kick off a Bulk Load for each file. Without publishing a single SSIS package.

Ran into issues when trying to use the UNC path.

I have a work around in place at the moment, but using the UNC path is much cleaner.
Post #1476566
Posted Tuesday, July 23, 2013 1:23 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, December 12, 2014 1:02 AM
Points: 823, Visits: 753
I think that you are correct in that BULK INSERT is more conservative on memory than a list-to-table function.

As for the setup as such, I guess SSIS fans gringe, but since I have not come around to learn SSIS myself, I am sympathetic to the idea.

I can't say why you get the error, but I assume that it is some internal restriction.


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1476767
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse