How to import several excel files using one dts package

It's very simple import, without any data modifications; from several
excel files to one table.
I tried the wisard and it gives me selection for only one file.
I am not used to create DTS packages and schedule jobs, so I would need
some help.

Thank you
InnaHi Inna,

Perhaps this article will be of help:
http://www.devx.com/getHelpOn/10MinuteSolution/18088

The article at the link above shows how to import Excel data into SQL
Server from an indeterminate number of Excel workbooks.

If the # of Excel files you have is small, you can do the following in
the DTS Designer:

1) Drag 1 Excel connection object per file into the DTS "canvas" (the
main area of the DTS Designer) and define the connections as
appropriate

2) Drag a connection object for your SQL Server and define it as
appropriate. One thing to note is that you should set the DB to connect
to the same as the DB you want to import the data into

3) Define a "Transform Data Task" (see your Tasks menu in the DTS
Designer) between each Excel connection and the SQL Server connection.

4) A line will appear between each Excel connection and the SQL Server
connection. Right click on each line and select "Properties" from the
menu that appears. Alter the properties of the task (e.g. column
mappings) as appropriate.

5) Execute the task

Hope that helps a bit.

How to import flat files into Microsoft SQL 2005 Express Edition automatically?

Iam aware that Microsoft SQL 2005 Express Edition does not have agent or DTS capabilities, but how may I automate an import of flat files to the DB tables? Must I use an external VB development of is there a way to schedule an import of flat files to Microsoft SQL 2005 Express Edition ?

hi,

you can perhaps have a look at the features provided by linked servers using JET data provider as reported in http://msdn2.microsoft.com/en-us/library/ms190479.aspx

say you have a situation like this, a flat.txt saved in d:\txt\flat.txt

<flat.txt>
FirstName|LastName
andrea|montanari
jr2006|jr2006
</flat.txt>

you can define a schema file (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp) like

<schema.ini>
[FLAT.TXT]
ColNameHeader=True
Format=Delimited(|)
</schema.ini>

and proceed, on SQL Server, registering a linked server based on JET 4.0 OLE DB provider, querying it and eventually importing data from it in a regular SQLX database table like

SET NOCOUNT ON;

EXEC sp_addlinkedserver txtsrv, 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'd:\txt\',
NULL,
'Text';

SELECT * FROM txtsrv...flat#txt;

EXEC sp_dropserver 'txtsrv', 'droplogins';

resulting in

--<--

FirstName LastName
-- --
andrea montanari
jr2006 jr2006

regards

|||

Andrea,

Thanks a bunch if your solution is as pretty as your name, then I believe we shall be OK.

regards

JR2006

|||

Andrea, Great solution and I am wondering if it will help me in what I am trying to accomplish. I have a CSV file that comes from another company. The file is a CSV only in the extension, since there are no commas or seperation in the data. here's what I am trying to do.

Here is a sample of the data and what I need to do with it.

1ETTX800401V411TTX BNSF851293
1ETTX800913V411TTX BNSF840295

This data needs to be brought into the data base as:

1 ETTX 800401 V 411 TTX BNSF 85 1293
1 ETTX 800913 V 411 TTX BNSF 84 0295

The seperation will ALWAYS occur after the 1, 5, 11, 12, 15,19, 23, 25 & 29th characters. Do you have any idea how I can accomplish this? Currently we are importing it into Access2003 and seperating it into the columns. Then merging it with the tables that it needs to be in. I would like to make this a simple broswe for the file, click the upload button and it sort and upload the information. Am I asking too much?

Let me know what you think.

Charles

|||

hi,

Kraven3d wrote:

Here is a sample of the data and what I need to do with it.

1ETTX800401V411TTX BNSF851293
1ETTX800913V411TTX BNSF840295

This data needs to be brought into the data base as:

1 ETTX 800401 V 411 TTX BNSF 85 1293
1 ETTX 800913 V 411 TTX BNSF 84 0295

The seperation will ALWAYS occur after the 1, 5, 11, 12, 15,19, 23, 25 & 29th characters. Do you have any idea how I can accomplish this?

you can define fixed width columns in the schema.ini as following

<flat.txt>
1ETTX800401V411TTX BNSF851293
1ETTX800913V411TTX BNSF840295
</flat.txt>

<schema.ini>
[Flat.TXT]
ColNameHeader=False
Format=FixedLength
Col1=Col1 Text Width 1
Col2=Col2 Text Width 4
Col3=Col3 Text Width 6
Col4=Col4 Text Width 1
Col5=Col5 Text Width 3
Col6=Col6 Text Width 4
Col7=Col7 Text Width 4
Col8=Col8 Text Width 2
Col9=Col9 Text Width 4
</schema.ini>

SET NOCOUNT ON;
EXEC sp_addlinkedserver txtsrv, 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'd:\Import\',
NULL,
'Text';
GO
CREATE TABLE #tText (
Col1 int,
Col2 char(4),
Col3 int,
Col4 char(1),
Col5 int,
Col6 varchar(4),
Col7 varchar(4),
Col8 int,
Col9 varchar(4)
);
SELECT * FROM txtsrv...flat#txt;

INSERT INTO #tText
SELECT CONVERT(int, Col1), Col2, CONVERT(int, Col3), Col4, CONVERT(int, Col5), Col6, Col7, CONVERT(int, Col8), Col9
FROM txtsrv...flat#txt;

SELECT * FROM #tText;
DROP TABLE #tText;
GO
EXEC sp_dropserver 'txtsrv', 'droplogins';

--<-
Col1 Col2 Col3 Col4 Col5 Col6 Col7 Col8 Col9
- - - - - - - -
1 ETTX 800401 V 411 TTX BNSF 85 1293
1 ETTX 800913 V 411 TTX BNSF 84 0295

Col1 Col2 Col3 Col4 Col5 Col6 Col7 Col8 Col9
-- - -- - -- - - -- -
1 ETTX 800401 V 411 TTX BNSF 85 1293
1 ETTX 800913 V 411 TTX BNSF 84 0295

then you can use the SELECT to perform whatever action you require... even populate a temp table, casting the data types as required ..

regards

|||Hi Andrea,

I'm trying to get it to work but I keep running into this error message:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication failed.
[OLE/DB provider returned message: Cannot start your application. The workgroup information file is missing or opened exclusively by another user.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].

Any ideas?

Thanks,

Bradley|||These approaches never occurred to me. I have found my heroine. Thanks for the great tips!

-B|||

B. Sandvik wrote:

These approaches never occurred to me. I have found my heroine. Thanks for the great tips!

-B

... I'm male

regards

|||

I guess they didn't go to your web site to see you picture like I did.

Mike

|||

[OT]

was looking for you in Redmond at the MVP summit...

I was able to meet Jens, but not you..

regards

|||

Will I need to define everything in the Flat.txt file? Here's what I mean.

I have entered only 2 lines in my original post, where my official txt file has over 1 million rows. The one I just got is comma delimited and I can use it as a qualifier if it will make it easier for me to import into my DB.

This is what the new file looks like:

1,AAMX,050167 ,C112,AAMX, , ., ,
1,AAMX,061468 ,C112,ACFX, , ., ,
1,AAMX,062068 ,C112,ACFX, , ., ,

I have 2 documents that I will need to do this on. 1 is the official EVERY entry and the other is an update file where I will need to see if a character has changed; if so update it, if not then it will leave it alone.

I know that that will be a simple update statement. But I will be getting a TXT file like this 2 times a month.

Any ideas would be helpful.

How to import flat files into Microsoft SQL 2005 Express Edition automatically?

Iam aware that Microsoft SQL 2005 Express Edition does not have agent or DTS capabilities, but how may I automate an import of flat files to the DB tables? Must I use an external VB development of is there a way to schedule an import of flat files to Microsoft SQL 2005 Express Edition ?

hi,

you can perhaps have a look at the features provided by linked servers using JET data provider as reported in http://msdn2.microsoft.com/en-us/library/ms190479.aspx

say you have a situation like this, a flat.txt saved in d:\txt\flat.txt

