Create Output Which Just Reflects Latest Conditions When Joining Back To Contract

Oct 10, 2013

col 1 is the pk for this table called Conditions - this table is related to contract.

Col 1 - PKContract TypeType ID
973300 711917 C30
973301 711917 C32
973302 711917 C31
1152323 711917 C30
1152324 711917 C31
1152325 711917 C32

A contract can many conditions so 1:M...Col 2 is the contract reference and the linking join to contract

Now - a condition related to an contract can have many re-trys and the causes the pk to increment. As you can see there are three conditions related to first attempt and then another three conditions.I want to create an ouput which just reflects the latest conditions when joining back to contract - Is this possible?I have requested to application providers to provide flag, but this will take some time...

View 8 Replies


ADVERTISEMENT

Conditions On Latest Record

May 18, 2006

I have a table that has records layed out as so:Table:fd_Id INT IDENTITY (1, 1)fd_User VARCHAR(30)fd_Effective DATETIMEData could be as follows:1 | "user1" | 6/20/20012 | "user2" | 6/1/20023 | "user2" | 6/5/20024 | "user2" | 6/5/20025 | "user2" | 2/1/20026 | "user3" | 9/1/20037 | "user3" | 10/2/20028 | "user4" | 1/1/2005What I need to retrieve from that table is the SINGLE LATEST item ofeach fd_User.Results:1 | "user1" | 6/20/20013 | "user2" | 6/5/2002 (or 4 | "user2" | 6/5/2002) since the dates arethe same but only 1 of them6 | "user3" | 9/1/20038 | "user4" | 1/1/2005

View 3 Replies View Related

Joining Multiple Tables With Conditions?

Mar 19, 2008



Hi,
I have a tbl called BANK.

This shows a banking history of transactions and includes a field called TransType and a field called PaymentID.


I also have two other tables called Suppliers and SubContractors.

