Reading a directory

I do a lot of file processing and I usually run a little script I copy
and paste to read directory information to see if a new file it there
and then process the file if it is. So, I decided to wise up and make
a stored procedure to automate a lot of that.

The pivotal step in this is that i run a command that looks like:

CREATE TABLE #DIR (FileName varchar(100))

DECLARE @.Cmd varchar(1050)
SET@.Cmd = 'DIR "' + @.Path + CASE WHEN RIGHT(@.Path, 1) = '\' THEN ''
ELSE '\' END + @.WildCard + '"'

INSERT INTO #DIR
EXEC master..xp_CmdShell @.Cmd

When I run the stored procedure I get back the files and folders in
there that match the wildcard and all is good!!!!

...Until I try to put that information into a table while calling that
stored procedure:

CREATE TABLE #Files (
Path varchar(100),
FileName varchar(100),
PathAndFileName varchar(150),
FileDateTime SmallDateTime,
FileLength int,
FileType Varchar(10))

INSERT INTO #Files
EXEC sp_GetFileNames @.Path = '\\isoft2\ftp\Legacy\Billing\', @.Wildcard
= '*.txt'

When I run this I get:

Server: Msg 8164, Level 16, State 1, Procedure sp_GetFileNames, Line
53
An INSERT EXEC statement cannot be nested.

Because I use an INSERT EXEC to with the results from the @.Cmd.

Anybody have any ideas how I can get that information into a table?

I did try to just copy the data to c:\temp\dir.txt and then bulk
import it in. But when it runs the @.Cmd to create the file it comes
back with a NULL value and my stored procedure returns two sets of
values... which I can't do.

So, I would appreciate anybody who can help.

Thanks!

-utahWhat I have done in the past is create a global temp table (##Files) and
then in the called procedure(sp_GetFileNames) insert into the global temp
table directly.

<Utahduck@.hotmail.comwrote in message
news:1172623203.275737.283660@.v33g2000cwv.googlegr oups.com...

Quote:

Originally Posted by

>I do a lot of file processing and I usually run a little script I copy
and paste to read directory information to see if a new file it there
and then process the file if it is. So, I decided to wise up and make
a stored procedure to automate a lot of that.
>
The pivotal step in this is that i run a command that looks like:
>
CREATE TABLE #DIR (FileName varchar(100))
>
DECLARE @.Cmd varchar(1050)
SET @.Cmd = 'DIR "' + @.Path + CASE WHEN RIGHT(@.Path, 1) = '\' THEN ''
ELSE '\' END + @.WildCard + '"'
>
INSERT INTO #DIR
EXEC master..xp_CmdShell @.Cmd
>
When I run the stored procedure I get back the files and folders in
there that match the wildcard and all is good!!!!
>
...Until I try to put that information into a table while calling that
stored procedure:
>
CREATE TABLE #Files (
Path varchar(100),
FileName varchar(100),
PathAndFileName varchar(150),
FileDateTime SmallDateTime,
FileLength int,
FileType Varchar(10))
>
INSERT INTO #Files
EXEC sp_GetFileNames @.Path = '\\isoft2\ftp\Legacy\Billing\', @.Wildcard
= '*.txt'
>
When I run this I get:
>
Server: Msg 8164, Level 16, State 1, Procedure sp_GetFileNames, Line
53
An INSERT EXEC statement cannot be nested.
>
Because I use an INSERT EXEC to with the results from the @.Cmd.
>
Anybody have any ideas how I can get that information into a table?
>
I did try to just copy the data to c:\temp\dir.txt and then bulk
import it in. But when it runs the @.Cmd to create the file it comes
back with a NULL value and my stored procedure returns two sets of
values... which I can't do.
>
So, I would appreciate anybody who can help.
>
Thanks!
>
-utah
>

|||Maybe I'm bot understanding your problem correctly ,, but if you did
CREATE TABLE #DIR (FileName varchar(100))

Quote:

Originally Posted by

