Showing posts with label solution. Show all posts
Showing posts with label solution. Show all posts

Thursday, March 29, 2012

Help with CustomComponent in SSIS-DataFlow

Hello Trying to figure out a clever solution for splitting multivalued columns out into n-columns. For that I've build a custom component in SSIS using ms-help://MS.VSCC.v80/MS.VSIPCC.v80/MS.SQLSVR.v9.en/dtsref9/html/4dc0f631-8fd6-4007-b573-ca67f58ca068.htm as an example. I need to be able to add columns to the OutputCollection in designtime, but the designer returns an error: Error at Data Flow Task [Uppercase [5910]]: The component "Uppercase" (5910) does not allow setting output column datatype properties. How do I enable the designer to accept designtime changes in the columncollection?  Kind regards

You have to override the method SetOutputColumnDataTypeProperties in your component and implement it like this:

public override void SetOutputColumnDataTypeProperties(int outputID, int outputColumnID, Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType dataType, int length, int precision, int scale, int codePage)

{

IDTSOutputCollection90 outputColl = this.ComponentMetaData.OutputCollection;

IDTSOutput90 output = outputColl.GetObjectByID(outputID);

IDTSOutputColumnCollection90 columnColl = output.OutputColumnCollection;

IDTSOutputColumn90 column = columnColl.GetObjectByID(outputColumnID);

column.SetDataTypeProperties(dataType, length, precision, scale, codePage);

}

Friday, March 23, 2012

Help with a stored procedure or DTS package

Can anyone offer me a solution to this , i have a table that hold
QI AN Quantity Price Order Refer
Area
28 96229392 15 83.98 1 A1
Level 1
28 960004877 55 192.68 2 B
Level 1
28 96011194 56 102.66 3 B1
Level 1
28 96011194 112 10.66 3 C
Level 2
and i want to transform it to
QI AN Quantity Price Refer
Area
28 grupanfa 0
Level 1
28 96229392 15 83.98 A1
28 960004877 55 192.68 B
28 96011194 56 102.66 B1
28 grupenda 0
28 grupanfa 0
Level 2
28 96011194 112 10.66 C
28 grupenda 0
can anyone please advise
Regards
JohnAre both these tables in sql server? what are the data types?
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"John" <topguy75@.hotmail.com> wrote in message
news:446afe1a$0$692$fa0fcedb@.news.zen.co.uk...
> Can anyone offer me a solution to this , i have a table that hold
> QI AN Quantity Price Order Refer
> Area
> 28 96229392 15 83.98 1 A1
> Level 1
> 28 960004877 55 192.68 2 B
> Level 1
> 28 96011194 56 102.66 3 B1
> Level 1
> 28 96011194 112 10.66 3 C
> Level 2
> and i want to transform it to
> QI AN Quantity Price Refer
> Area
> 28 grupanfa 0
> Level 1
> 28 96229392 15 83.98 A1
> 28 960004877 55 192.68 B
> 28 96011194 56 102.66 B1
> 28 grupenda 0
> 28 grupanfa 0
> Level 2
> 28 96011194 112 10.66 C
> 28 grupenda 0
> can anyone please advise
> Regards
> John
>|||The first talbe is in SQL, the second need creating as a temporary table,
can assume all columns are varchar(50)
Regards
john
"Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
news:8tOdnTrKJqZY1fbZRVnyuw@.bt.com...
> Are both these tables in sql server? what are the data types?
> --
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> ___________________________________
>
> "John" <topguy75@.hotmail.com> wrote in message
> news:446afe1a$0$692$fa0fcedb@.news.zen.co.uk...
>|||Where are groupenda and groupanfa coming from? Are they indicating the
start and end of groups, based on level? Why do these have a value of 0,
and is the 0 supposed to represent anything?
Are you trying to create a flat file for an export to another system?
This looks like something that you should be doing in an application or
report, rather than in SQL.
"John" <topguy75@.hotmail.com> wrote in message
news:446afe1a$0$692$fa0fcedb@.news.zen.co.uk...
> Can anyone offer me a solution to this , i have a table that hold
> QI AN Quantity Price Order Refer
> Area
> 28 96229392 15 83.98 1 A1
> Level 1
> 28 960004877 55 192.68 2 B
> Level 1
> 28 96011194 56 102.66 3 B1
> Level 1
> 28 96011194 112 10.66 3 C
> Level 2
> and i want to transform it to
> QI AN Quantity Price Refer
> Area
> 28 grupanfa 0
> Level 1
> 28 96229392 15 83.98 A1
> 28 960004877 55 192.68 B
> 28 96011194 56 102.66 B1
> 28 grupenda 0
> 28 grupanfa 0
> Level 2
> 28 96011194 112 10.66 C
> 28 grupenda 0
> can anyone please advise
> Regards
> John
>sql