tasks fail when loop is added DOWNSTREAM

[groleau]

The goal was to load the data in identically structured CSV files--all files in directory.

First attempt at building package with for each container got all sorts of cryptic error messages.

So I deleted the entire package and created one with the Import/Export wizard and a single file name.

Execute package gave me no errors or warnings.

The wizard creates three control tasks: DROP TABLE, CREATE TABLE, and data flow

I added the for loop and identified the directory, and put the filename in a variable.

I disconnected the data flow from the create table, and dragged it into the for-each.

I connected the create table to the for-each.

edited the expression to make the input connection manager user the variable. Execute package got errors in the drop table.

--
Wes Groleau

http://www.Parkview.com/

 

[mrdenny]

Personally, I'd skip SSIS all together.

Use the xp_dirtree command to get the list of files.

xp_dirtree 'd:\Path\To\Files\', 1, 1

Then use a cursor and BULK INSERT to load the data.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[groleau]

Sounds like a solution worth looking into. Thanks.

xp_dirtree - does the "xp" mean it won't work on Win 2000 ?

Will it break when the subdirectories also contain the .xls versions?

What would the bulk insert look like? Can SSIS call BULK_INSERT and xp_dirtree? Or would I just use a stored
proc (every week) to find all new records and mail to the responsible people?

--
Wes Groleau

http://www.Parkview.com/

 

[mrdenny]

xp_dirtree is an extended stored procedure. It will work on any operating system.

There is no need for SSIS with my solution. It all runs within a single T/SQL batch such as a stored procedure.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[groleau]

SSIS worked fine - loaded five months data in 15 seconds.

The problem was that (IMHO) the BIDS designer is buggy and putting the data flow into the loop container involves more steps than it should, not all of them intuitive.

But I suspect using xp_dirtree and bulk insert would have taken at least as much work to save some percentage of fifteen seconds. The SSIS package is graphically self-documenting.

--
Wes Groleau

http://www.Parkview.com/