Concatenate Input Columns

Hi,

In my data flow taks, The Source data is coming from AS400 has 4 columns,

I need to achieve the followings and require your help.

1. Generate a new column which will be combination of concating these 4 columns.

2. Need to add an extra row for Header & Footer.

Please Help.

Concatenation is achieved using the Derived Column component.

Adding your own header and footer rows is a bit more difficult because they need to have the same metadata as the data row. For this reason, concatenate all columns together so as to make a single, very wide, column. You can then use the UNION ALL component to put your header, data and footer together. The header and footer will probably be created using a source script component - though I'll leave that up to you.

-Jamie

|||

Re. Regarding Derived Column component

Source Columns

Col1 smallint, Col2 smallint, Col3 Decimal(12,4), Col4 Decimal(16,4)

Data will be the new derived column

Data = @.[User::TimeStamp] + "D" + Col1 + Col2 + Col3 + Col4

I need to know how to use Cast Operators as Data is DT_STR and Col1 & Col2 are smallint.

Thanks

|||

Look in the top right hand corner of the Derived Column UI. All the type cast operators are in there.

They are also all in BOL which you obviously haven't looked at:

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

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

BOL should ALWAYS be your first port of call. Not this forum!

-Jamie

|||

I have created two source script component one for Header and one for Footer. I am using OLE DB source for the detail records. While doing the union All, It just put the detail first and then Header and Footer.

Although Union All is setup like that

Union All Input 1 is Header

Union All Input 2 is detail and

Union All Input 3 is Footer

How can I make sure they go by order (Header,Detail,Footer).

Thanks again for your help

|||

Ahhh..I didn't think about that. Sorry. There is no way to guarantee the order.

I've thought of another way actually. Use a single script component transform to add the header and footer. It will have to be an asynchronous component.

Sorry for putting you on the wrong path.

-Jamie

Concatenate a string problem

Hi to all:

In my package, I have in OLE DB SOURCE a statement:

DECLARE @.CMonth as smalldatetime

SET @.CMonth = '11/1/2006'

select day(@.CMonth)+month(@.CMonth)+year(@.CMonth)as ID_Month

and in OLE DB DESTINATION, I have ID_Month column as a char(10).

I want to be result as concatenated string – 1112006, but I receive 2018 (As a result of calculation)

Can anybody help me? Thank you

DECLARE @.CMonth as smalldatetime

SET @.CMonth = '11/1/2006'

select cast(day(@.CMonth) as varchar(2))+cast(month(@.CMonth) as varchar(2))+cast(year(@.CMonth) as varchar(4)) as ID_Month

|||Thank you very much Attila Soos. It works great!

Computing several columns for each row in source table and joining to get result

