How to rename a file in SSIS

I want to rename a file that is a variable to a fixed file name in a specific folder in SQL Server System Integration Services using the File System Task Editor.

Example: File1.txt to Users.txt or File2 to Users.txt. The source file is a variable the destination file is not. Here is an example of the File System Task Editor

HOW DO I SPECIFY A FIXED OR VARIABLE FOLDER NAME WITH A SPECIFIC FILE NAME???

File System Task Editor

Properties Value

Destination Connection
IsDestinationPathVariable True
DestinationVariable User::ArchivePath
OverWriteDestination True

General
Name Rename a file
Description File System Task

Operation
Operation Rename file

Source Connection
IsSourcePathVariable True
SourceVariable User::ImportFilePath

Thanks

There is more than one way to do it, the last link is run by DTS/SSIS experts you will find most of what you need. Hope this helps.

http://blogs.conchango.com/jamiethomson/archive/2005/09/14/2149.aspx

http://msdn2.microsoft.com/en-us/library/ms140185.aspx

http://www.sqlis.com

How to reduce the time when using a variable

Hi,

I have a table with column value like '123 345 678 143 648' like that. What I need to do is I have to take each code value and put it as a new record in another table. So, if I say 'Select substring(column_name,1,3) from table' then it is very fast (fraction of second). But since I need to take each code and the # of codes in each record may vary, I am using a while loop to take each code and so I delclared a variable @.i and now my select statement is like this: 'Select substring(column_name,@.i,3) from table'. Interesting now this select statement is taking almost 2 mins for each iteration.

Why it is like this? Is there any way I can reduce the time taken to execute each iteration?

Thanks.

how many records of this kind are you processing.

the number of records must ne put into consideration.

a cursor that iterate with every records first must be put in place

then place the while loop inside every fetch of the cursor

do the processing(while loop) of the column

i think there should be two loops to handle this case.

one that iterates with the records

and one that iterates for every codes in the the column.

if this is an SP. a with recompile option will make your query faster

regards.

|||

What about

INSERT INTO table1

SELECT SUBSTRING(column_name,1,3) from table WHERE SUBSTRING(column_name,1,3) <> ''

UNION

SELECT SUBSTRING(column_name,4,3) from table WHERE SUBSTRING(column_name,4,3) <> ''

UNION

...

? Copy/paste above line until substring index matches column_name size; or, if you don't want to hardcode it - construct this query referring to column_name size and run it through EXEC.

This could be faster - try it out...

|||

If you are using SQL Server 2005 then you can do the following:

1. Create a table-valued function that takes a string and splits it based on space character. You can search the web for several sample functions that does this operation

2. Use it in a SELECT statement using APPLY operator like:

insert into tbl(i, ...)

select s.val

from table as t

cross apply splitstr(t.column_name) as s

In SQL Server 2000, you can do similar operation except there is no APPLY operator so you cannot use a TVF. You can use a table of numbers to split the string and then do a cross join with this table instead. Or the approach using UNION as suggested in another post will also work. Make sure you use UNION ALL though for better performance and to eliminate the unnecessary sorting.

|||Here is an article on handling arrays and lists - quite a few methods and instruments, may be useful.|||

Hi Umachandar,

Your tip was very helpful to me. Now the whole process completes in 2 mins. Thx.

sql

How to read parent Package in Script Task

Hi,
In SQL2000 DTS, I set a variable to DTSGlobalVariables.Parent. Then I can manipulate the package.
What is the equivalent way in SQL 2005 SSIS?
Thanks for all the help!
TTSQL Server 2005 Integration Services has no exact eqivalent. However, you can use package configurations and property expressions to accomplish the task that you describe. I especially recommend that you take a look at the Parent package variable configuration type.
Books Online includes a number of topics about package configurations and property expressions. You might find the following topics useful:
Package Configurations
Creating Package Configurations
Using Property Expressions in Packages
dtexec Utility
Configuration Class

Marianne
SQL Server User Education

This posting is provided "AS IS" with no warranties, and confers no rights.

|||Still - how can I read "Description" property of the current package in the "Script Task"?|||

In addition to Marianne's reply, check out this article for more information regarding the "DTSGlobalVariables.Parent" equivalent in SSIS.

http://blogs.conchango.com/jamiethomson/archive/2005/02/28/SSIS_3A00_-Dynamic-modification-of-SSIS-packages.aspx

How to read parent Package in Script Task

Hi,
In SQL2000 DTS, I set a variable to DTSGlobalVariables.Parent. Then I can manipulate the package.
What is the equivalent way in SQL 2005 SSIS?
Thanks for all the help!
TT
SQL Server 2005 Integration Services has no exact eqivalent. However, you can use package configurations and property expressions to accomplish the task that you describe. I especially recommend that you take a look at the Parent package variable configuration type.
Books Online includes a number of topics about package configurations and property expressions. You might find the following topics useful:
Package Configurations
Creating Package Configurations
Using Property Expressions in Packages
dtexec Utility
Configuration Class

Marianne
SQL Server User Education

This posting is provided "AS IS" with no warranties, and confers no rights.

|||Still - how can I read "Description" property of the current package in the "Script Task"?|||

In addition to Marianne's reply, check out this article for more information regarding the "DTSGlobalVariables.Parent" equivalent in SSIS.

http://blogs.conchango.com/jamiethomson/archive/2005/02/28/SSIS_3A00_-Dynamic-modification-of-SSIS-packages.aspx

How to read parent Package in Script Task

Hi,
In SQL2000 DTS, I set a variable to DTSGlobalVariables.Parent. Then I can manipulate the package.
What is the equivalent way in SQL 2005 SSIS?
Thanks for all the help!
TT
SQL Server 2005 Integration Services has no exact eqivalent. However, you can use package configurations and property expressions to accomplish the task that you describe. I especially recommend that you take a look at the Parent package variable configuration type.
Books Online includes a number of topics about package configurations and property expressions. You might find the following topics useful:
Package Configurations
Creating Package Configurations
Using Property Expressions in Packages
dtexec Utility
Configuration Class

Marianne
SQL Server User Education

This posting is provided "AS IS" with no warranties, and confers no rights.

|||Still - how can I read "Description" property of the current package in the "Script Task"?|||

In addition to Marianne's reply, check out this article for more information regarding the "DTSGlobalVariables.Parent" equivalent in SSIS.

http://blogs.conchango.com/jamiethomson/archive/2005/02/28/SSIS_3A00_-Dynamic-modification-of-SSIS-packages.aspx

How to read contents of text file from t-sql ?

Hi friends,
I need to read content of text file and store them in
string variable in my store procedure, Is there t-sql or
function to read content of text file ?
Thanks.There is no direct set of functions to do it, but you could use the sp_OA*
set of procedures to access the file. For an example of how to use these
procedures see:
http://www32.brinkster.com/srisamp/sqlArticles/article_32.htm
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"Kresna Rudy Kurniawan" <kresna_rk@.yahoo.com> wrote in message
news:081001c3a377$c35948b0$a601280a@.phx.gbl...
> Hi friends,
> I need to read content of text file and store them in
> string variable in my store procedure, Is there t-sql or
> function to read content of text file ?
>
> Thanks.

How to read a XML file from a stored procedure?

From a stored procedure, I would like to import into a varchar variable the
content of a
XML file before I invoke the sp_xml_preparedocument SP. If possible, I want
to avoid to
first have to import the XML file into a table.
Is it possible at all with SQL 2000 SP4 to directly import a XML file? How?
thanks.It seems XQuery is one way as I'm starting to read this article [1]
<%= Clinton Gallagher
[1] http://www.idealliance.org/proceedi.../XQueryRDS.html
"Gaetan" <me@.somewhere.com> wrote in message
news:4eqd129irfvdj5fem8v118jt9d6qd2gibm@.
4ax.com...
> From a stored procedure, I would like to import into a varchar variable
> the content of a
> XML file before I invoke the sp_xml_preparedocument SP. If possible, I
> want to avoid to
> first have to import the XML file into a table.
> Is it possible at all with SQL 2000 SP4 to directly import a XML file?
> How?
> thanks.|||XQuery could have been an avenue to explore had not been for the fact that I
'm stuck with
SQL 2000 SP4 for a while longer.
On Tue, 14 Mar 2006 10:26:15 -0600, "clintonG"
< csgallagher@.REMOVETHISTEXTmetromilwaukee
.com> wrote:

