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 - backing up SQL Server Databases...

Hello All,
I think this question should be easy... I am trying to setup a temporary
backup solution by just setting SQL Server to automatically backup databases
to a LARGE Drive on another machine.
I am able to do this MANUALLY on one of my SQL Server machines.. but, on
another machine.. it will not let me backup to a NETWORK DRIVE L: ... when I
try to setup a backup procedure.. only local drives are listed.. but, under
MY COMPUTER... L: is mapped... this is still the case after a restart..
etc...
I can not just type in L: .. as a place to backup to... because it says that
LOCATION does NOT Exist?
any help would be greatly appreciated...
thanks...
--
Systems Programmeruse a unc path \\servername\shareName
Greg Jackson
PDX, Oregon|||THANKS
"pdxJaxon" wrote:
> use a unc path \\servername\shareName
>
> Greg Jackson
> PDX, Oregon
>
>

EASY - backing up SQL Server Databases...

Hello All,
I think this question should be easy... I am trying to setup a temporary
backup solution by just setting SQL Server to automatically backup databases
to a LARGE Drive on another machine.
I am able to do this MANUALLY on one of my SQL Server machines.. but, on
another machine.. it will not let me backup to a NETWORK DRIVE L: ... when I
try to setup a backup procedure.. only local drives are listed.. but, under
MY COMPUTER... L: is mapped... this is still the case after a restart..
etc...
I can not just type in L: .. as a place to backup to... because it says that
LOCATION does NOT Exist?
any help would be greatly appreciated...
thanks...
Systems Programmer
use a unc path \\servername\shareName
Greg Jackson
PDX, Oregon
|||THANKS
"pdxJaxon" wrote:

> use a unc path \\servername\shareName
>
> Greg Jackson
> PDX, Oregon
>
>
sql

EASY - backing up SQL Server Databases...

Hello All,
I think this question should be easy... I am trying to setup a temporary
backup solution by just setting SQL Server to automatically backup databases
to a LARGE Drive on another machine.
I am able to do this MANUALLY on one of my SQL Server machines.. but, on
another machine.. it will not let me backup to a NETWORK DRIVE L: ... when
I
try to setup a backup procedure.. only local drives are listed.. but, under
MY COMPUTER... L: is mapped... this is still the case after a restart..
etc...
I can not just type in L: .. as a place to backup to... because it says that
LOCATION does NOT Exist?
any help would be greatly appreciated...
thanks...
--
Systems Programmeruse a unc path \\servername\shareName
Greg Jackson
PDX, Oregon|||THANKS
"pdxJaxon" wrote:

> use a unc path \\servername\shareName
>
> Greg Jackson
> PDX, Oregon
>
>

dynamic sql server statements

In stright t-sql or sql server 2000 stored procdures, I was wondering if you
can setup dynamic sql statments.
Basically I am going to have a sql server 2000 control table where users
can select via a web page, what columns they want to see with their own
unique (personal) sql server 2000. The control table will be setup to show
what columns
users want to display on their own individual sql server 2000 tables.
Thus basically if a user selects that they want to create a table with
columns #1, #9 and #16 from an option control table, I am wondering if there
is a way to do is a way to set this up with t-sql statements?
Thanks!There is, but there is certainly no magic way to do it. Are you talking
about loads of data? If the volume is relatively low, then I would just use
a regular proc and toss the results the user doesn't want to see.
Otherwise I would save in your settings the string value of the columns and
execute something like:
set @.query = 'select ' + @.columnList + ' from table'
And use sp_executeSQL to execute it. I expect you have a where clause too,
so you would add that.
You could then have a table like (you suggest it too):
create table userScreenPref
(
userId sysname,
screenName varchar(20),
columnList varchar(2000)
constraint PKuserScreenPref primary key (userId, columnList)
) --not exactly tested, but you get the point
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Wendy Elizabeth" <WendyElizabeth@.discussions.microsoft.com> wrote in
message news:603A78FD-D327-4263-8991-4340E6A5F9A5@.microsoft.com...
> In stright t-sql or sql server 2000 stored procdures, I was wondering if
> you
> can setup dynamic sql statments.
> Basically I am going to have a sql server 2000 control table where users
> can select via a web page, what columns they want to see with their own
> unique (personal) sql server 2000. The control table will be setup to show
> what columns
> users want to display on their own individual sql server 2000 tables.
> Thus basically if a user selects that they want to create a table with
> columns #1, #9 and #16 from an option control table, I am wondering if
> there
> is a way to do is a way to set this up with t-sql statements?
> Thanks!

