EBCDIC to ASCII conversion in SSIS

I tried to setup a flat file data source that has code page 37 (EBCDIC)

Then I have a flat file destionation that is ASCII.

And inbetween I have tried several different data flow conversion tasks liked Data Conversion, and Derived Column. But I keep getting errors about different code pages.

I also tried to load the EBCDIC data into a SQL Server DB, and it complains about different code page.

Has anyone been able to do this with SSIS out of the box, without any extra components ?

Clarence

EBCDIC 037 is one of the EBCDIC defined in SQL Server you have to use the collation below to create your database, tables and columns and you have to use Nvarchar and SSIS datatype for Nvarchar. To export to ASCII just do convert to Varchar before the export. Some EBCDIC code pages are not defined in SQL Server the link below shows those covered. Hope this helps.

SQL_EBCDIC037_CP1_CS_AS

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

|||

Wow, that's great !! I'm able to import it into a DB table now, but when I do something like this

SELECT

CONVERT(varchar(2), Rec_Type) Rec_Type

FROM dbo.CCP_FAC_EBCDIC

it's giving me an error:

An error occurred while executing batch. Error message is: Object reference not set to an instance of an object.

any ideas ?

|||

I don't think your convert to varchar definition is correct because nvarchar is double bytes that is one nvarchar is two varchar so the question is what is the size of the data you are exporting to ASCII. You could avoid the error by using SELECT INTO with the convert to varchar if the varchar is not big enough to be destination for your nvarchar your SELECT INTO will fail. Hope this helps.

|||

Thank you so much for your help !! I just used Convert to nvarchar instead of varchar and it works fine !

You're a life saver !

|||

ClarenceC wrote:

Thank you so much for your help !! I just used Convert to nvarchar instead of varchar and it works fine !

You're a life saver !

I am glad I could help.

easy question... i think

I have a ASP page where the user posts part of the name field. Then i need to provides the matches. (providing partial comparision of the field & correcting bad spelling)

example:

User posts 'softwair' (should be software)

the query look like this
Select Name from A where Name is like '%softwair%' <-- but appling soundex on it in the like field, or something like it.

I could make a copy of the column and update all info in the field to the soundex value, but then the like statement would not get it if the value was only part of it. for example say the data values where like 'Microsoft Software', or 'My Software Solutions', ect...You don't need to "update" the column to the soundex result, just create a computed column that would use soundex(field_name) as a function. But this will only answer the misspelling question.

select soundex('software'), soundex('softwair')

results in 'S136' for both.

Each Record on New Page

I have designed a report which is picking up the records from a sharepoint list with around 40-50 fields in it. As I was not able to fit all in a list view, therefore I designed the report in a profile view. I did this by using tables and putting the fields in the 'header' of the table rather than the 'Detail' section.

The report is looking quite good. As there can be more then one records I use the 'Last' function in order to ensure that the latest records are displayed.

What shall I do if I want to display all the records. Meaning each record can come on a new page. Is there something I can do, to do this.

Thanks

Amad

Set the PageBreakAtEnd property to true on each table you have.

|||

I need to display different blocks of information. e.g. Student Basic Information, Student Contact Information, Student Course Information etc.

For each block I have a made a separate table. I have rows of 'Header' type since if I take the 'Details' type of rows then rows will be displayed on one page. All the blocks of information are displayed on one page. Therefore each page contains information for one student

By your solution its giving a page break between two tables (or groups of information). What I want, is to display each student int a new page.

Thanks

|||

IIRC you can set grouping information on a list control, so you can place your tables in a list control, set the grouping to student name or ID, and have a page break at the end of each list.

sql

Each date on its own report page?

I am wondering how to set a page break so that when a date range is selected (01/04/06 to 30/04/06) each day would show up on it's own page with the date at the top? Is this possible?
Any help would be appreciated.
TIA
TaraDo you want to have every date in the range to have its own page even if there's no corresponding data for that date?
This isn't too difficult but I won't elaborate unless it's what you really want.

If you merely want each new (record) date in the range on its own page then group by the date. Change the Group options to be printed 'for each day' and check 'Repeat Group Header On Each Page'.
In the section expert, check 'New Page After' for the group footer .|||Hi there. Yes, that is exactly what I would like to do! How do I go about changing the group options? (sorry, very new to crystal reporting and working with pre-packaged reports that I am trying to modify) I found the section info yesterday and had tried the new page after the group footer, was missing the first step though :)

Thanks very much for your help!|||That's OK, I'm new to Crystal too.
To change the group options right click on the group header/footer on the left of the design view and choose 'Change Group'. You'll find the 'The section will be printed' bit on the Common tab, and the 'Repeat Group Header...' bit on the Options tab.|||Tara,

Just to note, the 'Repeat Group Header...' bit is only required if one day's worth of data spans more than one page and you want the (same) date repeated on each page.

Dynamicly displaying Page Header and Page Footer

I need to show or hide Page Header and Footer on the last page of a report
based on a parameter. Is there a way of doing this?
Thanks in advanceIt seems that you can't dynamically choose visibility for the header or
footer. It's either on or off. But you can control the visibility of
elements on the header and footer.
Create a footer. Set DisplayOnFirstPage and DisplayOnLastPage = Yes
Add a rectangle. Add controls to the footer inside the rectangle.
On the Visibility parameter of the rectangle, type
=IIF(Globals!PageNumber>1, False, True)
If your report stretches over 2 or more pages, you will see the content of
the rectangle on page 2 and out. You can change the expression according to
your needs, this was just a quick example.
Kaisa M. Lindahl Lervik
"Wendy" <Wendy@.discussions.microsoft.com> wrote in message
news:A80B4773-F77A-43B5-BA3C-C2662073B2FD@.microsoft.com...
>I need to show or hide Page Header and Footer on the last page of a report
> based on a parameter. Is there a way of doing this?
> Thanks in advance
>|||the same way :
From http://www.developmentnow.com
Posted via DevelopmentNow.com Group
http://www.developmentnow.com

Dynamically Showing Information

Is there a way of dynamically showing information from a sql server database in a web page without doing a postback.
My goal is to display information from a sql server database everytime the underlying table has received a change. I will also need to have the ability to format the cells based on the information being populated.
If anyone knows of any technology on this topic, or has attempted to do something similar please respond. Even if it's not quite what I'm looking for, the advice given may spin me in the right direction.
It is my belief that this may require creating this web page through a sql server wizard that offers the ability to watch the table for underlying changes. However I am not so sure about how I would then format the information displayed. Has anyone used
this technology before?
Thanks,
Pink
SQL Server has a web assistant. Start with the BOL topic "Using the Web
Assistant Wizard". Or you could write a trigger to push data modifications
to the web page.
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.

Dynamically generating RDL

I would like to point my Report Server to an ASPX page that outputs RDL.
Can this be done? Any examples or tutorials that anyone can point me to?
Thanks!
-mdbNot very easily. RS is a server based product. You have to deploy the RDL to
the server. BUT, with VS 2005 you have two new controls that have a local
mode, no server required. You can give the control your RDL (for these
controls it will be a RDLC file). It has to be RS 2005 format. These
controls come with VS, not SQL Server.
I really recommend checking out the controls. If generating RDL these
controls will make it much much much easier.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Michael Bray" <mbray@.makeDIntoDot_ctiusaDcom> wrote in message
news:Xns9746A0025EBA5mbrayctiusacom@.207.46.248.16...
>I would like to point my Report Server to an ASPX page that outputs RDL.
> Can this be done? Any examples or tutorials that anyone can point me to?
> Thanks!
> -mdb

Dynamically Creating Columns

I have an .aspx page where users can select columns from a table. I want to pass the Select statement to a SQL Reporting Services report, probably as a stored procedure, and open the report with the records for the columns selected as an Excel file. My problem is that I am not sure how to dynamically create the column headings and values in a table. Any thoughts on how to achieve this? Thanks!

