I need to transform Foxpro table to SQL Server table with merging all rows into one where all column values are the same except one . For this the only column with the different values , I want them also to be merged as coma or space delimited string. The question whether SSIS is a good candidate for this kind of data munging and also would be interested to know knowing as many as possible ways of doing that. Surely I may produce Foxpro script in 5 minutes which wil do that and be a pre-processor action before SSIS starts.
I have a problem with a Merge Join providing no output (when it should have 1890 rows). My Data Flow Task has 4 OLE Data Sources, 3 Multicasts, and 1 OLE Data Destination. I am experiencing the problem near the end of my data flow where two Multicasts create two parallel flows of data (see Level 1 below). I have two Merge Joins which join one leg from each multicast with a leg from the other multicast (see Level 2 below). Then the two remaining legs use a Merge to get my destination output (see Level 3 below).
I am experiencing my problem with the Merge Join (input A2, B2) --> (output C2) transformation. The Merge Join providing output C1 appropriately outputs 1890 rows, but C2 outputs 0 rows. Both Merge Joins are identical. The data is identically sorted prior to entering the problematic Merge Join and a DataViewer (Grid) verified that the data is appropriately entering in. Merge Join (input A2, B2) --> (output C2) has 667 rows as input A2 and 1890 rows as input B2 (using an inner join, just like the other merge join), but C2 baffles me with 0 rows of output (when it too should have 1890). I receive no Ouput errors and the execution completes showing all green.
I read about mysterious behavior with Merge Joins and have attempted modifying my EngineThreads property to values between 2 and 10, with no luck. Any help/ideas would be appreciated.
How to summarise the data in this table to a single row output per site (2 records for every SiteID). I am based in the UK so the data copied from SQL is in the US format. I can convert this to UK date without any issues.
CREATE TABLE [dbo].[MRMReadings]( [SiteIncomeID] [int] IDENTITY(1,1) NOT NULL, [SiteID] [nchar](5) NOT NULL,
[Code] ....
Is it possible to return the data in the following format merging 2 lines of data into one output:
I created a package that seems to work fine with a small amount of data. When I run the package however with more data (as in production) the merge join output is limites to 9963 rows, no matter if I change the number of input rows.
Situation as follows.
The package has 2 OLE DB Sources, in which SQL-statements have been defined in order to retrieve the data.
The flow of source 1 is: retrieving source data -> trimming (non-key) columns -> sorting on the key-columns.
The flow of source 2 is: retrieving source data -> deriving 2 new columns -> aggregating the data to the level of source 1 -> sorting on the key columns.
Then both flows are merged and other steps are performed.
If I test with just a couple of rows it works fine. But when I change the where-clause in the data source retrieval, so that the number of rows is for instance 15000 or 150000 the number of rows after the merge join is 9963.
When I run the package in debug-mode the step is colored green, nevertheless an error is displayed:
Error: 0xC0047022 at Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Merge Join" (4703) failed with error code 0xC0047020. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
To be honest, a few more errormessages appear, but they don't seem related to this issue. The package stops running after some 6000 rows have been written to the destination.
I am using SSIS in SQL Server Enterprise 2005. I have two OLE DB data sources from two disparate databases (IBM DB2 and Microsoft SQL Server), some columns from each of which are to be included in the merged output results. I have noted the various requirements in the forum postings with regard to sorting the OLE DB sources and specifying the output source columns as being sorted, as well as the requirement that the join fields in the two sources be close/exact matches. Yet, when I run this in VS, while the work area reflects the expected number of rows being input into the Merge Join transformation, no count is reflected as output from that transformation into the final destination table.Specifically, my two data sources (IBM DB2 and MS SQL) are configured as follows:
IBM DB2 contains an SQL statement that uses Cast operations to create the result columns.and an ORDER BY clause to ensure that the output is sorted by the desired two columns.. The OLE DB source property setting for IsSorted is set to true; the Output Columns folder column definitions for "key_ source_dtsy" and "key_source_dtrt" have their SortKeyPosition properties set to 1 and 2, respectively. Those field are both defined as data type DT_STR, with lengths of 4 and 2, respectively. Below is the Path metadata from the Data Flow Path editor from the path from this source:
IBM DB2 source"Name" "Data Type" "Precision" "Scale" "Length" "Code Page" "Sort Key Position" "Comparison Flags" "Source Component""ID_CODE" "DT_STR" "0" "0" "10" "1252" "0" "" "Source F0005 User Defined Codes""CODE_DESCR_1" "DT_STR" "0" "0" "30" "1252" "0" "" "Source F0005 User Defined Codes""CODE_DESCR_2" "DT_STR" "0" "0" "30" "1252" "0" "" "Source F0005 User Defined Codes""key_source_dtsy" "DT_STR" "0" "0" "4" "1252" "1" "" "Source F0005 User Defined Codes""key_source_dtrt" "DT_STR" "0" "0" "2" "1252" "2" "" "Source F0005
User Defined Codes:
MS SQL contains an SQL statement that takes the columns as they are in the MS SQL table (no Cast operations needed); it also uses an ORDER BY clause to ensure the output is sorted by the join columns. The OLE DB source property setting for IsSorted is set to true; the Output Columns folder columns for "key_source_dtsy" and "key_source_dtrt" have their SortKeyPosition properties set to 1 and 2, respectively. Those field are both defined as data type DT_STR, with lengths of 4 and 2, respectively. Below is the Path metadata from the Data Flow Path editor from the path from this source:
MS SQL source"Name" "Data Type" "Precision" "Scale" "Length" "Code Page" "Sort Key Position" "Comparison Flags" "Source Component""id_code_name" "DT_I2" "0" "0" "0" "0" "0" "" "Source CodeName in db dwVdFY""key_source_dtsy" "DT_STR" "0" "0" "4" "1252" "1" "" "Source CodeName in db dwVdFY""key_source_dtrt" "DT_STR" "0" "0" "2" "1252" "2" "" "Source CodeName in db dwVdFY"
The Merge Join transformation specifies an INNER JOIN using the columns named "key_source_dtsy" and "key_source_dtrt" from the respective data sources.I know there are alternative ways of accomplishing my intent (Lookup, port MS SQL table to IBM DB2 so join can occur in SELECT statement, etc.; however, I'd like to use this functionality and assume that it should work.
I want to create a stored procedure that will merge columns from tworows that contain duplicated contacts.I have can easily identify the duplicates and extract the UniqueIDs asparameters, but I can't figure out how to construct the actual updateSQL.@KeeperID int,@DupeID intUpdate ContactsSETa.Info1 = LEFT(TRIM(IsNull(a.info1,'') + ' ' IsNull(b.Info1,''))255),a.Info2 = LEFT(TRIM(IsNull(a.info2,'') + ' ' IsNull(b.Info2,'')),255),etc, etc...FROM(here's what I can't figure out)Contacts a ID = @KeeperIDContacts b ID = @DupeID
Hello,I ended up with two identical rows in one table. They should have differences but I cannot update one, as it tries to update both of them, or throws an error. How to update only one row, and leave other as is?
I'm using a query to see how many times an action was recorded on a person. The query works, it returns this:
John Smith 1 John Smith 1 John Smith 1 Jane Doh 1 Jane Doh 1 Al Johnson 1
but I need it to return totals like this
John Smith 3 Jane Doh 2 Al Johnson 1
This is the query I am using:
Select Player.First_Name, Player.Last_Name, COUNT(Action.Employee_ID) from Player INNER JOIN PlayerVisit on PlayerVisit.Player_ID = Player.Player_ID join Treatment on Treatment.Visit_ID = PlayerVisit.Visit_ID join Action on Treatment.Action_ID = Action.Action_ID group by Player.First_Name, Player.Last_Name, Action.Employee_Id;
I have a table which is updated daily using a MERGE statement. As records are insert, updated and deleted, I am saving the OUTPUT from the MERGE statement into a history table with a timestamp and action$ column appended to the record.
Using this history table, I'd like to rebuild the data based on specific past date. I was able to create a stored procedure that inspects each record in the history table and apply it to the data in a temp table. The stored procedure solution uses multiple queries to rebuild the data at a point in time. I was curious if there was an easier and more efficient solution using a table function.
I have an "insert into" statement that creates two identical rows in a table, with this statement: delete from [table] where [column] = @parameterINSERT INTO [table]([fields]) VALUES ([parameter values]) This is the code-behind that performs the insert: Dim dbConn As New SqlConnection(strConn)Dim cmd As New SqlCommand("sp_CreateUser", dbConn)cmd.CommandType = Data.CommandType.StoredProcedurecmd.Parameters.AddWithValue("@UserID", strUserID)cmd.Parameters.AddWithValue("@UserName", strUserName)cmd.Parameters.AddWithValue("@Email", strEmail)cmd.Parameters.AddWithValue("@FirstName", strFirstName)cmd.Parameters.AddWithValue("@LastName", strLastName)cmd.Parameters.AddWithValue("@Teacher", strTeacher)cmd.Parameters.AddWithValue("@GradYr", lngGradYr)Using dbConndbConn.Open()cmd.ExecuteNonQuery()dbConn.Close()cmd.Dispose()dbConn.Dispose()End Using I wonder if it inserts twice due to a postback issue. Is there a way to stop two rows from being created in the first place with the same "insert into" statement? I'd appreciate any advice.
I am trying to create an exception report that will show the difference between two versions of the same row. (Combination of two different sources in sql, with source 1 having childID = 0 and the other source having childID = 1; parentID is the link between them)
The results are as follows:
ParentID - ChildID - Col1 - Col2 - Col3 1 - 0 - AA - BB - CC 1 - 1 - AA - BF - CC 2 - 0 - GG - NN - TT 2 - 1 - DE - NN - TA 3 - 0 - etc 3 - 1 - etc 4 - etc
I have resulting rows from a query similar to the following:
The data is coming from a single table that contains only one coverage code column and one coverage code date, but the end user wants the two coverage code types and dates combined into a single row. So the SELECT looks something like this:
SELECT [Employee ID] = emp.employee_id, [Coverage Code 1] = enr.coverage_code, [Coverage Date 1] = enr.coverage_date, [Coverage Code 2] = case when enr.product_type = 'Accident.Accident' then enr.coverage_code else NULL end,
[Code] ....
I basically want to merge the like Employee ID's together into a single row like the following:
I know I have done this before and it is probably pretty simple.
CREATE TABLE [RS_A] ([ColA] [varchar] (10)[ColB] [int] NULL)CREATE TABLE [RS_B] ([ColA] [varchar] (10)[ColB] [int] NULL)INSERT INTO RS_AVALUES ('hemingway' , 1)INSERT INTO RS_AVALUES ('vidal' , 2)INSERT INTO RS_AVALUES ('dickens' , 3)INSERT INTO RS_AVALUES ('rushdie' , 4)INSERT INTO RS_BVALUES ('hemingway' , 1)INSERT INTO RS_BVALUES ('vidal' , 2)I need to find all the rows in A which do not exist in Bby matching on both ColA and ColBso the output should bedickens 3rushdie 4So if i write a query like this , I dont get the right result setSELECT A.ColA, A.ColBFROMRS_A AINNERJOIN RS_B BONA.ColA <B.ColAORB.ColB <B.ColBBut if i do the following, i do get the right result, but followingseems convoluted.SELECT A.ColA, A.ColBFROMRS_A AWHERE ColA + CAST(ColB AS VARCHAR)NOT IN (SELECT ColA+CAST(ColB AS VARCHAR) FROMRS_B B)
declare tableName table ( uniqueid int identity(1,1), id int, starttime datetime2(0), endtime datetime2(0), parameter int )
A stored procedure has new set of values for a given id. Sometimes the startime and endtime are the same, in which case I update the value of parameter. Sometimes I add a new time range (insert statement), and sometimes I delete a time range (delete statement).
I had a question on merge, with insert, delete and update and I got that resolved. However I have a different question regarding performance of the merge statement.
If my target table has hundreds of millions of records and I want to delete/update/insert a handful of records, will SQL server scan the entire target table? I can't have:
merge ( select * from tableName where id = 10 ) as target using ...
and I can't have:
merge tableName as target using [my query] as source on source.id = target.id and source.starttime = target.startime and source.endtime = target.endtime where target.id = 10 ...
This means I cannot filter the set of rows in the target table to a handful of records where id = 10.
I have access to a stored procedure that was written previously for a process that uses the output from the stored procedure to provide input to a BCP operation in a bat file that builds a flat text file for use in a different system.
To continue with the set up, here is the stored procedure in question: CREATE PROCEDURE [dbo].[HE_GetStks] AS
select top 15 Rating, rank, coname, PriceClose, pricechg, DailyVol, symbol from
(selectf.rating, f.rank, s.coname, cast ( f.priceclose as decimal(10,2)) as PriceClose, cast ( f.pricechg as decimal(10,2)) as pricechg, f.DailyVol, f.symbol from dailydata f, snames s where f.tendcash = 0 and f.status = 1 and f.typ = 1 and f.osid = s.osid) tt order by rating desc, rank desc
GO
The code in the calling bat file is: REM ************************* REM BCP .WRK FILE REM ************************* bcp "exec dailydb.[dbo].[HE_GetStks]" queryout "d:TABLESINPUTHE_GetStks.WRK" -S(local) -c -U<uname> -P<upass>
This works just peachy in the process for which it was designed, but I need to use the same stored procedure to grab the same data in order to store it in a historical table in the database. I know I could duplicate the code in a separate stored procedure that does the inserting into my database table, but I would like to avoid that and use this stored procedure in case the select statement is changed at some point in the future.
Am I missing something obvious in how to utilize this stored procedure from inside an insert statement in order to use the data it outputs? I know I cannot use an EXECUTE HE_GetStks as a subquery in my insert statement, but that is, in essence, what I am trying to accomplish.
I just wanted to bounce the issue of y'all before I go to The Boss and ask him to change the procedure to SET the data into a database table directly (change the select in the proc to an INSERT to a local table) then have the external BAT file use a GET procedure that just does the select from the local table. This is the method most of our similar jobs use when faced with this type of "intercept" task.
We have written an application which splits up our customers data intotheir individual databases. The structure of the databases is thesame. Is it better to create the same stored procedures in eachdatabase or have them in one central location and use the sp_executesqland execute the generated the SQL statement.Thank you.Mayur Patel
How can I create a table identical to another one? I need to copy the indexes a constraint too. Example: I have a table "employee" and I want another table "employee2" with the same indexes and primary key and references.
I've run into something that looks like a bug to me but I wanted to run it by the board:
Merge join 2 sorted tables.
Table1: ColumnA : Sort Order 1, ColumnB Sort Order 2
Table2 : ColumnA: Sort Order 1, ColumnB Sort Order 2, ColumnC not sorted
Merge Join the two tables on ColumnA and ColumnB...
Choose the following as output columns
A + B + C = works
C = works
A + C = works
B + C = NOT work.. error message: The column with the SortKeyPosition value of 0 is not valid. It should be 2.
Basically if you choose one or more of the sorted columns in the output at least one of them has to be the column with Sort position 1 or you'll get that error.
Is this a bug or intentional? If you do not have sort column 1 in the output that output could no longer be considered sorted... so perhaps the error is related to that (instead of error I'd expect some warning about the sorting). Interesting that it lets you choose C only becuase that also makes the output unsorted.
We have two queries that run nightly and we'd like to combine them and only have one result set instead of two. What's the best way to combine these? The only difference is the Table the information is being pulled from.
Query 1:
set nocount on select case when datalength(MICRACCTNUMBER) = 4 then convert(char(20),('001 000000000000'+MICRACCTNUMBER)) when datalength(MICRACCTNUMBER) = 5 then convert(char(20),('001 00000000000'+MICRACCTNUMBER)) when datalength(MICRACCTNUMBER) = 6 then convert(char(20),('001 0000000000'+MICRACCTNUMBER))
[code].....
Again, the only difference is the Table the info is coming from...
I have a sql statement that joins two tables and I get back a few thousand records when I run it in query tool in management studio.
But when I use SSIS merge join to join the two tables my output is 0 records.
I did sort the key column in both tables by setting 'sortkeyposition' property to 1 in advanced editor for output of both tables.
however the merge join returns nothing to my destination tables. Also I am doing a inner join. The task runs without error but returns nothing as well.. any ideas?
I used the MERGE function for the first time. Now I have to create a pipe-delimited delta file for a 3rd party client of any deltas that may exist in our database.
What is the best way to do this? I have OUTPUT to a result set of the deltas...but I have to send over the entire table to the 3rd party via a pipe-delimited file.
Does anyone know how i can go about merging preexisting pdf files and SQL server reporting services output. Can this be done in reporting services? For example, I have 5 pages from a pdf files which is created from another 3rd party software provider. I then i have output from sql reporting services. How can i merge these two outputs and deliver it over .Net/ ASP framework?
I have read a few other posts about how to merge multiple report output files into a single document e.g. a single pdf. There are a few approaches: 1) Generate post script files and then merge and oprint via a post script driver. 2) Generate seperate pdf files and then merge them into single document using a custom library.
I have a third idea and would appreciate any input:
Dynamically generate a RDL file that contains sub-reports, one for each report required in the final document, publish and run this as the final report. This could happen way before actually running the report i.e. the user has a tool where they select reports for a pack, tool then generates new RDL file and publishes it to sql reporting services, gets run at some later point in time.
Some challenges: -Generate a table of contents with page numbering? -Layout of sub-reports, not sure how they would be rendered across multiple pages? -Managing parametes across sub-reports at run time.
I'm doing a data conversion with one of my fields (SUMDWK) from one of the tables that will be used in a merge join. With the new, converted field, I do a look up. From this look up, I want to take a new field FiscalWeekOfYear, and replace the original field, SUMDWK. This is necessary because SUMDWK is one of the sorted fields. In the look up, it is not possible to change the Output Alias. Does anybody know a way around this? Thanks.
I'm trying to use the Merge component. When i attach a datasource to the the component, the Select Input/Output dialog box should popup.. It does, but VS.NET is hanging and i can only shutdown the procesess...
Any idea how i should solve this? how can i re-register this component?
I have Lookup task to determine if source data should be updated to or insert to the customer table. After Lookup task, the Error Output pipeline will redirect to insert new data to the table and the Output pipeline will update customer table. But these two tasks will be processing at the same time which causes stall on the process. Never end.....
The job is similiart to what Slow Changing Dimention does but it won't update the table at the same time.
In the first image as can be seens i have 2 different data sources and then they are being joined using "Merge Inner Join". The "sort" is on BusinessEntityID column of Person table and "Sort1" is on "PersonID" of Customer table. The merge join of these 2 result in 19,119 rows.
On the other hand, if i use single data source and use a query with inner join on tables used in the first image (ie. 2 tables being used in 2 different data sources) as depicted in second image. Also, since merge cannot operate without SortKey i have defined TerritoryID as sort key in the advanced editor. The number of rows i get after this is "10,274". My select query was :
SELECT P.BusinessEntityID, P.PersonType, P.Title, P.FirstName, P.MiddleName, P.LastName, P.Suffix, C.TerritoryID FROM stg.Person AS P INNER JOIN stg.Customer AS C ON C.CustomerID = P.BusinessEntityID ORDER BY C.TerritoryID;
According to me, it should have been the same as in first case i am using merge inner join and in second case i am using SELECT query with inner join. Upon drilling down i found that in the first case , my sort keys are BusinessEntityID and PersonID, if i modify this to CustomerID and BusinessEntityID as this is my join condition (in ithe inner join query shown above), i get the desired output. What i was wondering was, how the sort order change the Join Condition?
I am trying to build a SQL select query, but am struggling a little bit.I have a table with multiple inventory transactions, each with an ID and weight.
What I want to do is write a SQL query that returns records where there is a transaction for SysProg = 238, but return the weights for some of the other transactions in columns on the same row.Basically, from the above data set, what I would like to return is:
ID Weight (271) Weight (238) Weight (61 or 59) 210359 986 985 984 354852 568 523 521
