?
Solved

Error when using File system task to create dynamic directory in SSIS

Posted on 2014-11-14
7
Medium Priority
?
131 Views
Last Modified: 2016-02-11
Hi, I have a ssis package that includes creating a new directory based on file name of a file.  

I am using a file system task.  When I run the package everything works but I get the following error:

The connection '[new directory path here]" is not found.  This error is thrown by Connections collection when the specific connection element is not found.

I have three variables;
strDestinationArchive: directory path that the new directory should be added to
strArchiveFolderDate: substring of the filename which will be the new directory name
strDestinationArchivefolder: strDestinationArchive + strArchiveFolderDate

In my File System Task properties I have my SourceVariable set to strDestinationArchivefolder
Then I have an Expression added for Destination = strDestinationArchivefolder

I'm not sure how to get rid of this error.  Everything is working fine but this error is bugging me.  Can someone help me out?
0
Comment
Question by:imstac73
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40443894
<wild guess based on past experience>  Make sure both the paths have the final backslash character \, otherwise it may think the last subfolder is a partial file name and cut it off.

Just for kicks and giggles, create a script task (vb.net) right before the FSO task, pass all three variables as read only variables, then inside the script task write this:

msgbox Dts.Variables("strDestinationArchive").Value
msgbox Dts.Variables("strArchiveFolderDate").Value
msgbox Dts.Variables("strDestinationArchivefolder").Value

Then post the results into this question.
0
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 40445735
Hi, May be you could try deleting the File system task object and recreate it, read this worked to someone on net.  

Cheers.
0
 

Author Comment

by:imstac73
ID: 40448056
Created the script task and ran it.

Received an error on the strdestinationarchivefolder. image attached

My strdestinationarchivefolder expression value is as follows: @[User::strDestinationArchive]+ @[User::strArchiveFolderDate]   +"\\"
destinationarchiveerror.png
0
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 

Author Comment

by:imstac73
ID: 40448064
Tried recreating System File Task but received the same error.
0
 

Accepted Solution

by:
imstac73 earned 0 total points
ID: 40448432
I used a script task to assign the strDestinationArchiveFolder value and it got rid of the error.  Here is the blog I used to help.  http://sqlserversolutions.blogspot.com/2009/01/creating-directory-using-ssis.html
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40448476
Just to make this a better question for everyone that comes across it with the same issue, how about posting your script task code as a comment, and we'll accept that as the answer.   Thanks in advance.
0
 

Author Closing Comment

by:imstac73
ID: 40459262
Resolved the issue by using the blog link I posted.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question