×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

tasks fail when loop is added DOWNSTREAM

tasks fail when loop is added DOWNSTREAM

tasks fail when loop is added DOWNSTREAM

(OP)
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/

RE: tasks fail when loop is added DOWNSTREAM

Personally, I'd skip SSIS all together.

Use the xp_dirtree command to get the list of files.

CODE

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)
noevil
http://www.mrdenny.com

RE: tasks fail when loop is added DOWNSTREAM

(OP)
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/

RE: tasks fail when loop is added DOWNSTREAM

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)
noevil
http://www.mrdenny.com

RE: tasks fail when loop is added DOWNSTREAM

(OP)
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/

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close