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.

Dynamically pick the source and destination tables

I want to write a SSIS which picks up the source and destination tables at runtime is it possible. As we have a SSIS which is used to pull data from oracle but the source and destination table name changes.

If the metadata changes (that is the column names change and/or data types) then you cannot do this without manually accounting for the differences.

If the structures are the same, then you can build SQL statements to select against the appropriate table name. You can build the SQL in a variable expression.|||

Would you please give an example for this.

|||A little bit of searching will help you out...

I found: http://blogs.conchango.com/jamiethomson/archive/2005/12/09/2480.aspx|||

All the source tables have different data type and columns, so I think it is not possible to have 1 common SSIS for all of them.

We are storing our packages under the FileSystem on the server and executing them via jobs. So my question is if we make the changes in the package in BIDS(our Solution file) will it be reflected in the job or we'll have to import the package in File System?

|||

Paarul wrote:

All the source tables have different data type and columns, so I think it is not possible to have 1 common SSIS for all of them.

We are storing our packages under the FileSystem on the server and executing them via jobs. So my question is if we make the changes in the package in BIDS(our Solution file) will it be reflected in the job or we'll have to import the package in File System?

If you edit the package that's being referenced in the job, then the changes will be picked up on the next iteration.

dynamically delete data

Hi All,
I have the following situation.
Every month, I populate data from a source table.
This table has a field called process_date (char data type) and the
format is mmyy. So, 0406 means data for the month of April of 2006.
This source table always overlaps with old data. For example, for this
month it may have data for January, February or March of 2006, which I
already have processed.
What I do presently is I manually run a delete command and then insert
in the target table.
Such as:
delete Table1 where Process_Date<>'0406'
I want to make this automated so that I will not have to manually run
the above code.
I was wondering how could I achieve that?
I will highly appreciate your help.
Thanks a million in advance.
Best regards,
MamunHello Mamun,
You could create a SQL Server Agent job to run every month. This job
can execute the T-SQL statements you require to insert/delete the
required data and won't require any intervention by you (although you
should be checking that whenever the job executes it executes
successfully).
If you're new to creating SQL Server Agent jobs then SQL Server Books
Online should be able to run you through the process.
HTH,
Nate.
mamun wrote:
> Hi All,
> I have the following situation.
> Every month, I populate data from a source table.
> This table has a field called process_date (char data type) and the
> format is mmyy. So, 0406 means data for the month of April of 2006.
> This source table always overlaps with old data. For example, for this
> month it may have data for January, February or March of 2006, which I
> already have processed.
> What I do presently is I manually run a delete command and then insert
> in the target table.
> Such as:
> delete Table1 where Process_Date<>'0406'
> I want to make this automated so that I will not have to manually run
> the above code.
> I was wondering how could I achieve that?
> I will highly appreciate your help.
> Thanks a million in advance.
> Best regards,
> Mamun

Dynamically define the height of a chart

Current Situation:
I have a "Stacked Bar" chart and it has a certain ammount of lines depending on its data source. Sometimes if there are too many lines, only every other label shows up for that line on the left hand side (See image).

What I need:
Basically I need a solution that fixes my current situation. My first thouts were to dynamically size the height of the chart, but I haven't had much luck doing that. Also, I have tried to find properties for the char item that might let it grow.

If anyone has any suggestions, or know where I can get more information on this, I would appreciate it very much. Thanks in advance.

It looks like you have just a list of items to show in the chart.

Here is one approach:
* Add a list to the report, put the chart inside the list
* Add a (detail) group to the list with the following grouping expression:
=Int((RowNumber(Nothing)-1)/15)
This should result in the desired grouping of 15 items per (repeating) list instance.

Another approach is to define multiple charts of different sizes and use the Visibility.Hidden property on the chart to dynamically hide all charts but one. Note: you can use =CountRows("DatasetName") to determine the number of rows in a particular dataset and hide one chart e.g. if the total number of dataset rows is greater than 20: Visibility.Hidden property setting: =CountRows("DatasetName") > 20

-- Robert

