Help Required For Approach To Load Data Into Tables

Sep 27, 2007

Hi , I am loading the Data into the Tables with the constraints on and redirecting the error rows into a seperate table is there a way to capture the error rows from a execute sql task by directly loading data without constraints and later adding them with the execute sql task and redirecting them to error table as this approach would make the loads quicker. the approach now that i am using is on a row by row basis ..... and if i drop constraints and load data and then add constraints will this deposit the same error rows as in case of the current approach please send me ur suggestions

View 3 Replies


ADVERTISEMENT

Approach Help To Load Data From Flatfiles Into Relational Table Where Data Is Coming As Spaces In Few Columns From Flatfiles

Sep 18, 2007

Hi ,

My Input is a flat file source and it has spaces in few columns in the data . These columns are linked to another table as a foreign key and when i try loading them in a relational structure Foreigh key violation is occuring , is there a standard method to replace these spaces .

what approach should i take so that data gets loaded in a relational structure.

for example

Name Age Salary Address
dsds 23 fghghgh

Salary description level
2345 nnncncn 4

here salary is used in this example , the datatype is char in real scenario

what approach should i take to load the data in with cleansing the spaces in ssis

View 4 Replies View Related

SQL Server 2008 :: Incremental Load Only Required Data?

Apr 30, 2015

I have a table in Server A and it has 5 columns. One is Address & ID, CreateDatetime,..

I need to transfer data from this table from Server A to server B for a report pupose. The Address column in this table has some places two address in the table. I am giving ex below

Address
Houston, Dallas
Redmond
Sacramento
New Jersey, New York

I want to avoid the rows where address are two Houston, Dallas & New Jersey, NewYork to the destination table in server B and need to do incremental loads.

how to proceed with this?

The version we are using is Sql Server standard edition 2008r2

View 2 Replies View Related

Best Approach For A Required Entry

May 9, 2008

I have an online store with products and categories. The requirements are: Each product can be in one or more categories; a product must be in at least one category.

To accomplish the first requirement, I have a Product table, Category table, and a many-to-many Product2Category table. (Probably not relevant, but a requirement is the Category table is self-referencing to provide sub-categories.)

My question is for the second requirement that each Product be in at least one category. My assumption would be to add a column to the Product table called "ParentCategoryId", which is a foriegn key of the Category table's identity column.

What would be the best approach to ensure a product is in at least one category?

Thank you.

View 2 Replies View Related

T-SQL (SS2K8) :: Select Query To Pull Required Data From 3 Tables

Mar 19, 2014

I have three tables EmpIDs,EmpRoles and LatestRoles. I need to write a select Query to get roles of all employees present in EmpIDs table by referring EmpRoles and LatestRoles.

Where I stuck : The condition is first look into table EmpRoles and if it has more than one entry for a particular Employee ID than only need to get the Role from LatestRoles other wise consider the role from EmpRoles .

Example:

Create Table #EmpIDs (
EmplID int
)
Create Table #EmpRoles (
EMPID int,

[Code] ....

Employee ID 2 is having two roles defined in EmpRoles so for EmpID 2 need to fetch Role from LatestRoles table and for remaining ID's need to fetch from EmpRoles .

My Final Output of select query should be like below.

EmpID Role
1 Role1
2 Role2
3 Role1

View 5 Replies View Related

Transact SQL :: Load 2 Tables With Data From 1 Inventory?

Oct 3, 2015

To setup some data in some new tables in t-sql 2012, I need to create a relationship between 2 tables were data is obtained from an inventory file. The inventory file will be loaded to sql server 2012 from an excel spreadsheet.

1. Columns that will be loaded to the first table are:

1. Inventory category number,
2. Inventory date,
3. Inventory Category total amount, and
4. Inventory category quantity on hand.

This table will have a column called Item number. This will need to be the value of item number that is in the second table.

2. Columns that will be loaded to the second table are:

1. Item Number,
2. item date,
3. Item price, Item quantity on hand.

Thus can you show me the sql that will load the table into both tables? This sql would also need to show how to obtain the values of item number from the second table and place that value into the first table.

View 4 Replies View Related

SQL Tools :: Load Data From One Database To Another Using Task / Generate Script / Tables

Jun 15, 2015

I am trying to load the data from one data base to another using the Task->Generate Script->tables (data only).  So I saved the script of the data and when tried to run the script to the destination DB, getting the below error when ran the script.

Error: Visual Studio has encountered an exception. This may be caused by an extension.You can get more information by running the application together with the /log parameter on the command line, and then examining the file'C:UsersKK67AppDataRoamingMicrosoftAppEnv10.0ActivityLog.xm'. When tried opening the folder using the above path... don't see anything for AppData folder from that path.

View 2 Replies View Related

Integration Services :: How To Load Multiple Tables Data Into Single Excel File

Aug 26, 2015

My Requirement ,In Source Database 5 tables are there ( Emp,Loc,dept,Time,Product ), Destination is Single Excel file.But Dynamically how to load each table information to load into each sheet wise through SSIS Package?

View 3 Replies View Related

Integration Services :: Automate Process Using SSIS To Create Tables In Corresponding Database And Load Data

Oct 6, 2015

We are using SQL Server 2014 and SSDT-BI 2013. We have a reporting environment where business users create objects which need to be persisted for fiscal year reporting. Let's say for instance SQLSERVER1SRVR1 they create table objects like below in the reporting environment.

Accounting2014, Accounting2015 in AccountingDB; 
Sales2014, Sales2015 in SalesDB; 
Products2014, Products2015 in ProductsDB; 
Inventory2014, Inventory2015 in InventoryDB etc....

These tables are persisted for auditing in a different environment SQLSERVER2SRVR2 for finance & audit folks.We would want to automate this process using SSIS to create tables in corresponding database and load data. I tried using For Each Loop container but the catch is I could loop the source or destination but how do we loop on Source & Destination at the same time (i.e when source is in AccountingDB destination to be AccountingDB, source SalesDB then destination SalesDB so on etc....

View 6 Replies View Related

Not Able To Load The Application In Case Web Farm Garden When We Load Data Through Background Thread.

Dec 14, 2007

Hi,

Here I will describe my problem.
1. We are loading large amount of data from database on background thread which is starting on Application_start event in global.aspx.cs file.The data is later cached for subsquent request to improve the performance.
2. Now when we put the application on web farm garden, it is not able to load the application.
3. We are sending the request the servers through Router kind of application.
4 This application is working fine on single server enviornment.

Please help us.

Ajay Kumar Dwivedi

View 1 Replies View Related

Load All Data Without Knowing Old One Was Load In The Previous Time???

Apr 27, 2007

I just have done the SSIS example in the tutorial document included when install SQL 2005 ENT. I have a problem that whenever I test to run, the service load all data from source with out noticing about the data (I mean it load all the data to the destination), I do it several time and it continue to load all without checking. That mean the data is dublicated when the schedule run???



I think there should be a paramete or something like that to help the engine just load the new data to the destination. Could you help please?



Thank

View 3 Replies View Related

Best Approach To Encrypt Data?

Sep 12, 2007

Hi,

I want to encrypt certain data like password, ssn, credit card info etc before saving in database. Also, this encrypted data can be queried using standard SQL statements like:

select * from users where userid=454 and password = 'encrypted data'

The mechanism to encrypt data could be in a .net application. The code that does encryption/decryption should also be protected so that it doesnt work if it falls in wrong hands.

Can anyone suggest what would be the best way to accomplish above?

thanks,
dapi

View 3 Replies View Related

Data Normalization - Best Approach?

Feb 23, 2006

Hi!

I wonder what would be the best (at to be honest - how to do it at all) to perform data normalization with SSIS. The scenario is as follows:
I got plain table with several columns in it.Some of columns can be copied straight into destination tableSome columns (String) should be lookup in another table to get IDOn success just replace string with IDOn fail - create new record in lookup table and return newly created ID
Thanks for any ideas and maybe short samples

Anrijs Vitolins

View 1 Replies View Related

Best Approach To Move Data On A Same Server

Nov 26, 2007

I have staged my tables in a database which is in the same server as the destination database and they are on sql server 2005.

Now I need to push the data from the staged table to destination.


Which is the best approach in ssis ?


1) using execute sql task to a call stored procedure to push the data to a different database using server.dbo.table name from the stored procedure.


or


2) using dataflows to call a stored procedure and map source and destination.

View 13 Replies View Related

History/Data Change File Approach

Mar 4, 2005

I need to record in a table:
Who, When, What Field and New Value of Fields
When changes occur to an existing record.

The purpose is for users to occassionally view the changes. They'll want to be able to see the history of the record - who changed what and when.

I figured I'd add the needed code to the stored procedure that's doing the update for the record.

When the stored procedure is called to do the update, the PK and parameters are sent.

The SP could first retain the current state of the record from the disk,
then do the update, then "spin" thru the fields comparing the record state prior to the update and after. Differences could be parsed to a "Changes string" and in the end, this string is saved in a history record along with a few other fields:

Name, DateTime, Changes

FK to Changed Record: some int value
Name: Joe Blow
Date: 1/1/05 12:02pm
Changes: Severity: 23 Project: Everest Assigned Lab: 204

How does the above approach sound?

Is there a better way you'd suggest?

Any sample code for a system that spins thru the fields comparing 1 temporary record with another looking for changes?

Thanks,

Peter

View 3 Replies View Related

ETL Delta Pulling Huge Data.. Right Approach ?

Dec 3, 2006

Hi all,

In an approach of building an ETL tool, we are into a situation wherein, a table has to be loaded on an incremental basis. The first run all the records apporx 100 lacs has to be loaded. From the next run, only the records that got updated since the last run of the package or newly added are to be pulled from the source Database. One idea we had was to have two OLE DB Source components, in one get those records that got updated or was added newly, since we have upddate cols in the DB getting them is fairly simple, in the next OLEDB source load all the records form the Destination, pass it onto a Merge Join then have a Conditional Split down the piple line, and handle the updates cum insert.

Now the question is, how slow the show is gonna be ? Will there be a case that the Source DB returns records pretty fast and Merge Join fails in anticipation of all the records from the destination ?

What might be the ideal way to go about my scenario.. Please advice...

Thanks in advance.

View 13 Replies View Related

Combining 3 Tables To Get Required Results

Jul 2, 2014

I have a quick question for the SQL community about how to combine 3 tables to get the results needed...

The table names are :
setup_zipcode,
setup_category,
and listing

and inside setup_zipcode it has the columns:
zip_id , zip_code, zip_latitude and zip_longitude

and inside the setup_category it has the columns:
category_id, category_parent, category_path and category_name

and the final table for listing has the columns:
listing_id, listing_member, listing_category, listing_address, listing_city, listing_state, listing_zip and listing_country

I am having trouble getting them to inter-relate an query the needed results as I need to get back the LAT & LONG from the zip_latitude & zip_longitude columns for specific listing ids in certain categories...

So, the ONLY same variable between them is that listing_zip from LISTING table and zip_code from SETUP_ZIPCODE show the same zip codes..

How can I create a SQL query that checks the current category that is being displayed on the page results live and insert only the listing id (clients) that are in that listing_category and also pull that listing_id client's related zip_latitude & zip_longitude that relates to their specific listing_zip from the zip_code row in setup_zipcode?

I have tried many things and this looks like it would work but does not pull the related LAT & LONGs ...

$cat is assigned in a query previously on the page...

SELECT *
FROM
setup_zipcode, setup_category, listing
WHERE
listing_category LIKE '%-$cat-%'
AND
listing_zip = zip_code
AND
category_id = '$cat'
ORDER BY listing_title ASC

When I try to take the results (not sure if I am missing a step for printing the results after querying them or having to assign them somehow) and use the SMARTY TAGS assigned to zip_latitude and zip_longitude nothing shows on the published page... The other variables for the address do..