<flat.txt>
FirstName|LastName
andrea|montanari
jr2006|jr2006
</flat.txt>

you can define a schema file (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp) like

<schema.ini>
[FLAT.TXT]
ColNameHeader=True
Format=Delimited(|)
</schema.ini>

and proceed, on SQL Server, registering a linked server based on JET 4.0 OLE DB provider, querying it and eventually importing data from it in a regular SQLX database table like

SET NOCOUNT ON;

EXEC sp_addlinkedserver txtsrv, 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'd:\txt\',
NULL,
'Text';

SELECT * FROM txtsrv...flat#txt;

EXEC sp_dropserver 'txtsrv', 'droplogins';

resulting in

--<--

FirstName LastName
-- --
andrea montanari
jr2006 jr2006

regards

|||

Andrea,

Thanks a bunch if your solution is as pretty as your name, then I believe we shall be OK.

regards

JR2006

|||

Andrea, Great solution and I am wondering if it will help me in what I am trying to accomplish. I have a CSV file that comes from another company. The file is a CSV only in the extension, since there are no commas or seperation in the data. here's what I am trying to do.

Here is a sample of the data and what I need to do with it.

1ETTX800401V411TTX BNSF851293
1ETTX800913V411TTX BNSF840295

This data needs to be brought into the data base as:

1 ETTX 800401 V 411 TTX BNSF 85 1293
1 ETTX 800913 V 411 TTX BNSF 84 0295

The seperation will ALWAYS occur after the 1, 5, 11, 12, 15,19, 23, 25 & 29th characters. Do you have any idea how I can accomplish this? Currently we are importing it into Access2003 and seperating it into the columns. Then merging it with the tables that it needs to be in. I would like to make this a simple broswe for the file, click the upload button and it sort and upload the information. Am I asking too much?

Let me know what you think.

Charles

|||

hi,

Kraven3d wrote:

Here is a sample of the data and what I need to do with it.

1ETTX800401V411TTX BNSF851293
1ETTX800913V411TTX BNSF840295

This data needs to be brought into the data base as:

1 ETTX 800401 V 411 TTX BNSF 85 1293
1 ETTX 800913 V 411 TTX BNSF 84 0295

The seperation will ALWAYS occur after the 1, 5, 11, 12, 15,19, 23, 25 & 29th characters. Do you have any idea how I can accomplish this?

you can define fixed width columns in the schema.ini as following

<flat.txt>
1ETTX800401V411TTX BNSF851293
1ETTX800913V411TTX BNSF840295
</flat.txt>

<schema.ini>
[Flat.TXT]
ColNameHeader=False
Format=FixedLength
Col1=Col1 Text Width 1
Col2=Col2 Text Width 4
Col3=Col3 Text Width 6
Col4=Col4 Text Width 1
Col5=Col5 Text Width 3
Col6=Col6 Text Width 4
Col7=Col7 Text Width 4
Col8=Col8 Text Width 2
Col9=Col9 Text Width 4
</schema.ini>

SET NOCOUNT ON;
EXEC sp_addlinkedserver txtsrv, 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'd:\Import\',
NULL,
'Text';
GO
CREATE TABLE #tText (
Col1 int,
Col2 char(4),
Col3 int,
Col4 char(1),
Col5 int,
Col6 varchar(4),
Col7 varchar(4),
Col8 int,
Col9 varchar(4)
);
SELECT * FROM txtsrv...flat#txt;

INSERT INTO #tText
SELECT CONVERT(int, Col1), Col2, CONVERT(int, Col3), Col4, CONVERT(int, Col5), Col6, Col7, CONVERT(int, Col8), Col9
FROM txtsrv...flat#txt;

SELECT * FROM #tText;
DROP TABLE #tText;
GO
EXEC sp_dropserver 'txtsrv', 'droplogins';

--<-
Col1 Col2 Col3 Col4 Col5 Col6 Col7 Col8 Col9
- - - - - - - -
1 ETTX 800401 V 411 TTX BNSF 85 1293
1 ETTX 800913 V 411 TTX BNSF 84 0295

