Help getting error when using operation rename a file in the File system task editor?

Does anyone know how to do this using variables? Everytime I try it, I get the

Error: Failed to lock variable for read access with error 0xc00100001.

I also tried it writing a script and still the same error. If I hard code the values into the variables it works fine but I will be running this everday so that it will pull in the current date along with the filename. So the value of the variables will change everyday. Here is my expression:

@.[User::Variable] +(DT_WSTR,4) YEAR( GETDATE() )+"0"+(DT_WSTR,2) MONTH( GETDATE() ) + (DT_WSTR,2) DAY( GETDATE() )

The result:

C:\Documents and Settings\mroush\Desktop\OSU20060818

the 20060818 part will change everyday ie.(tomorrow will be 20060819, next day 20060820 and so on.)

I am not sure how you are doing this, were is the expression and which variable is selected in the File System Task editor.

How about this-

Create a variable called Filename.

Then go to the Properties grid for the Filename variable and set the EvaluateAsExpression to true. Now set the Expression property to your expression.

Now go back to the File System Task, and select variable Filename for the operation you want.

Is this what you have done or not?

Do you really need to rename the file? Normally I would use an Expression, like you have above, but on the ConnectionString property for my File/FlatFile connection, such that when I use the connection it automatically gets the "current" filename.

|||

Darren, thanks for the reply and

yes I done this in the expression property of the variable and set it to true, that works except it adds the file name twice. The result is below.

C:\Documents and Settings\mroush\Desktop\Cnet Apps File\Apps0821.txt0821.txt

How do I get it to only evaluate once and and it looks like everyday that it runs, it is going to just keep adding to the string? Is there away to make it clear the old filename before renaming the current one everyday or is there another way to do this? I have to rename the file with the current date everyday, instead of using 1 filename and setting the overwrite destination to true. Maybe this can't be done using this task?

Mike

|||

I don't get why there appears to be two filename parts. You have got two variables, one for the base path an then one for the full filename, upon which you set the expression?

Creating a new file everyday should be fine using an expression. If you are exporting thsi file, then why not set the expression on the flat file connection manager's connection string property, and save the extra step of the rename.

|||

Hi Darren, thanks again for responding.

Here are the two files that I am working with:

The file comes in everyday and is zipped as AAA20060822 with no extension. The numbers are the date and it changes everyday with the current date so I need the expression to get that date everyday when a new file appears. Like tomorrows file will be AAA20060823

The filename that I need to rename it as is BBBB0822.txt. It has to be renamed this way with the date format this way. I'm using 2 variables so that the dates are updated everyday automatically so that the task can be scheduled nightly. I know this is confusing but this is how the file comes in everday and this is how it needs to be renamed so the data can be processed correctly through our mainframe.

I've tried setting the isdestination and issourcepath variables to true and using the expressions in the source and destination properties through the expressions option but it errors telling me that it can't lock the variables or the variables have been renamed or do not exist in the container. Error 0xC00100001

So then I tried putting the expressions in the variables expression property and setting the evaluateasexpression property to true but it evaluates the expression twice each time I run the task adding the dates twice to the path causing it not to find the source file. I'm stupmed! I don't know what else to do? I works either way without an expression hard coding the filenames but not using variables that will change everday with the date?

Thanks,

mike

help create query against two tables

stepdefinition has
steptype
flowid
stepid

task has
taskid
flowid
stepid

flowid and stepid for both tables match; meaning that if i found a record in task with a certain taskid, i could query stepdefinition with the same flowid and stepid to find the steptype.

well, i wanna do it the other way around. I query stepdefinition to find a list of flowids and stepids for a specific steptype.

select flowid, stepid from stepdefinition where steptype = -3

Now, I want to find all taskids in task for each flowid/stepid combination

here's a visual

http://www.filecabin.com/up1/1144249279-task.gifselect taskid from task
where stepID+flowID in
(select stepID+flowID from stepdefinition where steptype = -3)
order by taskid

works|||select t.taskid, t.stepID, t.flowID
from task as t
join stepdefinition as s
on ( t.stepID = s.stepID
and t.flowID = s.flowID )
where s.steptype = -3

Help batch script task stuck in a loop

I have a .bat file that calls a script file to log in to a server and upload a file to my pub account. When I run the .bat file it just executes the command over and over and never executes the script file. Does anyone know why? Am I missing something in my script or .bat file commands below?

