How to consolidate duplicate records

Hello,
I'm assigned a task to clean up a table which has the data like below:
col1--col2--col3--col4
103 20 606 $50
103 20 606 $60
I was told to consolidate the sales$ and only keep one record, the primary
key is on col1,col2 and col3.
How can I do this?
Thanks,
SarahWhat about
SELECT col1,col2,col3,sum(col4) as sales
From YourTable
Group by col1,col2,col3
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"SG" <sguo@.coopervision.ca> schrieb im Newsbeitrag
news:%23DMa7GoRFHA.1392@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I'm assigned a task to clean up a table which has the data like below:
> col1--col2--col3--col4
> 103 20 606 $50
> 103 20 606 $60
> I was told to consolidate the sales$ and only keep one record, the primary
> key is on col1,col2 and col3.
> How can I do this?
> Thanks,
> Sarah
>|||You can use GROUP BY like:
SELECT col1, col2, col3, SUM( col4 ) AS "col4"
FROM tbl
GROUP BY col1, col2, col3 ;
Anith|||Thanks Anith and Jens for your quick response. How could I forget this? I
made a mistake to group by sales column which I should use sum()so it gave
me the wrong result.
Thanks again,
Sarah
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:eChkdLoRFHA.2252@.TK2MSFTNGP15.phx.gbl...
> You can use GROUP BY like:
> SELECT col1, col2, col3, SUM( col4 ) AS "col4"
> FROM tbl
> GROUP BY col1, col2, col3 ;
> --
> Anith
>

How to Connect remote Access database to SQL server

Hai,
I want to Convert remote Ms access data to sqlserver data.
Here i am using FTP Task in DTS for downloading Ms Access database to local machine.
After that, I convert it to sql data.
But, most of time, With download the mdb file, the message 'Opreation sucessfully' display.
Please give solution for this.
With regards,
dharmaprakash.
**********************************************************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...Please do not post separately in different groups.
Whats the matter if its performed successfully.
Jens Süßmeyer.

How to connect ExecuteSQLTask with data flow task

Hi, there;
I am doing my first SSIS project that moves data from dbf file to SQL2005.
What I did here is that I created a dataflow task (move data)with ASP.NET successfully (Very happy!). Now I want to create an ExecuteSQLTask that runs before data flow runs. This ExecuteSQLTask is used used to create a table if there is no destination table in the SQL2005.
This can be done easily in SSIS design mode, just need to connect those two box with a green line: )
I had a look example from MS website, but I didn't find a code example to connect this two task.
Does anybody know how to resolve this or have any link with sample code?

Big thanks here.

Not sure what the issue is. Just drag the green line to the data flow task.|||

I am creating SSIS package from C# manully, not using the SQL Server Business Intelligence Development Studio at all. So I'd like to know how to connect these two task using C# code.

Thanks.

|||

Try this:

.....

//Create the package
Package pkg = new Package();

//Add the tasks
TaskHost sqlTask = (TaskHost)pkg.Executables.Add("STOCK:SQLTask");
TaskHost pipelinetask = (TaskHost)pkg.Executables.Add("STOCK:PipelineTask");

//Add the PrecedenceConstraint
PrecedenceConstraint pc = pkg.PrecedenceConstraints.Add(sqltask, pipelinetask);
pc.Value = DTSExecResult.Success;

.....

HTH,
Ovidiu Burlacu

How to compose the connection String of a SSIS package that execute another package?

Dear All,

I now have two SSIS package, "TESTING" and "LOADING". The "TESTING" package have an execute package task that call the "LOADING" package. When I want to execute the TESTING package, how can I setup the connection string so that I can edit the password of the database connected by the "LOADING" package?

Regards,

Strike

You should use configurations to achieve this. There's plenty of material out there about SSIS configurations if you google it.

-Jamie

|||How about if I want to create a command file so that I can input the password externally in the command prompt?|||

You can pass values into a package via the command-line using the /SET option of dtexec.

If the package that you call is a parent package then you can pass the value through to the child using a parent package configuration.

-Jamie

|||

Hi Jamie,

I would like to know how can I setup the command line in the SQL Server Job Agent for executing the two packages mentioned above? I have already configured the parent variable for the child package. However, I have encountered some errors when executing the job, and I don't know how to set up the path for the child package in the command line as both of the packages are loaded from the SQL server, not from the file system.

Thank you very much!!

Strike Wong

|||For help building command lines, try DTExecUI.exe. Set the options you want, and then look at the Command Line page. You can copy the command from there. Obviously you can execute the package from there as well to check the validity of the command you have built.|||

Is this included in the MS SQL Server 2005?
I can't found it in the directories

|||

Yes -

How to: Run a Package Using the DTExecUI Utility
(http://msdn2.microsoft.com/en-us/library/d827d7b2-2eb6-4f4a-a775-ab00c444c0dd.aspx)

It should be available from a command prompt or the Run dialog, as the Bin folder were it lives should be on your PATH, set as part of the SQL 2005 install.

How to choose one of two success-paths?

I have an ActiveX Script Task in SQL Server 2000. It chooses one of two
possible success-paths depending on if a file exist or not. (Part of the old ActiveX Script for choosing next step is below)

I need to rewrite this for a Script Task in SQL Server 2005 but it seems like it
doesn't have this functionality and objects.

Does anyone know how to write the code for choosing the next step in a
Script Task or knows another way to solve my problem?

The package is not suppose to fail if the file is missing, it's must succeed and that's why I need two success-paths.

Regards,
Sara

'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main ()

Dim pkg
Dim stpContinuePkg
Dim stpExit

SET pkg = DTSGlobalVariables.Parent

SET stpContinuePkg = pkg.Steps("DTSStep_DTSActiveScriptTask_4")
SET stpExit = pkg.Steps("DTSStep_DTSActiveScriptTask_21")

If ...... Then
Main = DTSStepScriptResult_ExecuteTask
stpContinuePkg.DisableStep = False
stpExit.DisableStep = True
Else
.......
End If

Main = DTSTaskExecResult_Success

End Function

What you are talking about is conditional execution. You're right that you have to write script to do this in DTS but it is much easier in SSIS because you can put conditions on the precedence constraints.

All explained here: http://www.sqlis.com/default.aspx?306

-Jamie

|||

Thanks a lot for your answer!

Regards,
Sara