Col1 Col2 Col3 Col4 Col5 Col6 Col7 Col8 Col9
-- - -- - -- - - -- -
1 ETTX 800401 V 411 TTX BNSF 85 1293
1 ETTX 800913 V 411 TTX BNSF 84 0295

then you can use the SELECT to perform whatever action you require... even populate a temp table, casting the data types as required ..

regards

|||Hi Andrea,

I'm trying to get it to work but I keep running into this error message:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication failed.
[OLE/DB provider returned message: Cannot start your application. The workgroup information file is missing or opened exclusively by another user.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].

Any ideas?

Thanks,

Bradley|||These approaches never occurred to me. I have found my heroine. Thanks for the great tips!

-B|||

B. Sandvik wrote:

These approaches never occurred to me. I have found my heroine. Thanks for the great tips!

-B

... I'm male

regards

|||

I guess they didn't go to your web site to see you picture like I did.

Mike

|||

[OT]

was looking for you in Redmond at the MVP summit...

I was able to meet Jens, but not you..

regards

|||

Will I need to define everything in the Flat.txt file? Here's what I mean.

I have entered only 2 lines in my original post, where my official txt file has over 1 million rows. The one I just got is comma delimited and I can use it as a qualifier if it will make it easier for me to import into my DB.

This is what the new file looks like:

1,AAMX,050167 ,C112,AAMX, , ., ,
1,AAMX,061468 ,C112,ACFX, , ., ,
1,AAMX,062068 ,C112,ACFX, , ., ,

I have 2 documents that I will need to do this on. 1 is the official EVERY entry and the other is an update file where I will need to see if a character has changed; if so update it, if not then it will leave it alone.

I know that that will be a simple update statement. But I will be getting a TXT file like this 2 times a month.

Any ideas would be helpful.

How to import flat files into Microsoft SQL 2005 Express Edition automatically?

Iam aware that Microsoft SQL 2005 Express Edition does not have agent or DTS capabilities, but how may I automate an import of flat files to the DB tables? Must I use an external VB development of is there a way to schedule an import of flat files to Microsoft SQL 2005 Express Edition ?

hi,

you can perhaps have a look at the features provided by linked servers using JET data provider as reported in http://msdn2.microsoft.com/en-us/library/ms190479.aspx

say you have a situation like this, a flat.txt saved in d:\txt\flat.txt

<flat.txt>
FirstName|LastName
andrea|montanari
jr2006|jr2006
</flat.txt>

you can define a schema file (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp) like

<schema.ini>
[FLAT.TXT]
ColNameHeader=True
Format=Delimited(|)
</schema.ini>

and proceed, on SQL Server, registering a linked server based on JET 4.0 OLE DB provider, querying it and eventually importing data from it in a regular SQLX database table like

SET NOCOUNT ON;

EXEC sp_addlinkedserver txtsrv, 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'd:\txt\',
NULL,
'Text';

SELECT * FROM txtsrv...flat#txt;

EXEC sp_dropserver 'txtsrv', 'droplogins';

resulting in

--<--

FirstName LastName
-- --
andrea montanari
jr2006 jr2006

regards

|||

Andrea,

Thanks a bunch if your solution is as pretty as your name, then I believe we shall be OK.

regards

JR2006

|||

Andrea, Great solution and I am wondering if it will help me in what I am trying to accomplish. I have a CSV file that comes from another company. The file is a CSV only in the extension, since there are no commas or seperation in the data. here's what I am trying to do.

Here is a sample of the data and what I need to do with it.

1ETTX800401V411TTX BNSF851293
1ETTX800913V411TTX BNSF840295

This data needs to be brought into the data base as:

1 ETTX 800401 V 411 TTX BNSF 85 1293
1 ETTX 800913 V 411 TTX BNSF 84 0295

The seperation will ALWAYS occur after the 1, 5, 11, 12, 15,19, 23, 25 & 29th characters. Do you have any idea how I can accomplish this? Currently we are importing it into Access2003 and seperating it into the columns. Then merging it with the tables that it needs to be in. I would like to make this a simple broswe for the file, click the upload button and it sort and upload the information. Am I asking too much?

