Hi,
Is this anyway to finding updated/ deleted recored using anyother data flow transformation tasks without using sql task.
Can find the new records using merge join task.
Is there better way to merge master table using staging table?
Thanks in advance.
DF.
We need to Insert/Update a Fact Table from staging Table. currently we are using a SP which update Fact Table for Each region. this process is schedule, every 5 min job is run and Update fact table.but time of Insert and Update too long from staging to Fact, currently we are using merge statement for Insert and update.in my sp we are looping number how many region we need to update and at a time single Region we are updating using while loop in current SP.
View 7 Replies View RelatedHi I have a production table in SQL Server 2005 that has approx 500,000 records---every 6 hours this table needs to be truncated and filled
The basic SSIS package uses a script compentant and imports the data into a staging table which has the same structure as the production table. I have a final Execute SQL Task that Truncates the production table and does a Insert into production-select * from stage table.
Takes around 30 seconds to run this last Execute SQL Task--problem is there is a risk that our webservice will query this table during the Execute SQL Task and return incorrect results.
Q1: In this last Execute SQL Task if I used a BEGIN TRANSACTION and COMMIT TRANSACTION; will this be any quicker ?
Q2: In this last Execute SQL Task- would it be better to use a RENAME TABLE technique in TSQL--any code examples ??
Q3:Is there any way in TSQL I can compare EVERY FIELD in two tables ie Stage and Production which have identical data structures and figure out a way to update only the records that changed? Is SQL Server Replication the best way to do this
thanks kindly
Dave
Hello,
Maybe anyone have done that before?
I have table where i store SOURCE_TABLE_NAME and DESTINATION_TABLE_NAME, there is about 120+ tables.
i need make SSIS package which selects SOURCE_TABLE_NAME from source ole db, and loads it to DESTINATION_TABLE_NAME in destination ole db.
I made such SSIS package. set ole db source data access mode to table or view name variable.
set ole db destination data access mode to table or view name variable. set to variables defoult values (names of existing tables)
but when i loop table names is changed, it reports error, that can map columns, becouse in new tables is different columns.
how to solve that problem?
Hi,
I have a flat file with columns from a geographical hierarchy such as:
Country Zone State County City Store Sub Store , etc.
The file also has data columns for months to the right of the above columns such as:
Jul Aug Sept ......... basically 25 of these columns for two years' data for one product and another set of 25 columns for another kind of product. A typical record in the file looks like:
Country Zone State County City Store Substore
USA Southeast FL Hillsborough Tampa walmart Fletcher
May04_ProdA ....Jun06_prodA May04_ProdB...Jun06ProdB
144 160 180 158
I need to upload this data into a staging table in SQL Server 2005 using SSIS, I created a table with the geographical hierarchy columns but am trying to figure out a way to load the monthly data. I can create 50 columns for the 50 months ( 25 months for each product) but that would be very crude.
Is there a better way of inserting data from this flat file into a destination table? I need all the data in the staging table in one upload.
Thanks.
I have been working on this without any success. Anybody out there to help?
1. There are new files uploaded in the FTP site everyday by other regions. 3 regions and 1 file each. That means 3 files at 3 different times. The file name will be that same day's date.csv. Example; today's file will be A_20071106.csv , B_20071106.csv and C_20071106.csv. Tomorrow's will be A_20071107.csv,B_20071107.csv and C_20071107.csv. How do I do this to run everyday and take the file only for that day from FTP straight into SQL Staging table?
2. Everyday, immediately after each file is uploaded, to indicate the FTP file is loaded successfully, there will be a 20071106.dummy and so on for all three files will show up on the same folder in FTP. I must make this package runable only if the .done file arrives for that days date and then execute the .csv file. Otherwise check after 30 minutes if the .dummy is there yet or not. Do this until the .dummy file comes. Then execute the package that is on that time. Then do the same for the other two for that particular day. The times are 4pm for A, 6pm for B and 8.30pm for C
3. Then, if there is a new row, it should INSERT, any changes (based on 5 columns), it should update and if there was a row yesterday and it is not there today, DELETE.
4. All this must be automated.
I'm looking at SSIS and SqlBulkCopy as a possible method to quickly insert and process large amounts of data, my current method uses the sp_xml_preparedocument and OPENXML to parse an xml document of the data I want to process and insert into the database, however I'm noticing the performance of SqlServer parsing the xml document isn't that good.
I found the C# SqlBulkCopy method (new in .NET 2.0) and I was thinking it would be faster to use that to load my data into a staging table and then use SSIS to extract the data from the staging table, process and transform it as necessary and finally load it into the final destination tables. I was able to create an Integration Services Project that selects the data from the staging table, does a bit of processing on one of the fields (by calling a stored procedure), and finally loads the processed data into the final table.
The problem with this is I need to clean out the rows that were extracted from the staging table and I'm not sure how I can accomplish this (and I can't just issue a "delete from staging_table" because there maybe new records in the staging table that were not processed), perhaps I can either delete each record as it is proccessed or somehow get the last proccessed identity id from the staging table and delete all records less than or equal to that id.
thanks in advance for any help you can provide, maybe there is an easy way to accomplish what I'm trying to do.
-Matt Palmerlee
I'm trying to use Excel in SSIS to import the data from spreadsheet to a staging table. The package runs well from the web server using SSMS. But when I deploy and try to execute the package, I'm getting the below error. I've a question, whether I've to install the AccessDatabaseEngine driver in SQL database server or the web server where I'm executing the SSIS?
Error: The requested OLE DB provider Microsoft.Jet.OLEDB.4.0 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode.
What is the best way to transfer data from the staging table into the main table.
Example:
Staging Table Name: TableA_satge (# of rows - millions)
Main Table Name: TableA_main (# of rows - billions)
Note: Staging table may have some data same as the main table.
Currently I am doing:
- Load data into staging table (TableA_stage)
- Remove any duplication of rows from the staging table (TableA_stage)
- Disable all indexes on main table (TableA_main)
- Insert into main table (TableA_main) from staging table (TableA_stage)
- Remove any duplication of rows from the main table using CTE (TableA_main)
- Rebuild indexes on main_table (TableA_main)
The problem with the above method is that, it takes a lot of time and log file size grows very big.
I am trying to insert bulk data into main table from staging table in sql server 2012. If any error comes, this total activity is rollbacked. I don't want that to happen. I want to know the records where ever the problem persists, and the rest has to be inserted.
View 2 Replies View RelatedHi,
My scenario:
I have a master securities table which has 7 fields. As a part of the daily process I am uploading flat files into database tables. The flat files contains the master(static) security data as well as the analytics(transaction) data. I need to
1) separate the master (static) data from the flat files,
2) check whether that data is present in the master table, if not then insert that data into the master table
3) If data present then move that existing record to an history table and then update the main master table.
All the 7 fields need to be checked to uniquely identify a single record in the master table.
How can this be done? Whether we can us a combination of data flow items or write a sql procedure to do all this.
Thanks in advance for your help.
Regards,
$wapnil
Hi Guys,
I'm trying to import the contents of a CSV file into a staging table that I've created in SQL server 2005. To perform the import I have used the BCP utility with the use of a BCP format file.
The problem I'm having is that the data in some of the fields in the csv file are longer than the length of the corresponding field in my staging table. So when I try to import I get the following error:
[Microsoft][ODBC SQL Server Driver]String data, right truncation
And the record with the error does not import.
My question would be, is there a way of telling BCP to trim the
data before importing into the staging table? Could I somehow
specify in the BCP file to trim the data before importing or
is there a switch that i can specify in the BCP command which tells BCP to trim data to the length of the destination column.
If it helps I'm using the command below to run BCP.
bcp S_OLTPDEV.dbo.CX_LOTS_STG in %path1%CTS_SiebelLotsTo_Siebel_From_CTSlotupdates.csv -f %path1%CTS_SiebelLotsTo_Siebel_From_CTSLots_Format.fmt -F 1 -S %dbsrvr% -U %duname% -P %dpasswd%
Thanks in advance, any help would be really appreciated.
Hi All,
I am extracting source data which is in txt fille to OLE DB destination. But data of each day I want to save in different staging table. For Eg; tblProduct20081206, tblProduct20081207. How can it be done. I have seen lots of posting and script when destination is Txt. I want to use same table for staging but want to create different table for each day with adding date extension.
Please Help
I am trying to insert data from staging table to production table. In the staging table I only have period or date but no primary key.
This is my staging table
Create stagingtable(
[Period] [char](7) NOT NULL,
[CompanyCode] [varchar](100) NOT NULL,
[total] [int] NULL,
[status] [varchar](50) NULL
)
Create Production(
[Period] [char](7) NOT NULL,
[CompanyCode] [varchar](100) NOT NULL,
[total] [int] NULL,
[status] [varchar](50) NULL
)
I get this every month. What can I do to make sure only unique record are loaded into production table with no duplicate from previous month.
I don't know the correct syntax to accomplish this. I've looked in the on-line help but am not coming up with anything.
Basically I have two identically structured tables in two separate databases.
I wanted to select anything from the dbo2.table that isn't in the dbo1.table and insert it into dbo1.table.
I thought I could do something like... quote:
Select INTO dbo1.table
and then add my select statement. But that throws an error:
quote:Server: Msg 2714, Level 16, State 6, Line 1
There is already an object named 'bkupproceduresfeeschedule' in the database.
And the on-line documentation says an INTO is only for a new table.
How can I merge records from one table into another table?
Hi All,
In one of my interfaces,i am trying to Stage data in a ##table from a Flat file,so that i can iterate through the data in for each loop afterwards.
I have written a stored Proc to do this.But what i am observing is in the ##Table only the last Row will be persisted.
The SP looks like this
Code Snippet
CREATE PROCEDURE USP_SVInsS093Data_V001
@DetailRECTYPE nvarchar(5),
@StoreNumber int,
@Product nvarchar(20),
@Planogramref nvarchar(20),
@DisplayGroup nvarchar(5),
@ModSequence int,
@Shelfnumber int,
@Productposition int,
@StartModnumber nvarchar(20)
AS
SET NOCOUNT ON
BEGIN
if object_id('tempdb..##S093_TempTable') is not null
drop table ##S093_TempTable
CREATE TABLE ##S093_TempTable(
[DetailRECTYPE] [nvarchar](5) NULL,
[StoreNumber] [int] NULL,
[Product] [nvarchar](20) NULL,
[Planogramref] [nvarchar](20) NULL,
[DisplayGroup] [nvarchar](5) NULL,
[ModSequence] [int] NULL,
[Shelfnumber] [int] NULL,
[Productposition] [int] NULL,
[StartModnumber] [nvarchar](20) NULL
)
INSERT INTO ##S093_TempTable
([DetailRECTYPE]
,[StoreNumber]
,[Product]
,[Planogramref]
,[DisplayGroup]
,[ModSequence]
,[Shelfnumber]
,[Productposition]
,[StartModnumber])
VALUES
( @DetailRECTYPE ,
@StoreNumber ,
@Product ,
@Planogramref ,
@DisplayGroup ,
@ModSequence,
@Shelfnumber ,
@Productposition ,
@StartModnumber
)
If i have a select query after the Data Flow task only the last row is returned.
Now how to make the ##Table keep all the rows.
Any help will appreciated.
Thanks in Advance...
Cheers
Srikanth Katte
I have the following data
MASTER
id
name
DETAIL
id
master_id
name
I want a perform a query where i can get all the rows of the master table which have no relationed rows in detail table.
How can I do that???
I'm attempting to load some data into an explicit hierarchy in MDS 2012 via the staging table and struggling with the HierarchyName field. Specifically I'm loading data into stg.[Entity Name]_Consolidated and using the exact name of the explicit hierarchy I've set up in the front end web application.
Originally my hierarchy was labelled "Reporting Hierarchy" and when loading the data into staging using this name then running the batch from the Import Data screen I can see the error message "Error - The HierarchyName is missing or is not valid.". I've checked the table mdm.tblHierarchy and can see that the name there is exactly as it was in the staging table and have since renamed the hierarchy as "Reporting_Hierarchy" with the same results.
I have a large table with 100 Million records that has around 1 million duplicate records that need to be deleted.
I am running a script that creates a staging table called,DuplicateTable that collects all the duplicates and then I want to write a an effecient delete statement.
Is it possible to write something like:
delete from OrigTable O join DuplicateTable D
on O.Key = D.key
Or do I have to run a loop on the DuplicateTable and run a delete statement record by record ?
In Master tabel i have these date datas
2015-05-10
2015-05-11
2015-05-12
SO when i try to load from Master table to parent and child table i am using using expresssion like
select B.ID,A.* FROM FLATFILE_INVENTORY AS A JOIN DMS_INVENTORY AS B ON
A.ACDealerID=B.DMSDEALERID AND A.StockNumber=B.STOCKNUMBER AND
A.InventoryDate=B.INVENTORYDATE AND A.VehicleVIN=B.VEHICLEVIN
WHERE convert(date,A.[FtpDate]) = convert(date,GETDATE()) and convert(date,B.Ftpdate) = convert(date,getdate()) ;
If i use this Expression i am getting the current system date data's only from Master table to parent and child tables.
My Problem is If i do this in my local sserver using the above Expression if i loaded today date and if need to load yesterday date i can change my system date to yesterday date and i can run this Expression.so that yeserday date data alone will get loaded from Master to parent and child tables.
If i run this expression to remote server i cannot change the system date in server.
while using this Expression for current date its loads perfectly but when i try to load yesterday data it takes current date date only not the yesterday date data.
What is the Expression on which ever date i am trying load in the master table same date need to loaded in Parent and child table without changing the system Date.
In SQL Server 2005,here are two tables, created by the following SQL Statements:
CREATE TABLE student(
ID CHAR(6) PRIMARY KEY,
NAME VARCHAR(10),
AGE INT
);
CREATE TABLE score(
ID CHAR(6) PRIMARY KEY,
SCORE INT,
FOREIGN KEY(ID) REFERENCES student(ID)
);
For the length of Column ID is not enough, So I want to alter its length.The alter statement is:
ALTER TABLE student ALTER COLUMN ID CHAR(20)
For the table student is referenced by table score, the alter statement can not alter the column of the table student, and the SQL Server DBMS give the errors.
But, I can manually alter the length of the column ID in SQL SERVER Management Studio. How to alter column length of the master table(student) along with the slave table(score)?
Thanks!
I am stuck in the following scenario.
Tbl_Loan
Trans_ID
Emp_ID
Guarantor_1_ID
Guarantor_1_ID
TRN_01
EMP_01
[code]....
Now i have master Table for a device Utility. There is a attribute called "Device Type " in the table. Every Device Type has specific Device Attributes associate with it . Now attribute of Diffrent Device type are stored in Different Tables. Now when i select a particular value of Device Type ( lets say Type 1 or TYPE 2 ... ) then the table with has the attribute associated with particuter device type only has to be selected .
So how can I do this ???
How to form a realtion between the tables,... ????
We are designing a Staging layer to handle incremental load. I want to start with a simple scenario to design the staging.
In the source database There are two tables ex, tbl_Department, tbl_Employee. Both this table is loading a single table at destination database ex, tbl_EmployeRecord.
The query which is loading tbl_EmployeRecord is, SELECT EMPID,EMPNAME,DEPTNAME FROM tbl_Department D INNER JOIN tbl_Employee E ON D.DEPARTMENTID=E.DEPARTMENTID.
Now, we need to identify incremental load in tbl_Department, tbl_Employee and store it in staging and load only the incremental load to the destination.
The columns of the tables are,
tbl_Department : DEPARTMENTID,DEPTNAME
tbl_Employee : EMPID,EMPNAME,DEPARTMENTID
tbl_EmployeRecord : EMPID,EMPNAME,DEPTNAME
How to design the staging for this to handle Insert, Update and Delete.
In my ETL job I would like to truncate stg table but before truncating stging table, I want to make sure that all the records are inserted in the data model. The sample is as below
create table #stg (
CreateID int,
Name nvarchar(10)
)
insert into #stg
select 1, 'a' union all
select 2, 'b' union all
[Code] ....
How can I check among these tables and make sure that all values are loaded into the data model and the staging table can be truncated.
Sir,
Now I m also into an migration project Sir Can you help me
to How to Update table from flat file ..
datamigration needs to be done from flat file .
if the value in the flat file already exist then its an update else i should insert new record in the table.
Any idea?
Hi..
I want to update table using SSIS. I figured that there are two ways of it:
1. Use SQL command (but I cant use it as I have millions of rows)
2. Put updated rows in temp table and perform the batch update using Execute SQL task. (If possible, I dont want to use temp table)
Is there any other way?
Thanks
I am having one store procedure which use to load data from flat file to staging table dynamically. everything is working fine.Staging_temp table have single column.All the data stored in that single column below is the sample Data.
AB¯ALBERTA ¯93¯AI
AI¯ALBERTA INDIRECT ¯94¯AI
AL¯ALABAMA ¯30¯
[Code]....
where including/excluding a single column in an empty staging table would influence a resultset returning from distributed query? Both servers are SQL Server 2012. Nothing special about the staging table. It contains 12 columns with a mixture of INT and NVARCHAR(256) columns. In one case I exclude the column and the query returns in 17 seconds. When I include it the query does not return. Excluding the INSERT INTO the staging table and query returns in 17 secs with and without the column.
View 3 Replies View RelatedI am working on an HR project and I have one final component that I am stuck on.
I have an Excel File that is loaded into a folder every month.
I have built a package that captures the data from the excel file and loads it into a staging table (transforming a few bits of data).
I then combine it with another table in a view.
I have another package that loads that view into a Master table and I have added a Slowly Changing Dimension so that it only updates what has been changed. (it’s a table of all employees, positions, hire dates, term dates etc).
Our HR wants to have this data in a report (with charts and tables) and they wanted it to be in a familiar format. So I made a data connection with Excel loading the data into a series of pivot tables.
I have one final component that i cant seem to figure out. At the end of every year I need to capture a count of all Active Employees and all Termed employees for that year. Just a count.
So the data will look like this.
|Year|HistoricalHC|NumbTermedEmp|
|2010|447 |57 |
|2011|419 |67 |
|2012|420 |51 |
The data is in one table labeled [EEMaster]. To test the count I have the following.
SELECT COUNT([PersNo]) AS HistoricalHC
FROM [dbo].[EEMaster]
WHERE [ChangeStatus] = 'Current' AND [EmpStatusName] = 'Active'
this returns the HistoricalHC for 2013 as 418.
SELECT COUNT([PersNo]) AS NumbOfTermEE
FROM [dbo].[EEMaster]
WHERE [ChangeStatus] = 'Current' AND [EmpStatusName] = 'Withdrawn' AND [TermYear] = '2013'
This returns the Number of Termed employees for 2013 as 42.
I have created a table to report from called [dbo.TORateFY] that I have manually entered previous years data into.
|Year|HistoricalHC|NumbTermedEmp|
|2010|447 |57 |
|2011|419 |67 |
|2012|420 |51 |
I need a script (or possibly a couple of scripts) that will add the numbers every year with the year that the data came from.
(so on Dec 31st this package will run and add |2013|418|42| to the next row, and so on.
I have two tables each with a date field. I have to combine these two tables and sort on the date. I want the date from each table to be on the same column though, not in seperate columns.
Ex:
Table A
ID
Log_Time
ETI
Table B
ID
Log_Time
Action
Description
Thanks for the help
Rock *
thanks for reading.
kinda like like a UNION for columns maybe. i think that's what i'm looking for. take a look if you have a minute....
3 queries, each pretty clear (count the occurrences of a value in a column --> where someFlag='Y')...
SELECT Count(flag_A) as 'flag_A_confirmed', Year
FROM cases
WHERE flag_A = 'Y'
GROUP BY Year
SELECT Count(flag_B) as 'flag_B_confirmed', Year
FROM cases
WHERE flag_B = 'Y'
GROUP BY Year
SELECT Count(flag_C) as 'flag_C_confirmed', Year
FROM cases
WHERE flag_C = 'Y'
GROUP BY Year
but my desired output merges the columns together in one table ....
total confirmed flags for that year by type...
Yearflag_Aflag_Bflag_C
1998121533
1999161325
2000191421
2001... etc
i'm pretty sure i can do this using temp tables. any other ideas?
thanks for your input in advance. again.
I need to populate a table from several sources of raw data. For agiven security (stock) it is possible to only receive PARTS ofinformation from each of the different sources. It is also possibleto have conflicting data.I am looking to make a composite picture of a given security using thefollowing rules:1) The goal is to replace all NULL and Blank values with data2) Order of precedence (from highest to lowest) is Non-NULL Non-Blank--> Blank --> NULL3) In the case of Non-NULL Non-Blank values that conflict (aredifferent) leave existing value (even if NULL or Blank)For example:Given the following rows:Symbol Identity IdSource Exchange Type SubType Name-------- ------------ --------- --------- ------- ---------------------------TZA 901145102 CUSIP XNYS Stock NULL TV AZTECATZA 901145102 NULL NULL NULL NULLWSM 969904101 CUSIP XNYS Stock NULL WILLIAMSSONOMAWSM 969904101 NULL XNYS Stock NULLWILLIAMS-SONOMAWSM CUSIP XNYS Stock Common NULLWSM NULL CUSIP XASE Stock NULL WILLIAMSSONOMATYC 902124106 CUSIP XNYS Stock NULL TYCOTYC 902124106 CUSIP XNYS Stock NULL TYCOINTERNATIONALI am looking for the following results ('*' indicates changed value)Symbol Identity IdSource Exchange Type SubType Name-------- ------------ --------- --------- ------- ---------------------------TZA 901145102 CUSIP XNYS Stock NULL TV AZTECATZA 901145102 *CUSIP *XNYS *Stock NULL *TV AZTECAWSM 969904101 CUSIP XNYS Stock *Common WILLIAMSSONOMAWSM 969904101 *CUSIP XNYS Stock *CommonWILLIAMS-SONOMAWSM *969904101 CUSIP NULL Stock Common NULLWSM *969904101 CUSIP XASE Stock *Common WILLIAMSSONOMATYC 902124106 CUSIP XNYS Stock NULL TYCOTYC 902124106 CUSIP XNYS Stock NULL TYCOINTERNATIONAL
View 6 Replies View Related