>
DECLARE @.Cmd varchar(1050)
SET @.Cmd = 'DIR "' + @.Path + CASE WHEN RIGHT(@.Path, 1) = '\' THEN ''
ELSE '\' END + @.WildCard + '"'
>
INSERT INTO #DIR
EXEC master..xp_CmdShell @.Cmd

INSERT INTO myTABLE
SELECT filename FROM #DIR

would that not do the job?

--

Jack Vamvas
___________________________________
The latest IT jobs - www.ITjobfeed.com
<a href="http://links.10026.com/?link=http://www.itjobfeed.com">UK IT Jobs</a>

<Utahduck@.hotmail.comwrote in message
news:1172623203.275737.283660@.v33g2000cwv.googlegr oups.com...

Quote:

Originally Posted by

>I do a lot of file processing and I usually run a little script I copy
and paste to read directory information to see if a new file it there
and then process the file if it is. So, I decided to wise up and make
a stored procedure to automate a lot of that.
>
The pivotal step in this is that i run a command that looks like:
>
CREATE TABLE #DIR (FileName varchar(100))
>
DECLARE @.Cmd varchar(1050)
SET @.Cmd = 'DIR "' + @.Path + CASE WHEN RIGHT(@.Path, 1) = '\' THEN ''
ELSE '\' END + @.WildCard + '"'
>
INSERT INTO #DIR
EXEC master..xp_CmdShell @.Cmd
>
When I run the stored procedure I get back the files and folders in
there that match the wildcard and all is good!!!!
>
...Until I try to put that information into a table while calling that
stored procedure:
>
CREATE TABLE #Files (
Path varchar(100),
FileName varchar(100),
PathAndFileName varchar(150),
FileDateTime SmallDateTime,
FileLength int,
FileType Varchar(10))
>
INSERT INTO #Files
EXEC sp_GetFileNames @.Path = '\\isoft2\ftp\Legacy\Billing\', @.Wildcard
= '*.txt'
>
When I run this I get:
>
Server: Msg 8164, Level 16, State 1, Procedure sp_GetFileNames, Line
53
An INSERT EXEC statement cannot be nested.
>
Because I use an INSERT EXEC to with the results from the @.Cmd.
>
Anybody have any ideas how I can get that information into a table?
>
I did try to just copy the data to c:\temp\dir.txt and then bulk
import it in. But when it runs the @.Cmd to create the file it comes
back with a NULL value and my stored procedure returns two sets of
values... which I can't do.
>
So, I would appreciate anybody who can help.
>
Thanks!
>
-utah
>

|||utah,

You should paste your stored procedure. One thing, how are you getting from
a one column table (#Dir) to a multiple column table (#files) based upon
your insert? You are going to have to do some parsing to get all this info
into multiple columns.

-- Bill

<Utahduck@.hotmail.comwrote in message
news:1172623203.275737.283660@.v33g2000cwv.googlegr oups.com...

Quote:

Originally Posted by

>I do a lot of file processing and I usually run a little script I copy
and paste to read directory information to see if a new file it there
and then process the file if it is. So, I decided to wise up and make
a stored procedure to automate a lot of that.
>
The pivotal step in this is that i run a command that looks like:
>
CREATE TABLE #DIR (FileName varchar(100))
>
DECLARE @.Cmd varchar(1050)
SET @.Cmd = 'DIR "' + @.Path + CASE WHEN RIGHT(@.Path, 1) = '\' THEN ''
ELSE '\' END + @.WildCard + '"'
>
INSERT INTO #DIR
EXEC master..xp_CmdShell @.Cmd
>
When I run the stored procedure I get back the files and folders in
there that match the wildcard and all is good!!!!
>
...Until I try to put that information into a table while calling that
stored procedure:
>
CREATE TABLE #Files (
Path varchar(100),
FileName varchar(100),
PathAndFileName varchar(150),
FileDateTime SmallDateTime,
FileLength int,
FileType Varchar(10))
>
INSERT INTO #Files
EXEC sp_GetFileNames @.Path = '\\isoft2\ftp\Legacy\Billing\', @.Wildcard
= '*.txt'
>
When I run this I get:
>
Server: Msg 8164, Level 16, State 1, Procedure sp_GetFileNames, Line
53
An INSERT EXEC statement cannot be nested.
>
Because I use an INSERT EXEC to with the results from the @.Cmd.
>
Anybody have any ideas how I can get that information into a table?
>
I did try to just copy the data to c:\temp\dir.txt and then bulk
import it in. But when it runs the @.Cmd to create the file it comes
back with a NULL value and my stored procedure returns two sets of
values... which I can't do.
>
So, I would appreciate anybody who can help.
>
Thanks!
>
-utah
>