|||Thanks for the reply. This seems like a good solution, however other problems were present that I didn't notice before. If you look at the x-axis of my graph it too does not display the labels correctly. My boss would like me to now explore using image creation on the fly to possibly fix this problem. Do you think that using a list would fix my problem with the x-axis?|||

You didn't explain why you think the x-axis labels are displayed incorrectly - but if the problem is that lots of data is shown in one chart and the x-axis looks crowded, splitting the underlying dataset into multiple charts will help.

-- Robert

Dynamic XSD Schema Location For XML Source

I want to be able to programmatically set the schema location for an XML source. I first thought it would be a simple task using expressions and variables but it doesn't appear to allow anything in the way of setting it at runtime. Is this possible?

It does allow it, but you don't set the expression directly on the component. You have to set it on the data-flow that contains the component.

Switch back to control-flow and look at expressions of the data-flow. Its self-explanatory from there.

-Jamie

|||Beautiful! Thank you.|||Thanks Jamie... just wanted to chime in and say this was really helpful - I would have never thought of looking on the properties of the data flow.

Dynamic Text Parser?

Hi Guys,

I have a script task that is supposed to read and parse a fixed width source file.

Basically, I want to make the FieldWidths dynamic so that I'll be able to reuse this package with different files. So Instead of hardcoding the field widths directly into my script task, I want it to be stored somewhere that the package can get when executions starts. Is there a way of doing this?

The code looks like this:

Using Reader As New TextFieldParser(mTempFilePAth)

Reader.TextFieldType = FieldType.FixedWidth

Reader.SetFieldWidths(1, 8, 8, 8, 4, 8) <-- I want to change this to handle dynamic widths.

Hi,

The best way to make your package re-usable is to use a "Integration Services Variable", see this link for help on variables http://msdn2.microsoft.com/en-us/library/ms141085.aspx and this one for accessing from a script http://msdn2.microsoft.com/en-us/library/aa337079.aspx.

The main gotcha is you need to be aware of is this bit from the second link "You can make existing variables available for read-only or read/write

access by your custom script by entering comma-delimited lists of

variables in the ReadOnlyVariables and ReadWriteVariables fields on the Script page of the Script Transformation Editor."

Also does SetFieldWidths take a variable number of arguments? If so you may need to define a string SSIS variable with the arguments comma seperated, then parse them into the seperate arguments for SetFieldWidths

Dave

Dynamic SQL in SSIS with Oracle

Hi ,

1.Dynamic Sql

-

My source table is Oracle .we want to have dynamic query the following steps we have done.

a.Created a new variable called StrSQL & ProductID variable contains values for where clause.
b.Set EvaluateAsExpression=TRUE
c.Set Expression=""select * from prod where product_id = " + @.[ProductID]
d.OLE DB Source component, opening up the editor
e.Set Data Access Mode="SQL Command from variable"
f.Set VariableName = "StrSQL"

But i am getting the following error

Error at DataFlow Task[OLEDB Source[1]]:An OLEDB error has occured, Error code: 0x80040e14.
AN OLE DB records is available . Source "Microsoft OLEDB Provider for Oracle" Hresult: 0x80040e14
Description : "ORA-00936:missing expression

What could be the problme.how about the support of dynamic query (Oracle) in ssis?

2.Clarification in Lookup.

--

How to Pass Parameters to Lookup. (Dynamic sql in Lookup)

example

select * from mastertable where reportdate = ?

here also my source is oracle table

Thanks

Jegan

The fact that the source is Oracle is irrelevant as far as the dynamic SQL is concerned. You should take the result of your expression and try and execute it against the source in yur query tool of choice (i.e. outside of SSIS). Verify that the query is correct.

-Jamie

|||

In c. of your question; you have a duplicate quotes

c.Set Expression=""select * from prod where product_id = " + @.[ProductID]

It may be that the problem or is just a typo in your post?

Other than that I don't see any other reason for the error; the steps you described look right.

Regarding the lookup question; I will not recommend that approach. If you have a dynamic query in a lookup it would mean to perform the lookup query for every row in your data flow impacting performance. However, I think you could do it by going to the advanced tab of the lookup transform and editing the query there.

