How to remove {CR}{LF} and Tab {t} characters from source data?

I have built a SSIS package that reads in data from a SQL Server 2005 source database into a flat file destination. The Row Delimiter is {CR}{LF}. The Column Delimiter is Tab {t}.

The data being read from the SQL Server database contains both {CR}{LF} and Tab {t} characters in various fields on several rows.

How can I process the input data from the SQL Server to remove these characters before passing it to the destination output file?

Sorry if this is obvious to all, but I am only just starting with SSIS...

Many thanks

Adrian

If you have the data in a DT_STR ro DT_WSTR column, you can use the Derived Column transform with a REPLACE() function to replace those characters with empty strings.

Otherwise, you might want to use a Script component.

Thanks
Mark

How to Refresh a Data Source View and autogenerate/Re-Deploy the corresponding Report Model

Hi everyone!,

Our team is new to SSRS and we are trying to figure out how to refresh the data source view, autogenerate the corressponding report model and Re-Deploy the Report Model (rewrite existing Report Model) on the server on a periodoc basis or trigger the whole action whenever there is a change in the database on some specific tables.

We prefer to do this action through a Agent job ( a diff approach is also welcome).

Are there any stored procs out there?

Appreciate your time.

-PN

Can anybody please tell me if this is possible or not in the current version of SSRS or VS.

Appreciate your time guys.

|||This won't be easy but I think it is possible. There is no eventing that RS provides to help you with this. The DSV schema information gets embedded in the model definition (under the <Schema> element). So, you need to load the model defintion (GetModelDefinition() API), make the changes to the DSV schema, update the model, and save it back (SetModelDefinition). As far subscribing for notifications, you are on your own. Probably CLR DDL trigger will be your best joice to sink the table schema change event.|||

Hi Teo,

Thanks for your answer. I was thinking around the same lines but did not know the Methods to accomplish this task.

|||

Hi,

My project requirements goes the same way. I was trying to refresh the Report Model in the way mentioned by Teo. But the GetModelDefinition will fetch the report model SMDL.

How to get the DSV schema? Is there anything am missing out?

Did any one got the solution for the same?

Thanks.

|||Assuming a model on top of a relational database, the DSV schema gets embedded in the model definition (see <DataSourceView> element) upon deploy.|||Hi Teo,

Thanks for ur help. I tried in the same lines and was able to achieve my task.

I need to know whether it is recomended to change the xml definition of the Report Model.

Thanks

How to Refresh a Data Source View and autogenerate/Re-Deploy the corresponding Report Model

Hi everyone!,

Our team is new to SSRS and we are trying to figure out how to refresh the data source view, autogenerate the corressponding report model and Re-Deploy the Report Model (rewrite existing Report Model) on the server on a periodoc basis or trigger the whole action whenever there is a change in the database on some specific tables.

We prefer to do this action through a Agent job ( a diff approach is also welcome).

Are there any stored procs out there?

Appreciate your time.

-PN

Can anybody please tell me if this is possible or not in the current version of SSRS or VS.

Appreciate your time guys.

|||This won't be easy but I think it is possible. There is no eventing that RS provides to help you with this. The DSV schema information gets embedded in the model definition (under the <Schema> element). So, you need to load the model defintion (GetModelDefinition() API), make the changes to the DSV schema, update the model, and save it back (SetModelDefinition). As far subscribing for notifications, you are on your own. Probably CLR DDL trigger will be your best joice to sink the table schema change event.|||

Hi Teo,

Thanks for your answer. I was thinking around the same lines but did not know the Methods to accomplish this task.

|||

Hi,

My project requirements goes the same way. I was trying to refresh the Report Model in the way mentioned by Teo. But the GetModelDefinition will fetch the report model SMDL.

How to get the DSV schema? Is there anything am missing out?

Did any one got the solution for the same?

Thanks.

|||Assuming a model on top of a relational database, the DSV schema gets embedded in the model definition (see <DataSourceView> element) upon deploy.|||Hi Teo,

Thanks for ur help. I tried in the same lines and was able to achieve my task.

I need to know whether it is recomended to change the xml definition of the Report Model.

Thanks

How to reference a shared datasource already on report server for data driven subscription

I see there's MS documentaion on creating a data driven subscription.
The example shows to create a data source for the data retrieval plan.
see
http://msdn2.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005.createdatadrivensubscription.aspx
I would like to set the data source to a share data source on the
server. How should I implement that?
I have tried to use the following but failed:
...
delivery.Item = rs.GetDataSourceContents("/Data Sources/
testDataSource");
...I have worked this out: By creating a DataSourceReference object:
DataSourceReference reference = new
DataSourceReference();
reference.Reference = "/Data Sources/testDataSource";
delivery.Item = reference;
But then I got error like:
The current action cannot be completed because the user data source
credentials that are required to execute this report are not stored in
the report server database.
My credential information is stored with data source. If I manually
create the data-driven subscription from Report Manager, and use the
same shared data source, I wouldn't get this error. Anyone knows why?
Thanks

How to redirect the error of a source Flat file to the Destination Flat file?

Hi all,

I m using SSIS and i am transfering the data from Flat File Source to the OLE DB destination File. The source file contain some corrupt data which i am transfering to the other Flat file destination file.

Debugging is succesful but i am not getting any error output in the Flat file destination file.

i had done exactly which is written in the msdn tutorial of SSIS.

Plz tell me why i am not getting the error output in the destination flat file?

thanx

have you set the Error Output to "Redirect Row" on the task ?

How to Read ODBC source database?

Hello,

I am trying to find how to do read using ODBC sources ? I am using Sybase as relational source.

Thanks
AllwynIdeally you would find an OLE-DB provider over ODBC. There is a informative thread on the topic here-

Re: ODBC Destination in SSIS - MSDN
(http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=106559&SiteID=1)