Solved

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

Posted on 2014-11-14
7
127 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

My client has a dictionary table. They're defining a list of standard naming convention. Now, they are requiring my team to provide us a mechanism how to match new incoming data with existing data in their system.
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.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.

707 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