I have a loop defined as $listing_id and section is var so when I pull the query info using {$listing_address[var]}, {$listing_city[var]}, etc.. they work, but {$zip_latitude[var]} or {$listing_id[var].$zip_latitude} and {$listing_id[var].zip_latitude} so on do not work..

If I can get the variables to exist in the SQL QUERY then at least I will know that is correct and can work on how to get them to show correctly in the address for a map afterwards...

View 1 Replies View Related

Parameters Approach To Fill Report Header With Source Data Doesn't Work

Jan 19, 2007

It's well known issue, that one can't use any dataset fields in a
report header/footer directly. One of the approach is to create
query-based parameter that basically equals
=First(Fields!@FieldName@.Value, "@DataSetName@") and use that
parameter value instead. But it doesn't work in my case!



My report displays some entity description and is parametrized with
EntityID param. Its header contains entity name that, according to the
approach, is queried from the data source through the EntityName
report parameter. There's important issue: the report is displayed in
ReportViewer control, that is embedded into my application and entity
ID parameter isn't ser by user in ReportViewer parameters area. Its
default value is changed by the application with SetReportParameters()
web method every time a user wants to view the report according to the
entity the user is exploring in the application. But after the report
has been rendered, its header always contains not actual (outdated)
entity name. Nevertheless, the report body contains actual data
(including entity name). If I alter entity ID parameter in ReportViewer
or in web-based Report Manager and refresh report, header displays
correct entity name.



What's wrong in the workflow described?

View 3 Replies View Related

Load 4 Different Csv Files Into 4 Different Tables?

Feb 10, 2015

I have written bulk insert and doing this manully but need to automate it and file location changes evertime .

BULK INSERT dbo.alpha
FROM 'C:posted 2015-01-16HOSPITALHOSPFY2010hosp_2010_ALPHA.CSV'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '',
ROWS_PER_BATCH = 10000,
TABLOCK
)
BULK INSERT dbo.RPT
FROM 'C:posted 2015-01-16HOSPITALHOSPFY2010hosp_2010_RPT.CSV'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '',
ROWS_PER_BATCH = 10000,
TABLOCK
)

how to proceed?

View 6 Replies View Related

DTS Load To Multiple Tables

Feb 17, 2006

I have SQL Server 2003 Standard and am attempting to use DTS for a data load/transformation and I€™m not sure if I am using the right tool for the job. I have a somewhat denormalized Access database that has to be loaded into a normalized SQL Server database. Values from one row in any of the source tables generally need to be separated and inserted into several destination (SQL Server) tables. There are no unique ids in the source data since it is coming from a 3rd party and the tables are not related to others. I€™ve created a DTS Package and have the beginnings of several Transform Data Tasks. Each destination table has an Identity id column, which is calculated automatically. The roadblock I€™ve run into is that I can€™t figure out how to take each newly created ID and insert it into a new row in another table as a foreign key. Basically, I have to move data from a single input row to new rows in multiple destination tables and create ids (PK, FK) that tie these tables together. The Transform Data Task only allows me to reference one source and one destination, not multiple destinations. I hope this makes sense. Any suggestions would be appreciated.

Here€™s a simple example that may help illustrate the problem. My database is much more complex.

Input table is called parcels and each row has three columns: Address, Owner, and Legal_Description.

Output database has two tables that will receive this data: Parcel table will have the Address, Owner and Parcel_ID (auto calculated). Legal table will have Legal_Description, Legal_Desc_ID (auto calculated), and Parcel_ID.

When the row is inserted into the Parcel table, the newly auto calculated Parcel_ID has to be captured. Next create a row into the Legal table and insert the Parcel_ID so the two rows are related.

How can I do this through DTS? Thanks for any suggestions, code snippets, or references.

View 1 Replies View Related

How To Load Some Tables/views To Memory?

Feb 12, 2008

This is a SQL2000 sp4 Ent. machine.
I was heard that loading certain tables/views (the most frequently used ones) could help in performance.

How could i do that?

thanks
david

View 4 Replies View Related

How Load Tables Form One Database To Another

