Solved

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

Posted on 2014-11-14
7
107 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
  • 4
  • 2
7 Comments
 
LVL 65

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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

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 65

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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

In couple weeks ago, I encountered an extremely difficult problem while deploying 2008 SSIS packages to a new environment (SQL Server 2014 standard).  My scenario is: We have one C# application that is calling 2008R2 SSIS packages to load text fi…
Lessons learned during ten years of interviewing for SQL Server Integration Services (SSIS) and other Extract-Transform-Load (ETL) contract roles and two years of staff manager interviewing contractors.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

762 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now