SQL Server 2008 :: Collation Used By SSIS MERGE JOIN Task
Aug 27, 2012
Can the collation used by SSIS be changed or influenced during install or run time? We have found that our databases, that use a mandatory "LATIN1_GENERAL_BIN", have incorrect SSIS Merge Join output. Changing our database collation in testing didn't make a difference. What matters is the data. Which Windows collation is SSIS using?
Example Data:
FIRSTNAME
FIRSTNAME
FIRSTS-A-NAME
FIRSTS_A_NAME
FIRST_NAME
FIRST_NAME
FIRSTname
FIRSTname
FIRS_NAME
put in a Sort task before the Merge Join task as setting advanced properties isn't enough (as described by Eric Johnson here --> [URL] ......
We are using 64-bit SQL Server 2008 R2 w/ SP1 in Windows Server 2008 R2 ENT w/ SP1.
UPDATE from ETL team: Explicitly ordering the source with "COLLATE Latin1_General_CS_AS" seems to have the same effect as using a separate sort task. We don't feel that we can rely on our findings, however, unless we have documentation that this collation is what is behind SSIS.
View 2 Replies
ADVERTISEMENT
Aug 10, 2006
Hi,
I am pretty new to SSIS. I am transferring some rows from 2 source tables to 1 destination table.
The 2 source tables have 1000 rows.They act as the 2 inputs to a merge join transformation where i perform the join between the 2 tables based on a couple of fields. But for some reason the output of the merge join gives me about 1018 rows .Shouldnt the destination also have only 1000 rows?
How do i solve tis problem?
Thanks in advance
Sat
View 1 Replies
View Related
Oct 15, 2015
I am using SQL Server 2008. In ServerProperty function, there are two properties called “Collation” and “CollationID”. In some cases, I will only know the CollationID. Is it possible get the collation name from the CollationID? Is there a function called CollationNameFromID?
View 1 Replies
View Related
Mar 3, 2015
I have built the following query in SSMS, when I add it to an Execute SQL Task in SSIS. I get this error -
"[Execute SQL Task] Error: Executing the query "SELECT @columnz = COALESCE(@columnz + ',[' + times..." failed with the following error:
"Must declare the scalar variable "@columnz".". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."
Query:
use design
drop table tmpNCPCNCDownstreamMaxUtilization3wks
select node, max(utilization) as max_Utilization, DATE
into tmpNCPCNCDownstreamMaxUtilization3wks
from stage_ncpcncdownstream_temporal
WHERE Date BETWEEN DATEADD(day, -20, GETDATE()) AND GETDATE()
[code]....
View 1 Replies
View Related
Apr 29, 2015
I'm trying to call WinSCP in a SSIS Execute Process Task using a .bat file to automate. SSIS is returning a generic failure error message ("Error: 0xC0029151 at Execute Process Task, Execute Process Task: In Executing "MyServerC$Program Files (x86)WinSCPWinSCP.exe" "-script=MyNASShareWinSCPFTP.bat " at "", The process exit code was "1" while the expected was "0"."). So now I'm trying to run the .bat file by itself. Unfortunately, the command prompt rushes by so fast that I can't see what the server is doing.
I can't find anything on the WinSCP site that indicates how to slow down the .bat file processing or log the remote server responses. I do see how to use the /log switch when using the interactive command line console, but that's not what I want to do.
View 9 Replies
View Related
Jul 24, 2015
I am trying to pull AD users and their attributes to a SQL server table.
I am using the script task in SSIS. (I don't use openquery and linkedserver for that is not applied to our case).
I can pull most of columns, but there are some columns like below are so hard to pull:
, [Disabled]
,[AccountLockedOut]
,[CannotChangePassword]
,[PWNeverExpire]
,[PasswordNotRequired]
I did some research and find they are all in the userAccountControl attribute.
So I did something like below
If (results.Properties.Contains("userAccountControl")) Disabled = results.Properties["userAccountControl"][0].ToString();
But found all values I got is 1. That is not correct.
By reading this, it should return some values like mentioned in this
[URL]
View 0 Replies
View Related
Jun 1, 2015
I have a package which is creating a excel file 'ExcelName.xls' and storing in 'E:ReportingDelivered_Reports'. Now I have to attach this report using send mail task and send it to given mail id. To achieve this I have configured the send mail task and in Expression Builder, I have selected the below expression:
"E:ReportingDelivered_ReportsExcelName_"+
((DT_WSTR,4)Year(@[System::StartTime]))+
RIGHT("0"+((DT_WSTR,2)Month(@[System::StartTime])),2)+
RIGHT("0"+((DT_WSTR,2)Day(@[System::StartTime])),2)+".xls"
I need file name should be 'ExcelName_20150601' where suffix is the current date. But I recieve file which name is 'ExcelName', which is the original file name.
View 7 Replies
View Related
Mar 6, 2008
I am working on an ssis package and i find an problem while using the merge join for merging 2 OLEDB Data sources .
data source 1 is : - The table formed my an sal server comand , that out put is given to a multicast since i want to sare that output amoung 2o other tables.
So the the left input for the merge join is OLEDB source , which contains direct data from source table
I am usong Inner join on one column
The problem is i am not getting the expected rows as out put of merge .
I tried to join the two tables in sqlserver query window and i am getting expected result
What could be the problem
The first table is
Reservations.ReservationManual
second table is Out put of the following query
Select Distinct B.ReservationID as R
from Property.Main A ,Reservations.Reservations B ,Reservations.ReservationRooms C
Where
A.propertyID = B.PropertyId And
C.ReservationID = B.ReservationID And
getdate() >=C.Until +A.ReservationOffLineDays
i am not getting the expected result here in SSIS package merge join
But if i try to execute the following in query editer in management studio i am getting the expected result !!
declare @temp as table
(ResID Varchar(50)
)
Insert into @temp
(ResID)
Select Distinct B.ReservationID as R
from Property.Main A ,Reservations.Reservations B ,Reservations.ReservationRooms C
Where
A.propertyID = B.PropertyId And
C.ReservationID = B.ReservationID And
getdate() >=C.Until +A.ReservationOffLineDays
select * from Reservations.ReservationManual A , @temp b
Where A.reservationID = b.resID
View 10 Replies
View Related
Feb 7, 2006
I had this (what seems to be a) simple question asked today and I'm afraid I didn't like my answer. Does anyone know the proper answer to this one:
Any ideas on how I can constrain a lookup or merge join based on the dimension row's effective and expired dates so three criteria are needed as follows:
1. DataStagingSource.ModifyDate < DataWarehouseDimension.RowExpiredDate AND
2. DataStagingSource.ModifyDate >= DataWarehouseDimension.RowEffectiveDate AND
3. DataStagingSource.NaturalKey = DataWarehouseDimension.NaturalKey
-- Brian
View 3 Replies
View Related
Jan 20, 2006
Setup:
I need to run an Insert query which pulls data from a table located on server A database AA Table AAA conditional on (or JOINED with) Table BBB in database BB sever B. In SQL 2000 it could be done as:
From Server A:
sp_addlinkedserver B
INSERT dbo.ResultsTable
SELECT SourceTable.* FROM B.BB.dbo.BBB SourceTable
INNER JOIN A.AA.dbo.AAA ConditionTable ON SourceTable.RecID = ConditionTable.RecID
sp_dropserver B
In SSIS one of the possible solutions is to use a package which does the following:
OPEN A + OPEN B-> SORT A + SORT B->MERGE JOIN A and B->OUTPUT RESULT
The problem with this approach is that it's extremely slow for large datafiles (50M records each)
Questions:
1) In the procedure above could the SORT step be avoided?
2) Is there another approach to run cross-servers JOIN in SSIS?
Thank you
View 3 Replies
View Related
Sep 15, 2015
Is it possible to change the collation of your instance?
As far as I know it is not possible to change the collation once SQL Server has been installed.
When I said that SQL needs to be reinstalled at work the guys sent me this link: [URL]....
I ran it exactly like that but the collation didn't change.
I have also restarted the services but the collation is still the same.
Am I running this code wrong or am I right about not being able to change the collation after installation?
View 3 Replies
View Related
Aug 5, 2015
Our SQL Server 2008 r2 has collation Latin general.
And my database 'DB1' , 'DB2' has collation set to Japanese unicode.
My sql team has informed they cannot change system collation as it hosts other db's as well.
My Query: What will be the impact of changing collation from JP to Latin.
View 1 Replies
View Related
Jun 19, 2015
Historically I've always written a VB script to copy a file from a sharepoint library. I don't like this method because I have to input a username & password in the script and maintain a config file.
Yesterday I was playing around with using a file system task. The sharepoint file has a UNC path so why not? I created a simple test package with a single file system task that copies the sharepoint file (addressed via UNC) to another network location. Package runs fine locally.
I try running on our utility server but am getting a "The file name [SHAREPOINT UNC PATH] specified in the connection was not valid" error. Package is running with a proxy on the server and the proxy account has the same permissions to the sharepoint site (so far as I can tell) as me.
View 0 Replies
View Related
Mar 12, 2015
I'm not even sure this is possible but I'm using MERGE in a process that has 3 source tables (the process steps through each source table sequentially) and I need to delete from the Target database occasionally.
My current code is
sqlMerge = "MERGE " + TableName + " AS target USING @CData AS source" +
" ON target.TotRsp = source.TotRsp AND target.ClientRef = source.ClientRef AND target.dbPatID = source.dbPatID" +
" WHEN MATCHED THEN" +
" UPDATE SET dbPatFirstName = source.dbPatFirstName, dbPatLastName = source.dbPatLastName,
[Code] ....
The Target db data is made up from several different clients and when the MERGE runs it uses TotRsp, ClientRef and dbPatID to uniquely match a source row to the target row and if no match it inserts the source row.
My problem is that when this runs with Source A first, it will delete all merged data from Source B & C. Then when Source B runs it will insert all Source B data but delete all from A & C and so on.
Is there way that that I can include additional clauses into NOT MATCHED BY SOURCE THEN so it knows only to delete when data has come from say Source A. Once the data is in the target table there is no reference to which source table it came from tho.
If there isn't a solution I suppose I could always add an extra column to the target db to indicate which source it came from and then have something like
NOT MATCHED BY SOURCE AND t.Source = 'SourceA'.
That's quite a bit of work my end to do that tho so I'd like to be sure it works.
View 1 Replies
View Related
Aug 25, 2015
SELECT
part.num, woitem.qtytarget AS woitemqty,
(SELECT LIST(wo.num, ',')
FROM wo INNER JOIN moitem ON wo.moitemid = moitem.id
WHERE moitem.moid = mo.id) AS wonums, mo."USERID" AS mo_USERID
[Code] ...
View 5 Replies
View Related
May 22, 2015
I have two xml source and i need only left restricted data.
how can i perform left restricted join?
View 2 Replies
View Related
Feb 19, 2015
I need the requirements of merging multiple rows of same ID as single row.
My Table Data:
IDLanguage1Language2Language3Language4
1001NULL JAPANESENULL NULL
1001SPANISH NULL NULL NULL
1001NULL NULL NULL ENGLISH
1001NULL NULL RUSSIAN NULL
Required Output Should be,
IDLanguage1Language2Language3Language4
1001SPANISH JAPANESERUSSIAN ENGLISH
How to achieve this output. Tried grouping but its not working also producing the same result.
View 1 Replies
View Related
Mar 4, 2015
We have a central office with a SQL2005SP4 server (yeah, I know... old as heck) that's the main database and it has multiple subscribers in regional offices. Well... one of the regional offices server is failing, and it needs to be replace.
The original server is an ancient Win2003 86x
The Server team will build a new Win2008r2 64x, and use the same name and IP address
And I'm tasked with the SQL part.
I'll be installing the same version/patch of SQL, but 64x instead, and migrate all databases, including the system databases.
How do I handle replication? Do I need to reintialize from scratch? or can I just use the backup as a starting point?
View 1 Replies
View Related
Jul 8, 2015
Merge two rows into one (conditions or Pivot?)
I have Temptable showing as
Columns:EmpName, Code, Balance
Rows1: EmpA, X, 12
Rows2: EmpA, Y, 10
I want to insert the above temp table to another table with column names defined below like this
Empname, Vacation Hours, Sicks Hours
EmpA, 12, 10
Basically if it is X it is vacation hours and if it is Y it is sick Hours. Needs a simple logic to place the apprpriate hours(Balance) to its respective columns. I'm not sure how I can achieve this in using Pivot or Conditions.
View 3 Replies
View Related
Aug 10, 2007
Scenario:
OLEDB source 1
SELECT ...
,[MANUAL DCD ID] <-- this column set to sort order = 1
...
FROM [dbo].[XLSDCI] ORDER BY [MANUAL DCD ID] ASC
OLEDB source 2
SELECT ...
,[Bo Tkt Num] <-- this column set to sort order = 1
...
FROM ....[dbo].[FFFenics] ORDER BY [Bo Tkt Num] ASC
These two tasks are followed immediately by a MERGE JOIN
All columns in source1 are ticked, all column in source2 are ticked, join key is shown above.
join type is left outer join (source 1 -> source 2)
result of source1 (..dcd column)
...
4-400-8000119
4-400-8000120
4-400-8000121
4-400-8000122 <--row not joining
4-400-8000123
4-400-8000124
...
result of source2 (..tkt num column)
...
4-400-1000118
4-400-1000119
4-400-1000120
4-400-1000121
4-400-1000122 <--row not joining
4-400-1000123
4-400-1000124
4-400-1000125
...
All other rows are joining as expected.
Why is it failing for this one row?
View 1 Replies
View Related
Feb 5, 2007
I've gote 2 Tables with about 50.000 rows and I left outer join them with MERGE JOIN.
The result are 9999 rows. Has anybody got the same problem. Maybe it's a bug!?
View 2 Replies
View Related
Jun 5, 2006
I have a merge join (full outer join) task in a data flow. The left input comes from a flat file source and then a script transformation which does some custom grouping. The right input comes from an oledb source. The script transformation output is asynchronous (SynchronousInputID=0). The left input has many more rows (200,000+) than the right input (2,500). I run it from VS 2005 by right-click/execute on the data flow task. The merge join remains yellow and the task never finishes. I do see a row count above the flat file destination that reaches a certain number and seems to get stuck there. When I test with a smaller file on the left it works OK. Any suggestions?
View 3 Replies
View Related
Feb 18, 2015
I've got a partitioned table where I am trying to switch the first partition into a staging table, merge the first boundary and later drop the file and the file group.
This is my starting point:
boundary_idBoundary_valuePartitionNumber
11/09/2012 0:002
21/10/2012 0:003
PartitionNumberPartitionRowsFileGroupName
1799AdtLog_Archive_201208
2300AdtLog_Archive_201209
After I switch partition 1 to a staging table I run:
ALTER PARTITION SCHEME My_ps NEXT USED AdtLog_Archive_201209;
and
ALTER PARTITION FUNCTION My_pf() MERGE RANGE ('2012-09-01 00:00:00.000');
I expect the 300 records from the former 2nd partition to stay in AdtLog_Archive_201209, however I get this:
PartitionNumberPartitionRowsFileGroupName
1300AdtLog_Archive_201208
2310AdtLog_Archive_201210
How do I make sure that the data stays in AdtLog_Archive_201209 file group?
View 1 Replies
View Related
Sep 22, 2015
I have a merge replication. Currently works fine. Publisher & Distributor are on the same server. I need to change the location of the alternate folder for the snapshot files.
I’ll probably just change it through the GUI, but would I use sp_changedistpublisher or sp_changemergepublication if I were scripting everything?
My real concern is the subscribers. Do I have to ‘tell’ the subscribers where the alt folder has been changed to? Do I just run sp_changemergepullsubscription on the subscribers?
View 1 Replies
View Related
Nov 6, 2015
For study effect, can I configure merge replication using just one SQL Server Instance?
View 3 Replies
View Related
Feb 21, 2008
I am using the "Transfer SQL Server Objects Task" to copy some tables from database A to database B including data.
The tables, primary key constraints, Foreign key, data and all transfers nicely except for "DEFAULT CONSTRAINTS" on the tables.
I have failed to find any option in the "Transfer SQL Server Objects Task" task to explicitly say "copy default constraints". So I guess logically it should happen automatically but it doesn't. I hope it is not a bug :-)
Any option anyone knows will help.
Thanks.
View 17 Replies
View Related
Feb 1, 2007
In short, does the Transfer SQL Server Objects Task? support distributed transactions?
In trying to use a Transfer SQL Server Objects Task? in a container using a transaction on the container. The task is set to support the transaction. It is setup to copy table data from several tables from a non-domain server (sql server 2000) to a domain-based server (sql server 2005). I get an error stating, This task can not participate in a transaction?.
I am wondering if it means exactly what it says this task in SSIS cant participate at all. Or does it mean that it wont in this scenario for some reason. I attempted a simple copy of data from mssql 2005 to mssql 2005 (same server) and the task still failed). MSDTC appears to be running properly on my machine and such (I can do a simple distributed transaction across linked server to the 2000 server in Query Analyzer (QA)). Also, MSDTC appears to be working on both servers with distributed transaction query tests in QA.
Heres the error info
SSIS package "Development BusinessContacts and Products Migration.dtsx" starting.
Information: 0x4001100A at Copy BusinessContacts Data: Starting distributed transaction for this container.
Error: 0xC002F319 at Copy BusinessContacts database table data 1, Transfer SQL Server Objects Task: This task can not participate in a transaction.
Task failed: Copy BusinessContacts database table data 1
Information: 0x4001100C at Copy BusinessContacts database table data 1: Aborting the current distributed transaction.
Information: 0x4001100C at Copy BusinessContacts Data: Aborting the current distributed transaction.
SSIS package "Development BusinessContacts and Products Migration.dtsx" finished: Failure.
The program '[4700] Development BusinessContacts and Products Migration.dtsx: DTS' has exited with code 0 (0x0).
View 9 Replies
View Related
Feb 10, 2015
I have a table:
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.
View 1 Replies
View Related
Apr 26, 2015
With merge/insert statements ...Is DISTINCT best way to handle problem of source table containing duplicate rows, along with WHERE NOT IN statement? the source dataset is large and having to do DISTINCT and further filtering is taxing on the ETL.
DDL
source table
CREATE TABLE [dbo].[source](
[Product_ID] [INT] NOT NULL,
[ProductCode] [VARCHAR](20) NULL,
[ProductName] [VARCHAR](100) NULL,
[ProductColor] [VARCHAR](20) NULL,
[code]....
View 0 Replies
View Related
May 1, 2007
Hello,
I've restored a SQL Server 2000 database with a Latin1_General_BIN collation from a .dmp file to a SQL Server 2005 server with a default collation of SQL_Latin1_General_CP1_CI_AS. When I try to change the database collation I get hundreds of the following error:
The object 'CK_PM10200_GLPOSTD_00AF8CF' is dependent on database collation. So, in this case, is it even possible to change the collation if there are objects in the database that are dependent on it?
Thanks,
Bruce
View 7 Replies
View Related
Nov 7, 2006
Is there a way to do a super-table join ie two table join with no matching criteria? I am pulling in a sheet from XL and joining to a table in SQLServer. The join should read something like for every row in the sheet I need that row and a code from a table. 100 rows in the sheet merged with 10 codes from the table = 1000 result rows.
This is the simple sql (no join on the tables):
select 1.code, 2.rowdetail
from tblcodes 1, tblelements 2
But how to do this in SSIS?
Thanks - Ken
View 2 Replies
View Related
May 6, 2008
I read that merge joins work a lot faster than hash joins. How would you convert a hash join into a merge join? (Referring to output on Execution Plan diagrams.)
THANKS
View 3 Replies
View Related
Jul 28, 2015
I have few tables, which are replicated and partitioned. They also have archival process. I want to avoid having to run that same process on the subscriber.
Replication of partition switching is easy. However I am not sure how to replicate merge range and empty filegroup/file drops.
There the following article options:
Copy file group associations
Copy table partitioning schemes
Copy index partitioning schemes
I am not sure if these are enough to implement the replication of merge range and empty filegroup/file drops.
I could not find and option to copy partition functions.
View 0 Replies
View Related
