July 7, 2017 at 8:32 am
I have a script that sets a variable to a date based on the files in a folder.
For example:
Variable: FileDate
Files:
07062017ABCDog.txt
06062017ABCDog.txt
05062017ABCDog.txt
Here I set FileDate = 05062017
And then I use this to set the connection string on a flat file connection manager using an expression.
ConnectionString = @[User::FilePath] + @[User::FileDate] + "ABCDog.txt"
This works fine locally but when it is scheduled to run on a server the file date is not set. I created an email task to send me the value of the file date and it comes in empty which is what is set during design time.
/*
string path = Dts.Variables["User::FilePath"].Value.ToString();
string filePath = Directory.EnumerateFiles(path)
.OrderBy(filename => filename)
.Where(file => file.Contains("ABCDog"))
.FirstOrDefault();
string fileName = Path.GetFileName(filePath);
string fileDate = fileName?.Substring(0, 8) ?? "";
if(fileDate.All(char.IsDigit) && !string.IsNullOrWhiteSpace(fileDate))
{
Dts.Variables["User::FileDate"].Value = fileDate;
Dts.TaskResult = (int)ScriptResults.Success;
}
else
{
Dts.TaskResult = (int)ScriptResults.Failure;
}
*/
Dts.Variables["User::FileDate"].Value = "07062017";
Dts.TaskResult = (int)ScriptResults.Success;
The code being used works when I run this locally. I tested hard coding the value and it still works locally but not when scheduled to run using a proxy user. It is still empty when ran there.
What else can I try or change?
July 7, 2017 at 8:38 am
loerac25 - Friday, July 7, 2017 8:32 AMI have a script that sets a variable to a date based on the files in a folder.For example:
Variable: FileDate
Files:
07062017ABCDog.txt
06062017ABCDog.txt
05062017ABCDog.txtHere I set FileDate = 05062017
And then I use this to set the connection string on a flat file connection manager using an expression.
ConnectionString = @[User::FilePath] + @[User::FileDate] + "ABCDog.txt"
This works fine locally but when it is scheduled to run on a server the file date is not set. I created an email task to send me the value of the file date and it comes in empty which is what is set during design time.
/*
string path = Dts.Variables["User::FilePath"].Value.ToString();string filePath = Directory.EnumerateFiles(path)
.OrderBy(filename => filename)
.Where(file => file.Contains("ABCDog"))
.FirstOrDefault();string fileName = Path.GetFileName(filePath);
string fileDate = fileName?.Substring(0, 8) ?? "";if(fileDate.All(char.IsDigit) && !string.IsNullOrWhiteSpace(fileDate))
{
Dts.Variables["User::FileDate"].Value = fileDate;
Dts.TaskResult = (int)ScriptResults.Success;
}
else
{
Dts.TaskResult = (int)ScriptResults.Failure;
}
*/
Dts.Variables["User::FileDate"].Value = "07062017";
Dts.TaskResult = (int)ScriptResults.Success;The code being used works when I run this locally. I tested hard coding the value and it still works locally but not when scheduled to run using a proxy user. It is still empty when ran there.
What else can I try or change?
Could this be a permissions issue? If the user on the server cannot see User::FilePath, that could be the problem.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 7, 2017 at 8:42 am
At first I thought the same thing, Phil. Then i read it again and, unless I'm mistaken, the OP is actually setting the value of a date to a variable, and then using this to derive the filename, rather than getting the date from the file's name. Correct? That would not imply a permission issue, or at least, not at this point.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 7, 2017 at 8:45 am
Phil Parkin - Friday, July 7, 2017 8:38 AMloerac25 - Friday, July 7, 2017 8:32 AMI have a script that sets a variable to a date based on the files in a folder.For example:
Variable: FileDate
Files:
07062017ABCDog.txt
06062017ABCDog.txt
05062017ABCDog.txtHere I set FileDate = 05062017
And then I use this to set the connection string on a flat file connection manager using an expression.
ConnectionString = @[User::FilePath] + @[User::FileDate] + "ABCDog.txt"
This works fine locally but when it is scheduled to run on a server the file date is not set. I created an email task to send me the value of the file date and it comes in empty which is what is set during design time.
/*
string path = Dts.Variables["User::FilePath"].Value.ToString();string filePath = Directory.EnumerateFiles(path)
.OrderBy(filename => filename)
.Where(file => file.Contains("ABCDog"))
.FirstOrDefault();string fileName = Path.GetFileName(filePath);
string fileDate = fileName?.Substring(0, 8) ?? "";if(fileDate.All(char.IsDigit) && !string.IsNullOrWhiteSpace(fileDate))
{
Dts.Variables["User::FileDate"].Value = fileDate;
Dts.TaskResult = (int)ScriptResults.Success;
}
else
{
Dts.TaskResult = (int)ScriptResults.Failure;
}
*/
Dts.Variables["User::FileDate"].Value = "07062017";
Dts.TaskResult = (int)ScriptResults.Success;The code being used works when I run this locally. I tested hard coding the value and it still works locally but not when scheduled to run using a proxy user. It is still empty when ran there.
What else can I try or change?
Could this be a permissions issue? If the user on the server cannot see User::FilePath, that could be the problem.
Well I created an email task just to see what was being set inside the User:FilePath and User:FileDate variable and it shows that FilePath has the path string inside it but FileDate is empty. When I run this locally the FileDate has a value when the email arrives so I don't think it has to do with permissions to the path as it is looking for a file that doesn't exist since the FileDate is empty. and I did give the proxy user full access to the paths used.
July 7, 2017 at 8:46 am
Thom A - Friday, July 7, 2017 8:42 AMAt first I thought the same thing, Phil. Then i read it again and, unless I'm mistaken, the OP is actually setting the value of a date to a variable, and then using this to derive the filename, rather than getting the date from the file's name. Correct? That would not imply a permission issue, or at least, not at this point.
That's correct Thom.
July 7, 2017 at 8:56 am
loerac25 - Friday, July 7, 2017 8:46 AMThom A - Friday, July 7, 2017 8:42 AMAt first I thought the same thing, Phil. Then i read it again and, unless I'm mistaken, the OP is actually setting the value of a date to a variable, and then using this to derive the filename, rather than getting the date from the file's name. Correct? That would not imply a permission issue, or at least, not at this point.That's correct Thom.
OK, thanks guys, didn't read closely enough.
This is a tough one to troubleshoot. How about tweaking the code just a little bit, just to be sure (untested):
Dts.Variables["User::FileDate"].Value = "07062017";
if (Dts.Variables["User::FileDate"].Value == "07062017")
Dts.TaskResult = (int)ScriptResults.Failure
else
Dts.TaskResult = (int)ScriptResults.Success;
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 7, 2017 at 11:15 am
Phil Parkin - Friday, July 7, 2017 8:56 AMloerac25 - Friday, July 7, 2017 8:46 AMThom A - Friday, July 7, 2017 8:42 AMAt first I thought the same thing, Phil. Then i read it again and, unless I'm mistaken, the OP is actually setting the value of a date to a variable, and then using this to derive the filename, rather than getting the date from the file's name. Correct? That would not imply a permission issue, or at least, not at this point.That's correct Thom.
OK, thanks guys, didn't read closely enough.
This is a tough one to troubleshoot. How about tweaking the code just a little bit, just to be sure (untested):
Dts.Variables["User::FileDate"].Value = "07062017";
if (Dts.Variables["User::FileDate"].Value == "07062017")
Dts.TaskResult = (int)ScriptResults.Failure
else
Dts.TaskResult = (int)ScriptResults.Success;
So this is great. I had this run and believe that the Script isn't being run. Not sure where to go from here.
But basically inserted this code and the failure path did not get executed and it went through the normal route.
I am creating my package in Visual Studios 2015 and I am not the one scheduling the package. I send the .dtsx package to someone and they schedule it. Could there be a reason the script isn't being ran?
July 7, 2017 at 11:37 am
Phil Parkin - Friday, July 7, 2017 8:56 AMloerac25 - Friday, July 7, 2017 8:46 AMThom A - Friday, July 7, 2017 8:42 AMAt first I thought the same thing, Phil. Then i read it again and, unless I'm mistaken, the OP is actually setting the value of a date to a variable, and then using this to derive the filename, rather than getting the date from the file's name. Correct? That would not imply a permission issue, or at least, not at this point.That's correct Thom.
OK, thanks guys, didn't read closely enough.
This is a tough one to troubleshoot. How about tweaking the code just a little bit, just to be sure (untested):
Dts.Variables["User::FileDate"].Value = "07062017";
if (Dts.Variables["User::FileDate"].Value == "07062017")
Dts.TaskResult = (int)ScriptResults.Failure
else
Dts.TaskResult = (int)ScriptResults.Success;
Turns out it was a versioning issue. I was targeting SQL server 2016 which makes use of the .Net framework 4.5.
I changed it to target "SQL server 2014" and now it works. Making use of .Net framework 4.0.
I'm guessing the .Net Framework version was the problem but it is not executing my script and working as intended. Thanks for the help here. It did give me direction in debugging this.
Found solution here:
http://techqa.info/database/question/132206/ssis-script-task-doesn%27t-appear-to-run-when-scheduled
July 7, 2017 at 12:41 pm
Well done on getting to the root of that issue.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply