SQL 2012 :: Extracting Data From Compressed CLOB XML Using SSIS?
Apr 14, 2014
Source System : Oracle
Target System : Ms Sql Server 2012
ETL Tool Used : SSIS
My source data is present in XML File which is stored in CLOB column Of Oracle. CLOB column is compressed.I need to Migrate data by Uncompressing XML to SQL 2012 .
Do I need to define XML column in SQL Server 2012 for storing Uncompressed CLOB values ?
How to uncompress the clob and extract the required data from XML using SSIS .
View 2 Replies
ADVERTISEMENT
Jun 10, 2014
How to access the XML, CLOB and BLOB Oracle data through linked server from SQL Server 2012?
I want to retrieve XML, CLOB and BLOB data from SQL Server 2012 through Oracle Linked server .
View 1 Replies
View Related
Sep 28, 2015
I'm trying to extract some data from an XML column, into the demo below I would like to obtain the CommandText value but my attempts so far are in vain, I'm fairly sure its just a path issue in the .query command but I just can't seem to get it to work.
create table #demo (field1 xml)
insert into #demo (field1)
values ('<SharedDataSet xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner" xmlns="http://schemas.microsoft.com/sqlserver/reporting/2010/01/shareddatasetdefinition">
<DataSet Name="DataSet1">
[Code] ....
View 6 Replies
View Related
Apr 2, 2008
I am using data flow task.And data flow source uses ole db for olap 9.0 to connect my ssas. sql comment is my access mode. A mdx query extracts data. Data flow destination is sql server table. Error said Data Flow Task: OLE DB Source [579]: The output "OLE DB Source Output" (589) references an external data type that cannot be mapped to a Data Flow task data type. I guess it is a implicit data type convertion problem. But how to solve it???
I appreciate for all suggestion. Thanks.
View 6 Replies
View Related
Jul 19, 2007
Noticed weird activity today with pull merge.
When your snapshot is set to be delivered via ftp and compressed in a cab file. If you add a new article to your publication and re-run the snapshot the agent will be unable to pull the snapshot down as it for some reason doesnt think its compressed. It is failing to find the scripts it needs inside the cab file despite the cab file existing in the correct location.
Here is the error.
2007-07-19 09:57:29.855 Snapshot files will be downloaded via ftp
2007-07-19 09:57:29.886 Connecting to ftp site 'SQL3'
2007-07-19 09:57:29.933 The schema script 'empActive_127.sch' could not be propagated to the subscriber.
2007-07-19 09:57:29.933 Category:NULL
Source: Merge Replication Provider
Number: -2147201001
Message: The schema script 'empActive_127.sch' could not be propagated to the subscriber.
2007-07-19 09:57:29.933 Category:AGENT
Source: SQL2SQL2005
Number: 20033
Message: The process could not retrieve file 'SQL3_CCUSA_ATLAS_SYSTEM TABLES/20070719055712/empActive_127.sch' from the FTP site 'SQL3'.
2007-07-19 09:57:29.949 CategoryS
Source:
Number: 12003
Message: 200 Type set to I.
200 PORT command successful.
550 SQL3_CCUSA_ATLAS_SYSTEM TABLES/20070719055712/empActive_127.sch: The system cannot find the file specified.
550 SQL3_CCUSA_ATLAS_SYSTEM TABLES/20070719055712/empActive_127.sch: The system cannot find
View 1 Replies
View Related
Mar 19, 2015
From my query I am getting results like below in one of the column:
'immediate due 14,289.00
04/15/15 5,213.00
05/15/15 5,213.00
06/15/15 5,213.00
07/15/15 5,213.00
08/15/15 5,213.00
09/15/15 5,213.00
10/15/15 5,213.00
11/15/15 5,210.00'
this same type of many rows are there (i just mentioned one) but having same pattern with tabs as delimiter in between dates and amount.
I need something that shows Date on one side representing particular amount on the other
For Immediate Due it will be current date and the amount besides it.
how can I achieve this.
View 8 Replies
View Related
Aug 27, 2014
I am currently working on a BI project and I am meant to use AtTask Project Management Application as one of my data sources for the ETL.which SSIS component that I can use to load data from the AtTask into my data warehouse.
View 4 Replies
View Related
Jun 11, 2015
I just completed a copy-Only compressed backup of a DB (with a FULL Recovery Model ) on SQL Server 2012 and the resulting backup (the bak file) is 1/100th the size of the data & log file. Is the compression in SQL Server 2012 just that good or did something else happen that I did not catch? Below is the T-SQL to re-create the backup. The size of the data file is 750MB and the log file is 75GB and is %95 used according to the SQLPERF command.
Does the compression in SQL Server 2012 simply that good
BACKUP DATABASE [MYBIGOLEDB] TO DISK = N'Z:Microsoft SQL ServerMSSQL11.MSSQLSERVERMSSQLBackupMYBIGOLEDB_20150611.bak' WITH COPY_ONLY, NOFORMAT, INIT, NAME = N'MYBIGOLEDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
GO
View 1 Replies
View Related
Oct 5, 2013
Using a code snippet borrowed from a co-worker, I have put together a query that, among other things, pulls a list value out of an xml clob field and displays it in the query results. My query as it stands right now is below, followed by a snippet from the xml clob that I am pulling from.
select * from
(Select Wtr_Service_Tag, Wtr_Tran_Origin, Wtr_Send_Date, Wtr_Receive_Date,
to_char(substr(wtr_req_xml,instr(substr(wtr_req_xml,1,8000),'SID')+8,12)) Asset_Tag
from ws_transactions
Where Wtr_Service_Tag In ('20458749610')
[Code] ....
This query is only able to pull the first value in the list.
I have two questions...
[1]How can I edit this query to pull all of the list items when there are more than 1? I have another field, in a separate table, that I can pull from to get that number.
[2]This one may be more complex. As currently written, the query pulls a fixed number of characters from the xml clob and either returns not enough data, or too much because the values I need to pull could be of varying lengths. I have no way to query what those lengths might be.
View 2 Replies
View Related
May 10, 2007
I have two tables Encounter & Encounter_History. They have same columns. One column is of type CLOB. My requirement is to retrieve all the distinct records from both the tables with order by a date column. But problem is, UNION does not work in case of CLOB data type.
I know it will work if I use UNION ALL, but it returns duplicate records.
Please give me suggestion, how to solve this problem.
For example: The following query does not work since column1 is a CLOB data type
select column1 from table1
union
select column1 from table2
Thanks
View 1 Replies
View Related
Apr 27, 2007
Hello,
I have a table of compressed data and am looking for an efficient way to expand the data for reporting purposes.
The table is used to store the number of hours a given contractor works and is stored in the following fashion:
cntHours
58
20
58
20
The first row represents the number of sequential days where an employee worked the same # of hours. Once the # of hours changes, a new record is created. In this simple example, the first row shows an employee working Monday-Friday (5) for a total of 8 hours each day. The second row represents the weekend (2 days) where the employee worked 0 hours.
What I need to do is explode this out to show 1 record per day. Ideally I'd like to write a function to do this as I would be linking to another table which has the start and end date for the contractor and would allow me to apply individual dates to each record based on the contractor start date through to the end date.
Thanks for any help you can provide!
View 2 Replies
View Related
Jan 25, 2007
Hi all,
I am trying to use bcp to output data to a compressed (zipped) folder.
The bcp command is called from a step in scheduled job in SQL 2005 (T-SQL) similar to:
SET@chvCommand =
'bcp [working_t] out D:EprojectsEdataCdata200701.dat'
+ ' -c -STPISQL -T'
EXECUTE master.dbo.xp_cmdshell @chvCommand, NO_OUTPUT
.... where Cdata is a compressed (zipped) folder.
The scheduled job seems to work without errors, but afterwards there is nothing in the compressed folder.
If Cdata is a regular folder everything works fine.
Thanks for you insight.
View 8 Replies
View Related
Mar 7, 2014
I want to extract day of the week from given date.
If the given date is 07-03-2014(dd-mm-yyyy).IF i run the query the output should be =Friday
View 2 Replies
View Related
Jul 20, 2005
Hi everyone,I encountered an error "Need to run the object to perform this operationCode execution exception: EXCEPTION_ACCESS_VIOLATION" When I try to import data from Oracle to MS SQL Server with EnterpriseManager (version 8.0) using DTS Import/Export Wizard. There are 508 rowsin Oracle table and I did get first 42 rows imported to SQL Server.Anyone knows what does the above error message mean and what causes therest of the row failed importing?Thanks very much in advance!Rene Z.--Posted via http://dbforums.com
View 1 Replies
View Related
Aug 18, 2014
I need extracting string that is between certain characters that are in certain position.
Here is the DDL:
DROP TABLE [dbo].[StoreNumberTest]
CREATE TABLE [dbo].[StoreNumberTest](
[StoreNumber] [varchar](50) NULL,
[StoreNumberParsed] [varchar](50) NULL)
INSERT INTO [dbo].[StoreNumberTest]
[Code] ....
What I need to accomplish is to extract the string that is between the third and fifth '-' (dash) and insert it into the StoreNumberParsed while eliminating the fourth dash.
Sample output would be:
KY117
CA132
OH174
MD163
FL191
I know that parse, charindex, patindex all might come in play, but not sure how to construct the statement.
View 5 Replies
View Related
Dec 4, 2014
I am trying to extract what I believe is called a Node from a column in a table that contains XML. What's the best way to do this? It is pretty straightforward since I'm not even looking to include a WHERE clause.
What I have so far is:
SELECT CCH.OrderID, CCH.CCXML.query('/cc/auth')
FROM tblCCH AS CCH
View 3 Replies
View Related
Jul 12, 2006
Hi all,
I am supposed to maintain a bunch of excels documenting all the mappings I'm doing in SSIS. The excels have the following format:
Target Field | Target Type | Source Table | Source Field | Source Type | Transformation Rule
Apart from being incredibly tedious, it is hard to keep every excel current, as there are other people adding and taking information from the database model.
I plan to extract this information from SSIS and create the excels dinamically, but, apart from parsing the .dtsx for each package, I see no other way of getting what I need.
Any suggestions on how I should do this? Is there an easier way?
Cheers,
David Leal
View 4 Replies
View Related
Apr 3, 2014
I need to see inside a SSIS 2012 project a new SSIS installed component, but in the SSDT 2010 I cannot see the SSIS Data Flow Items tab for adding data source/data destination respect to the choose toolbox items pane.
View 4 Replies
View Related
Jun 20, 2007
Who knows whether exists embedded tool in win2003 or SSIS for extracting the files from ZIP?
Thanks in advance.
View 4 Replies
View Related
Jun 30, 2015
We have several hundred very simple ETL SSIS 2K8 package files (*.dtsx).
I'd like to be able to interrogate them to determine source and destination fields.
There's no great need to map source to dest or to extract data types.
So far, the most promising candidate is to load them using OPENROWSET into an XML field in a SS table.No problem there, but querying using OPENXML has me stumped.
The package files will change a couple of times per year, so the process needs to be repeatable with minimal manual intervention.
View 3 Replies
View Related
Sep 18, 2015
I want to import date from excel file from the row if this row has a character like 'TableName', and I don't know the rownumber and end column number ,so I can't use openrowset 'select * from Sheet$ A11:N'. And I am thing if I can get the rownumber of the row contain string 'TableName' and the max column ,that May be easy to extract data.
View 2 Replies
View Related
Jul 10, 2014
In cases when the SSIS package has to be deployed in SQL Server, when we use ProtectionLevel ="server storage" while creating SSIS Catalog, does it use TDE? or Database Standard Encryption? if the database is using TDE?
View 0 Replies
View Related
Jul 15, 2014
I've two SQL databases, the first one having the schema named sch1 and the second one with the schema named sch2. I need to copy data of more tables belonged to the sch1 into the sch2 of the second SQL Server db and I'd like to create a SSIS 2012 pkg in order to achieve this goal.
View 2 Replies
View Related
Aug 7, 2014
In cases when the SSIS package has to be deployed in SQL Server, when we use ProtectionLevel ="server storage" while creating SSIS Catalog, does it use TDE? or Database Standard Encryption? if the database is using TDE?
View 5 Replies
View Related
Sep 1, 2014
I am a complete newbie to SSIS. I can create a simple package to transfer data between SQL instances and thats about it.
I have tableA (source data) and tableB (Destination data). TableA has 4 column and tableB has 5. I want to transfer all of the columns from tableA into TableB, but the 5th column in tableB needs to be populated with the ServerInstance name of the server TableA sits on. Do I need to have multiple data sources to achieve this? I have tried but no matter how I set it up, the Column in the destination is set to ignore.
View 2 Replies
View Related
Oct 3, 2014
Currently, I am using T-SQL to combine some data and using an ISNULL function like this:
,ISNULL(H.Last_Name, S.PatientLastName)
,ISNULL(H.First_Name, S.PatientFirstName)
I am wanting to change from using a query to move this data to using an SSIS Data Flow. I am familiar with using Merge Join to combine the two tables (H & S in this case), but I'm not sure where I can use the ISNULL in the manner described above. Is there a way to do it in the Merge Join? Do I have to do it after the Merge Join?
View 3 Replies
View Related
Oct 29, 2014
I would like to know how I can add the following sample code to my Source data on Data Flow on SSIS, or what other options there are. The main issue is time as we have talking about 100's of millions of rows
select Sample,
CASE
WHEN Sample IS NULL
THEN NULL
WHEN SUBSTRING(Sample, 1, 6) IS NULL
THEN ' '
ELSE RTRIM(SUBSTRING(Sample, 1, 6))
END AS [Sample_1_6]
from TestTable
what I have done at this stage is just to Create a SQL task with a Insert into
INSERT INTO [dbo].[TestTable1]
([Sample]
,[Sample_1_6])
select Sample,
CASE WHEN Sample IS NULL =THEN NULL
WHEN SUBSTRING(Sample, 1, 6) IS NULL THEN ' '
ELSE RTRIM(SUBSTRING(Sample, 1, 6))
END AS [Sample_1_6]
from TestTable
If there is a way adding this to a dataflow so I van use fast load that would really be the best solution. I know there are derived columns, but would this really be faster than the straight insert into in a SQL Task? If this is the way to go what is the code I would use in the derived column or any other option.
View 7 Replies
View Related
Feb 6, 2001
hi all,
i have a device file which i know for sure contains all the data for a database (not the log). is there any way to recover the data from this file?
thanx.
:-)
hemant
View 2 Replies
View Related
Mar 27, 2008
I want to extract a particular portion of primary key field data.For example- suppose there are fifty data in PK field starting from 1...50.Now I want only to extract data from 25 to 50.It is bcos I want to retrieve only the new data that is inserted after a specified time.For example-today there is data starting from 1 to 25 which I have retrieved and saved elsewhere.By tomorrow there will be new data starting from 26 to 50 or more.Now I just want to extract this additional data from the database so that I dont need to extract all data again.Could anybody pls tell me how to do this.How can I do it using common SQL bcos I have to make a program for it.
View 1 Replies
View Related
May 19, 2004
Cheers
I would really apprecite if anyone could make a comment on this...
what i wanna know is....
there are two tables, called 'A' and 'B' with identical structures...
is it possible to Perform the following in a single SQL ....
* If A->Field1 = B->Field1 then A->Field2 + b->Field2
* Records of table 'A' which not in table 'B' and
* Records of table 'B' which not in table 'A'
Thanks
View 5 Replies
View Related
Mar 22, 2007
This is my sample table Data
table1
TagID|TagName
--------------
1|xyz
2|123abc
3|a3bc
4|arsdew
5|xyc324
..............
..............
TagID--Primary Key
table2
TopicID|Topic
----------------
1|jkkj
2|kjgg
3|jhkj
4|refc
5|huhf
8|fyhj
9|jjk
...............
.............
..........
TopicID---Primary Key
table3
DetID|TagID|TopicID
-----------------------
1|1|1
2|1|2
3|1|3
4|2|4
5|2|5
8|3|8
9|3|9
....................
...................
.................
DetID---Primary key
TagID and TopicID----Foreign key
i need Topic based on TagName.
This xyz(TagName) have a 3 Topic. i need the 3 Topic
Once i select 123abc(TagName) that particular Topic I need..
any Query in Single Line.......................
View 2 Replies
View Related
Jan 22, 2008
Hi!
I'm analyzing the problem of extracting data from sql server 6.5 in order to load it into oracle.
I already know about the bcp tool, but I'd like to know if you would consider any other approach based on SSIS, or any other tool like SQL Load.
regards, and thanks in advance for your help.
View 1 Replies
View Related