I have come across this several times now, and I cannot figure out how to do
it better. Say I have a simple table called SourceTable:
DECLARE @.sourceTable TABLE
(
data1 INT,
data2 INT,
data3 INT,
data4 INT
)
I need to create a table (view, tv function, etc.) that looks something like
DECLARE @.resultTable TABLE
(
data1 INT,
data2 INT,
data3 INT,
data4 INT,
date1 SMALLDATETIME,
date2 SMALLDATETIME
)
where date1 and date2 are calculated (with functions) using data1...data4
from the same row plus another parameter supplied by the user. So you see
what I want is so simple: For each row in @.sourceTable, evaluate a
table-valued function getDates() that returns a single row containing date1
and date2, and join the result to produce @.resultTable. However, I can't
figure out any syntax to do this straightforwardly.
In some cases where date2 depends on date1, I can use nested queries, so I
can do something like
SELECT
data1,
data2,
data3,
data4,
date1,
date2 = getDate2(@.userInput, date1, data3, data4)
FROM (
SELECT
data1,
data2,
data3,
data4,
date1 = getDate1(@.userInput, data1, data2)
FROM
@.sourceTable
) T1
But recently, I have had several problems where it would be more efficient
and maintainable if I could return both date1 and date2 from a table-valued
function as a single row with two columns. This is because the relationship
between date1 and date2 is more complicated and they can't just be computed
sequentially. My first attempt was to write a TV function that basically
was
CREATE FUNCTION getDates (@.userInput INT, @.data1 INT, @.data2 INT, @.data3
INT, @.data4 INT)
RETURNS @.dates TABLE (date1 SMALLDATETIME, date2 SMALLDATETIME) AS
BEGIN
DECLARE @.date1 SMALLDATETIME
SET @.date1 = getDate1(@.userInput, @.data1, @.data2)
DECLARE @.date2 SMALLDATETIME
SET @.date2 = getDate2(@.userInput, @.data3, @.data4)
IF (@.date1 < @.date2)
SET @.date1 = getDate1(@.date2, @.data1, @.data2)
INSERT INTO @.dates
SELECT @.date1, @.date2
RETURN
END
I tried to join the function with the source table to get my result table as
follows:
SELECT
ST.data1,
ST.data2,
ST.data3,
ST.data4,
D.date1,
D.date2
FROM @.sourceTable ST
INNER JOIN getDates(
@.userInput,
ST.data1,
ST.data2,
ST.data3,
ST.data4) D
but SQL Server always complains when it reaches the 'ST' in the second
argument of getDates(), because apparently ST is not available in that
context. I tried using a cursor to evaluate getDates() for each row in
@.sourceTable and join the result to produce @.resultTable, but something was
just wrong and the query batch would never finish executing in query
analyzer. (I debugged and found that the cursor was implemented properly,
it was just extremely slow or was hanging in QA.) For now, I am using a
several-level-deep nested query that performs the logic of of my getDates()
function. Each query level performs one calculation or condition on one of
the two dates, and the rest of the columns just get carried along. For
example:
SELECT
data1,
data2,
data3,
data4,
date1 = CASE WHEN (date1 < date2)
THEN getDate1(date2, data1, data2)
ELSE date1
END,
date2
FROM (
SELECT
data1,
data2,
data3,
data4,
date1,
date2 = getDate2(@.userInput, data3, data4)
FROM (
SELECT
data1,
data2,
data3,
data4,
date1 = getDate1(@.userInput, data1, data2)
FROM
@.sourceTable
) RT1
) RT2
The query is actually a few levels deeper because I have to calculate other
things based on date1, and there are many more columns. This is horrible in
terms of readability and maintanability because the logic is distributed
throughout each level of the query, and I have to repeat all the columns at
each level. If I could return more than one column from a correlated
subquery, I would be fine, but I don't believe this is possible. Can
someone please help?Well, at least I know it wasn't just me. Thanks!
"Steve Kass" <skass@.drew.edu> wrote in message
news:%23hvbu$gEGHA.2012@.TK2MSFTNGP14.phx.gbl...
> Dustbort,
> SQL Server 2000 and earlier do not support "correlated joins",
> which is what you are trying to write. In your example, the
> right-hand table is a table-valued function that is a different
> table for each row of the left-hand table.
> In SQL Server 2005, this can be done with the new
> APPLY operator. In 2000, there is no easy way,
> though it's possible that there is an easier way to solve
> your specific problem.
> Steve Kass
> Drew University
>
> dustbort wrote:
>

Complicated (at least to me) insert

The best way to explain this is by example.

I have a source table with many columns.

Source
SYMBOL
EXCHANGE_NAME
CUSIP
TYPE
ISSUE_NAME
and so on

Then I have 3 other destination tables.

Exchanges
EXCHANGE_ID IDENTITY
EXCHANGE_NAME UNIQUE

SecurityMaster
SECURITY_MASTER_ID IDENTITY
SYMBOL UNIQUE
CUSIP
TYPE
ISSUE_NAME
and so on

Exchange_mm_SecurityMaster
EXCHANGE_ID
SECURITY_MASTER_ID

-- The Source table has multiple rows of the same symbol.
-- The Exchanges table is already populated with all the exchanges.
-- A single security (in the SecurityMaster table) can belong to many
Exchanges, hence the Exchange_mm_SecurityMaster table.

