Transform Variable - Add OR Operator

May 1, 2015

I wish my transform variable, so that adding the OR operator, and the words within the quotes are not to put the OR.

ALTER PROCEDURE
@Product = ' 'ORANGE LEMON' BANANA APPLE 'PEACH PEAR' '
AS

-- I WANT TRANSFORM THE WORDS

@PRODUCT = 'ORANGE LEMON' OR BANANA OR APPLE 'PEACH PEAR'

SELECT Description
FROM Production.ProductDescription
WHERE CONTAINS(Description, @PRODUCT)
GO

View 2 Replies


ADVERTISEMENT

SQL Server 2012 :: Transform Variable - Add Operator OR

May 1, 2015

I wish my transform variable, so that adding the OR operator, and the words within the quotes are not to put the OR.

ALTER PROCEDURE
@Product = ' 'ORANGE LEMON' BANANA APPLE 'PEACH PEAR' '
AS

-- I WANT TRANSFORM THE WORDS

@PRODUCT = 'ORANGE LEMON' OR BANANA OR APPLE 'PEACH PEAR'

SELECT Description
FROM Production.ProductDescription
WHERE CONTAINS(Description, @PRODUCT)
GO

View 8 Replies View Related

SQL Server 2014 :: Transform Variable - Add Operator OR

May 2, 2015

I have a text box on a web application that allows the user to type what words they want to look. In the first version of the web application the user wrote the words he wanted and was wanted on the table by CONTAINS various words with the OR operator . What I wanted now was to allow the user to write several words, but with a particularity . The words that were inside '' was like a word.

I wish my transform variable, so that adding the OR operator, and the words within the quotes are not to put the OR.

ALTER PROCEDURE
@Product = ' 'ORANGE LEMON' BANANA APPLE 'PEACH PEAR' '
AS

-- I WANT TRANSFORM THE WORDS
@PRODUCT = 'ORANGE LEMON' OR BANANA OR APPLE 'PEACH PEAR'

What I meant was that even using the full-text functionality. I'm using CONTAINS . But before the CONTAISN was :

SELECT Description FROM Production.ProductDescription WHERE CONTAINS ( Description, PRODUCT )

Since PRODUCT and was researched by words such as ORANGE LEMON OR OR OR BANANA APPLE OR PEAR OR PEACH .

What is wanted now the words that come from the WEB application within '' stay as if it were the AND operator then was :

Product = '' ORANGE LEMON ' BANANA APPLE ' PEACH PEAR ''

PRODUCT = ' ( ORANGE AND LEMON ) OR BANANA OR APPLE OR ( PEACH AND PEAR) '

View 3 Replies View Related

Lookup Transform With Variable Parameter

Jun 8, 2006

Is it possible to use a VARIABLE in the Lookup Transform? I am setting the cache mode to partial and have modified the caching SQL statement on the advanced tab to include the parameterized query, but the parameter button only allows me to select columns to map to the parameter. I need to use a variable instead. I see the ParameterMap property of the transform in the advanced editor, but don't see how I can use this to map to a variable.

Can this be done, or do I need to use a new source, sort and left join component to accomplish the same thing?

Thanks!

Brandon

View 3 Replies View Related

How To Pass A Variable Into In Operator

Jul 19, 2006

How can i execute this query thru Stored procedure ?

delete from ts_imported where ts_imported.c_change_symbol in ('A','B')

Symbols ('A','B') varies
How to pass this in paramter


I tried this

