Filter clause

We are moving to SQL 2005 Standard Edition are use numerouse filters
for replication. In SQL 2000, we had a lot of the filters setup using
an OR statement in the filterclause (ie. a.company = b.company or
a.subcompany = b.company), and had no problems with adding the filters.
In SQL 2005, creating these filters takes forever, if created at all.
Has anyone out there seen a problem like this?
Any help would be appreciated.
Thanks,
Amy Marshall
Can you define "takes forever" as well as "if created at all"? Are they not
being created? Are you receiving errors? What is happening?
As far as creating these en mass. if you are using the GUI and are stuck in
that world, then plan on spending a few days clicking through and setting
this stuff up. Instead, you can very easily setup the base replication
configuration, generate a script, and then add all of the filters into the
script, in a fraction of the time it takes to use the GUI.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
<marshallae@.bowater.com> wrote in message
news:1135868828.946674.289840@.g14g2000cwa.googlegr oups.com...
> We are moving to SQL 2005 Standard Edition are use numerouse filters
> for replication. In SQL 2000, we had a lot of the filters setup using
> an OR statement in the filterclause (ie. a.company = b.company or
> a.subcompany = b.company), and had no problems with adding the filters.
> In SQL 2005, creating these filters takes forever, if created at all.
> Has anyone out there seen a problem like this?
> Any help would be appreciated.
> Thanks,
> Amy Marshall
>
|||How many tables? Did you select the option to automatically generate
filters? This is very lengthy for large related tables in both SQL 2000 and
SQL 2005.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<marshallae@.bowater.com> wrote in message
news:1135868828.946674.289840@.g14g2000cwa.googlegr oups.com...
> We are moving to SQL 2005 Standard Edition are use numerouse filters
> for replication. In SQL 2000, we had a lot of the filters setup using
> an OR statement in the filterclause (ie. a.company = b.company or
> a.subcompany = b.company), and had no problems with adding the filters.
> In SQL 2005, creating these filters takes forever, if created at all.
> Has anyone out there seen a problem like this?
> Any help would be appreciated.
> Thanks,
> Amy Marshall
>
|||I have about 10 tables that use the 'OR' filter that links to one
table. What I did was script the package from SQL 2000 and ran it in
Query Analyzer on SQL 2005. If I take out the 'OR' statement, then the
filter will be created in less than a second. With the 'OR' statement,
I usually end up cancelling it after 5-10 minutes for each table with
that filter. (We were testing with the CTP Sept. version, and did not
have this issue...Could it be Standard vs. Enterprise?)
Thanks,
Amy
|||No, the edition doesn't matter. I can't reproduce this on the RTM bits.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
<marshallae@.bowater.com> wrote in message
news:1136292153.538075.219420@.g14g2000cwa.googlegr oups.com...
>I have about 10 tables that use the 'OR' filter that links to one
> table. What I did was script the package from SQL 2000 and ran it in
> Query Analyzer on SQL 2005. If I take out the 'OR' statement, then the
> filter will be created in less than a second. With the 'OR' statement,
> I usually end up cancelling it after 5-10 minutes for each table with
> that filter. (We were testing with the CTP Sept. version, and did not
> have this issue...Could it be Standard vs. Enterprise?)
> Thanks,
> Amy
>

File Task Moving with variables...

I have a 'file system task' moving files from one server to another for processing. I have defined the path and filename as separate variables. When I attempt to pass them together into the task I receive an error.

Source Path & Filename:

@.[User:: DataSourceFolder]+"\\"+ @.[User::CD_PaidClaimSource]

Outputs:

\\umrdwh2\FTP\CLAIMDAILY.DATA

Destination Path Now: (Filename not specified per another thread.)

@.[User::WorkingFolder]

Original Destination Path: (Which did not work.)

@.[User::WorkingFolder]+"\\"+ @.[User::CD_PaidClaimSource]

I receive validation errors:

Error 1 Validation error. CLAIMDAILY Move : Failed to lock variable "\\umrdwh2\FTP\CLAIMDAILY.DATA" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.". ClaimLoading_MASTER.dtsx 0 0

