Best approach for pushing records to MS Access

All,

I am new to DTS/SSIS and have a couple of questions about using it to solve a problem. We have an application running on SQL Server 2005 where status records are written to a status table. I need to be able to send those records over to a status table in a legacy application running on Access.

Originally, I thought about writing a custom c# stored proc and accessing Access from it and then someone pointed me to DTS/SSIS.

Is there a way to exectute the package based on a trigger event that a row was inserted or updated? If not and I take a scheduled approach (every 3 minutes, etc.) do I have to maintain a column for the records that get processed so they are not picked up again.

In general is using SSIS the approach to take? The overall business requirements are straight forward, but I am not sure if SSIS is overkill for this or not.

Thanks,

Steve

If I use an Execute SQL Task on the Control Flow, how do I use that resulting dataset as a Data Source on the Data Flow? I added a variable named 0 and type object, but I cannot figure out how to reference it on the Data Flow designer tab.

Beginner question about inserting records

Hi,
I am new to SQL Server (version 8) and hope that someone can help me
solve a problem.
A colleague has set up a SQL Server database with several tables for
which he has constructed multiple applications. As a result, their
structure cannot be altered in any way.
I have received updates for the data -- but the updates are in dbase
files. I would like to insert these dbase files into the existing table
structures (a simple thing to do in Foxpro), but I can't seem to figure
out how to do it in SQL Server.
Can someone point me in the right direction? (I've searched the
archives and gotten a few hints about bulk insert but I'm missing some
critical piece of the puzzle.)
Many thanks,
Jo<jacmcginty@.gmail.com> wrote in message
news:1116859509.543432.227390@.g14g2000cwa.googlegroups.com...
> Hi,
> I am new to SQL Server (version 8) and hope that someone can help me
> solve a problem.
> A colleague has set up a SQL Server database with several tables for
> which he has constructed multiple applications. As a result, their
> structure cannot be altered in any way.
> I have received updates for the data -- but the updates are in dbase
> files. I would like to insert these dbase files into the existing table
> structures (a simple thing to do in Foxpro), but I can't seem to figure
> out how to do it in SQL Server.
> Can someone point me in the right direction? (I've searched the
> archives and gotten a few hints about bulk insert but I'm missing some
> critical piece of the puzzle.)
> Many thanks,
> Jo
>
Jo, Take a look at BULK INSERT and bcp from the books online. If these
will not suffice and you must perform transformations (data validation,
re-sequencing of keys, normalizing values (yes = 1, no = 0 etc).on the data
as it comes in., then take a look at Data Transformation Services (DTS).
DTS has a wizard for doing imports like you are talking about.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||BULK INSERT is a TSQL command, but it doesn't understand complex file formats. What you should be
looking at is "DTS". which is a COM objects for import/export. There are three tools in SLQ Server
that uses this COM object:
DTS Wizard. This is the easiest one to use. Start here.
Package Designer. Use this after you done a few wizards.
DTSRUN.EXE. Schedule a package created with any of above two.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jacmcginty@.gmail.com> wrote in message
news:1116859509.543432.227390@.g14g2000cwa.googlegroups.com...
> Hi,
> I am new to SQL Server (version 8) and hope that someone can help me
> solve a problem.
> A colleague has set up a SQL Server database with several tables for
> which he has constructed multiple applications. As a result, their
> structure cannot be altered in any way.
> I have received updates for the data -- but the updates are in dbase
> files. I would like to insert these dbase files into the existing table
> structures (a simple thing to do in Foxpro), but I can't seem to figure
> out how to do it in SQL Server.
> Can someone point me in the right direction? (I've searched the
> archives and gotten a few hints about bulk insert but I'm missing some
> critical piece of the puzzle.)
> Many thanks,
> Jo
>|||Hi, and thank you. The DTS wizard did the trick for this particular
problem (although I see that bulk insert may be helpful in the future).
May I ask a couple of follow-up questions regarding DTS?
1. Is it possible, using the DTS wizard, to change a field name on the
fly?
For example, some of the data tables that I am updating include
underscores in the field names. I need to remove the underscores to
make the updates compatible with previous issues of the data. (I could
do it manually after the import is completed, but since I expect to
repeat this exercise frequently, it would be helpful if I could make
this change during the import.)
2. Once a DTS package has been saved, can it be edited?
For example, I realized after I had saved a DTS and run it that one of
my field types was incorrect, but I couldn't see how to edit the DTS.
(I ended up recreating the whole thing to fix the error.)
Many thanks,
Jo

Beginner prob: generating string

Help!

I'm new to T-SQL and trying to code a solve for the following problem, without a good idea of how I can make T-SQL jump through hoops. Here is the problem.

I need to populate a single string with values from a series of possible fields.

eg. a vehicle can be 4 wheel drive (4WD) and have air conditioning (Aircon).

So for each stock record I want to test each of a range of values (all or none can be true) in a column for a match. Based on that, add to the output string (that would look like "4wd ac ab abs").

So far I have :

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE proc [dbo].[qry_EXPERIMENT_02-12-2003]

--provides information for the 'notes' field of the results screen.
--default value: NULL
@.notesString nvarchar (30) = NULL

as

SELECT
tblStockExtra.StockID,

(
SELECT
CASE WHEN tblExtra.ExtraHeader = '4WD' THEN '4WD ' END,
CASE WHEN tblExtra.ExtraHeader = 'Aachk' THEN 'aa ' END,
CASE WHEN tblExtra.ExtraHeader = 'Aircon' OR tblExtra.ExtraHeader = 'DualAircon' OR tblExtra.ExtraHeader = 'ClimateAirCon' THEN 'ac ' END,
CASE WHEN tblExtra.ExtraHeader = 'ABS' THEN 'abs ' END,
CASE WHEN tblExtra.ExtraHeader = 'AlloyWheel' OR tblExtra.ExtraHeader = 'FactoryAlloyWheel ' THEN 'aw ' END,
CASE WHEN tblExtra.ExtraHeader = 'SRSAirBag' OR tblExtra.ExtraHeader = 'DualSRSAirBag' THEN 'ab ' END,
CASE WHEN tblExtra.ExtraHeader = 'CD' OR tblExtra.ExtraHeader = 'CDShuttle' THEN 'cd ' END

FROM tblExtra
INNER JOIN tblStockExtra ON tblExtra.ExtraID = tblStockExtra.ExtraID
INNER JOIN tblStock ON tblStockExtra.StockID = tblStock.StockID
) AS notes

FROM tblStockExtra, tblExtra

GROUP BY tblStockExtra.StockID, tblExtra.Extraheader

ORDER BY tblStockExtra.StockID

This doesn't work, as I assume a subquery can only return 1 value. I'm hoping someone knowledgable can see what I'm attempting to do and enlighten me!

Do I need to be using Dynamic SQL?

Many thanks for your thoughts and time,

KC.You are right, this will not work. It would be easier for us if you post :

a) table descriptions of tblStock, tblExtra, tblStockExtra
b) the foreign keys between those tables (that way we can have an idea about the relationships between them)
c) some sample data
d) an example of how you would like to output to be formatted|||Originally posted by cvandemaele
You are right, this will not work. It would be easier for us if you post :