Let me know what you think.

Charles

|||

hi,

Kraven3d wrote:

Here is a sample of the data and what I need to do with it.

1ETTX800401V411TTX BNSF851293
1ETTX800913V411TTX BNSF840295

This data needs to be brought into the data base as:

1 ETTX 800401 V 411 TTX BNSF 85 1293
1 ETTX 800913 V 411 TTX BNSF 84 0295

The seperation will ALWAYS occur after the 1, 5, 11, 12, 15,19, 23, 25 & 29th characters. Do you have any idea how I can accomplish this?

you can define fixed width columns in the schema.ini as following

<flat.txt>
1ETTX800401V411TTX BNSF851293
1ETTX800913V411TTX BNSF840295
</flat.txt>

<schema.ini>
[Flat.TXT]
ColNameHeader=False
Format=FixedLength
Col1=Col1 Text Width 1
Col2=Col2 Text Width 4
Col3=Col3 Text Width 6
Col4=Col4 Text Width 1
Col5=Col5 Text Width 3
Col6=Col6 Text Width 4
Col7=Col7 Text Width 4
Col8=Col8 Text Width 2
Col9=Col9 Text Width 4
</schema.ini>

SET NOCOUNT ON;
EXEC sp_addlinkedserver txtsrv, 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'd:\Import\',
NULL,
'Text';
GO
CREATE TABLE #tText (
Col1 int,
Col2 char(4),
Col3 int,
Col4 char(1),
Col5 int,
Col6 varchar(4),
Col7 varchar(4),
Col8 int,
Col9 varchar(4)
);
SELECT * FROM txtsrv...flat#txt;

INSERT INTO #tText
SELECT CONVERT(int, Col1), Col2, CONVERT(int, Col3), Col4, CONVERT(int, Col5), Col6, Col7, CONVERT(int, Col8), Col9
FROM txtsrv...flat#txt;

SELECT * FROM #tText;
DROP TABLE #tText;
GO
EXEC sp_dropserver 'txtsrv', 'droplogins';

--<-
Col1 Col2 Col3 Col4 Col5 Col6 Col7 Col8 Col9
- - - - - - - -
1 ETTX 800401 V 411 TTX BNSF 85 1293
1 ETTX 800913 V 411 TTX BNSF 84 0295

Col1 Col2 Col3 Col4 Col5 Col6 Col7 Col8 Col9
-- - -- - -- - - -- -
1 ETTX 800401 V 411 TTX BNSF 85 1293
1 ETTX 800913 V 411 TTX BNSF 84 0295

then you can use the SELECT to perform whatever action you require... even populate a temp table, casting the data types as required ..

regards

|||Hi Andrea,

I'm trying to get it to work but I keep running into this error message:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication failed.
[OLE/DB provider returned message: Cannot start your application. The workgroup information file is missing or opened exclusively by another user.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].

Any ideas?

Thanks,

Bradley|||These approaches never occurred to me. I have found my heroine. Thanks for the great tips!

-B|||

B. Sandvik wrote:

These approaches never occurred to me. I have found my heroine. Thanks for the great tips!

-B

... I'm male

regards

|||

I guess they didn't go to your web site to see you picture like I did.

Mike

|||

[OT]

was looking for you in Redmond at the MVP summit...

I was able to meet Jens, but not you..

regards

|||

Will I need to define everything in the Flat.txt file? Here's what I mean.

I have entered only 2 lines in my original post, where my official txt file has over 1 million rows. The one I just got is comma delimited and I can use it as a qualifier if it will make it easier for me to import into my DB.

This is what the new file looks like:

1,AAMX,050167 ,C112,AAMX, , ., ,
1,AAMX,061468 ,C112,ACFX, , ., ,
1,AAMX,062068 ,C112,ACFX, , ., ,

I have 2 documents that I will need to do this on. 1 is the official EVERY entry and the other is an update file where I will need to see if a character has changed; if so update it, if not then it will leave it alone.

I know that that will be a simple update statement. But I will be getting a TXT file like this 2 times a month.

Any ideas would be helpful.

How to import flat files into Microsoft SQL 2005 Express Edition automatically?

Iam aware that Microsoft SQL 2005 Express Edition does not have agent or DTS capabilities, but how may I automate an import of flat files to the DB tables? Must I use an external VB development of is there a way to schedule an import of flat files to Microsoft SQL 2005 Express Edition ?

hi,

you can perhaps have a look at the features provided by linked servers using JET data provider as reported in http://msdn2.microsoft.com/en-us/library/ms190479.aspx

say you have a situation like this, a flat.txt saved in d:\txt\flat.txt

<flat.txt>
FirstName|LastName
andrea|montanari
jr2006|jr2006
</flat.txt>

you can define a schema file (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp) like

<schema.ini>
[FLAT.TXT]
ColNameHeader=True
Format=Delimited(|)
</schema.ini>

and proceed, on SQL Server, registering a linked server based on JET 4.0 OLE DB provider, querying it and eventually importing data from it in a regular SQLX database table like

SET NOCOUNT ON;

EXEC sp_addlinkedserver txtsrv, 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'd:\txt\',
NULL,
'Text';

SELECT * FROM txtsrv...flat#txt;

EXEC sp_dropserver 'txtsrv', 'droplogins';

resulting in

--<--

FirstName LastName
-- --
andrea montanari
jr2006 jr2006

regards

|||

Andrea,

Thanks a bunch if your solution is as pretty as your name, then I believe we shall be OK.

regards

JR2006

|||

Andrea, Great solution and I am wondering if it will help me in what I am trying to accomplish. I have a CSV file that comes from another company. The file is a CSV only in the extension, since there are no commas or seperation in the data. here's what I am trying to do.

Here is a sample of the data and what I need to do with it.

1ETTX800401V411TTX BNSF851293
1ETTX800913V411TTX BNSF840295

This data needs to be brought into the data base as:

1 ETTX 800401 V 411 TTX BNSF 85 1293
1 ETTX 800913 V 411 TTX BNSF 84 0295

The seperation will ALWAYS occur after the 1, 5, 11, 12, 15,19, 23, 25 & 29th characters. Do you have any idea how I can accomplish this? Currently we are importing it into Access2003 and seperating it into the columns. Then merging it with the tables that it needs to be in. I would like to make this a simple broswe for the file, click the upload button and it sort and upload the information. Am I asking too much?

Let me know what you think.

Charles

|||

hi,

Kraven3d wrote:

Here is a sample of the data and what I need to do with it.

1ETTX800401V411TTX BNSF851293
1ETTX800913V411TTX BNSF840295

This data needs to be brought into the data base as:

1 ETTX 800401 V 411 TTX BNSF 85 1293
1 ETTX 800913 V 411 TTX BNSF 84 0295

The seperation will ALWAYS occur after the 1, 5, 11, 12, 15,19, 23, 25 & 29th characters. Do you have any idea how I can accomplish this?

you can define fixed width columns in the schema.ini as following

<flat.txt>
1ETTX800401V411TTX BNSF851293
1ETTX800913V411TTX BNSF840295
</flat.txt>

<schema.ini>
[Flat.TXT]
ColNameHeader=False
Format=FixedLength
Col1=Col1 Text Width 1
Col2=Col2 Text Width 4
Col3=Col3 Text Width 6
Col4=Col4 Text Width 1
Col5=Col5 Text Width 3
Col6=Col6 Text Width 4
Col7=Col7 Text Width 4
Col8=Col8 Text Width 2
Col9=Col9 Text Width 4
</schema.ini>

SET NOCOUNT ON;
EXEC sp_addlinkedserver txtsrv, 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'd:\Import\',
NULL,
'Text';
GO
CREATE TABLE #tText (
Col1 int,
Col2 char(4),
Col3 int,
Col4 char(1),
Col5 int,
Col6 varchar(4),
Col7 varchar(4),
Col8 int,
Col9 varchar(4)
);
SELECT * FROM txtsrv...flat#txt;

INSERT INTO #tText
SELECT CONVERT(int, Col1), Col2, CONVERT(int, Col3), Col4, CONVERT(int, Col5), Col6, Col7, CONVERT(int, Col8), Col9
FROM txtsrv...flat#txt;

SELECT * FROM #tText;
DROP TABLE #tText;
GO
EXEC sp_dropserver 'txtsrv', 'droplogins';

--<-
Col1 Col2 Col3 Col4 Col5 Col6 Col7 Col8 Col9
- - - - - - - -
1 ETTX 800401 V 411 TTX BNSF 85 1293
1 ETTX 800913 V 411 TTX BNSF 84 0295

Col1 Col2 Col3 Col4 Col5 Col6 Col7 Col8 Col9
-- - -- - -- - - -- -
1 ETTX 800401 V 411 TTX BNSF 85 1293
1 ETTX 800913 V 411 TTX BNSF 84 0295

then you can use the SELECT to perform whatever action you require... even populate a temp table, casting the data types as required ..

regards

|||Hi Andrea,

I'm trying to get it to work but I keep running into this error message:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication failed.
[OLE/DB provider returned message: Cannot start your application. The workgroup information file is missing or opened exclusively by another user.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].

Any ideas?

Thanks,

Bradley|||These approaches never occurred to me. I have found my heroine. Thanks for the great tips!

-B|||

B. Sandvik wrote:

These approaches never occurred to me. I have found my heroine. Thanks for the great tips!

-B

... I'm male

regards

|||

I guess they didn't go to your web site to see you picture like I did.

Mike

|||

[OT]

was looking for you in Redmond at the MVP summit...

I was able to meet Jens, but not you..

regards

|||

Will I need to define everything in the Flat.txt file? Here's what I mean.

I have entered only 2 lines in my original post, where my official txt file has over 1 million rows. The one I just got is comma delimited and I can use it as a qualifier if it will make it easier for me to import into my DB.

This is what the new file looks like:

1,AAMX,050167 ,C112,AAMX, , ., ,
1,AAMX,061468 ,C112,ACFX, , ., ,
1,AAMX,062068 ,C112,ACFX, , ., ,

I have 2 documents that I will need to do this on. 1 is the official EVERY entry and the other is an update file where I will need to see if a character has changed; if so update it, if not then it will leave it alone.

I know that that will be a simple update statement. But I will be getting a TXT file like this 2 times a month.

Any ideas would be helpful.

How To Import Access to SQLServer with Parameter from SQLServer, Help Pls!

Hello Expert!

I have 2 Database – Access & SQLServer(ver 7)

I need to Import Data TblShift from Access to SQLServer – using DTS I’ve done this successfully!

Now I want to use parameter so I only importing record within range (e.g. ShiftDate BETWEEN 05-24-2006 AND 06-23-2006)

In SQLServer, I have created table to store the date range as following:

TblParameter
DateFrom: 04/24/2006
DateTo: 05/23/2006

How do I use the date range from TblParameter(SQLServer) to import record from TblShift(Access) using DTS?

Is this possible or any better solution for this?

TIA

Regards,

have you tried using the ole db source component? If you use a parameterized query, you can map variables to parameters to specify the values for individual parameters in the SQL statements.|||

Hi Douglas,

I newbie to sqlserver dts,

I don't know how to do as u have suggested

Can u explain step by step?

Or can u guide me to the place where I can see some sample

TIA

Regards

|||

Try doing this:

http://blogs.conchango.com/jamiethomson/archive/2005/12/09/2480.aspx

-Jamie

How to import a bunch of files?

I have a large number of text files that I want to import into a database. I
have created a DTS package that knows how to import and transform the data
so that it can be imported but that seems to be hard coded to a particular
file. Doing this by hand 18 thousand times is not something I want to do. Is
there a command line interface that I can call from either DOS or WSH to
import all of this data? I'm thinking that something like
for %%f in (*.txt) do <import %%f>
would be great. Anyone have a suggestion on how this could be done?
Richard Lewis Haggard
Looping, Importing and Archiving
http://www.sqldts.com/default.aspx?246
For SQL2005 you can use the ForEach Loop file enumerator
Looping over files with the Foreach Loop
http://www.sqlis.com/default.aspx?55
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Richard Lewis Haggard" <HaggardAtWorldDotStdDotCom> wrote in message
news:eYhbjZf%23FHA.1032@.TK2MSFTNGP09.phx.gbl...
>I have a large number of text files that I want to import into a database.
>I have created a DTS package that knows how to import and transform the
>data so that it can be imported but that seems to be hard coded to a
>particular file. Doing this by hand 18 thousand times is not something I
>want to do. Is there a command line interface that I can call from either
>DOS or WSH to import all of this data? I'm thinking that something like
> for %%f in (*.txt) do <import %%f>
> would be great. Anyone have a suggestion on how this could be done?
> --
> Richard Lewis Haggard
>
|||I ended up doing it with a dtsrun package called from a pair of batch files.
In this case, c:\StockWizPro98\OUTPUT is the destination directory that
receives periodic updates and data drops. I decided to make a temp directory
that will have a single file in it and to create a DTS package that knows
how to import data from it. The
first batch file iterates through all of the input files, copies and renames
them to what the DTS package is looking for and calls the package to import
the data. Simple.
1.bat
pushd c:\StockWizPro98\OUTPUT
for %%f in (*.txt) do call 2 %%f
popd
2.bat
pushd c:\StockWizPro98\OUTPUT\temp
copy /y ..\%1 temp.txt
dtsrun /s (local) /n StockImport /e
popd
Richard Lewis Haggard
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:%23aBh2ff%23FHA.740@.TK2MSFTNGP12.phx.gbl...
> Looping, Importing and Archiving
> http://www.sqldts.com/default.aspx?246
> For SQL2005 you can use the ForEach Loop file enumerator
> Looping over files with the Foreach Loop
> http://www.sqlis.com/default.aspx?55
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Richard Lewis Haggard" <HaggardAtWorldDotStdDotCom> wrote in message
> news:eYhbjZf%23FHA.1032@.TK2MSFTNGP09.phx.gbl...
>

How to I run a DTS package from a stored procedure? thank

I created a DTS package to transfer data from a remote database into my local database, I want to run this DTS in my stored procedure, can I do that?

Please help me, thanks a lot

Hi,

you have to call it via dtsrun on the command prompt, ousing xp_cmdshell.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de|||

Hi Jens Suessmeyer,

could you be more specific? can you give me a example?

thanks

|||Sure, look the the DTSRUN syntax, you can start the dtsrun either with a GUID naming the package which is stored in SQL Server or by a structured storage file, using the XP_CMDSHELL 'DTSRUN SomePackage' will get you the package run.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||Another method would be to use the system OLE automation SPs and use the DTS object model to invoke the package. Please search the web for several examples. Btw, this question is more suited for the SQL Server Integration Services newsgroup so I will move the thread there so someone there can point you to appropriate resources/links.|||

Please read through the article on 'Data Transformation Services (DTS)' @. http://www.databasejournal.com/features/mssql/article.php/1459181
The example in this article demostrates the use of OLE stored procedures and its benefits.

Btw - This forum majorly deals with SQL Server 2005 - Integration Services.

For DTS (SQL Server 2000) related questions post @. http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.sqlserver.dts&cat=en_US_2b8e81a3-be64-42fa-bd81-c6d41de5a219&lang=en&cr=US

Thanks,
Loonysan

|||I would keep away from using OLE stored procedures - it is a convinient way to do it, but you are running inside SQL Server process. Thus any problems with DTS package may affect the server. Running the package outside using DTSRUN utility is better, as the SQL Server and package run in isolated processes and don't affect each other.

If you mistyped DTS, but was really asking about SSIS (this is SSIS forum after all) - the recommended way is to create Agent Job with the package step, don't assing any schedule to this job, and then start this job from your SQL stored procedure by calling Agent's SP. This provides the isolation as with DTSRUN, but additionally you may specify user context for the SSIS package - so the package does not have to run under the same user as SQL Server.