how to rename files before send files?

I'm using script task to rename files inside foreach loop files,code like shown below

Dim file As New System.IO.FileInfo(CStr(Dts.Variables("User::FileName").Value))

dim newname as string = Split(file.Name, "_")(0) & ".jpg"
Dts.Variables("User::outputname").Value = file.DirectoryName & "\" & newname

Then,i use ftp task to send files,but prompt "the variables User::outputname doesn't contains file path(s)"

I tried to use file system task to perform that,but failed either

Rename file operation in file system only can rename a file in a specified location,who can help me?

TIA

Have you looked at what outputname does contain? Does the file specified exist as it should for the FTP task?

The File System Task can use variables, which themselves can be used to supply expressions, see the EvaluateAsExpression property and set the Expression property. This way you can use dynamic paths in the File System Task.

sql

How to remove unwanted characters during ETL?

Can unwanted characters (e.g. control codes) be replaced or removed in varchar fields during extraction inside DTS package?

SQL Server/SSIS 2005.

Thanks, Andrei.

Inside of an SSIS package, yes. DTS is old news now! You can use the REPLACE() function along with a CHAR() function in a derived column transformation.

To remove tabs: REPLACE([column],char(9),"")|||

Thanks, Phil!

This is the way to go.

Unfortunatelly there are couple problems.
1) Function CHAR isn't supported in SSIS (DTSx) package.

2) I'd like to have a filter against any invalid character, e.g. ASCII range 0-31. I'm not sure if SSIS's REPLACE function can do that.

I wonder if a custom function, e.g. from a CLR assembly, could be used in SSIS.

Regards, Andrei.

|||

Andrei Kuzmenkov wrote:

Thanks, Phil!

This is the way to go.

Unfortunatelly there are couple problems.
1) Function CHAR isn't supported in SSIS (DTSx) package.

2) I'd like to have a filter against any invalid character, e.g. ASCII range 0-31. I'm not sure if SSIS's REPLACE function can do that.

I wonder if a custom function, e.g. from a CLR assembly, could be used in SSIS.

Regards, Andrei.

CHAR() is too supported, it's just not in the drop down list. You can use it inside the replace function, just as I noted. You just have to type it in there. You could always write 31|||

Andrei Kuzmenkov wrote:

I wonder if a custom function, e.g. from a CLR assembly, could be used in SSIS.

Yes, you can create Script Transform and easily call custom function from there. Or if it is one-time code, you may simply place it in the Script Transform code as well.

Note that the custom assembly needs to be placed to GAC and because of to limitation of VSA we use for scripting it also should be in Windows\Microsoft.NET\Framework\v2.0.50727 (this is only needed for design time).

|||

In VS2005 I'm getting "The Function CHAR was not recognized".

How to read data from remote server inside a transaction?

Hello, everyone:

I have a local transaction,

BEGIN TRAN
INSERT Z_Test SELECT STATE_CODE FROM View_STATE_CODE
COMMIT

View_STATE_CODE points to remote SQL server named PROD. There is error when I run this query:

Server: Msg 8501, Level 16, State 1, Line 12
MSDTC on server 'PROD' is unavailable.
Server: Msg 7391, Level 16, State 1, Line 12
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d01c].

It looks like remote server is not available inside the local transaction. How to handle that?

Thanks

ZYT1) make sure MSDTC is running, u can see that from SQl Service Manager.
2) add this code before 'begin tran' , SET XACT_ABORT ON
3) instead of 'begin tran' begin distributed tran'
4) And SET XACT_ABORT OFF, AFTER COMMIT TRAN
Eg:

SET XACT_ABORT ON
begin distributed tran

insert into sometable select * from remoteserver.database.dbo..foo

commit tran
SET XACT_ABORT OFF


or check this site http://support.microsoft.com/default.aspx?scid=kb;en-us;839279 if ur SQL Server 2000 server installed on Windows Server 2003 or Windows XP Service Pack 2|||Hello, Mailler:

Thanks. MSDTC is running. The query you posted doesn't work.

ZYT|||what error u getting now.Is SQL Server 2000 server installed on Windows Server 2003 or Windows XP Service ?|||Hi, Mailler:

Thanks. The error is still "MSDTC on server 'PROD' is unavailable.
". The SQL Server 2000 is running on Windows XP SP1. Is it the reason?

ZYT|||check the link i given in first reply

How to query two MS SQL DBs on the same server inside a Stored Procedure

Okay, so I have a problem and I would be REALLY grateful for any
assistance anyone can offer because I have found little or no help on
the web anywhere.

I want to access and do joins between tables in two different SQL db's
on the same server. Heres what Im dealing with.

In one database resides all of my security features for our clients,
where it decides who can login, etc etc...

In another database, I need to cross reference with a few fields in my
security db.

See the issue Im running into here is that because the way the people
have their databases set up for different products, I would normally
have to put these tables with security features in every database...
which is horrible, because every time I do an update I would have to
do it in 12 different places. Thats not efficient at all.

So I thought if I had one central DB, where all security features are
controlled from, that would be perfect... now the issue is cross
referencing and doing joins with other tables that ARENT in the same
db...

have I lost you yet?

I appreciate all of your help!

THANKS!!google@.digitallsd.com (JMack) wrote in message news:<472b479f.0402170642.37a57121@.posting.google.com>...
> Okay, so I have a problem and I would be REALLY grateful for any
> assistance anyone can offer because I have found little or no help on
> the web anywhere.
> I want to access and do joins between tables in two different SQL db's
> on the same server. Heres what Im dealing with.
> In one database resides all of my security features for our clients,
> where it decides who can login, etc etc...
> In another database, I need to cross reference with a few fields in my
> security db.
> See the issue Im running into here is that because the way the people
> have their databases set up for different products, I would normally
> have to put these tables with security features in every database...
> which is horrible, because every time I do an update I would have to
> do it in 12 different places. Thats not efficient at all.
> So I thought if I had one central DB, where all security features are
> controlled from, that would be perfect... now the issue is cross
> referencing and doing joins with other tables that ARENT in the same
> db...
>
> have I lost you yet?
> I appreciate all of your help!
> THANKS!!

As a general answer to your question, you can write code like this:

select *
from dbo.ThisTable t1
join ThatDatabase.dbo.ThatTable t2
on t1.KeyColumn = t2.KeyColumn

Assuming you have SQL2000 (you didn't mention the version), you should
review the "Using Ownership Chains" topic in Books Online for
information about cross-database ownership chains (and there is an
article in the current SQL Server Magazine also).

Simon|||well unfortunately I'm in SQL 7 so that option doesnt apply to me.

The only other way I can see around it, is taking the tables I need
available to all the db's and replicating them from one publishing db...

which is overkill, but because of the way the system is set up this is
the only other option i can think of aside from replication is trying to
get IT to updgrade to SQL 2000

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||
Oh damn buddy,

you have saved my life. IT WORKS.

THANK YOU SO MUCH!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||"Jeremy Mack" <google@.digitallsd.com> wrote in message
news:4032799b$0$199$75868355@.news.frii.net...
> well unfortunately I'm in SQL 7 so that option doesnt apply to me.
> The only other way I can see around it, is taking the tables I need
> available to all the db's and replicating them from one publishing db...
> which is overkill, but because of the way the system is set up this is
> the only other option i can think of aside from replication is trying to
> get IT to updgrade to SQL 2000
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Sorry if my explanation was misleading - joining on another database will
work fine in SQL7, but in SQL2000 the default cross-database chaining
behaviour changed in SP3, which is why it's worth reviewing the
documentation.

Simon|||JMack (google@.digitallsd.com) writes:
> Okay, so I have a problem and I would be REALLY grateful for any
> assistance anyone can offer because I have found little or no help on
> the web anywhere.
> I want to access and do joins between tables in two different SQL db's
> on the same server. Heres what Im dealing with.
> In one database resides all of my security features for our clients,
> where it decides who can login, etc etc...
> In another database, I need to cross reference with a few fields in my
> security db.
> See the issue Im running into here is that because the way the people
> have their databases set up for different products, I would normally
> have to put these tables with security features in every database...
> which is horrible, because every time I do an update I would have to
> do it in 12 different places. Thats not efficient at all.
> So I thought if I had one central DB, where all security features are
> controlled from, that would be perfect... now the issue is cross
> referencing and doing joins with other tables that ARENT in the same
> db...

I see that you have got a solution working.

But I am a little wary of hard-coding database references. The day
you need to set up a test environment on the same server, you have
trouble...

One alternative would be to have a central database which you maintain,
and then use replication to push those updates to the other places.
Although admittedly, replication might be a little heavy-duty for this...

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp