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 redirect a DTS package and connections

Hi,

I have a DTS package (SQL2k, sp3) that I can to execute agains a new
database in the same server. I don't see how to easily redirect. If you
change the connection properties and clear the transformations you have to
redo them one at a time (too long).

If you don't data still goes to the old database.

What to do?

Sd"SD" <npspam@.nowhere.net> wrote in message
news:483161980e01c04754af03e36c264ae2@.news.teranew s.com...
> Hi,
> I have a DTS package (SQL2k, sp3) that I can to execute agains a new
> database in the same server. I don't see how to easily redirect. If you
> change the connection properties and clear the transformations you have to
> redo them one at a time (too long).
> If you don't data still goes to the old database.
> What to do?
> Sd

Check out the dynamic properties task - it allows you to set any properties
dynamically as the package is running. In this case, you could have a global
variable to store the database name, and use the task to assign it to the
connection. At runtime, you can pass the value for the global variable to
dtsrun.exe, or get it from an .INI file or SELECT query.

Simon

How to read/select data from a text file in TLSQL

Hello All,
I wanna write a TSQl script which will read the data from a text file(having table like structure). Not with DTS, I wanna do this programatically as sql batch file. Kindly help me this is very urgent.
Thanks in advance
Thazul:)Why is dts not an option ? Please provide a sample your data from the text file - is it delimited or fixed ?|||Is it possible to read a "csv" file?

It is (as I've understood) via ODBC, but I guess ODBC won't help in this particular case?|||Originally posted by thazanm
Hello All,
I wanna write a TSQl script which will read the data from a text file(having table like structure). Not with DTS, I wanna do this programatically as sql batch file. Kindly help me this is very urgent.
Thanks in advance
Thazul:)

Thanks for ur reply.
I wanna transfer data offline, so i cant use DTS. I am doing this offline data transfer using bcp(getting data from source database and making files then read from files and put it into target database). This is helpfull for me when the target database is fresh, when the target databse is having data, it need some validation for duplication, parent child relationship based on identity columns.

All I want is, I am creating few file for each table using bcp with -c or
-n option. After creating this I wanna read the data from created files to do some validation in TSQL and then load into target database.

I mentioned offline mean, I am not sure what will be the target database and I am not sure about their database informations. This script has to go as a pre install script before our product get installed.

I wanna put this in a simple way. In oracle there is Pl/SQL package available to open, read and write data from files to table and vice versa like UTL_FILE.<procedures>. Is there any functions or procedures available in SQLServer TSQL.

Thanks in advance
Thazul|||You may have already mentioned why you can't do this - in situations like this I have a holding database and work with the data using tsql this way. Massage the data with a stored procedure and then output the data to the destination database/table.|||Alrigth, I am sorry for the improper information provided.
Well I am already having a TSQL script which will transfer data from one database to another database after doing all kind of validations. The isssue is doing it offline. I cant connect two database at the client place. Some client places the dont agree to connect with our database.
I this facility(reading from files having table like data) is available then I can go for complete batch file.
I really appricate ur suggestion, if anyone can provide me the syntax or comman, it will be great.

Thanks in advance,
Thazul|||Are you looking for a way to read in a file, run validations, then output the data to a file ?|||RE:The isssue is doing it offline. I cant connect two database at the client place. Some client places the dont agree to connect with our database.

In some situations one may attach an MDF file (pre-populated with the necessary data in tables), and subsequently use it as the staging area database for validation, transfer, etc. (to other DB targets). In this way the stageing area DB is effectively populated "off-line" (from a client perspective).|||Originally posted by rnealejr
Are you looking for a way to read in a file, run validations, then output the data to a file ?

Hi,
I am looking for reading the data from file and run validation and then store it into database tables.

Thanks in advance|||I am confused as to why you can't use dts within your tsql - but you can accomplish this task by using bcp within your tsql, have your tsql validate and spit it out to a table(s).
If this does not satisify your requirements, please let me know - a step by step detail of the entire process would be helpful- offline is a little vague at this point, since tsql implies online.

How to read the content of DTS local package?

I create a local package in DTS a few months ago. And set a schedule to run daily. Now i need to modify. How to read the content of DTS local package? i try to right click the package in job, and edit, but i didn't see the content. How can i see it and modify. Why mine can't be read? Thanks.you need to go under :

Data Transformation Services ->Local Packages in Enterprise manager.

You'll see your package there.|||I can find the package, But i want to see the content. As this is a package that import some of data from another database. So i can't read the content that means the query. I forgot, but i need to modify. Thanks.|||YYU I think SQL SERVER 2005 has a utility that allows you to SEE/EDIT your DTS file, regarless of the version used to create this files.

You can also try to monitor the activty on your SQL SERVER using the PROFILER TOOL, wich comes with SQL SERVER 2000+, and run your DTS files. Your SQL QUERY should appear in your TRACE FILE.

BTW this forum ROCKS!!

BIGGGG UP!!|||Thanks. It is great to have sql server 2005. We will have later. Let's see.

Thanks again!

How to read SQL Statement in T-SQL Statement Task?

hi all,

after convert from DTS to SSIS, how can i open the SQL statement? because i only saw a line in the properties of the task.

i open the DTSX in vs2005, but i can open to view the SQL statement.

in sql 2000 just double click on the SQL Task, then will popup a dialog form to show the SQL task.

please help.

thanks a lot

hi all,

i found it already....

right click the task and choose edit and then click on the SQL statement.

thanks.....

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 DTS log ?

Hi everybody,
Someone to know a program for DTS log reading ?
When I open it with Wordpad there was same confusion.
Thanks a lot.To view package logs

In SQL Server Enterprise Manager, expand Data Transformation Services.

Do one of the following:
Right-click Local Packages (if the Data Transformation Services (DTS) package log was saved to Microsoft SQL Server) and then click Package Logs.

Right-click Meta Data Services Packages (if the package log was saved to SQL Server 2000 Meta Data Services), and then click Package Logs.

Click Local Packages or Meta Data Services Packages, and in the details pane, right-click a package and click Package Logs.