a) table descriptions of tblStock, tblExtra, tblStockExtra
b) the foreign keys between those tables (that way we can have an idea about the relationships between them)
c) some sample data
d) an example of how you would like to output to be formatted

A) Many to many realtionship between tblStock and tblExtra with tblStockExtra as a bridging table
B) Referential integrity not enforced (Don't ask! Its not mine :) )
C)

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblExtra]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblExtra]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblStock]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblStock]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblStockExtra]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblStockExtra]
GO

CREATE TABLE [dbo].[tblExtra] (
[ExtraID] [int] NOT NULL ,
[ExtraDesc] [nvarchar] (255) NULL ,
[ExtraAbbrv] [nvarchar] (255) NULL ,
[ExtraHeader] [nvarchar] (50) NULL ,
[Group] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblStock] (
[StockID] [int] NOT NULL ,
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblStockExtra] (
[StockExtraID] [int] IDENTITY (1, 1) NOT NULL ,
[StockID] [int] NULL ,
[ExtraID] [int] NULL
) ON [PRIMARY]
SET IDENTITY_INSERT [dbo].[tblStockExtra] ON
GO

ALTER TABLE [dbo].[tblStockExtra] WITH NOCHECK ADD
CONSTRAINT [PK_tblStockExtra] PRIMARY KEY NONCLUSTERED
(
[StockExtraID]
) ON [PRIMARY]
GO

INSERT INTO tblStock
(StockID)
VALUES (64);
INSERT
INTO tblStock(StockID)
VALUES (65);
INSERT
INTO tblStock(StockID)
VALUES (467);
INSERT
INTO tblStock(StockID)
VALUES (487);
INSERT
INTO tblStock(StockID)
VALUES (944);

--4wd
INSERT INTO tblExtra
(ExtraID, ExtraDesc, ExtraAbbrv, ExtraHeader, [Group])
VALUES (41,'4 wheel drive', '4WD', '4WD', 14)

--aachk
INSERT INTO tblExtra
(ExtraID, ExtraDesc, ExtraAbbrv, ExtraHeader, [Group])
VALUES (52, 'AA check', 'AA', 'Aachk', 17)