How do I overcome this without hard coding the path in the task?

You should be editing the file connection managers (source and destination) via variable expressions, not the file system task, I believe.|||

CaptainMyCaptain wrote:

Error 1 Validation error. CLAIMDAILY Move : Failed to lock variable "\\umrdwh2\FTP\CLAIMDAILY.DATA" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.". ClaimLoading_MASTER.dtsx 0 0

That error is showing you the value of your variable, not it's name. Not sure how that could have happened, but hopefully it helps you figure out what is wrong.
|||

JayH wrote:

CaptainMyCaptain wrote:

Error 1 Validation error. CLAIMDAILY Move : Failed to lock variable "\\umrdwh2\FTP\CLAIMDAILY.DATA" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.". ClaimLoading_MASTER.dtsx 0 0

That error is showing you the value of your variable, not it's name. Not sure how that could have happened, but hopefully it helps you figure out what is wrong.

Oh, I think I get it. Did you specify that expression as the Source Path in the File System Task? I didn't think the UI allowed that. If so, you need to create a new variable, set it EvaluateAsExpression=true and put the expression there. Then reference that variable from your File System Task's SourceVariable property.
|||An example to illustrate what we're saying:

http://www.ssistalk.com/file_system_task.jpg|||

The picture helped immensely, thank you. I was trying to establish the source and destination on the expressions screen as well as the general screen which was causing some of the error. Once I removed the destination and source from the expressions screen it worked better.

I am having another issue now, however. Following the syntax in the picture exactly I am getting an error because the filename is being considered a part of the folder path in addition to the filename. I have as an expression:

My expression (like your picture):

"\\\\Apsw9026g1\\Admin\\Logs\\CLAIMDAILY.DATA"

Which outputs:

\\Apsw9026g1\Admin\Logs\CLAIMDAILY.DATA

But this is the error on execution:

Information: 0xC002F30E at CLAIMDAILY Move, File System Task: File or directory "\\Apsw9026g1\Admin\Logs\CLAIMDAILY.DATA\CLAIMDAILY.DATA" was deleted.

Error: 0xC002F304 at CLAIMDAILY Move, File System Task: An error occurred with the following error message: "Could not find a part of the path '\\Apsw9026g1\Admin\Logs\CLAIMDAILY.DATA\CLAIMDAILY.DATA'.".

Why is it duplicating the filename as part of the path? Did I miss a setting?

Roger

|||Try deleting the file system task and starting over.|||

Deleting everything variable, task, container, etc and recreating all of it results in the same error. Ideas? The funny part is the system loves the 'Source'; there are no errors, and it is the same syntax. It is only the 'destination' that is having trouble.

Rog

|||

CaptainMyCaptain wrote:

The picture helped immensely, thank you. I was trying to establish the source and destination on the expressions screen as well as the general screen which was causing some of the error. Once I removed the destination and source from the expressions screen it worked better.

I am having another issue now, however. Following the syntax in the picture exactly I am getting an error because the filename is being considered a part of the folder path in addition to the filename. I have as an expression:

My expression (like your picture):

"\\\\Apsw9026g1\\Admin\\Logs\\CLAIMDAILY.DATA"

Which outputs:

\\Apsw9026g1\Admin\Logs\CLAIMDAILY.DATA

But this is the error on execution:

Information: 0xC002F30E at CLAIMDAILY Move, File System Task: File or directory "\\Apsw9026g1\Admin\Logs\CLAIMDAILY.DATA\CLAIMDAILY.DATA" was deleted.

Error: 0xC002F304 at CLAIMDAILY Move, File System Task: An error occurred with the following error message: "Could not find a part of the path '\\Apsw9026g1\Admin\Logs\CLAIMDAILY.DATA\CLAIMDAILY.DATA'.".

Why is it duplicating the filename as part of the path? Did I miss a setting?

Roger

Roger,

I have an example that uses the file system task to move/rename files in case you are interested: http://rafael-salas.blogspot.com/2007/03/ssis-file-system-task-move-and-rename.html