my .bat file contains this>ftp -s:script.txt

my script file contains this:

open myserver.com
username
password
put W:\UPLOAD\doc.txt
bye
exit

Thanks,

Mike

Looping doesn't seem reminiscent of the batch file or the script you have listed here. I sounds like it may be due to some logic in your package. Any chance this is happening in your package?|||

It works now.

|||

Mike,

I have the same problem, can you post the solution?

Thanks.

Sebastian.

|||

Instead of using the batch file, I used a ftp script and called it using the Execute Process Task Editor:

Executable: C:\WINDOWS\system32\ftp.exe

Arguments: -s:"C:\Documents and Settings\me\Desktop\SQL 2005 DMS project-Prod\Download E-Apps\ftpscript.txt"

the ftpscript.txt file contains the following:

open hostname
me
password
Ascii
put filepath\name
bye
exit

sql

Help batch script task stuck in a loop

I have a .bat file that calls a script file to log in to a server and upload a file to my pub account. When I run the .bat file it just executes the command over and over and never executes the script file. Does anyone know why? Am I missing something in my script or .bat file commands below?

my .bat file contains this>ftp -s:script.txt

my script file contains this:

open myserver.com
username
password
put W:\UPLOAD\doc.txt
bye
exit

Thanks,

Mike

Looping doesn't seem reminiscent of the batch file or the script you have listed here. I sounds like it may be due to some logic in your package. Any chance this is happening in your package?|||

It works now.

|||

Mike,

I have the same problem, can you post the solution?

Thanks.

Sebastian.

|||

Instead of using the batch file, I used a ftp script and called it using the Execute Process Task Editor:

Executable: C:\WINDOWS\system32\ftp.exe

Arguments: -s:"C:\Documents and Settings\me\Desktop\SQL 2005 DMS project-Prod\Download E-Apps\ftpscript.txt"

the ftpscript.txt file contains the following:

open hostname
me
password
Ascii
put filepath\name
bye
exit

Help - Tasks that have circular dependencies

Hello,

I have a package, which calls a sub package to poulate a table depending on a flag in the database (using an ExecuteSQL task to return flagged table name).

The inner package populates some tables, and calculates what needs to be processed next. It sets the next flag.

However, I can't make this work in the control flow, as once the Execute package has completed, I need to start again from the top, as the flag will have changed to the next item.

I hope that I have explained this well enough.

I really need this to work, but SSIS will not let me create a circular dependency. Does anyone know a way around this, or can offer me an alternative solution?

I am getting desperate, so any suggestions will be welcome

Many thanks

The Foreach Loop Container may be of use. have you evaluated that?

Regards

-Jamie

|||

Hello Jamie,

Thanks for the reply.

Yes, I have, but as I have to pass it a recordset at the start of the for each.. it doesn't seem to fit the bill. I have to pick up these tables in an order only defined at run time by the sub package.

|||The Foreach container is exactly what you need to use. You will need to store your recordset in a user variable using an ExecuteSQL task and then use the Foreach container with a Foreach ADO enumerator. More direction is available on BOL but that should be enough to get you started.|||

Oh dear,

I am not explaining this very well, I;m sorry.

On the first pass I will get a table name from ny ontrol table which beeds to be populated. Once this is populated, I will mark the parent tables next to be processed in my control table.

So on the second iteration, I need to retrieve a new recordset to pass to the For Each loop. And so on.

Sorry if I am being obtuse, but I can't see how I can make this work.

Help -- How do I exit a Foreach Loop

I have a foreach loop that is processing files in a directory. One of the tasks is the execute sql task that calls a stored proc. If this stored proc returns a certain value then I need to exit the loop and not process any more files. But I have been unable to figure out how to make this happen.

Any help would be greatly appreciated.

Thanks!

GN

Hi,

I am not sure whether this will work or not. But just try. In the 'Foreach loop' container, select 'Expressions' tab and assign a boolean variable to the propery 'Disable'. Then based on the result, when required, set the value of the variable to 'True'. If it does not work, try this option for 'ForceExectionResult' also.

Let me know if any one of it works.

|||

GN wrote:

I have a foreach loop that is processing files in a directory. One of the tasks is the execute sql task that calls a stored proc. If this stored proc returns a certain value then I need to exit the loop and not process any more files. But I have been unable to figure out how to make this happen.