Now. If I just wanted to insert into the SecurityMaster table without
touching the Exchange_mm_SecurityMaster table I could just execute:

INSERT INTO SecurityMaster ([SYMBOL], [CUSIP], [TYPE], [ISSUE_NAME])
SELECT DISTINCT[SYMBOL], [CUSIP], [TYPE], [ISSUE_NAME]
FROM Source
WHERE NOT EXISTS (SELECT * FROM SecurityMaster SM WHERE SM.SYMBOL =
Source.SYMBOL)

Now to the Exchange_mm_SecurityMaster. I need the individual identity
values for each row inserted into SecurityMaster so I can then turn
around and insert into Exchange_mm_SecurityMaster. Here are the
issues/possibilities as I see it.

- @.@.IDENTITY will not work since I am not inserting a single row at a
time

- I guess I could INSERT INTO SecurityMaster first, THEN do another
INSERT INTO Exchange_mm_SecurityMaster with different where clause.

- I could create a stored procedure that does a single insert into
SecurityMaster and Exchange_mm_SecurityMaster. Then call that
procedure for each row in the SELECT DISTRICT from the Source table.
My main worry is the number of arguments passed in. My example only
shows a few but a regular SecurityMster table could have 30-50
columns.

- Maybe do something with a trigger but I am not sure if I can pass
the EXCHANGE_NAME value to the SecurityMaster trigger when that table
does not need it.

Hope I explained it clearly. Any help would be appreciated.Jason (JayCallas@.hotmail.com) writes:
> Now to the Exchange_mm_SecurityMaster. I need the individual identity
> values for each row inserted into SecurityMaster so I can then turn
> around and insert into Exchange_mm_SecurityMaster. Here are the
> issues/possibilities as I see it.
> - @.@.IDENTITY will not work since I am not inserting a single row at a
> time
> - I guess I could INSERT INTO SecurityMaster first, THEN do another
> INSERT INTO Exchange_mm_SecurityMaster with different where clause.

The dangers of having too many IDENTITY columns.

You appear to have a natural key for both tables; use these for the
connection table too.

If you really need artificial keys, I would recommened skipping the
IDENTITY property. Instead take data through a temp table with an
IDENTITY column. Then determin the highest ID in use in the target
table, and now you can compute what keys the newly inserted rows
will have.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns93F31A3D90F4Yazorman@.127.0.0.1>...
> Jason (JayCallas@.hotmail.com) writes:
> > Now to the Exchange_mm_SecurityMaster. I need the individual identity
> > values for each row inserted into SecurityMaster so I can then turn
> > around and insert into Exchange_mm_SecurityMaster. Here are the
> > issues/possibilities as I see it.
> > - @.@.IDENTITY will not work since I am not inserting a single row at a
> > time
> > - I guess I could INSERT INTO SecurityMaster first, THEN do another
> > INSERT INTO Exchange_mm_SecurityMaster with different where clause.
> The dangers of having too many IDENTITY columns.

Huh. I am confused. Why is it too many?

I have only 2 IDENTITY columns -- one in SecurityMaster and another in
Exchanges.

The table Exchange_mm_SecurityMaster is for many-to-many entries. The
same security from SecurityMaster table can be on multiple exchanges
from Exchanges table.

> You appear to have a natural key for both tables; use these for the
> connection table too.

My problem is not which key I use. My problem is finding the best
approach to inserting many rows at once.

Or are you saying instead of using the IDENTITY columns, from
SecurityMaster and Exchanges, in Exchange_mm_SecurityMaster, use the
SYMBOL and EXCHANGE columns?

> If you really need artificial keys, I would recommened skipping the
> IDENTITY property. Instead take data through a temp table with an
> IDENTITY column. Then determin the highest ID in use in the target
> table, and now you can compute what keys the newly inserted rows
> will have.

Not sure how this would help.

(Just for my own information -- IF I did use the IDENTITY columns,
what would be the best approach to inserting into both tables?)

Thank you for your help in this matter.|||Jason (JayCallas@.hotmail.com) writes:
> Huh. I am confused. Why is it too many?
> I have only 2 IDENTITY columns -- one in SecurityMaster and another in
> Exchanges.

Since both tables appears to have natural one-column keys, I am not
convinced that using IDENTITY is called for.

> Or are you saying instead of using the IDENTITY columns, from
> SecurityMaster and Exchanges, in Exchange_mm_SecurityMaster, use the
> SYMBOL and EXCHANGE columns?

Yes.

>> If you really need artificial keys, I would recommened skipping the
>> IDENTITY property. Instead take data through a temp table with an
>> IDENTITY column. Then determin the highest ID in use in the target
>> table, and now you can compute what keys the newly inserted rows
>> will have.
> Not sure how this would help.

As I understood it, problem is that you say:

INSERT tbl_a (...)
SELECT ...
FROM src

INSERT tbl_b (...)
SELECT ...
FROM src

And now you are to insert into the relation table, but you don't know
what the keys are.

But since the natural keys come from the src, you could say:

INSERT tbl_c (a_ident, b_ident)
SELECT a.a_ident, b_ident
FROM src s
JOIN tbl_a ON a.a_narural_key = s.a_natural_key
JOIN tbl_b ON b.b_narural_key = s.b_natural_key

Provided that you have all information available. Since your post
only included sketches of what you are doing, it is difficult to
tell if this is entirely applicable.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Complex XML Parsing/Shredding

Hi,

I have generated a sample XML document from the XSD file using XMLSpy and used these in XML Source stage.

While I was parsing/Shredding the XML file and to write to sequential files I got the following error message.

The XML Source Adapter does not support mixed content model on Complex Types.

Additional Information:
Pipeline component has returned HRESULT error code )xC02092A1 from a method call. [Microsoft.SqlServer.DTSPipelineWrap]

Can anybody give a sample code with Complex XML type.

--

Find attached the XSD format. This is FYI.

Not sure I understand the question.

Empty, simple and element-only content models for complex types are supported, but mixed content is not.

Are you asking for a sample of a schema that has one of those content models?

|||

Are you able to get my XSD schema attached in my previous question? If not, could you please tell me how to send you the XSD schema

My schema content will be changed based on the instrument type.

For example,

<instrument type='Equity'>
<equityrelatedcolumns>value</equityrelatecolumns>
<pricinglist>
<price1/>
<price2/>
...
...
...
<pricen/>
</pricinglist>
</instrument>
<instrument type='Bond'>
<bondrelatedcolumns>value</bondrelatedcolumns>
<pricinglist>
<price1/>
<price2/>
...
...
...
<pricen/>
</pricinglist>
</instrument>

|||

Current SSIS support for loading XML does not support Complex Types with mixed content models. I looked at the schema provided and noticed several complex types with mixed="true" defined.

However, based on the XML sample provided, it is unclear to me why this is required since known of the element instances contained any non-element content. This would lead me to believe that at least fot the sample provided, you could change the content model on the ComplexTypes to not be mixed to work around the limitation of SSIS.

If this is not feasible, then you will have to preprocess te instance documents with an XSLT to turn the non-element content for the ComplexTypes with mixed content into element conent.

Hope that helps -

Andy

|||

Thanks Andy. The XSD file is the correct one. Our original XML file will contain mixed content type only.

We generated sample XML file through XMLSPY and specified NULL for all the contents. That is the reason the XML file looks like simple content.

Hope SSIS next version might look into this issue.

Complex T-SQL

Guys
I have a data table that contains emails that could come from one or
more source. I want to add a column to the table to reflect the
combination of sources that the record came from.
Basically I am trying to create the hybird_src and the hybrid tables
from the data_table. Then I want to update a new column on data table
with hybrid_id.
I know I can do this with a cursor, but it seems like there should be a
better (set based) way to accomplish this. Does anyone have any
suggestions?
create table #data_table(email_id int,src_id int)
--raw data
insert into #data_table (1,5)
insert into #data_table (1,6)
insert into #data_table (1,7)
insert into #data_table (2,5)
insert into #data_table (2,6)
insert into #data_table (2,7)
insert into #data_table (3,5)
insert into #data_table (3,6)
insert into #data_table (3,7)
insert into #data_table (4,5)
insert into #data_table (4,6)
insert into #data_table (5,5)
insert into #data_table (5,9)
insert into #data_table (5,4)
insert into #data_table (5,20)
insert into #data_table (6,20)
insert into #data_table (6,5)
insert into #data_table (6,9)
insert into #data_table (6,4)
create table #hybrid_src (hybrid_id int,src_id int)
--results I am looking for
insert into #hybrid_src (1,5)
insert into #hybrid_src (1,6)
insert into #hybrid_src (1,7)
insert into #hybrid_src (2,5)
insert into #hybrid_src (2,6)
insert into #hybrid_src (3,5)
insert into #hybrid_src (3,9)
insert into #hybrid_src (3,4)
insert into #hybrid_src (3,20)
create table #hybrid(hybrid_id int,hybrid_name varchar(200))
--results I am looking for
INSERT INTO #hybrid(1,'5,6,7')
INSERT INTO #hybrid(2,'5,6')
INSERT INTO #hybrid(3,'4,5,9,20')Dave wrote:
> Guys
> I have a data table that contains emails that could come from one or
> more source. I want to add a column to the table to reflect the
> combination of sources that the record came from.
> Basically I am trying to create the hybird_src and the hybrid tables
> from the data_table. Then I want to update a new column on data table
> with hybrid_id.
> I know I can do this with a cursor, but it seems like there should be a
> better (set based) way to accomplish this. Does anyone have any
> suggestions?
>
Why would you want to destroy the apparently sensible and practical
design you already have by kludging it into the "hybrid" tables that
you say you want? Concatenating lots of values together in a column
just results in redundancy and denormalization. If you need to display
it that way in a report then do it in your presentation tier, not in
the database.
David Portas
SQL Server MVP
--|||I am trying to accurately reflect which source an email came from.
Since an email can belong to more than one source it makes since (at
least to me so far) to report on a hybrid of all the valid sources.
If I model email transactions in a Datamart the fact grain would be the
email. I need a source dimension.
Any feedback on this would be very helpful. Including a better way to
model this.

Complex counting, joining and grouping

I have a sales stats table and I want to do some counting and grouping by
source and cluster (join required).
The 2 tables and sample data are as follows:
CREATE TABLE
dbo.stat (id_source varchar(10) not null,
id_period int not null,
id_dept varchar(10) not null,
ind_Domestic char(1) not null,
trade_count int not null)
CREATE TABLE dbo.stat_Hierarchy(id_dept varchar(5) not null,
id_cluster varchar(10) not null)
INSERT INTO stat VALUES('INTERNET',200601,'N41','Y',250)
INSERT INTO stat VALUES('INTERNET',200601,'N41','N',100)
INSERT INTO stat VALUES('INTERNET',200601,'S51','Y',200)
INSERT INTO stat VALUES('INTERNET',200601,'S51','N',120)
INSERT INTO stat VALUES('INTERNET',200601,'021','Y',50)
INSERT INTO stat VALUES('INTERNET',200601,'021','N',70)
INSERT INTO stat VALUES('INTERNET',200601,'131','Y',30)
INSERT INTO stat VALUES('INTERNET',200601,'131','N',70)
INSERT INTO stat VALUES('STORE',200601,'00P','Y',130)
INSERT INTO stat VALUES('STORE',200601,'00P','N',1)
INSERT INTO stat VALUES('STORE',200601,'00S','N',100)
INSERT INTO stat VALUES('STORE',200601,'N41','Y',130)
INSERT INTO stat VALUES('STORE',200601,'N41','N',250)
INSERT INTO stat VALUES('STORE',200601,'S51','Y',110)
INSERT INTO stat VALUES('STORE',200601,'S51','N',320)
INSERT INTO stat VALUES('STORE',200601,'021','Y',30)
INSERT INTO stat VALUES('STORE',200601,'021','N',40)
INSERT INTO stat VALUES('AGENCY',200601,'0101','Y',50)
INSERT INTO stat VALUES('AGENCY',200601,'0101','N',10)
INSERT INTO stat VALUES('AGENCY',200601,'0100300','Y',100)
INSERT INTO stat VALUES('AGENCY',200601,'0100300','N',320)
INSERT INTO stat VALUES('AGENCY',200601,'021','Y',150)
INSERT INTO stat VALUES('AGENCY',200601,'021','N',50)
INSERT INTO stat VALUES('AGENCY',200601,'131','Y',20)
INSERT INTO stat VALUES('AGENCY',200601,'131','N',80)
INSERT INTO stat_Hierarchy VALUES('00P','IB_CL_OTHE')
INSERT INTO stat_Hierarchy VALUES('00S','IB_CL_OTHE')
INSERT INTO stat_Hierarchy VALUES('0101','LV_CL_SALE')
INSERT INTO stat_Hierarchy VALUES('021','LV_CL_SALE')
INSERT INTO stat_Hierarchy VALUES('131','LV_CL_SALE')
INSERT INTO stat_Hierarchy VALUES('0100300','HV_CL_SALE')
INSERT INTO stat_Hierarchy VALUES('N41','HV_CL_SALE')
INSERT INTO stat_Hierarchy VALUES('S51','HV_CL_SALE')
And I want the data to be returned like so:
id_cluster,id_source,ind_Domestic,trade_count
HV_CL_SALE,INTERNET,N,220
HV_CL_SALE,INTERNET,Y,450
HV_CL_SALE,STORE,N,570
HV_CL_SALE,STORE,Y,240
IB_CL_OTHE,STORE,N,101
IB_CL_OTHE,STORE,Y,130
LV_CL_SALE,AGENCY,N,140
LV_CL_SALE,AGENCY,Y,200
LV_CL_SALE,INTERNET,N,140
LV_CL_SALE,INTERNET,Y,80
LV_CL_SALE,STORE,N,40
LV_CL_SALE,STORE,Y,30
How could I do this?select
h.id_cluster,
s.id_source,
s.ind_Domestic,
sum(s.trade_count) as trade_count
from stat s
inner join stat_Hierarchy h on h.id_dept=s.id_dept
group by h.id_cluster,s.id_source,s.ind_Domestic
order by h.id_cluster,s.id_source,s.ind_Domestic

Compiled Code as a data source

Does anyone have any examples of a c# program and that returns a record set
from sql server that could be compiled and referenced as a data source in an
RDL file? If so I would sure like an example of the C# and the RDL file that
references it. Thanks.no.. but MS Access can do this for sure
On Apr 19, 7:10 am, Greg Larsen <gregalar...@.removeit.msn.com> wrote:
> Does anyone have any examples of a c# program and that returns a record set
> from sql server that could be compiled and referenced as a data source in an
> RDL file? If so I would sure like an example of the C# and the RDL file that
> references it. Thanks.|||Instead use web services using c# to directly access the datasource and
reports itself. A good sample is given in the BOL.
Amarnath
"Greg Larsen" wrote:
> Does anyone have any examples of a c# program and that returns a record set
> from sql server that could be compiled and referenced as a data source in an
> RDL file? If so I would sure like an example of the C# and the RDL file that
> references it. Thanks.|||yeah.. or you could write your own compiler and install linux
I mean wtf
fuck web services
fuck C#
can I put a form in a BI project and loop through all of the reports
in my PROJECT?
until I can do this; SSRS can fuck themselves
On Apr 19, 11:28 pm, Amarnath <Amarn...@.discussions.microsoft.com>
wrote:
> Instead use web services using c# to directly access the datasource and
> reports itself. A good sample is given in the BOL.
> Amarnath
> "Greg Larsen" wrote:
> > Does anyone have any examples of a c# program and that returns a record set
> > from sql server that could be compiled and referenced as a data source in an
> > RDL file? If so I would sure like an example of the C# and the RDL file that
> > references it. Thanks.