--aircon
INSERT INTO tblExtra
(ExtraID, ExtraDesc, ExtraAbbrv, ExtraHeader, [Group])
VALUES (8, 'air-conditioning', 'AC', 'AirCon', 2)
INSERT INTO tblExtra
(ExtraID, ExtraDesc, ExtraAbbrv, ExtraHeader, [Group])
VALUES (9, 'climate air-conditioning', 'AAC', 'ClimateAirCon', 2)
INSERT INTO tblExtra
(ExtraID, ExtraDesc, ExtraAbbrv, ExtraHeader, [Group])
VALUES (10, 'dual air condtioning', 'DAC', 'DualAirCon', 2)

--abs
INSERT INTO tblExtra
(ExtraID, ExtraDesc, ExtraAbbrv, ExtraHeader, [Group])
VALUES (39, 'antilock braking system', 'ABS', 'ABS', 12)

--alloys
INSERT INTO tblExtra
(ExtraID, ExtraDesc, ExtraAbbrv, ExtraHeader, [Group])
VALUES (27, 'alloy wheels', 'AW', 'AlloyWheel', 9)
INSERT INTO tblExtra
(ExtraID, ExtraDesc, ExtraAbbrv, ExtraHeader, [Group])
VALUES (28, 'factory alloy wheels', 'FAW', 'FactoryAlloyWheel', 9)

--airbags
INSERT INTO tblExtra
(ExtraID, ExtraDesc, ExtraAbbrv, ExtraHeader, [Group])
VALUES (25, 'SRS airbag', 'AB', 'SRSAirBag', 8)
INSERT INTO tblExtra
(ExtraID, ExtraDesc, ExtraAbbrv, ExtraHeader, [Group])
VALUES (26, 'dual SRS airbag', 'DAB', 'DualSRSAirBag', 8)

--CD
INSERT INTO tblExtra
(ExtraID, ExtraDesc, ExtraAbbrv, ExtraHeader, [Group])
VALUES (13, 'CD', 'CD', 'CD', 3)
INSERT INTO tblExtra
(ExtraID, ExtraDesc, ExtraAbbrv, ExtraHeader, [Group])
VALUES (14, 'CD shuttle', 'CDS', 'CDShuttle', 3)

INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (1112,64,8)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (1113,64,12)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (1115,64,23)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (1116,64,15)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (1117,64,60)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (1118,64,7)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (1118,64,59)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (1119,65,8)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (1120,65,39)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (1121,65,14)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (1122,65,23)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (1123,65,26)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (1124,65,16)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (1125,65,15)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (1126,65,43)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (1127,65,7)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (1128,65,12)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (4357,467,7)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (4358,467,8)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (4359,467,11)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (4465,487,7)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (4466,487,8)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (4467,487,11)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (4468,487,67)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (9188,944,7)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (9189,944,8)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (9190,944,12)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (9191,944,16)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (9192,944,15)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (9193,944,20)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (9194,944,23)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (9195,944,28)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (9196,944,64)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (9197,944,41)

D) I want the output as: "Extra1 Extra2 Extra3"

Thanks for your help!

KC.|||As the list of extras linked to an item in StockId is unlimited, the list of extras is returned as a large string, seperated by a single character.

First of all, create the following function

create function dbo.fGetOptionList (@.CarId int, @.Separator char(1))
returns nvarchar(510)
as
begin

-- cursor that will browse throught the list of extra for the given car
declare cOptionList
cursor for
select
options.extraabbrv as OptionAbbrv
from tblstock as cars
inner join tblstockextra as cars_options
on cars.stockid = cars_options.stockid
inner join tblextra as options
on options.extraid = cars_options.extraid
where Cars.stockid = @.CarId

declare @.OptionAbbrv nvarchar(510)
declare @.AllOptions nvarchar(510)

open cOptionList
fetch next from cOptionList into @.OptionAbbrv
while @.@.fetch_status = 0
begin
set @.AllOptions = rtrim(isnull(@.AllOptions,'')) + rtrim(isnull(@.OptionAbbrv,'')) + @.Separator
fetch next from cOptionList into @.OptionAbbrv
end
close cOptionList
deallocate cOptionList

return @.AllOptions

end

Afterwards, just :
SELECT
stockid,
dbo.fgetoptionlist(stockid, ';') as OptionList
FROM tblstock

It will return :
stockid OptionList
---- -----
64 AC;
65 AC;ABS;CDS;DAB;
467 AC;
487 AC;
944 AC;FAW;4WD;

(5 row(s) affected)|||Wow!

Thanks for your time and energy! I truly wasn't expecting the solution on a plate! Please accept my sincerest thanks, you've saved me a great deal of effort!

I feel disappointed I can't reward you with a commendation as in other forums, but you've made my day!

:) :) :)

KC.