declare @Symbol varchar(20)
declare @apos char(1)
set @apos=''''

set @Symbol = @apos +'A' + @apos + ',' + @apos + 'B' + @apos
delete from ts_imported where c_change_symbol in (@Symbol)

Doesn't produce correct output!

View 7 Replies View Related

PIVOT Operator For Variable Number Of Transformations

Feb 19, 2007

Hi, i'm trying to port a pivot query from access to sqlserver.
I'm trying this query:

SELECT IDMerce, [1] AS [Department-1], [2] AS [Department-2], [3] AS
[Department-3], [4] AS [Department-4]
FROM (SELECT IDMerce, Pezzi, IDMagazzino
FROM Disponibilita) p PIVOT (sum(Pezzi) FOR
IDMagazzino IN ([1], [2], [3], [4])) AS pvt


this works, but in my case i don't know in advance how many transformations
i need, so there is a solution?
Thanks

View 1 Replies View Related

Integration Services :: Assign Value Of Aggregate Transform To A Variable Without Using Script Task In SSIS

Jun 2, 2011

In my current project i have a requirement to assign value of an aggregate transform to a variable. But i need to accomplish it without using a script task.

View 3 Replies View Related

Invalid Operator For Data Type. Operator Equals Boolean AND, Type Equals Nvarchar

Jun 2, 2004

I get this error when I attempt to read from a datareader using the following sql statement:

Dim mysql As String = "SELECT PayrollTrans.PayrollID, Employees.[EmpFirstName] & ' ' & " _
& " Employees.[emplastname] AS FullName, Employees.[City] & ', ' & Employees.[State] & ' ' & Employees.[zip] AS CityState " _
& " , PayrollTrans.Date, PayrollTrans.EmployeeID, PayrollTrans.RegHours, " _
& " PayrollTrans.OTHours , PayrollTrans.RegPay, PayrollTrans.OTPay, " _
& " PayrollTrans.FedTax, PayrollTrans.FICATax, PayrollTrans.MedicareTax, " _
& " PayrollTrans.ESCTax, PayrollTrans.StateTax, PayrollTrans.ESCEMPTax, " _
& " PayrollTrans.FUTATax, PayrollTrans.NetPay, Employees.EmployeeID, " _
& " Employees.Address1, Employees.Address2, Employees.SSAN, " _
& " Employees.PayType, Employees.RegPayRate, Employees.OTPayRate, " _
& " Employees.MaritalStatus, Employees.FedExemption, Employees.StateExemption, " _
& " Employees.Active, Employees.SelectforPay, Employees.PayDate " _
& " FROM PayrollTrans, Employees where PayrollTrans.EmployeeID = Employees.EmployeeID;"

my reader command list as follows:

Dim objCM As New SqlClient.SqlCommand(mysql, SqlConnection1)
Dim objDR As SqlClient.SqlDataReader
objDR = objCM.ExecuteReader


Any ideas on where I am going wrong?

Thanks in advance

View 3 Replies View Related

Invalid Operator For Data Type. Operator Equals Boolean AND, Type Equals Datetime.

May 18, 2004

I am getting a error message saying: Invalid operator for data type. Operator equals boolean AND, type equals datetime.

I traced the pointer to @gdo and @gd, they are both dates!

INSERT INTO AdminAlerts values (CURRENT_USER, 'UPDATE', getDate(), @biui, 'Updated booking, ID of booking updated: ' & @biui & ', Booking date and time before/after update: ' & @gdo & '/' & @gd & ', Room number before/after update: ' & @rno & '/' & @rn & ' and Customer ID before/after update: ' & @cio & '/' & @ci)


If I cut that two dates out it works fine.
Could someone tell me the syntax to include a date in a string :confused:

View 3 Replies View Related

DTS Transform

Jul 13, 2004

I get this error:

column 6 ('CheckDate', dbType_dbtimestamp), status 6: dat over flow
invalid character value for cast specification

Here is the sql generated :

CREATE TABLE [DMS].[dbo].[Master] (
[Date_Entered] smalldatetime NULL,
[Initials] nvarchar (10) NULL,
[VendorName] nvarchar (50) NULL,
[CheckNum] nvarchar (20) NULL,
[ExpenseType] nvarchar (10) NULL,
[CheckDate] smalldatetime NULL,
[CheckAmount] money NULL
)


IM guessing i need to change date_enteed type or do a cast

any help pls

!~<M

View 3 Replies View Related

Look UP Transform

Apr 10, 2008



Hi ,

i am using to Lookup transform for the matching records , if my source records are matching with lookup records it work fine , if not it through the following error


[Lookup [34]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Lookup" (34)" failed because error code 0xC020901E occurred, and the error row disposition on "output "Lookup Output" (36)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.



Please advice , how i can solve this .




Thanks in Advance
Raja Ragothaman

View 1 Replies View Related

DTS Transform Issue

Jun 28, 2006

I am importing data from Excel to a SQL table using a simple DTS. At times the DTS fails because one of the columns in the Excel file may have an invalid time date entry. Sometimes the time will be an invalid negative number and will cause an overflow error durring import to the SQL table column.

Is there a way to capture the data before writing it to the table and validate it and if it is invalid, or more specifically a negative nuimber, enter a default value or a null value?

If there is could you be specific in how to setup the DTS transformation script.

TIA
Jeff

View 4 Replies View Related

Transform XML From A SELECT...FOR XML

Jan 14, 2008

I want to transform elements in a XML-respons.

If I use FOR XML PATH, ELEMENTS in the SELECT-statment I get the following result:

<row>
<nr>99</nr>
<namn>test bolag</namn>
</row>
<row>
<nr>02</nr>
<namn>test bolag 2</namn>
</row>

I want to modify the elements so that they look like this insteed in the SELECT-statment.

<row>
<field name="nr">99</field>
<field name="namn">test bolag</field>
</row>
<row>
<field name="nr">98</field>
<field name="namn">test bolag 2</field>
</row>

I need to transform the resulting XML because I need to send it to a webservice with a mySQL database on the reciving end.

Please help me :-)

View 5 Replies View Related

Data Transform

Jun 22, 2008

Hi friends,
Can somebody tell me how to do this-
How can we Analyze existing code used to transform data into the Operations Data Warehouse, and make changes to correspond to upcoming changes in the SAP data sources.
Thanks

sk

View 1 Replies View Related

Transform Recordset

Jul 20, 2005

Hi,I have table where i have the following fields in datasheetview:id | date | image | question1 | question2 | question..NI would like to have it in this way:id | date | image | questionnr | answer1 01-01-2004 test.tif 1 1000 (this is the value offield question1)As you can see the first 3 fields remains the same, but the records of those3 fields should be inserted for each record of the question fields. I can dothis with a union query for each questionfield, but.....How can i do this automatically, because there are more than 500 columns.

View 1 Replies View Related

Script Transform

Jun 25, 2006

Hi,

My requirement is to check whether value of a particular column is null or not. if it is null I have to enter warning messages into the temp table I have created.

For this I am using Script Transform

Now I want to know how to write info from script transform to a table using SSIS.

Currently I am using the following code in script component

[Code]

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim ConnString As String

ConnString = "Data Source=ABC;Initial Catalog=XXXX;Integrated Security=SSPI;"

Dim sqcn As New SqlConnection(ConnString)

''Dim sqlCmd As New SqlCommand(ConnString, sqcn)

'sqcn.Open()

if Row.Col1_IsNull Then

sqlCmd.CommandText = "Insert into AuditLog values('ERROR','Missing','" + Row.Col5 + "','" + Row.Col6 + "') "

sqlCmd.ExecuteNonQuery()

end if

End Sub

[/Code]



Without using SqlConnection and SqlCommand is thereany way I can get the Connection Obj and able to insert rec in the table.



Thanks



View 1 Replies View Related

OLE DB Command Transform

May 8, 2007

Hi All ,





I am creating packages from a template package whicg I have built ,I have managed to implement basically everything sucessfully .Setting Properties on all the different tasks ,Connections etc except for the OLE DB Command transform.



I have not been sucessfull in getting to the properties or collections which allows me to do the mapping of the Command to parameters (Command Below),I am aware that the command executes for every row . I really need help with how to now do the mapping between the columns and the Paramaters programmatically in c#



I have set the sql command properties of the OLE DB Command Transform,as below



//Setting Update Comand ComponentProperties

IDTSComponentMetaData90 oledbCMDUpdate = dataflow.ComponentMetaDataCollection[0];

oledbCMDUpdate.Name = "name" ;

oledbCMDUpdate.RuntimeConnectionCollection[0].ConnectionManagerID = pack.Connections[0].ID;

CManagedComponentWrapper instanceCMD = oledbCMDUpdate.Instantiate();

instanceCMD.SetComponentProperty("SqlCommand", GetUpdateSQL(tablename)) ;



The Sql that is returned by the GetUpdateSQL(tablename)) method is below



UPDATE [ADM_AdjustmentAction]
SET AdjustmentActionCode = ?
,AdjustmentActionName = ?
,AdjustmentActionDescription = ?
,AdjustmentActionEFD = ?
,AdjustmentActionETD = ?
,UserID = ?
,ProcessDatetime = ?
,ModuleID = ?
WHERE AdjustmentActionID = ?



Thanks in Advance

Cedric


View 5 Replies View Related

TRANSFORM Query

Aug 31, 2006

I'm new to SQL Server 2005. I used the TRANSFORM query in Access to display the data I had stored in columns into rows. I want to do something similar in SQL Server 2005 but it doesnt let me. I have used the same query here, but it gives an error.

This is the query I was running in Access and it was working:

TRANSFORM Max(Schools.Expense) AS MaxOfExpense
SELECT Schools.[FiscalYear], Max(Schools.[FIPS]) AS [Total Of FIPS]
FROM Schools
GROUP BY Schools.[FiscalYear], Schools.[FIPS]
PIVOT Schools.[DataID];

If there some other syntax for SQL Server or it doesnt support this command or what???

View 1 Replies View Related

Need Help With LOOKUP Transform

Aug 22, 2007

I have a very simple problem I am trying to solve.

I have a table with a "DateEntered" field, and I have an ssis pkg set up to load data from a file into the database table. I just want to make sure that no one loads the same file twice in one day.

For example, if today is 8/22/07, and "DateEntered" is "2007-08-22", then I want to add a Lookup transform to run a query that will check and see if there's any rows in the table with a "DateEntered" is "2007-08-22". If so, don't load the file again!

Here's my query:

SELECT Code
FROM myTable
WHERE DATEADD(dd, DATEDIFF(dd, 0, DateEntered), 0) = DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)

(all the dateadd stuff is doing is removing the time portion from the DateEntered field, so we are comparing apples to apples).

Now, if the query returns a bunch of "Codes" then we know that the data has already been entered for the day! So far, so good.

Now, how do I set up the Lookup to get it to work? I'm getting this error message:
Error 1 Validation error. Data Flow Task: Lookup [1299]: The lookup transform must contain at least one input column joined to a reference column, and none were specified. You must specify at least one join column. FXRateLoader.dtsx 0 0

But I thought I did this! On the columns tab, I have:
Lookup column: code
Lookup operation: Replace 'code'
Output alias: code

I have my error output set to:
Lookup output - redirect row

I don't know what I'm doing, obviously..........!

Need assistance :-)

View 13 Replies View Related

Lookup Transform

Jan 24, 2007

Hi!
I am a newbie, grateful for some help. I have a Source Ole DB w sql-command selecting the customer.salary and customer.occupation, which I want to match with demo_id in Ole DB destination.
salary, occupation also in dim_demographic.
But in Lookup editor I find no column demo_id... how do I do this?

View 20 Replies View Related

Need Help On Lookup Transform

Jun 25, 2007

Please suggest me an article that describes 'Lookup Transform' (Specifically passing parameters) in detail.



Note: Is it possible to pass "variable" as parameters in Lookup transform?

View 10 Replies View Related

CLR SP To Transform Xml (SecurityException)

Feb 27, 2006

I am in the process of creating a simple managed stored procedure using C# and VS2005. The goal is to transforms an Xml document with a Xslt file that may contain a very simple script .

<msxsl:script language="C#" implements-prefix="user">
<![CDATA[
public static string Lower(string value)
{return value.ToLower();}
]]>
</msxsl:script>

At this stage my stored procedure code is extremly simple.

[Microsoft.SqlServer.Server.SqlProcedure]
public static void Manufacture(string xsltFile, string xmlFile)
{
XslCompiledTransform xslt = new XslCompiledTransform(false);
PermissionSet ps1 = new PermissionSet(PermissionState.Unrestricted);
XmlSecureResolver resolver = new XmlSecureResolver(new XmlUrlResolver(), ps1);
xslt.Load(xsltFile, XsltSettings.TrustedXslt, resolver);
....more code....
xslt.Transform(xmlFile, xslArg, ms);
}

If I execute the SP above and the script exists in the Xslt file, I get the following error upon loading the Xslt file. If I remove the script it transforms perfectly.

A .NET Framework error occurred during execution of user defined routine or aggregate 'Manufacture': System.Security.SecurityException: Request failed.
System.Security.SecurityException:
at System.Xml.Xsl.Xslt.Scripts.CompileClass(ScriptClass script)
at System.Xml.Xsl.Xslt.Scripts.CompileScripts()
at System.Xml.Xsl.Xslt.QilGenerator.Compile(Compiler compiler)
at System.Xml.Xsl.Xslt.QilGenerator.CompileStylesheet(Compiler compiler)
at System.Xml.Xsl.Xslt.Compiler.Compile(Object stylesheet, XmlResolver xmlResolver, QilExpression& qil)
at System.Xml.Xsl.XslCompiledTransform.CompileToQil(Object stylesheet, XsltSettings settings, XmlResolver stylesheetResolver)
at System.Xml.Xsl.XslCompiledTransform.LoadInternal(Object stylesheet, XsltSettings settings, XmlResolver stylesheetResolver)
at System.Xml.Xsl.XslCompiledTransform.Load(String stylesheetUri, XsltSettings settings, XmlResolver stylesheetResolver)

I have tried messing around with CAS and giving virtually everything full trust. Nothing resolves the issue.

Any ideas?

View 6 Replies View Related

LookUP Transform...

Apr 16, 2007

Dear friends,

I have a ETL that have a Lookup transform to get a rate from a table SpotRates.

The problem is when the match od some date in SpotRates Table doens't exist...

And for that records I need to lookup for next date...

For example...



SpotRate Table









Date
Currency
Rate

05-04-2006 0:00
DOLAR ESTADOS UNIDOS
1,2262

06-04-2006 0:00
DOLAR ESTADOS UNIDOS
1,2312

07-04-2006 0:00
DOLAR ESTADOS UNIDOS
1,2179

10-04-2006 0:00
DOLAR ESTADOS UNIDOS
1,2099

11-04-2006 0:00
DOLAR ESTADOS UNIDOS
1,2105

12-04-2006 0:00
DOLAR ESTADOS UNIDOS
1,2125

13-04-2006 0:00
DOLAR ESTADOS UNIDOS
1,2094

18-04-2006 0:00
DOLAR ESTADOS UNIDOS
1,2252

19-04-2006 0:00
DOLAR ESTADOS UNIDOS
1,2346

20-04-2006 0:00
DOLAR ESTADOS UNIDOS
1,2346

21-04-2006 0:00
DOLAR ESTADOS UNIDOS
1,2315

24-04-2006 0:00
DOLAR ESTADOS UNIDOS
1,2365

25-04-2006 0:00
DOLAR ESTADOS UNIDOS
1,2425



When I first try to lookup the date 17-04-2006, doesnt give me any records... and I need to create a new lookup for the next date from 17-04-2006. And in this example the next date is 18-04-2006.. How can I do it??

I made a sql query date gives me the next date with 2 parameters ... but I'm having some errors...



SELECT TOP 1 Data
FROM Spot_Rates
WHERE (Currencies_Name = ?) AND (Data > CONVERT(DATETIME, ?, 102))
ORDER BY Data DESC



In this exampple, the parameters returned from lookup1 is:

Currencies_name= 'DOLAR ESTADOS UNIDOS'

DATE='17-04-2006'



I need to create a second lookup transform to return the next date/currency for each row that didnt match in the first lookup...

Regards,



Pedro









View 16 Replies View Related

Lookup Transform

Aug 10, 2007

I want to do something relatively simple with SSIS but can't find an easy way to do this (isint it always the case with SSIS )

I have a column lets say called iorg_id, and I want to lookup the matching rows for this col in a table.
In this table iorg_id may have several potential matching rows. In this table there is another col called 'Amount'.
I want to retrieve for each iorg_id the matching iorg_id in the other table but only the row with the largest value in the 'Amount' col.

I couldn't find a way to do this all in the Lookup Transform.
I can match the iorg_ids and retrieve the Amount column, but can't find a way just to retrieve the matching row with the largest value in the Amount col. The only way I can think to do this is then run the output from the Transform through an Aggregate function and determine the Max (although haven't tested this yet).

Seems strange to me in that the SQL in the Advanced tab gives me something like:
select * from
(select * from [dbo].[Table1]) as refTable
where [refTable].[iorg_id] = ?

where I believe the first 'select *' is retrieving all the cols that are listed in the LookupColumns list in the Columns tab.
I thought I would be able to amend this to something like:
select max(amount) from
(select * from [dbo].[Table1]) as refTable
where [refTable].[iorg_id] = ?

but I get a metadata type error.

So, questions are:
Is it possible to do this all in the Lookup Transform are do I have to use the Aggregate function as I think ?
Why is it not possible to amend the sql in the Advanced tab to manipulate the returned data ?

View 3 Replies View Related

Pivot Transform Help

Sep 5, 2007

I need to transform the following layout by hopefully using the pivot transform, but am confused about the editor ......I have a compound primary key that I want to keep intact but then values in the row need to be broken out into their own row.

I need to go from this...

PKcol1 PKcol2 PKcol3 col4 col5 col6 col7
A 2007 1 Y N N N
A 2007 2 Y Y N N
A 2007 3 N N N Y

into this....

A 2007 1 col4 Y
A 2007 1 col5 N
A 2007 1 col6 N
A 2007 1 col7 N
A 2007 2 col4 Y
A 2007 2 col5 Y
A 2007 2 col6 N
A 2007 2 col7 N
A 2007 3 col4 N
A 2007 3 col5 N
A 2007 3 col6 N
A 2007 3 col7 Y


Can I do this using the pivot transform? Any suggestions?

View 4 Replies View Related

TRANSFORM && PIVOT Equivalents?

Jan 16, 2003

I am trying to find the equivalent to MS Access's TRANSFORM and PIVOT in T-SQL. I've tried using GROUP BY with CUBE, and I can't seem to get the data in the correct format. Can someone help? And please explain things to me like I am an idiot, because I am.

Here is the current table and the desired results that I want.

Current Table
MonthTypeSubtypeTotalTime
1TaskASubA5
1TaskASubB10
1TaskASubC8
1TaskBSubX5
2TaskASubA4
2TaskBSubX5

Desired Result:
TypeSubTypeJanuaryFebruary
TaskASubA54
TaskASubB100
TaskASubC80
TaskBSubX55

Thanks.
Colleen

View 5 Replies View Related

DTS - Datapump, Transform Data

Feb 24, 2001

Hi,

On my MS SQL Server 2000, I am trying to create a generic way to load tables into my datawarehouse.

I have as input to the process a large number of table definition(s) stored individually as files on my server. And, ascii delimited data files in various locations but mostly accessible via NFS mounts.

I created two DTS package in MSSQL2K that in theory represents what I want to do:

package1
... invoke package2 with global variables to load a system of related tables

package2
... check for a trigger file
... set the "Execute SQL Task" statement to my first file
... run the "Execute SQL Task" which drop/add's a table
... set a "Connection" to a data source file that I want to use
... run the transformation
and, with that my package starts to fall apart
... set the "Execute SQL Task" statement to the next file, and
...... goback and execute it

I can't figure out how to set the table in the transformation section to the table I want to use. And, I assume next to have the transformations links between the source and new table relinked.

The source files contain in the first row the column names as found in the tables I just created.

thanks,

Dave Rowsome

View 1 Replies View Related

TRANSFORM MUTLIPLE COLUMNS

Jun 18, 2008

Hi all, new to SSIS so please bear with me on the noobie question:

Situation: have a SQL database with several tables, each table has several char fields that represent dates (ex. YYYYMMDDHHMMSSMS)- this SQL database is created weekly from an extract of an old Oracle RDB database maintained by a third party vendor.

Need to copy the data to a new database and tables
Then for each table:
1. check each char date column and if the value is '1858111700000000' (Oracle dummy date) then change to SQL low date, if it's not then transform the date into SQL server date format. I' ve tried some of the data controls - just need to know which ones to use and in what order.

What would be the best controls to do iterative processing in an efficiant manner? Some tables have upto 5 million rows

Any Ideas would be appreciated! Thanks!

View 5 Replies View Related

How To Transform Fact Table Only By SQL?

Jul 20, 2005

Hi,this is easy with OLAP tools, but I need to do it just with MS-SQLserver:fatTableyeartypeval97a197b297c398a498b598c6....yeartype_atype_btype_c971239845699...The problem is number of different types - not just 3 like a,b,c butmore than 100, so I don't want to do it manually likeselectyear, a.val, b.val, c.valfrom(select year, val from factTable where type='a') afull join (select year, val from factTable where type='b') bon a.year = b.yearfull join (select year, val from factTable where type='c') con a.year = c.yearis it possible somehow with DTS or otherwise? I just need to presentthe data in spreadsheet in more readable form, but I cannot find anyway how to export the result from MS-SQLserverOLAPservices to Excel...Martin

View 2 Replies View Related

Lookup Transform + Composite Key

Oct 27, 2007

I am trying to digest this logic, and have been unsuccessful so far. I am designing a package for incremental loads, but the destination table has a composite primary key on 2 columns, one of which is nullable. The source data comes from a SPROC. Uptill now, I have been banging my head trying to get this logic to work via the Lookup transform with a conditional split, but it doesn't work. Am I on the right track, or should I be using the SCD Wizard?

As a side note, I have been trying to work a solution using Andrew's blogpost on doing incremental loads: http://sqlblog.com/blogs/andy_leonard/archive/2007/07/09/ssis-design-pattern-incremental-loads.aspx

Thanks.

View 8 Replies View Related

Error Using Pivot Transform

Jan 5, 2006

Hey, did someone try to use the pivot transform?
It's not what you would call easy to configure...

In BOL there is the following section:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/55f5db6e-6777-435f-8a06-b68c129f8437.htm

I have followed the instructions there and I get an error when I try to map the new output column to the pivotkey:

"Output column "Yellow" (69) cannot be mapped to PivotKey input column"

So I mapped the new output column to the column holding the quantity values. This worked.
May this be a bug in BOL?

Further on I got some strange behaviour of the pivot transform:

My input is the following CSV-Flatfile:
sk1;sk2;pk1;qty
A;1;Yellow;2
A;1;Green;1
B;1;Yellow;5
A;2;Blue;3
A;2;Green;9
A;1;Green;5

If I omit the last line, everything is fine.
When passing the file as is into the pivot transform the output is:

SK1    SK2    Yellow    Green    Blue
A        1        2            1            NULL
B        1        5            NULL    NULL
A        2        NULL    9            3
A        1        NULL    5            NULL

As you can see the key a;1 is duplicate. The cols SK1 and SK2 are my primary key of the destination table.

When the input is sorted by the first two colums the pivot transform throws an Error:

Error: 0xC02020CF at Pivot w sort, Pivot [39]: Duplicate pivot key value "Green".

I expected to have a sum over all Green for the key A;1. It seems that I have to use an aggregate which in my opinion should
be obsolete here.

As a conclusion I have to say that this task is far away from perfect but not bad for a start.

Regards
Fridtjof

View 3 Replies View Related

Datafile SSIS Transform XML - Help

May 19, 2007

Hi,



I want to insert the contents of a data file into a SQL Server table with XML datatype column using SSIS. I am fairly new to SSIS and script tasks.

Example:

Salary Data file:

EmpID, Name, Salary



100, John, 100000

200, Scott, 200000



Database Table:

create table xmlTable (id int identity(1,1), xmlColumn XML)



The contents of the file needs to be written to xmlTable. For each row in the Salary Data file there will be a corresponding row in the xmlTable.



xmlTable (Result):



1, <EmpID 100...>

2,<EmpID 200...>



Any help with details is greatly appreciated.



Thanks,

Rob















View 4 Replies View Related

Multi-Merge Transform

Jun 14, 2006

I would like a component that has the following functionality (if this already exists, let me know):

Pretty much, I want a multiple input merge join transform that joins X number of inputs so long as they have the same sorted key value. I only care about inner joins.

The reason for this component is that I have a data flow that is having to perform a LOT of lookups. Having to do them one after another is incredibly slow, so I thought that I could multi-cast them and perform several at once, which works fine, but merging the army of split inputs is messy with multiple sorts and merge join transforms. I can live with it this way, but having a single transform to collect all the inputs would look a lot nicer and a lot less to configure.

Cheers!

Brandon

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved