IMPORT new Data since last IMPORT - DTS/Stored Procs?

Hello:

I am not sure how to implement the following, but I believe it entails using DTS, and hopefully it is fine that I post it here b/c ultimately I will need this backend data for my frontend .aspx pages:

On a weekly basis, I need to IMPORT some data located on a remote Oracle DB into SQL Server 2k. Since there is so much data to transfer, I would only like to transfer the data that is new to the table since the last IMPORT, i.e. a week ago and leave behin the OLD data.

Is DTS the correct way to go or do I have more control via DTS with STORED PROCEDURES? Does anyone have any good references for me?

On a similar note, once this Oracle data is IMPORTED into a certain table, I would like to EXPORT some of these NEWLY acquired rows matching certain criteria into another table for auditing purposes. For this scenario, should I implement a TRIGGER UPDATE event here on the first table?

Any advice will be greatly appreciated!I wouldn't use a trigger.

You can use DTS to call stored procedures, so you can just do it all in one DTS package.

'a week ago and leave behin the OLD data' You need to filter the data coming in. You can pass variables to DTS jobs to alter the way they execute. I think you could pass in a variable to alter the where clause within the query you specify for DTS. I haven't used variables for that reason, so I 'm not sure. Anyway, schedule that with a job and pass in the appropriate date for your filter. That would get the import into SQL. Then run whatever procedures you need to clean up the data, check for validity, etc. Then call a step to export records based on whatever criteria. I'd do it all in one DTS package.

Here's a place to start
http://www.swynk.com/friends/green/DTSHowTo3.asp|||What DTS book do you recommend I acquire to learn more about passing variables to DTS and other related topics?

I also referenced the URL you gave, but I didn't believe it was too helpful in my case.

Thanks.|||I'm a google junkie; I don't have any books on DTS.

http://www.databasejournal.com/features/mssql/article.php/1461501

This link is on the page of the first link and gets a little bit more there. You have to use the same concept as in these examples.

What I would do is this:

1) create a DTS package that uses a query to pull from the oracle database. Just a canned one that has your desired date range specified so you can see what your where clause will look like.

2) save the package to a Visual Basic File. Open that file in Notepad and find the query you specified and you can see what all the object names/details are that you'll need to reference

3) Follow the examples in these two links to create an activex task in your package that will alter the query with variable that you pass into it. Actually, you don't need to pass in variables if you don't want. You could just put the logic into the activex task that determines, based on the current date, what the date range should be. It's just VBScript, so you have all the date/time functions available.

I know this is a little sketchy, but I used these examples to figure it out the first time so they do help. Swynk has some good stuff so you could search their site or join their forum also.

import excel

need some help with getting this stored procedure working.
I'm trying to import excel spreadsheet into an existing table
getting error message of syntax near "*"
===========================================
CREATE PROCEDURE [dbo].importExcel
AS
--Create linked server
EXEC sp_addlinkedserver 'ExcelSource', 'Jet
4.0','Microsoft.Jet.OLEDB.4.0','C:\temp\tables.xls ', NULL,'Excel 5.0'
GO
--login to linked server
EXEC sp_addlinkedsrvlogin 'ExcelSource', 'false', 'sa', 'Admin', NULL
GO
-- import spreadsheet data into database table...
SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
DECLARE @.SQLString1 VARCHAR(100)
SET @.SQLString1 = 'SELECT * FROM ExcelSource...[table$]'
DECLARE @.SQLString2 VARCHAR(100)
SET @.SQLString2 = 'INSERT INTO table ' + @.SQLString1
-- convert variable from VARCHAR to NVARCHAR
DECLARE @.S2 NVARCHAR(1000)
SET @.S2 = CAST(@.SQLString2 as NVarchar(1000))
EXECUTE sp_executesql @.S2
GO
EXEC sp_dropserver 'ExcelSource', 'droplogins'
GO
hi,
"TJS" <nospam@.here.com> ha scritto nel messaggio
news:10qlms38norp6e3@.corp.supernews.com
> need some help with getting this stored procedure working.
> I'm trying to import excel spreadsheet into an existing table
> getting error message of syntax near "*"
> SET @.SQLString1 = 'SELECT * FROM ExcelSource...[table$]'
what is the name of the Excel sheet?
usually it defaults to Sheet1$, so the correct syntax is
SET @.SQLString1 = 'SELECT * FROM ExcelSource...[Sheet1$]'
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||the name is correct , I am only testing at this time.
I did remove the extra GO statements and that seems to have resolved the
error messages.
I now have to find an ASP file to dynamically generate the sql statements
for each spreadsheet table and pass them into the stored procedure because
the live database tables have primary keys. That unfortunately requires
using column lists...
I can't believe somebody hasn't already done all this ?
sql

Import DBF data to a MySQL Table

Hi
I'm trying to sync a DBF file stored on my customer server with the
MySQL table on my server.
I'm still looking for an automatic way to do it, however, for now i'm
trying to find a way to create a small app that will help me do it
faster than manually.
My question is: how do I import the data from my DBF file to my MySQL
Server?
I already have the code to select data from the DBF, but can't seems to
be able to find a fast way to import it. I tried the way of inserting
each line, but its really slow since I have aroung 10,000 rows.
Any idea that would make the process faster ?
Thanks!
EricI think you bought a ticket to the wrong show. You should probably search
for a MySQL newsgroup...
<ericpoirier@.gmail.com> wrote in message
news:1149186767.242174.200480@.c74g2000cwc.googlegroups.com...
> Hi
> I'm trying to sync a DBF file stored on my customer server with the
> MySQL table on my server.
> I'm still looking for an automatic way to do it, however, for now i'm
> trying to find a way to create a small app that will help me do it
> faster than manually.
> My question is: how do I import the data from my DBF file to my MySQL
> Server?
> I already have the code to select data from the DBF, but can't seems to
> be able to find a fast way to import it. I tried the way of inserting
> each line, but its really slow since I have aroung 10,000 rows.
> Any idea that would make the process faster ?
> Thanks!
> Eric
>|||Yeah I know, new here and don't know how to delete posts.
Mike C# wrote:
> I think you bought a ticket to the wrong show. You should probably search
> for a MySQL newsgroup...
> <ericpoirier@.gmail.com> wrote in message
> news:1149186767.242174.200480@.c74g2000cwc.googlegroups.com...|||MySQL can import a comma separated value text file in a very short time. My
experience was 1.2 million records in about 10 seconds. If you can export th
e
MS SQL data as comma separated value file, then have a VB Script or program
to import the file into MySQL data base. There are several 3rd party
applications that will convert data from one database to another.
"ericpoirier@.gmail.com" wrote:

> Hi
> I'm trying to sync a DBF file stored on my customer server with the
> MySQL table on my server.
> I'm still looking for an automatic way to do it, however, for now i'm
> trying to find a way to create a small app that will help me do it
> faster than manually.
> My question is: how do I import the data from my DBF file to my MySQL
> Server?
> I already have the code to select data from the DBF, but can't seems to
> be able to find a fast way to import it. I tried the way of inserting
> each line, but its really slow since I have aroung 10,000 rows.
> Any idea that would make the process faster ?
> Thanks!
> Eric
>

Import Data Problem

Hi all...
I am trying to copy all tables, views, and stored procedures (except for only
one table the user does not want copied) from a database on a remote server to
another database on a local server. Every time I try to run this copy procedure,
it runs for a while and the throws the following error:
ALTER TABLE statement conflicted with TABLE FOREIGN KEY constraint
'Foreign_Key_Name'. The conflict occurred in database 'LocalDB', table
'Local_Table_Name'.
The source tables have multiple relationships defined to multiple fields in each
table, and I think this is where it is failing.
What can I do to work around this to get the object copied?
Thanks!
Do you get this error when you try to create the script or when you run the
script?
If you get it when you run the script you have a dependency problem. You
will have to investigate when the problem occurs and order these elements
of your script accordingly.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Darrell" <Darrell.Wright.nospam@.okc.gov> wrote in message
news:%235fjmbdFFHA.3616@.TK2MSFTNGP10.phx.gbl...
> Hi all...
> I am trying to copy all tables, views, and stored procedures (except for
only
> one table the user does not want copied) from a database on a remote
server to
> another database on a local server. Every time I try to run this copy
procedure,
> it runs for a while and the throws the following error:
> ALTER TABLE statement conflicted with TABLE FOREIGN KEY constraint
> 'Foreign_Key_Name'. The conflict occurred in database 'LocalDB', table
> 'Local_Table_Name'.
> The source tables have multiple relationships defined to multiple fields
in each
> table, and I think this is where it is failing.
> What can I do to work around this to get the object copied?
> Thanks!
|||I just modify and click through the Import Data Wizard, then click the button to
make it go, and sit back and wait. It shows progress, then after a loooong while
(because this is a large database), the error I described pops up. I also have a
DTS package built that I can alter and run. It only has the one task "Copy SQL
Server Objects". So, I am not quite sure what you mean by "order these elements."
Hilary Cotter wrote:
> Do you get this error when you try to create the script or when you run the
> script?
> If you get it when you run the script you have a dependency problem. You
> will have to investigate when the problem occurs and order these elements
> of your script accordingly.
>
|||take the problem table out and try to DTS it again, unfortunately this will
mean you are starting from scratch again. So you might want to find out what
tables have been successfully exported and then only DTS the tables which
are missing.
You might want also want to look at backing up the database and restoring it
on the destination server.
The problem is SQL Server uses a table called sysdepends to track
dependencies. This table can get out of sync by various database
operations - like alter proc statements.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Darrell" <Darrell.Wright.nospam@.okc.gov> wrote in message
news:exNXBHfFFHA.4072@.TK2MSFTNGP10.phx.gbl...
> I just modify and click through the Import Data Wizard, then click the
button to
> make it go, and sit back and wait. It shows progress, then after a loooong
while
> (because this is a large database), the error I described pops up. I also
have a
> DTS package built that I can alter and run. It only has the one task "Copy
SQL
> Server Objects". So, I am not quite sure what you mean by "order these
elements."[vbcol=seagreen]
> Hilary Cotter wrote:
the[vbcol=seagreen]
elements[vbcol=seagreen]

Import CSV file into SQL TABLE

Hi,
i try to import a csv file with tabulation field separator and linefeed as
rowfield.
I've write the following "stored procedure" but it don't import nothing.
My csv file have 41 colums and n rows; the colums is separated by "tab" and
some fields are blank.
Thank you for help!
Gianluca
CREATE PROCEDURE Import
AS
--Step 1: Build Valid BULK INSERT Statement
DECLARE @.SQL varchar(200)
TRUNCATE TABLE table_csv
SET @.SQL = "BULK INSERT table_csv
FROM 'G:\file_csv.dat'
WITH
( FIELDTERMINATOR = '\t' ,
ROWTERMINATOR = '\n' ,
FIRSTROW = 2
)"
--Step 2: Execute BULK INSERT statement
EXEC (@.SQL)
GOHi,
Try executing the BULK INSERT from Query Analyzer or Query Window (SQL
2005). This will through the exact error.
THanks
Hari
SQL Server MVP
"Gianluca Floris" <gianluca.flo_NOSPAM_ris@.tiscali.it> wrote in message
news:45055ce9$0$998$5fc30a8@.news.tiscali.it...
> Hi,
> i try to import a csv file with tabulation field separator and linefeed as
> rowfield.
> I've write the following "stored procedure" but it don't import nothing.
> My csv file have 41 colums and n rows; the colums is separated by "tab"
> and some fields are blank.
> Thank you for help!
> Gianluca
> --
> CREATE PROCEDURE Import
> AS
> --Step 1: Build Valid BULK INSERT Statement
> DECLARE @.SQL varchar(200)
> TRUNCATE TABLE table_csv
> SET @.SQL = "BULK INSERT table_csv
> FROM 'G:\file_csv.dat'
> WITH
> ( FIELDTERMINATOR = '\t' ,
> ROWTERMINATOR = '\n' ,
> FIRSTROW = 2
> )"
> --Step 2: Execute BULK INSERT statement
> EXEC (@.SQL)
> GO
>
> --
>
>

Import CSV file into SQL TABLE

Hi,
i try to import a csv file with tabulation field separator and linefeed as
rowfield.
I've write the following "stored procedure" but it don't import nothing.
My csv file have 41 colums and n rows; the colums is separated by "tab" and
some fields are blank.
Thank you for help!
Gianluca
--
CREATE PROCEDURE Import
AS
--Step 1: Build Valid BULK INSERT Statement
DECLARE @.SQL varchar(200)
TRUNCATE TABLE table_csv
SET @.SQL = "BULK INSERT table_csv
FROM 'G:\file_csv.dat'
WITH
( FIELDTERMINATOR = '\t' ,
ROWTERMINATOR = '\n' ,
FIRSTROW = 2
)"
--Step 2: Execute BULK INSERT statement
EXEC (@.SQL)
GO
--Hi,
Try executing the BULK INSERT from Query Analyzer or Query Window (SQL
2005). This will through the exact error.
THanks
Hari
SQL Server MVP
"Gianluca Floris" <gianluca.flo_NOSPAM_ris@.tiscali.it> wrote in message
news:45055ce9$0$998$5fc30a8@.news.tiscali.it...
> Hi,
> i try to import a csv file with tabulation field separator and linefeed as
> rowfield.
> I've write the following "stored procedure" but it don't import nothing.
> My csv file have 41 colums and n rows; the colums is separated by "tab"
> and some fields are blank.
> Thank you for help!
> Gianluca
> --
> CREATE PROCEDURE Import
> AS
> --Step 1: Build Valid BULK INSERT Statement
> DECLARE @.SQL varchar(200)
> TRUNCATE TABLE table_csv
> SET @.SQL = "BULK INSERT table_csv
> FROM 'G:\file_csv.dat'
> WITH
> ( FIELDTERMINATOR = '\t' ,
> ROWTERMINATOR = '\n' ,
> FIRSTROW = 2
> )"
> --Step 2: Execute BULK INSERT statement
> EXEC (@.SQL)
> GO
>
> --
>
>

Import Column Transformation

I have a SQL Server table that has a "Text" type column. RTF files are stored in this column. On a monthly basis, new or updated RTF files are dropped into a shared folder. I need to upload these into the SQL Server table. Reviewing some of the entries in this forum, I know I probably should be using the Import Column Transformation, but for the last week I can't get it to work successfully.

Another column in the table contains the unique file name(varchar). At runtime the user inputs the fully qualified path to the RTF files which I store in an SSIS variable. Within the package I use a Derived Column Transformation to concatenate these two elements and then pass them as input to the Import Column. I'm expecting the RTF file to come out as output.

How does the Import Column match up the file with the proper table row? Do I need to provide the looping mechanism or does the Import Column handle that?

I've been searching for a working example with no luck. Can someone provide or point to a good sample?

Thanks.

It sounds like you are close to having it working.

The Import Column transform doesn't handle looping itself... it will process multiple rows, though. Each row can specify a different file name. That file will be read and the contents placed in the output column for that row. So, if you have all the file names at once in a table, you can process them in one shot with the Import Column.

However, you may need to be using a foreach loop container in the control flow to get your file names one at a time. In that case, the data flow with your derived column and import column transforms would need to be within the loop as well.

Let me know if this helps.

Thanks
Mark

|||Thanks Mark, that got me further along. I got passed some of the errors I was gettting. I think I'm getting closer after realizing that I need to manually set the "FileDataColumnID". Still having problems with the looping though. Have you done or run across any working examples? I think that would really help. Tom

import accessfiles with stored procedures

Hi,
Could somebody tell me how to import access-files to a sql server database?
I would like to import all tables from access to the temp, do some
data-manipulation and then insert into a database. I know that dts can
import, but i want to address the tempdb so i don't have to create
temptables in my databases.
I hope someone can help me.Jason
One option is
INSERT INTO .....
SELECT *
FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="d:\northwind.mdb";
User ID=Admin;Password='
)...Customers
"Jason" <jasonlewis@.hotrmail.com> wrote in message
news:esocaw2EFHA.3416@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Could somebody tell me how to import access-files to a sql server
database?
> I would like to import all tables from access to the temp, do some
> data-manipulation and then insert into a database. I know that dts can
> import, but i want to address the tempdb so i don't have to create
> temptables in my databases.
> I hope someone can help me.
>|||Hi Uri,
I've tried to use your example, but i'm getting errors which says that it
cannot find the path, but i'm sure the file is there?
Here's the statement:
SELECT *
FROM OPENDATASOURCE( 'Microsoft.Jet.OLEDB.4.0','Data
Source="D:\klient.mdb";User ID=Admin;Password=')...sysdata
And here's the error:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: Could not find file 'D:\klient.mdb'.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: ].
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:etAE$62EFHA.2568@.TK2MSFTNGP10.phx.gbl...
> Jason
> One option is
> INSERT INTO .....
> SELECT *
> FROM OPENDATASOURCE(
> 'Microsoft.Jet.OLEDB.4.0',
> 'Data Source="d:\northwind.mdb";
> User ID=Admin;Password='
> )...Customers
>
> "Jason" <jasonlewis@.hotrmail.com> wrote in message
> news:esocaw2EFHA.3416@.TK2MSFTNGP09.phx.gbl...
> database?
>|||Jason
Have you ran on the server or client machine?
Where is the file's location on the server?
"Jason" <jasonlewis@.hotrmail.com> wrote in message
news:OY5T7zAFFHA.1564@.TK2MSFTNGP09.phx.gbl...
> Hi Uri,
> I've tried to use your example, but i'm getting errors which says that it
> cannot find the path, but i'm sure the file is there?
> Here's the statement:
> SELECT *
> FROM OPENDATASOURCE( 'Microsoft.Jet.OLEDB.4.0','Data
> Source="D:\klient.mdb";User ID=Admin;Password=')...sysdata
> And here's the error:
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
> [OLE/DB provider returned message: Could not find file 'D:\klient.mdb'.]
> OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
> IDBInitialize::Initialize returned 0x80004005: ].
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:etAE$62EFHA.2568@.TK2MSFTNGP10.phx.gbl...
>|||Hi Uri,
The files resides on a client pc.
Greetings,
J
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eU3lf$AFFHA.2156@.TK2MSFTNGP10.phx.gbl...
> Jason
> Have you ran on the server or client machine?
> Where is the file's location on the server?
>
> "Jason" <jasonlewis@.hotrmail.com> wrote in message
> news:OY5T7zAFFHA.1564@.TK2MSFTNGP09.phx.gbl...
it
can
>|||Then you need to specify an UNC path for the file, and the share need to acc
essible to SQL Server
and the account that SQL Server is using need to have permissions on the sha
re.
SQL Server is a *server* product, the queries are executed on the server, no
t the client. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jason" <jasonlewis@.hotrmail.com> wrote in message news:Oub81JCFFHA.3940@.tk2msftngp13.phx.g
bl...
> Hi Uri,
> The files resides on a client pc.
> Greetings,
> J
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eU3lf$AFFHA.2156@.TK2MSFTNGP10.phx.gbl...
> it
> can
>

Implicit Transaction mode in SQL Server 2000

Hi all:

I know i can use the sentence SET IMPLICIT_TRANSACTIONS ON in a Stored Procedure to force SQL Server to set the connection into implicit transaction mode.

Have i a sentence or configuration to force all SQL Server connections to implicit transaction mode?

Thanks in advance.

Nope, there is no way to change the default isolation level.

I have requested that this be added into the next release of SQL Server. Here is a link to the feature request https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=263746

|||Moving to appropriate forum from Documentation forum section.