Alternatively, you could include reportdate as a column available to the input of your lookup; then you just use it as a part of the join in the lookup component. Or may be you can create a view at run time with that query and then use that view in the lookup.

Those are just a couple of ideas; I hope this takes you further with a solution to your issues

Rafael Salas

|||

Rafael Salas,

Thanks for the Lookup solution.

Regarding dynamic sql yes its a typo error in my post. the same steps are working when we use a sql server connectivity.but for oracle i am not able to see the available columns in the column tab .what could be the problem any property need to be set?.

Thanks

Jegan.T

dynamic SQL as report source

I would like to use a stored procedure with parameters which creates and
executes dynamic sql as a source for a report.
I get an error, though, stating
"could not generate a list of fields for the query".
I tried adding the fields manually to the report definition and matching
them with the fields in the dynamic SQL, but I received error messages like:
"The value expression for the textbox 'blah' refers to the field 'blah'.
Report item expressions can only refer to fields within the current data set
scope. ..."
I guess I can just put all the sql into the dataset directly, but it would
be easier to manage in a stored procedure.
It needs to be dynamic because I have to build a dynamic Order By clause
with different alternative data types, so I can't use a Case statement.
The stored procedure is something like this:
Create Procedure sp
@.param1 int,
@.param2 int
as
declare @.sqlString (varchar(200))
set @.sqlString = 'select col1, col2
from tbl1
where col1 = ' + @.param1 +
'col2 = ' + @.param2
exec @.sqlString
Thanks!
Billwhat degree of dinamic is the order by clause you are making ... I mean, it
ends up being 3 or 4 cases, or the caller has complete control which column
to order?
Why do I say that? ... well, if there are 3 or 4 cases, u can put the static
part of the query in a user defined function, and have 3 or 4 cases where u
return a select of the function with a static order clause ...
Other than that, I cant think of anything ...
"bill" wrote:
> I would like to use a stored procedure with parameters which creates and
> executes dynamic sql as a source for a report.
> I get an error, though, stating
> "could not generate a list of fields for the query".
> I tried adding the fields manually to the report definition and matching
> them with the fields in the dynamic SQL, but I received error messages like:
> "The value expression for the textbox 'blah' refers to the field 'blah'.
> Report item expressions can only refer to fields within the current data set
> scope. ..."
> I guess I can just put all the sql into the dataset directly, but it would
> be easier to manage in a stored procedure.
> It needs to be dynamic because I have to build a dynamic Order By clause
> with different alternative data types, so I can't use a Case statement.
> The stored procedure is something like this:
> Create Procedure sp
> @.param1 int,
> @.param2 int
> as
> declare @.sqlString (varchar(200))
> set @.sqlString => 'select col1, col2
> from tbl1
> where col1 = ' + @.param1 +
> 'col2 = ' + @.param2
> exec @.sqlString
> Thanks!
> Bill
>
>|||Yes you have to add them manually or type them each time...
"bill" <belgie@.datamti.com> wrote in message
news:eHX3q2CnEHA.3684@.TK2MSFTNGP10.phx.gbl...
> I would like to use a stored procedure with parameters which creates and
> executes dynamic sql as a source for a report.
> I get an error, though, stating
> "could not generate a list of fields for the query".
> I tried adding the fields manually to the report definition and matching
> them with the fields in the dynamic SQL, but I received error messages like:
> "The value expression for the textbox 'blah' refers to the field 'blah'.
> Report item expressions can only refer to fields within the current data set
> scope. ..."
> I guess I can just put all the sql into the dataset directly, but it would
> be easier to manage in a stored procedure.
> It needs to be dynamic because I have to build a dynamic Order By clause
> with different alternative data types, so I can't use a Case statement.
> The stored procedure is something like this:
> Create Procedure sp
> @.param1 int,
> @.param2 int
> as
> declare @.sqlString (varchar(200))
> set @.sqlString => 'select col1, col2
> from tbl1
> where col1 = ' + @.param1 +
> 'col2 = ' + @.param2
> exec @.sqlString
> Thanks!
> Bill
>
>