|||(Utahduck@.hotmail.com) writes:

Quote:

Originally Posted by

INSERT INTO #Files
EXEC sp_GetFileNames @.Path = '\\isoft2\ftp\Legacy\Billing\', @.Wildcard
>= '*.txt'

Note that the sp_ prefix is reserved for system procedures, and SQL Server
will first look for these in the master database. Do not use it for your
own code.

Quote:

Originally Posted by

When I run this I get:
>
Server: Msg 8164, Level 16, State 1, Procedure sp_GetFileNames, Line
53
An INSERT EXEC statement cannot be nested.
>
Because I use an INSERT EXEC to with the results from the @.Cmd.
>
Anybody have any ideas how I can get that information into a table?

I have an article on my web site that discusses a couple of alternatives:
http://www.sommarskog.se/share_data.html.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Reaading and Processing a Recordset from Com object

Hi everybody.

I need help with the next topic.

From a stored procedure in sqlserver, I need to call a com+ dll, this dll connect ot another database diferent to sqlserver y return this dll must return to stored procedure a recordser for ther processing.

I tried with sp_OACreate, sp_OAMethod, sp_OAGetProperty but I did not know how to process a recordset

Thanks

Erik

You can return the recordset data as a multi-dimensional array and this will be streamed to the client. You can use INSERT...EXEC on the server-side to capture the results into a temporary table for example. You can't return the recordset object directly since SQL Server will not know how to process it. See sp_OAMethod topic in BOL for more details.

Raw File as Source for Multiple Packages

I have a question regarding Raw Files. I am breaking a large package into more modular components for better processing and debugging.

The process will start with a preparatory dataflow that will create a Raw File(s). This Raw File will then be used as the source in possibly 6 data flows and/or packages.

My question is whether 1 Raw File can be read concurrently by the multiple jobs and how this would affect processing. I'm assuming that this would slow processing.

My other option is to Multicast the writing of the Raw File to 5 other versions of the file. All would be identical except for filename. Obviously this would use more disk space but this is not a concern as we have lots of disk space. Our concern is for speedy processing.

If you have experience with Raw Files, please let me know how you approached this issue. As always, blogs and specific examples are always great!

Thanks in advance.

Why not use a SQL Server table instead?|||

Do you mean to load my candidate items to a dynamically created temp table? And then I can query against this table in successive packages.

What is the process for creating the temp table? I think on my Control Flow palette, I use an ExecuteSQL task to execute a Create Temp table SQL...next a Data Flow Task can then load the table and finally after all Processing packages/flows complete, I use Execute SQL Task to Drop or Truncate the table.

Is this the process or is there a better way?

|||

omegarazor wrote:

Do you mean to load my candidate items to a dynamically created temp table? And then I can query against this table in successive packages.

What is the process for creating the temp table? I think on my Control Flow palette, I use an ExecuteSQL task to execute a Create Temp table SQL...next a Data Flow Task can then load the table and finally after all Processing packages/flows complete, I use Execute SQL Task to Drop or Truncate the table.

Is this the process or is there a better way?

This is the process, but I would like to add two refinements:

If you're going to be dynamically creating and dropping the table, be certain to build the first Execute SQL task so that it has a "IF EXISTS .. DROP TABLE; CREATE TABLE" logic, so it will run correctly regardless of whether the table already exists at run time. (Use the script generation tools in SSMS to build this script.) Set DelayValidation = True for any tasks that rely on the table, so the package can run regardless of whether the table exists.