Any help would be greatly appreciated.

Thanks!

GN

have you considered using the script task instead?|||

Place the workflow in a sequence inside the ForEach Loop.

Place a script task at the beginning of the loop with a precedence constraint going to the sequence.

Place an expression on the precedence constraint to only execute if a variable is true, call the variable, "ExecuteBody". ExecuteBody==true

Set op to expression only

When the loop should terminate, set the ExecuteBody variable to false. The loop will traverse the entire remaining collection, but because there is nothing to execute, it will rip through it extremely fast and terminate.

K

Help - DTS Custom Task to handle Job Failuer

I need to find a better way to handle DTS Job Failure issue. Currently, we have about 50 jobs which executed through DTS packages. Everytime when sources were not there or came in late, the DTS sent out an email to my page which I carried every day. Some those came in during the holidays even though I know the source party wont generate the source files at that day.

Trying to avoid to get beep everytime when job was failed. Someone suggested that it is possible to add kind of executable file within the Custom task and let it trigger the DTS packages. If, for example, a holiday then dont run the package so I wont get the page.

Any hints suggestions would be greatly appreciated

J827I get unhappy when I see 0 replies, but I have not done this before but this is how I would handle it.

I would write a dll in VB. It would check todays date against a list of holidays I might keep in a database table. If it did'nt return a result I would would use the VB syntax for firing off stored procedures (still in my dll).

Of course before you do this you need to already have your 50 DTS packages in place.

I would then build a new DTS package whose only step was to fire off this custom DLL. Register your cutom task and assign it icon and then build your DTS package using the DTS Designer (not the wizard).

Good luck.|||create a table or array with the dates of all non action days (holidays weekends down days).
create a master package that runs all of your sub packages(use the execute package task).
create a Execute SQL (or activex)Task to evaluate the NonActiveDays table against the execution of the package if getdate() = @.nonactiveday, return 0
else 1
then pass that value as a global variable to the task
when the 1 hits, run the package
this is a fairly simple solution.

and just for giggles if your code returns a 0, you can have the task email you a reminder that the task didnt run becuase of a holiday.

this opens you up to many possibllilties, for instance you could have this code run in a job the day before to remind you that the next day is a holiday and dts wont run...|||thanks thanks!|||Are you executing this from the web like in ASP.Net or something?

If you are, the problem is that the DTS package needs to run in the same security context that you created it in. The IUSR_etc... account in IIS does not have the ability to execute the package. The workarounds are sloppy and I have discussed them on this forum before. Do a search for ASP and DTS.|||I created a C# Console Application to trigger the DTS package directly and it worked (see code below). But it was an EXE (not DLL) file type.

I would like to plug this app as part of control in an existing DTS package by adding it into a Custom Task. I just noticed the DTS Custom Task only allows adding DLL format file to be registered. Then I recreated the app with Class library Type and tried to register / run Regsvr32 appName. I got Could not find entry point for DLLRegisterServer in the DLL / appName.DLL was load but the DLLRegisterServer entry point was not found, the file cant be registered.

Any helps would be highly appreciated!

---------------------
try
{
DTS.Package2Class package = new DTS.Package2Class();
object pVarPersistStgOfHost = null;

string ServerName = "DEV";
string ServerUserName = null;
string ServerPassword = null;
string PackagePassword = null;
string PackageGUID = null;
string PackageVersionGUID = null;
string PackagName = "DTSTestPackage";


package.LoadFromSQLServer(
ServerName,
ServerUserName,
ServerPassword,
DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrus tedConnection,
PackagePassword,
PackageGUID,
PackageVersionGUID,
PackagName,
ref pVarPersistStgOfHost);

package.Execute();
package.UnInitialize();

// force Release() on COM object
System.Runtime.InteropServices.Marshal.ReleaseComO bject(package);

package = null;
}
catch(System.Runtime.InteropServices.COMException e)
{
Console.WriteLine("COMException {0}", e.ErrorCode.ToString() );
Console.WriteLine("{0}", e.Message);
Console.WriteLine("{0}", e.Source);
Console.WriteLine("Stack dump\n{0}\n", e.StackTrace);
}
catch(System.Exception e)
{
Console.WriteLine("Exception");
Console.WriteLine("{0}", e.Message);
Console.WriteLine("{0}", e.Source);
Console.WriteLine("Stack dump\n{0}\n", e.StackTrace);
}