HI,amiejoye:

When do you want to dynamically create the column heading? In report data retrieving or other period?

|||

if you want to dynamically add headers then you can use code section in reporting service.

Else you can use drag and drop the fileds(clumn name ) that is comin from the dataset

|||

I want to create the column headings and retrieve the records when I click Submit from the .aspx page. Basically, when I click Submit, that's how I want the age to open, with the data from the Select statement I created.

|||

I think that should not be aproblem because the vb code section in the reporting service depends on the select query only

Dynamically change the SelectCommand property

Hello

I have a gridview that I use on a products page, my problems is certain products have different attributes that I would like to display.

Therefore what I would like to do is change the SelectCommand property to my SQLDatasource depending on the querystring that is passed.

For instance in my page load event I would have a CASE statement with numerous SQLString Variables.

Here is the current coding for my datasource

<asp:SqlDataSourceID="SqlDataSource2"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

SelectCommand="SELECT PS.ProductSizeMM AS [Coupling Size], PS.ProductWallThickness AS [To Suit], PS.Cost AS [Price], PS.Sold_By AS [Sold by] FROM tblProduct AS P INNER JOIN tblProductSize AS PS ON P.ProductCode = PS.ProductCode WHERE (P.ProductDescription = @.ProductDescription) ORDER BY PS.Sorter">

<SelectParameters>

<asp:QueryStringParameterName="ProductDescription"QueryStringField="ProductDescription"/>

</SelectParameters>

</asp:SqlDataSource>

I have tried declaring a string variable in my page load event (SQLString) then setting the

SelectCommand="SQLString" but this causes a syntax error

Exception Details:System.Data.SqlClient.SqlException: Incorrect syntax near 'SQLString'.

Any help would be greatly appreicated!!

Hi,

a cleaner way to use would be by doing it in the SQLDataSource's Selecting event:http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.selecting.aspx.

Grz, Kris.

|||

You can't do that declaratively within your SqlDataSource. Instead, you'll need to set it within the code-behind like so:

SqlDataSource2.SelectCommand = SQLString

|||

Thanks it worked a treat

Dynamic value on Tool tip

I have 2 columns in a table,

one is name and other is percentage,

but in web page(front end), only name column is displayed,

when the cursor is focused on the name, it should display the corresponding percentage for that name. ( Like a tool tip).

can anyone help me on this

Quote:

Originally Posted by sajithamol

I have 2 columns in a table,

one is name and other is percentage,

but in web page(front end), only name column is displayed,

when the cursor is focused on the name, it should display the corresponding percentage for that name. ( Like a tool tip).

can anyone help me on this

Check the following link

http://msdn2.microsoft.com/en-us/library/system.windows.forms.tooltip.settooltip.aspx

Dynamic SqlDataSource

Ok, here's my situation.

I have a dynamic page that accepts a "type" query string. This type query string is the name of a table I want to display on the page in the GridView. Creating a different SqlDataSource/Strongly typed class for every type isn't possible because I need to make this flexible for future updates. So therefor I basically need to be able to Sort, Page, Edit and Delete with the Grid View without knowing the table name at compile-time.

Any help is appreciated,

Thanks!

Displaying the table contents is relatively straightforward. In your code-behind, you just create a SelectCommand of SELECT * FROM " + Request.QueryString["table"]. Of course, you would have to verify that the contents of the querystring is a valid value. The bigger problem is with the update and delete commands. You could create stored procedures for all tables, then fetch the names of the appropriate ones from a database depending on the value of "table" in the querystring. But that isn't particularly flexible. An alternative is toprogrammatically examine the selected table's schema, and dynamically construct update and delete commands based on the column names.|||Thanks for your response. I actually found a way to dynamically get the data itself to enable paging/sorting. I did this just by creating a stored procedure called GetTable with a @.table parameter. So this works well. As for updating and deleting I'll look into that, thanks.|||At least you are using Stored Procedures. Kewl!