>It seems XQuery is one way as I'm starting to read this article [1]
><%= Clinton Gallagher
>[1] http://www.idealliance.org/proceedi.../XQueryRDS.html
>|||You would have to write an extended stored procedure that reads the data
from a file. SQL Server 2005 has native ability to read from a file but not
SQL Server 2000.
My recommendation is to read the file in the client code and pass it to the
database as a stored procedure parameter...
Best regards
Michael
"Gaetan" <me@.somewhere.com> wrote in message
news:4eqd129irfvdj5fem8v118jt9d6qd2gibm@.
4ax.com...
> From a stored procedure, I would like to import into a varchar variable
> the content of a
> XML file before I invoke the sp_xml_preparedocument SP. If possible, I
> want to avoid to
> first have to import the XML file into a table.
> Is it possible at all with SQL 2000 SP4 to directly import a XML file?
> How?
> thanks.|||we are haveing a similar issue attempting to read xml through our own
firewall, (we can only have sql port and port 80 available) so no fileopen
methods are available:
so we cant use vbnet:readxml method as the filepath violates firewall
security: it is becoming a showstopper for our solution . if we could
paramaterise a sp passing in the filename problem would be solved.
All we have found so far is a declare @.doc; exec @.doc = cmdShell
and execute a dos "type" command to type out the contents into the variable.
There are several examples of this method on the net.
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:uoqgZ$uTGHA.1708@.TK2MSFTNGP14.phx.gbl...
> You would have to write an extended stored procedure that reads the data
> from a file. SQL Server 2005 has native ability to read from a file but
> not SQL Server 2000.
> My recommendation is to read the file in the client code and pass it to
> the database as a stored procedure parameter...
> Best regards
> Michael
> "Gaetan" <me@.somewhere.com> wrote in message
> news:4eqd129irfvdj5fem8v118jt9d6qd2gibm@.
4ax.com...
>

How to read a XML file from a stored procedure?

From a stored procedure, I would like to import into a varchar variable the content of a
XML file before I invoke the sp_xml_preparedocument SP. If possible, I want to avoid to
first have to import the XML file into a table.
Is it possible at all with SQL 2000 SP4 to directly import a XML file? How?
thanks.
It seems XQuery is one way as I'm starting to read this article [1]
<%= Clinton Gallagher
[1] http://www.idealliance.org/proceedin...XQueryRDS.html
"Gaetan" <me@.somewhere.com> wrote in message
news:4eqd129irfvdj5fem8v118jt9d6qd2gibm@.4ax.com...
> From a stored procedure, I would like to import into a varchar variable
> the content of a
> XML file before I invoke the sp_xml_preparedocument SP. If possible, I
> want to avoid to
> first have to import the XML file into a table.
> Is it possible at all with SQL 2000 SP4 to directly import a XML file?
> How?
> thanks.
|||XQuery could have been an avenue to explore had not been for the fact that I'm stuck with
SQL 2000 SP4 for a while longer.
On Tue, 14 Mar 2006 10:26:15 -0600, "clintonG"
<csgallagher@.REMOVETHISTEXTmetromilwaukee.com> wrote:

>It seems XQuery is one way as I'm starting to read this article [1]
><%= Clinton Gallagher
>[1] http://www.idealliance.org/proceedin...XQueryRDS.html
>
|||You would have to write an extended stored procedure that reads the data
from a file. SQL Server 2005 has native ability to read from a file but not
SQL Server 2000.
My recommendation is to read the file in the client code and pass it to the
database as a stored procedure parameter...
Best regards
Michael
"Gaetan" <me@.somewhere.com> wrote in message
news:4eqd129irfvdj5fem8v118jt9d6qd2gibm@.4ax.com...
> From a stored procedure, I would like to import into a varchar variable
> the content of a
> XML file before I invoke the sp_xml_preparedocument SP. If possible, I
> want to avoid to
> first have to import the XML file into a table.
> Is it possible at all with SQL 2000 SP4 to directly import a XML file?
> How?
> thanks.

How to read a package variable in a custom component?

What I tried, based on something I found in Jamies blog:

IDTSVariables90 vars = null;
this.VariableDispenser.GetVariables(out vars);

However the vars stays empty, while I have 3 user variables defined. I saw in the documentation that one should not use 'VariableDispenser' since it is for internal use. But is there a better way, like in a script task, you just do
Dts.Variables("BatchID") .

Youre input is appreciated,
HenkPlease note the additional comments in the BOL topic on the Variable Dispenser property of the PipelineComponent base class. (The HTML always suffers when being copied and pasted, see below.) Can you try locking the variable first?
--

Remarks

The VariableDispenser is used to read and write variables in the package that contains the component. Before reading or writing a variable, it must be locked using one of the following methods; LockForRead, LockForWrite, LockOneForRead, or LockOneForWrite. After the variables are locked using the dispenser, they are available through the IDTSVariables90 interface.

Example

The following example demonstrates how to use the VariableDispenser to lock a single variable, and multiple variables.

C# Copy Code // Lock two variables, and then retrieve them by calling GetVariables. IDTSVariables90 variables = null; VariableDispenser.LockForRead("variable1"); VariableDispenser.LockForRead("variable2"); VariableDispenser.GetVariables(ref variables); object variable1 = variables[0].Value; object variable2 = variables[1].Value; // Retrieve a single variable. IDTSVariables90 variables = null; VariableDispenser.LockOneForRead("variable1", ref variables); object variable1 = variables[0].Value;

|||Thanks Douglas!