Aug 1, 2001

Hi,
My problem is I only can put the database table to another database.I use example to ********. For example. My database name is Career. The user has a database name called Online. The Online has already some tables in the database they created before. I just need load my database career tables to the Online database. My database career has seven tables. These tables has a reltionship for each other. I just need to load these table to the Online database, But don't build relation to the tables that already exist in the Online database.
Besides, I want to know how load the stored procedure from my database to another database.

View 1 Replies View Related

DTS Load Master File To Different Tables

Feb 24, 2004

We need to load a "master" flat file to SQL Server tables. The file is a dump from mainframe. Based on a field called "record_type", each record in the file has different columns. I would use the following as an example (the real file is much more complicated than this, but you get the idea):

For instance, my file has:

20M02221984PAPepsi1000
23F11121987MD1000
01M09182003TXCocacola1100
34F03041970DC900

If "M", the fields are "age", "gender", "birthdate", "state", "salary"

If "F", the fields are "age", "gender", "birthdate", "state", "company", "salary"

We need to load the file (only one file) into two different tables, M_table, and F_table. But I have researched and discovered in DTS the source (TEXT file) can not be queried against to filter on the gender field.

Since each record may have different number of fields, I cannot really load the flat file into a "staging" table.

Does anyone has any idea on how to achieve this? Thanks in advance!!!

View 3 Replies View Related

Load Text Files In To Tables

Oct 15, 2007

Hi all,

I have around 140 text files in a folder where all the file entries are delimited by tabs. Currently i am copying the data and then pasting it in to the tables and it is perfectly working fine. I have already created tables for those 140 files. But now i want to load those files in to the table at a single shot. How can i do it? I am using SQLServer 2005.

Sarvan

View 2 Replies View Related

Load XML Files Into Tables Via SSIS

Jul 19, 2007

Hi



I am new to SSIS and am looking to load XML files (with a DTD definition) into tables via a SSIS package. I have created a XML task and am able to load the XML and output it to file. I have also stripped out the DTD definition and am able through a dataflow using XML source an OLE db Destination load and map the XML to table sin my DB. But have no idea how to get the data in when it has a DTD definition included. I either want to put each file into a row in a table then query it. Or from the SSIS package input the relevant info into a set of staging tables or the real tables.



Any help/pointers would be appreciated.

View 10 Replies View Related

How To Implement Incremental Load In Fact Tables.

Feb 19, 2007

Hi all,

i have a fact table which loads through package,when
i m trying to load this table by running the package,i m truncating the
fact table and loading the fresh data,instaed of this without
truncating the fact table i have to implement the incremental logic in
this.


For this i can use SCD or Conditional split,but problem
here is i have many source tables to load this fact table,so its
very difficult to trace the changes in different source tables.

can any one help me out in this??????

View 1 Replies View Related

Load Ordering For Dimension And Fact Tables

Sep 22, 2006

Hi ,

I have situation where I get data from SRC Flat file and have to load Dimensional table and also fact table, using same data flow(have no other choice since I have to unpivot some src data). Since I have to load both tables in same data flow, I have to have a way to put load ordering constraint (I know informatica allows that). Does any one have any idea on how this can be done in SSIS?

I would be really grateful.

Thanks

View 3 Replies View Related

Dynamicly Load Tables For DataSources/DataDestinations

Apr 1, 2006

Hi everybody,

The names of the tables that sould be transfered from the production system to the DWH are stored in a table in the production system. Yet I haven't found a proper way to dynamicly define these tables as DataSources and DataDestinations within an Integration Services project.

I hope somebody can help me. Thanks.

View 1 Replies View Related

SSIS Packages That Load Different Text Files Into Tables

Jan 25, 2013

So i have about 80 different SSIS PAckages that load different text files into tables. each package has a config file that contains password, server name and user id. I want to forward these packages to a use who will execute them. So instead of giving the user 80 packages to execute manually, how do i put these packages into one parent package so that the user has to only execute this parent package instead of executing 80 different packages NB. package exec is manually. (we will automate later) so this is a temp measure.

View 2 Replies View Related

SQL Server 2012 :: Load Folder Name And Images Into Tables

Dec 8, 2014

I need to load images from folders into a SQL Server table and I have done it successfully for individual images, however I need to load all the names of the folders and sub folders names in separate columns + load all images.

So the folders look like as in the screenshot and the final result of the table in SQL Server should look like the second screenshot.

View 9 Replies View Related

Integration Services :: Load Different Source Files Into Tables

Oct 12, 2015

I have one small requirement.. I want to load the different types of files(.txt, .csv, .tsv, .xlsx).

Using one forearch loop container how can I load the files to database and I shouldn't use the script task to split the filenames. Is there any other way to load all the files using forearch loop container, exesql task..

View 2 Replies View Related

Sample Data Required

Feb 13, 2007

Hello.

Though this is not directly a SQL related question but I could not think of some other forum to post this request. <y apologies if I should have posted elsewhere...


I need some sample data for a departmental store (like WalMart of KMart) which sell a wide variety of things and probably everything under the sun :)

I need data in such a manner that we have 4-5 levels of data hierarchy. For example we can have a data hierarchy like follows:

Classification
Category
Sub Category
Product Group
Item


The above is just an example but I hope that most of you already know what I neeed. An example of data based on the following hierarchy can be as follows (shown in a tabular manner)


Classification | Category | Sub Category | Product Group | Item
---------------------------------------------------------------
Household Items| Electronics| Entertainment| Audio| SONY Room entertainment box, 2 speakers 300 W each, ....
Household Items| Electronics| Entertainment| Audio| Phillips Boom Box, ...specifications....
Household Items| Electronics| Entertainment| Video| SAMSUNG DVD Player model SG-17X5 ...
Household Items| Electronics| Entertainment| T.V | SONG TRINTORN 51" .....
...
...

Groceries | Drinks | Colas| Coke | COke 12-Pack Cans ....
Groceries | Drinks | Colas| Coke | COke 2.25 Litre Jumbo Pack..
Groceries | Drinks | Colas| PEPSI| PEPSI 2.25 Litre Jumbo Pack..
.....
...

Toys | Girls | Dolls| Barbie | Barbie princees model A21 ..
...
..



I hope the above provides you with a clear understanding of my requirement.

I'll be extremely grateful for your help.

Thanks & Regards.

View 4 Replies View Related

Sample Data Required

Feb 13, 2007

Sample data required


Hello.

Though this is not directly a SQL related question but I could not think of some other forum to post this request. <y

apologies if I should have posted elsewhere...


I need some sample data for a departmental store (like WalMart of KMart) which sell a wide variety of things and probably

everything under the sun :)

I need data in such a manner that we have 4-5 levels of data hierarchy. For example we can have a data hierarchy like

follows:

Classification
Category
Sub Category
Product Group
Item


The above is just an example but I hope that most of you already know what I neeed. An example of data based on the following

hierarchy can be as follows (shown in a tabular manner)


Classification | Category | Sub Category | Product Group | Item
---------------------------------------------------------------
Household Items| Electronics| Entertainment| Audio| SONY Room entertainment box, 2 speakers 300 W each, ....
Household Items| Electronics| Entertainment| Audio| Phillips Boom Box, ...specifications....
Household Items| Electronics| Entertainment| Video| SAMSUNG DVD Player model SG-17X5 ...
Household Items| Electronics| Entertainment| T.V | SONG TRINTORN 51" .....
...
...

Groceries | Drinks | Colas| Coke | COke 12-Pack Cans ....
Groceries | Drinks | Colas| Coke | COke 2.25 Litre Jumbo Pack..
Groceries | Drinks | Colas| PEPSI| PEPSI 2.25 Litre Jumbo Pack..
.....
...

Toys | Girls | Dolls| Barbie | Barbie princees model A21 ..
...
..



I hope the above provides you with a clear understanding of my requirement.

I'll be extremely grateful for your help.

Thanks & Regards.

View 3 Replies View Related







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