Dynamic SQL in Stored Proc

Environment:
Window2K workstation, SQL Server 2000 Vesion 8.00.760 (SP3)
Setup:
I have a database setup so that NO users (except dbo) have READ,
UPDATE, or DELETE access to my database. But I have a single role
called MySPUser that is granted EXECUTE access to all of my stored
procs that do all data access for the system. The MySPUser role has a
single user in that group called MyUser, which is a windows domain
level account. My Webserver then impersonates that user when it calls
the stored procs. This setup worked on both my development machine and
my development test machine.
Problem:
So everything was going great for about a year when my dev machine
crashed. When I rebuilt the box with the same software (os and sql
included) everything seemed to be working just fine. The impersonated
user can still call all the stored proc and either retrieve or update
data. The only problem is that I have 2 stored proc that require
Dynamic SQL and they have stopped working. I now receive the following
error message when executing one of the stored procs.
SELECT permission denied on object 'tblMyTable', database
'MyApplication-Dev', owner 'dbo'.
I have tried deleting the users from the database and server and fully
rebuilding the users and roles with no luck. If I change my connection
string to point to my Test machine, which was built a year ago and also
uses Win2K and SQL2K SP3 everything seems to work fine.
Question:
What could cause Dynamic SQL Stored Procs to execute under a different
security context than Non-Dynamic SQL Stored Procs?
Any help would be greatly appreciated.
Will
P.S. I need to user dynamic sql because the sql statement is a query
for data by the user that can be searched on 12 different fields
simultaniously. Therefore the number of combinations of statements I
would need to build would be huge.It sounds like the user was granted access to the underlying tables.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Will" <WillCWirtz@.Yahoo.com> wrote in message
news:1129649346.104637.10300@.g43g2000cwa.googlegroups.com...
Environment:
Window2K workstation, SQL Server 2000 Vesion 8.00.760 (SP3)
Setup:
I have a database setup so that NO users (except dbo) have READ,
UPDATE, or DELETE access to my database. But I have a single role
called MySPUser that is granted EXECUTE access to all of my stored
procs that do all data access for the system. The MySPUser role has a
single user in that group called MyUser, which is a windows domain
level account. My Webserver then impersonates that user when it calls
the stored procs. This setup worked on both my development machine and
my development test machine.
Problem:
So everything was going great for about a year when my dev machine
crashed. When I rebuilt the box with the same software (os and sql
included) everything seemed to be working just fine. The impersonated
user can still call all the stored proc and either retrieve or update
data. The only problem is that I have 2 stored proc that require
Dynamic SQL and they have stopped working. I now receive the following
error message when executing one of the stored procs.
SELECT permission denied on object 'tblMyTable', database
'MyApplication-Dev', owner 'dbo'.
I have tried deleting the users from the database and server and fully
rebuilding the users and roles with no luck. If I change my connection
string to point to my Test machine, which was built a year ago and also
uses Win2K and SQL2K SP3 everything seems to work fine.
Question:
What could cause Dynamic SQL Stored Procs to execute under a different
security context than Non-Dynamic SQL Stored Procs?
Any help would be greatly appreciated.
Will
P.S. I need to user dynamic sql because the sql statement is a query
for data by the user that can be searched on 12 different fields
simultaniously. Therefore the number of combinations of statements I
would need to build would be huge.