Solved

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

Posted on 2014-11-14
7
116 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

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.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
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.

861 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