For each record in the bank, I need to match up a record in either the suppliers or subcontractors tbl based on the PaymentID value. I know if the record relates to either a Supplier or Subcontractor based on the value of the TransType field which will be either SUPPLIER or SUBCONTRACTOR or Null (in which case a match doesn't matter)


I have a working query based on joining just the Supplier tbl.. but how do I do the join to the other tbl aswell?


So overall, for each record in the bank, if the transtype is SUPPLIER I need to look in the supplier tbl for a match for that paymentID, and if the transtype is SUBCONTRACTOR, I need to do the same but in SUBCONTRACTOR tbl.

How would I best write that?

thanks for any help!

View 5 Replies View Related

Alter Service &&<SVC1&&> (Add Contract &&<CONTRACT&&>) Does Not Really Work, Need Help

Aug 20, 2007

Hi,

Sure you can run


Create Service SVC1 ON QUEUE QUEUE1 (CONTRACT1);

alter service SVC1 (add contract CONTRACT2);

But You can only send message using contract1, when you try to send msg using contract2, it always say can not find CONTRACT "contract2".

The following query shows the contract is there.

select s.*,c.* from sys.service_contract_usages U

inner join sys.services S on S.service_id = U.service_id

inner join sys.service_contracts C on U.service_contract_id=C.service_contract_id

where S.name='SVC1'



declare @lMsg xml

declare @ConversationHandle uniqueidentifier

set @lMsg = '<test>testing</test>'

Begin Transaction

Begin Dialog @ConversationHandle

From Service SVC1

To Service 'SVC2'

On Contract contract1

WITH Encryption=off;

SEND

ON CONVERSATION @ConversationHandle

Message Type [type1]

Commit

The above works, but the following will not work.


declare @lMsg xml

declare @ConversationHandle uniqueidentifier

set @lMsg = '<test>testing</test>'

Begin Transaction

Begin Dialog @ConversationHandle

From Service SVC1

To Service 'SVC2'

On Contract CONTRACT2

WITH Encryption=off;

SEND

ON CONVERSATION @ConversationHandle

Message Type [type2]

(@lMsg)

Commit

Any idea ?

Thanks!

View 6 Replies View Related

T-SQL (SS2K8) :: Joining Two Tables - Output Result In The Order By DivID

Mar 25, 2014

I have two tables to be joined

Doc:
ID, DivID

Division:
ID, Div
CREATE TABLE [dbo].[Doc](
[ID] [int] NULL,
[DivID] [int] NULL

[Code] ...

As you can see, some Divisions have no correspondents in Doc, I want to show the count(1) result as 0 for those Division, and output the result in the order by DivID

View 5 Replies View Related

Create A View To Get Latest Status For Each Application

Apr 24, 2007

I would like some help creating a view that will display the latest status for each application. The lastest status should be based on CreateDt. 
For example:
Table Structure:
============
Application: ApplicationID, Name, Address, City, State, Zip, etc..
ApplicationAction: ApplicationActionID, ApplicationID, Status (Ex:new, reviewed, approved, closed), CreateDt 
  
View should display:
==============
ApplicantID, ApplicantActionID, Status, CreateDt  
 
Example:
==========
ApplicantID=4, Name=Bob Smith, etc....
ApplicantActionID=1,  ApplicantID=4, Status=New,  CreatDt=1/3/20071:00
ApplicantActionID=2,  ApplicantID=4, Status=Reviewed,  CreatDt=1/3/2007 2:00
ApplicantActionID=3,  ApplicantID=4, Status=Approved,  CreatDt=1/4/2007 1:00
 .... etc....
View should return:
Applicant=4, ApplicantActionID=3, Status=Approved,  CreatDt=1/4/2007 1:00
etc....
 
 
 
 

View 4 Replies View Related

Reporting Services :: How To Create 2 Conditions From 2 Datasets In Row Visibility SSRS

Apr 29, 2015

In ssrs, I want to create an expression for the row visibility. But, the expression will contain 2 conditions from different 2 datasets (DealStarts & RowofTrendingVisibility). I have applied a solution from online, but got an error message is 

"The Visibility.Hidden expression for the tablix ‘Tablix9’ contains an error: [BC30451] Name 'launchdate' is not declared. "

I think that there is a minor issue in my syntax.

=iif ((Last(MonthName("DealStarts"))=monthname(month(today())) or launchdate ("RowofTrendingVisibility")<Parameters!StartDate.Value),true, false)

View 3 Replies View Related

How To Create Merge Replication With Latest Update Always From Subscribers

May 13, 2006

Hi ,

I am trying to create Replication Topology (Merge Replication) like below.

Subscriber1 --> Publisher <-- Subscriber2.

I have created both subscribers with Subscription Type as Server with Priority as 75. I am updating the Column A of Row_10 in Subscriber1 on time say 11 am. After i am running the Starting synchronizing agent from Subscriber1. The value propagated to Publisher now publisher contains the latest value in Column A. Uptonow the Subscriber2 is not synchronized with Publisher.

Now in Subscriber2 also Column A of Row_10 is updated say at 11.10 am. Actually now Publisher contains the value from Subscriber1 for that Column and in Subscriber2 we have the same column updated.

Now i am running the Synchronization in Subscriber2, i am getting the result which is not expected. Here Publisher's value is propagated to Subscriber2. But as per real scenario Subscriber2 has the Latest value which is updated on 11.10 AM.

I don't know what am i missing here. Actually merge replication should see the time stamp and it has to decide winner. But here it always considers publisher as a winner and puts the data to Subscriber.

Can anyone help ?

Thanks in advance

View 4 Replies View Related

Getting The Value That Was Just Inserted Passed Back Output Parameter

Mar 1, 2007

I have a SP that inserts data into a table with an identity column I want to get that id and use it in my response.redirect however I keep getting 0 back instead of  the identity. Relevent Sql 2005 Declare @NEWEMPID int --after an insert happens in sqlSet @NEWEMPID= SCOPE_IDENTITY()--last thing in my sqlReturn @NEWEMPID Or I tried NEWEMPID as an output parameter VB.Net  Dim retValParam As New SqlParameter("@NEWEMPID", SqlDbType.Int) retValParam.Direction = ParameterDirection.ReturnValue'execute the sqldatasource insert command
sqlEmpNewEmp.Insert()
Dim EmpIDVar As Integer = Convert.ToInt32(retValParam.Value) Response.Redirect("http://www.mysite.com?EMPID=" + Server.HtmlEncode(EmpIDVar)) sqlEmpNewEmp.InsertParameters.Clear() 

View 8 Replies View Related

Transact SQL :: Create A Report Which Would Give Latest Transaction On Database

Jul 17, 2015

I'm trying to create a report which would give the latest transaction on a database, which all sit on different servers. I wanted to know if there is an a simple way tracking the latest transactions instead of getting the information from the database tables.

View 3 Replies View Related

How To Call A Stored Procedure On MS Sqlserver From Web Forms And Get Back Output

Apr 7, 2006

Hi,
How to call a stored procedure on MS Sqlserver from web forms and get back output
Thanx,
Nishu

View 1 Replies View Related

Joining Two Dropdown Lists To Create Third Dropdownlist

Oct 23, 2007

How do i add two values of Dropdownlist 1 and 2 to create No.3
Is there an easy way through Visual Studio to do this than Realms of code that i have been reading online,
 Surely, there is some command that is like Select Distinct Name from Clients WHERE city=@city + country=@country
 Or is it not this easy?

View 2 Replies View Related

JOIN Efficiency Using Multiple ON Conditions Versus WHERE Conditions

Jan 10, 2008

My question is fairly simple. When I join between two tables, I always use the ON syntax. For example:


SELECT

*
FROM

Users

JOIN UserRoles

ON (Users.UserRoleId = UserRoles.UserRoleId)


No problems there. However, if I then decide to further filter the selection based on some trait of the UserRole, I have two options: I can add the condition as a WHERE statement, or I can add the condition within the ON block.

--Version 1:

SELECT

*
FROM

Users

JOIN UserRoles

ON (Users.UserRoleId = UserRoles.UserRoleId)
WHERE

UserRoles.Active = 'TRUE'


-- Version 2

SELECT

*
FROM

Users

JOIN UserRoles

ON (Users.UserRoleId = UserRoles.UserRoleId

AND UserRoles.Active = 'TRUE')


So, the question is, which is faster/better, if either? The Query Analyzer shows the two queries have the exact same execution plan, which makes sense, since they're both joining the same tables. However, I'm wondering if adding the condition in the ON statement results in fewer rows the JOIN statement initially needs to join up, thus reducing the overall initial size of the results table before the WHERE conditions are applied.

So is there a difference, performance wise? I imagine that if Users had a thousand records, and UserRoles had 10 records, then the JOIN would create a cartesian product of the two tables, resulting in 10,000 records in the table before the WHERE conditions are applied. However, if only three of the UserRoles is set to Active, would that mean that the resulting table, before applying WHERE conditions, would only contain 3000 records?

Thanks for whatever information you can provide.

View 7 Replies View Related

Can Anyone Offer Me Any Suggestions On How To Create A Back Up Site W/ Db In The Even

Mar 23, 2004

I am currently running a site on a shared environment with sql db. The account is with verio.com: Windows Server 2003m SQL 2000.

What we would like to do is create a back up site with a backup db. I would like this functionality so that in the event of a crash, while the current site is being repaired, or if the server is down for a prolonged period, I can simply switch to the backup site w/ db.

What would the preferable method of doing this in such an environment? Any help would be greatly appreciated. Thanks.

View 4 Replies View Related

Sql Server DBA Contract

Apr 16, 2008

Hi,

can someone please advise me, that if i were to do those video tutorials on sql server 2000, 2005 on:

http://www.computer-training-software.com/data.htm

http://cgi.ebay.co.uk/MCSE-MCSA-2003-DVD-VIDEO-SUPERB-10-DVDS_W0QQitemZ230242309797QQihZ013QQcategoryZ41865QQssPageNameZWDVWQQrdZ1QQcmdZViewItem


Will i be able to do a contract which requires about 2 years experience?

View 6 Replies View Related

DBA Contract Without Local Admin Priveleges

Apr 30, 2007

O what a place to be I started this Contract as an (Interim) for a new DBA role, for an application support Company last month & all was going well. The User Application is run via Citrix against multiple Hosted Sybase ASA Databases. I introduced SQL 2005 with Reporting Services as a mixed Data Mart Remote Query via ODBC Linked Servers setup. Because they had never had a DBA before the Data I was able to pull from over thirty seperate databases into one and present via Reporting Services has blown them away. And then one day the Senior Support Analyst told me he had put the main most important Sybase Database on a completely seperate domain he had created(with no Trust between the two) , because he was unable to secure the existing domain against unauhorized remote internet intrusion & Viruses. (I never liked the idea that hosted customers were domainusers on the Corporate network) To add insult to injury he then told me to install & maintain another SQL Box on the new domain, OK so far. I logged into the supoposed new box via citrix & then remote desktop, and to my disbelief he had the desktop locked down - no access to control panel or anything - he asked me why i needed access - I told him - he asked me why I need to have reboot priveleges - I told him. So now he's installed 2005 himself in the vain hope I can work without Local Admin privelages or need to unlock the Desktop - he certainly won't give me Domain Admin. I Just cannot believe I'm unable to persuade him to Unlock the Desktop & have even threatened to walk out unless he lets me do my Job. He probably does'nt like me but there can be absolutely no question about my abilities or accessing data that i should'nt. He's basically read a Deny by Default article and expects me to start of as a user with a locked desktop and then request & justify escalating my security from there. Is this possible ? Good Grief :eek: Any ideas what I should do ? Thanks GW

View 12 Replies View Related

Receiving A Message With A Specific Contract

Jun 28, 2006

If the service is defined with multiple contracts is there a way to receive a message with a specific contract?

View 5 Replies View Related

Create Table From Query Output

May 5, 2008

I have a query that is spliting up FullName into FistName and LastName columns. Need help writing a query that dump that output into a new table.

Thanks
Kevin

View 6 Replies View Related

Create Table From Query Output

Oct 25, 2013

I have the following sql query in vb.net and ms access , how do I create a table from the query result ?

SELECT 'Table1' AS [Table], SUM(a) - SUM(b) AS Result FROM table1

I have tried but it does not work

create table tble10 as SELECT 'Table1' AS [Table], SUM(a) - SUM(b) AS Result FROM table1

View 14 Replies View Related

Create Procedure Output Ntext

Oct 24, 2005

Hello,I need to produce with T-SQL a user defined function or storedprocedure that make one SLQ-Statement and prepare as string from theresult set.The request muss be able to return a very long unicode string. Thereturn value nvarchar is being truncated so I'm trying to create astored procedure, that returns a ntext string.I can't manage it (I am no T-SQL specialist). Maybe someone can helpme?Thanks for your help.-----Here is my sp:alter procedure F_FUNCTION (@userid int, @parentid int, @status int,@return ntext output)ASBEGINDECLARE @onelevel nvarchar(4000)DECLARE @pos varchar(1000)DECLARE @leveldone varchar(100)DECLARE @levelplaned varchar(100)DECLARE @planeddate nvarchar(4000)DECLARE @elementid varchar(10)DECLARE @levelid varchar(10)DECLARE @levelstatus varchar(10)DECLARE @levelupd nvarchar(4000)DECLARE @levelauthor varchar(10)DECLARE @prevelementid varchar(10)BEGINdeclare level_cursor CURSOR FORSELECTB.ElementPos,B.LevelID,A.LevelDone,A.LevelPlaned,A .PlanedDate,A.MatrixContentID,A.Status,convert(varchar,A.Upd,126) as Upd,A.AuthorFROM T_TABLE1 as B left outer join T_TABLE2 as A on(A.MatrixContentID=B.ID AND A.UserID=@userid AND A.Status<>3)where B.ParentID=@parentidORDER BY B.ElementID,B.ElementPosENDset @onelevel=''OPEN level_cursorFETCH NEXT FROM level_cursorINTO @pos,@levelid,@leveldone, @levelplaned,@planeddate,@elementid, @levelstatus,@levelupd,@levelauthorWHILE @@FETCH_STATUS = 0BEGINset @prevelementid=@elementidif (@pos IS NULL)set @onelevel=''elseset @onelevel=@posif (@elementid IS NULL)set @onelevel=@onelevel+'*-*'elseset @onelevel=@onelevel+'*-*'+@elementidif (@levelid IS NULL)set @onelevel=@onelevel+'*-*'elseset @onelevel=@onelevel+'*-*'+@levelidif (@leveldone IS NULL)set @onelevel=@onelevel+'*-*'elseset @onelevel=@onelevel+'*-*'+@leveldoneif (@levelplaned IS NULL)set @onelevel=@onelevel+'*-*'elseset @onelevel=@onelevel+'*-*'+@levelplanedif (@planeddate IS NULL)set @onelevel=@onelevel+'*-*'elseset @onelevel=@onelevel+'*-*'+@planeddateif (@levelstatus IS NULL)set @onelevel=@onelevel+'*-*'elseset @onelevel=@onelevel+'*-*'+@levelstatusif (@levelupd IS NULL)set @onelevel=@onelevel+'*-*'elseset @onelevel=@onelevel+'*-*'+@levelupdif (@levelauthor IS NULL)set @onelevel=@onelevel+'*-*'elseset @onelevel=@onelevel+'*-*'+@levelauthor-- Part Outputprint @onelevelif (@return is NULL)exec(@return+@onelevel)elseexec(@return+'*;*'+@onelevel)FETCH NEXT FROM level_cursorINTO @pos,@levelid, @leveldone, @levelplaned,@planeddate,@elementid,@levelstatus,@levelupd,@levelauthorif (@prevelementid IS NOT NULL AND @prevelementid=@elementid)FETCH NEXT FROM level_cursorINTO @pos,@levelid, @leveldone, @levelplaned,@planeddate,@elementid,@levelstatus,@levelupd,@levelauthorENDCLOSE level_cursorDEALLOCATE level_cursorRETURNEND-----Call of the function with:exec dbo.F_FUNCTION 550,1632, 0, ''Here the beginning of the query analyser output:1.00000*-*691*-*1684*-*3*-*0*-**-*0*-*2005-09-22T00:43:00*-*277Server: Msg 170, Level 15, State 1, Line 1Line 1: Incorrect syntax near '*'.-----

View 6 Replies View Related

Populating Sales Figures By Contract Length

Jan 9, 2014

Aim – Take the “One Month Figures”, look at the revenue start date, and calculate the sales by the “Contract Length”, broken down by the year in the “Revenue_State_Date”.

For Example

As the Revenue_State_Date starts on 01/01/2015, you have 12months of full trading in 2015, so you need to calculate 12 months of sales,

This needs to be spread out over the contract length

For eg
YearSales Months
2015£2,914,033.3312
2016£2,914,033.3312
2017£2,914,033.3312
2018£2,914,033.3312
2018£1,457,016.676
Total£13,113,150.0054

My query is

SELECT
[Total_Contract_Value__c] as TCV,
[Contract_Term__c] As Contract_Length,
[Revenue_Start_Date__c] as Revenue_State_Date,
[Total_Contract_Value__c]/[Contract_Term__c] as One_Month_Sales
FROM [FDMS].[Dan].[Raz_Reporting_LCS]
where ID = '006a0000015qcGsAAI'

Which produces following results

TCV £13,113,150.00
Contract_Length 54
Revenue_State_Date01/01/2015
One Month Figures £242,836.11

View 3 Replies View Related

Calculate Contract Days In Financial Year

Feb 4, 2015

I have a table with 3 fields: Contract_No, Start_Date, End_Date. I need to calculate how many of the days between the Start and End dates fall within each financial year (FY) beginning 1st April and ending 31st March.

So for example if the Contract Start_Date is 26/01/2012 and the End_Date is 20/05/2012 or is null then the number of days for 2012 FY is 49 (counting from 01/04/2012 to 20/05/2012).

If the End_Date is null for the same contract, then the number of days for 2012 FY would be 365.

Since the contract period from Start_Date to End_Date might span more than one or even several FY I need to be able to show this in columns seperately for each FY. What is the best way of achieving this?

View 6 Replies View Related

Is It Ok To Post A SSIS Contract Vacancy On This Forum?

Jun 30, 2006

Hi,

I wonder if anyone could tell me whether it is acceptable to post a SSIS contract vacancy on this forum?

If not, where would be the best forum/site to find UK-based SSIS developer contractors?

Many thanks,

Gavin.

View 5 Replies View Related

How To 'create New/overwrite Existing' Worksheets In Xls Output?

Jul 12, 2007

anyone know how you can do this?

View 3 Replies View Related

Contract And Queue Confusion If Named The Same Plus Positioning Issue.............

Nov 17, 2006

Hello,

The problem I was having regarding re-enable a queue was because it was in another database. I am guessing it did that because the contract and Queues have the same name. I double checked and nothing pointed to that database. I fixed it because I can re-enable the queue in the other database and it is fixed. My question is why did this happen? The other issue I am still having is regarding why the queue got disabled to begin with. I understand that it was because of positioning but do not understand how or why and how to provent it from being disabled.

Thanks,

Scott Allison...

View 1 Replies View Related

Server Broker Contract Talking To Multiple Computers

Nov 16, 2006

Hello,

I solved the previous issue I had and now need to find out how to make it so I have Service Broker running on one machine and the services running on another machine. I mean you can set a connection string to post a message to the service broker but then it will need to respond and the connection string in the method called does not allow you to put a server in the connection string. The other issue is because the stored procedure calls the method in your code based on a namespace and then a method but does not specify a server. I am sure a way exists but am not familiar with it. This is the last piece of the architecture I need to work out before I finish designing the system.

Thanks,
Scott Allison...

View 3 Replies View Related

Transact SQL :: Get A List Of All Contracts Equal Or After To Their Maximum Contract

Jul 7, 2015

I have two tables and a CTE table.

-Shop : list of all shops with an ID_SHOP
-Contract : list of all contract with a FK to the shop FK_SHOP. A shop can have multiple contracts

I have a cte where I calculate for each FK_SHOP the maximum contract start date. I now want to join my shop table with the contract table but only for the contracts >= their maximum contract start date.

WITH cteContracts AS
(
SELECT
FK_SHOP
,ID_CONTRACT
,MAX(cont.DTT_START_DATE) DT_STARTDATE

[Code] ....

View 3 Replies View Related

SSIS Read Excel Rows And Create New Output

Feb 1, 2013

I have an excel file with following data:

Agent State Exposure Insured Name
Rogers Inc MA 100,000 John Smith
SAN Group RI 200,000 Jim Morrison
SAN Group RI 100,000 Jimi Hendrix
123 Agency MA 300,000 Mickey Mouse
Rogers Inc MA 50,000 Mike Greenwell

I want to be able to read the file and create new excel files for each Agent listed. So for Example, the above file would create 3 separate files since there are 3 different Agents listed. Each Agent file would contain the same information from the original file. The name of the file would be somethign like AgentName.xls...So the SAN group file would have this:

Agent State Exposure Insured Name
Rogers Inc MA 100,000 John Smith
SAN Group RI 200,000 Jim Morrison
SAN Group RI 100,000 Jimi Hendrix

Is there a way to accomplish this in SSIS?

View 2 Replies View Related

SQL Server 2012 :: Create A Function That Take A Value And Run Some Logic And Output The Value?

Feb 20, 2015

I would like to create a function that take a value and run some logic and output the value

I have a table like this

Table A
value
*
001
004.00
3.0
1.22

Logic I want to run is

The value that you are passing is numeric and numeric with only decimal 0 value, and then convert it to integer otherwise leave as it is

So if I run a query something like this

Select value, fn_convertointerger(value) as converted_value from TableA

I will get

Value converted_value
* *
001 1
004.00 4
3.0 3
1.22 1.22
2.02 2.02
4.000 4
Jkil& Jkil&

How can I create a function like this to convert specific numeric value?

View 9 Replies View Related

When To Create Columns And Metadata For Custom Asynchronous Component Output

Apr 17, 2006

I'm having a tad bit of trouble getting output from an asynchronous component that I've written and am looking for some insight.

This component takes in a name string passed from upstream and parses the name components into standardized output fields. I'm using an asynchronous component because if the name string contains two names ("Fred & Wilma Flintstone") I'm outputting one row for Fred and one for Wilma. I've gotten it to run and with debugging have observed what appeared to me to be proper execution, but zero rows are flowing out of it.

In my ProvideComponentProperties method, I add the three fields and there associated metadata to the OutputColumnCollection. Is this method where this should occur? It's before the PrimeOutput method, so I didn't know if I should be creating the output columns in ProcessInput (i.e., after the output buffer is provided by PrimeOutput.)

In ProcessInput, I'm using AddRow for each input row and another if it contains a second name, setting the value for each index using the buffer's SetString method, to no avail. I can observe it to this point, but then don't know what's in that output buffer (if I'm using the wrong buffer index value, etc)

Thanks.

View 3 Replies View Related

Create Output Columns Based On Input In Custom Component

Aug 28, 2007



I'm trying to create a fairly simple custom transform component (because I've read that's the easiest one to create) which will take one column from a flat file source and based on the first row create the output columns.
I'm actually trying to write a component that will solve the now well known problem with parsing CSV files in SSIS. I have a lot of source files and all have many columns so a component that can read in the first line from the CSV file and create the output columns automatically will save me lots of time when migrating the old DTS packages.

I have the basic component set up but I'm stuck when trying to override the OnInputPathAttached method because I don't know how to use the inputID to get the first line from the input (the buffer).
Are there any good examples for creating output columns dynamically based on the input buffer?
Should I just give up on on the transform and create a custom source component instead?

View 5 Replies View Related

Create Output File In A Specific Layout - Extra Spaces Between Fields

Mar 17, 2014

I'm trying to create an output file in a specific layout. For some reason my output file is adding an extra 10 spaces between the Account Number and the Check Number in the statement below. The rest of the output file looks fine. Where the extra 10 spaces are coming from? I need 1 Filler Space between these fields.

SELECT DISTINCT
CASE p.PaymentMethodID WHEN 10 THEN 'I' WHEN 60 THEN 'V' WHEN 50 THEN 'S' ELSE 'I' END
+ CONVERT(CHAR(1), '')
+ (REPLICATE('0', 20 - LEN(ba.AccountNumber))+ CONVERT(CHAR(20), ba.AccountNumber))
+ CONVERT(CHAR(1), '')
+ (REPLICATE('0', 18 - LEN(p.CheckNumber)) + CONVERT(VARCHAR(18), p.CheckNumber))

[Code] .....

View 2 Replies View Related

Transact SQL :: Create Job Which Automatically Convert Output Into Excel And Send Mail

Oct 31, 2015

I have an existing MS SQL database (2008 R2). I have a very simple SQL script. I need to automate this script means wants to create a job which runs on a Friday basis and save the output results of the query as a excel file and then automatically sends the mail to everyone.

View 9 Replies View Related







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