If I recall correctly, when using variables in the file system task, IsDestiantionPathVariable and IsSourcePathVarible, and depending on the operation used; some times the task expect only the path that points to the file and some times it expects the path AND the file name. In your case it looks like it only expects the path; so remove the file name from the expression. Do not assume both variables need path and file name.

|||

That was the answer. Thank you all for helping this greenhorn to get his job done!

Roger

File System Task - Moving a File

I have a File System Task that uses variables to resolve the destination and source paths of a document. When I select the 'copyfile' operation...the document is copied from the source to the destination without error.

However when I change the property from 'copyfile' to 'movefile' I get an error and the document is not moved.

The source and destination variables contain a valid file path name since the copy commmand is working as expected. However when I alter the properties of the File System task to move the document. I get the following error:

Could not find a part of the path 'G:\Common\Information Systems\DropFiles\nrt\NRT_Confirmation\Order Confirmation Report_11062006.xls\Order Confirmation Report_11062006.xls

It seems a little nonsensical since the document file paths are valid when performing a copy. For some reason the error log is showing that that the file path is the document name "Order Confirmation Report_11062006.xls" and adding it twice to the the directory path called "'G:\Common\Information Systems\DropFiles\nrt\NRT_Confirmation\" as you can see in the above error message.

To replicate the 'move' action...I added an extra File System Task that deletes the document once the copy has been performed. I would like some insight into why this doesn't seem to work.

Thank you.

...cordell...

Do no specify the file name for the Target.. in the Usage Type of File Connection properties specify it as Existing Folder instead of file and just select the folder path as you are moving an existing file.

File System Task - Moving a File

I have a File System Task that uses variables to resolve the destination and source paths of a document. When I select the 'copyfile' operation...the document is copied from the source to the destination without error.

However when I change the property from 'copyfile' to 'movefile' I get an error and the document is not moved.

The source and destination variables contain a valid file path name since the copy commmand is working as expected. However when I alter the properties of the File System task to move the document. I get the following error:

Could not find a part of the path 'G:\Common\Information Systems\DropFiles\nrt\NRT_Confirmation\Order Confirmation Report_11062006.xls\Order Confirmation Report_11062006.xls

It seems a little nonsensical since the document file paths are valid when performing a copy. For some reason the error log is showing that that the file path is the document name "Order Confirmation Report_11062006.xls" and adding it twice to the the directory path called "'G:\Common\Information Systems\DropFiles\nrt\NRT_Confirmation\" as you can see in the above error message.

To replicate the 'move' action...I added an extra File System Task that deletes the document once the copy has been performed. I would like some insight into why this doesn't seem to work.

Thank you.

...cordell...

Do no specify the file name for the Target.. in the Usage Type of File Connection properties specify it as Existing Folder instead of file and just select the folder path as you are moving an existing file.

File Groups: moving a large table

We have recently added a new file group and file on a new drive. We have tested it by moving a small table to the new file group. We would like to relocate a new table to this file group but the table is about (we estimate) 75GB. My question is this: How long can we expect the transfer of data from the current file group to the new one for this table? I understand that depending on our hardware the answer may vary but does anyone have a rough estimate?

The current current (primary) file is located on a DELL SAN and the new secondary group is on a EMC 4700 both are connected via fiber channel.

Also a bonus question would be: Does a "normal" database backup created as a maintenance plan backup the seconary data as well into the BAK file?

Like I said a rough estimate is fine.

Many thanks,

Scott

Well, 75 GB of data to move, 1 GB dedicated NIC/SAN. Unknowns include: amount and complexity of indexing, other demands on the server and SAN...

With little other activity, my WAG* is that you will be involved for at least 2-3 hours -and it could go up quite a bit if there are a lot of indexes and other demands on the SAN pipe.

Best widow of opportunity -NOT 9-5, Mon-Fri!

Yes, a 'normal' FULL backup will backup all files for the database.

(With my two points, I would like to buy a vowel please...)

* Wild A$$ed Guess

|||Thanks Arnie!
|||Should be noted that the process will halt if the log file grows larger that the disk it lives on can handle.

pingcarlo@.gmail.com
pingchuck@.hotmail.com
pingccat@.yahoo.com