import multiple accessfiles in dts

Hi,

How can i import multiple accessfiles in sql server 2000 by using a dts
package?

The dts package should import files with the most current date in a
directory (not today's date). The date can be found in the name, e.g.:
<companyname>_20041214.mdb.

Also i would like to use the companyname from the filename to fill in a
empty column called companyname, so i can import all files into one
normalized table.

The files are placed in on a root drive.

If someone can help me on this i would be very happy.

Thnx.Hi

The following should get you started:
http://www.sqldts.com/default.aspx?292
http://www.sqldts.com/default.aspx?248
http://www.sqldts.com/default.aspx?201

You can store the company name in a global variable, then use an activeX
transform to populate a column from that global variable.
http://www.sqldts.com/default.aspx?279,4

John

"Ezekil" <ezekil@.lycos.com> wrote in message
news:41c281c0$0$45956$a344fe98@.news.wanadoo.nl...
> Hi,
> How can i import multiple accessfiles in sql server 2000 by using a dts
> package?
> The dts package should import files with the most current date in a
> directory (not today's date). The date can be found in the name, e.g.:
> <companyname>_20041214.mdb.
> Also i would like to use the companyname from the filename to fill in a
> empty column called companyname, so i can import all files into one
> normalized table.
> The files are placed in on a root drive.
> If someone can help me on this i would be very happy.
> Thnx.

import large field from SSIS

Hi,

I am making a SSIS package that imports data from a application using a custom ODBC driver. The field in the application is set to be a "longvarchar" type field and can be from 2 characters to 2MB of data.

I've created a ODBC data connection in the SSIS package and use a "DataReader Source" to read the data I need. The sql statement is very simple

Select log from tablename

When I try to run the SSIS package with that statement it just goes to yellow on the DataReader Source and stops. It stays like that until I stop it. If I select other fields except for that field it works fine. Also I've been able to get it to succeed getting the log field if I select a log record that's not too big. The largest one I've been able to get is 800 characters, but I got one with 2500 characters that just stops on yellow.

In the Progress log the last line says:

[DTS.Pipeline] Information: Execute phase is beginning.

Does anyone have any ideas on how to resolve this?
Have you check the ValidateMetadeta properties of data source Is there any warning message appear in you data reader ?|||I've tried both with having the ValidateMetaData option to false and true but it doesn't make any difference. There is no error/warning messages in the progress log and I don't know of any other places to look for error messages.

This is really starting to annoy me, but this is the only way we can get the data out of that system so I need this to work...
|||

Hi,

DataReaderSrc is not particularly efficient about dealing with BLOB data, such as DT_NTEXT or DT_IMAGE columns. It may be that it is just being slow...

Unfortunately, DataReaderSrc does not utilize the perf counters for BLOB bytes read -- this is a known issue that is planned to be fixed in a future release. If you look at perfmon while the package is running, what's happening with the CPU and memory usage?

mark

|||Thanks for your answer. When looking at the perfmon and the task manager while running the package I was a bit surprised.
First of in the perfmon the Memory object is steady on 0, the Physical Disk object is going up and down from 0 to 20 and then there is the occasional spike up to 100.
Then there is the processor object which stays at around 50 constantly. Looking at the task manager the process: "DtsDebugHost.exe" is staying at 50% CPU and using 30.976 K memory. Is this normal when executing a package?

I was thinking too that it might just be slow. But I changed my query to only select 1 record based on the id of the record and it still stays on for 20 min+ (I stopped it after that). If I select a record that got less data in the BLOB field then it completes within 10 seconds.

My development server is a Intel Xeon dual 3.6 GHz with 3 GB memory so I don't think our server is good enough

If the future fix will fix this issue that will be good enough, because I sort of told people that we have to find a different way to create the reports.
|||

The "future fix" I spoke of was just to add the performance counters to DataReaderSrc, which would help diagnose issues like this one.

I will try to set up a similar scenario here to see if I can reproduce the behaviour you are seeing.

thanks

Mark

|||

Hi Josh,

I created a package with a datareader source, and used connections of the type:

.NET Providers\SqlClient Data Provider

.NET Providers\Odbc Data Provider

In both cases, i was able to read 20 Mb of TEXT data in a second or two.

Is it possible for you to try using a different driver/provider?

Can you try using some other application with your driver/provider to see if you can read the data or if you have the same problem?

thanks

Mark

import large field from SSIS

Hi,

I am making a SSIS package that imports data from a application using a custom ODBC driver. The field in the application is set to be a "longvarchar" type field and can be from 2 characters to 2MB of data.

I've created a ODBC data connection in the SSIS package and use a "DataReader Source" to read the data I need. The sql statement is very simple

Select log from tablename

When I try to run the SSIS package with that statement it just goes to yellow on the DataReader Source and stops. It stays like that until I stop it. If I select other fields except for that field it works fine. Also I've been able to get it to succeed getting the log field if I select a log record that's not too big. The largest one I've been able to get is 800 characters, but I got one with 2500 characters that just stops on yellow.

In the Progress log the last line says:

[DTS.Pipeline] Information: Execute phase is beginning.

Does anyone have any ideas on how to resolve this?
Have you check the ValidateMetadeta properties of data source Is there any warning message appear in you data reader ?|||I've tried both with having the ValidateMetaData option to false and true but it doesn't make any difference. There is no error/warning messages in the progress log and I don't know of any other places to look for error messages.

This is really starting to annoy me, but this is the only way we can get the data out of that system so I need this to work...
|||

Hi,

DataReaderSrc is not particularly efficient about dealing with BLOB data, such as DT_NTEXT or DT_IMAGE columns. It may be that it is just being slow...

Unfortunately, DataReaderSrc does not utilize the perf counters for BLOB bytes read -- this is a known issue that is planned to be fixed in a future release. If you look at perfmon while the package is running, what's happening with the CPU and memory usage?

mark

|||Thanks for your answer. When looking at the perfmon and the task manager while running the package I was a bit surprised.
First of in the perfmon the Memory object is steady on 0, the Physical Disk object is going up and down from 0 to 20 and then there is the occasional spike up to 100.
Then there is the processor object which stays at around 50 constantly. Looking at the task manager the process: "DtsDebugHost.exe" is staying at 50% CPU and using 30.976 K memory. Is this normal when executing a package?

I was thinking too that it might just be slow. But I changed my query to only select 1 record based on the id of the record and it still stays on for 20 min+ (I stopped it after that). If I select a record that got less data in the BLOB field then it completes within 10 seconds.

My development server is a Intel Xeon dual 3.6 GHz with 3 GB memory so I don't think our server is good enough

If the future fix will fix this issue that will be good enough, because I sort of told people that we have to find a different way to create the reports.
|||

The "future fix" I spoke of was just to add the performance counters to DataReaderSrc, which would help diagnose issues like this one.

I will try to set up a similar scenario here to see if I can reproduce the behaviour you are seeing.

thanks

Mark

|||

Hi Josh,

I created a package with a datareader source, and used connections of the type:

.NET Providers\SqlClient Data Provider

.NET Providers\Odbc Data Provider

In both cases, i was able to read 20 Mb of TEXT data in a second or two.

Is it possible for you to try using a different driver/provider?

Can you try using some other application with your driver/provider to see if you can read the data or if you have the same problem?

thanks

Mark

Import Flat file to sql server programmatically

Hi,

i want to import flat file data to sql server. i created a package in vb.net. if the import table column is identity means i got

Failure inserting into the read-only column "ID".
Column metadata validation failed.
"component "OLE DB Destination" (10)" failed validation and returned validation status "VS_ISBROKEN".
One or more component failed validation.
There were errors during task validation. error.

how can i rectify this error? or how can i ignore the identity column in coding.

thanks & regards,

sivani

Don't map a column to the identity column in the table. Leave it out.

For instance, in terms of SQL this won't work:

insert into table (ID, Description) values (1,'Row1');

But this will:

insert into table (Description) values ('Row1');

So in your package, don't map a data flow field to the identity column.|||

Hi,

Thanks for your reply. i want it to do programmatically. Give me some sample code in vb.net to igmore the identity column.

regards,

sivani

|||

Hi,

i have created a package successfully to import flat file. i got some error while i am importing fixedwidth textfiles. The error is,

No column was specified to allow the component to advance through the file.
component "Flat File Source" (1) failed the pre-execute phase and returned error code 0xC02020F0.

and my text file data like this,

00001234561195469 040000 0000 7674491234 47674491234 211252901209030000199804130128431998041301292900003800IXCRD1

00001234568002251234 0400234096321234 0000 8002251234 09782251234 111170401218830000199804130634501998041305345900000400IXCRD1

can anybody give me a solution?

Thanks & regards,

sivani

|||

Did you find an anwer to your question?

Jay

|||

Sivani wrote:

Hi,

Thanks for your reply. i want it to do programmatically. Give me some sample code in vb.net to igmore the identity column.

regards,

sivani

I don't understand. Don't do anything with the identity column. Leave it out/alone.|||

Yes, Finally i found the solution

Jayanthi.

Import Flat file to sql server programmatically

Hi,

i want to import flat file data to sql server. i created a package in vb.net. if the import table column is identity means i got

Failure inserting into the read-only column "ID".
Column metadata validation failed.
"component "OLE DB Destination" (10)" failed validation and returned validation status "VS_ISBROKEN".
One or more component failed validation.
There were errors during task validation. error.

how can i rectify this error? or how can i ignore the identity column in coding.

thanks & regards,

sivani

Don't map a column to the identity column in the table. Leave it out.

For instance, in terms of SQL this won't work:

insert into table (ID, Description) values (1,'Row1');

But this will:

insert into table (Description) values ('Row1');

So in your package, don't map a data flow field to the identity column.|||

Hi,

Thanks for your reply. i want it to do programmatically. Give me some sample code in vb.net to igmore the identity column.

regards,

sivani

|||

Hi,

i have created a package successfully to import flat file. i got some error while i am importing fixedwidth textfiles. The error is,

No column was specified to allow the component to advance through the file.
component "Flat File Source" (1) failed the pre-execute phase and returned error code 0xC02020F0.

and my text file data like this,

00001234561195469 040000 0000 7674491234 47674491234 211252901209030000199804130128431998041301292900003800IXCRD1

00001234568002251234 0400234096321234 0000 8002251234 09782251234 111170401218830000199804130634501998041305345900000400IXCRD1

can anybody give me a solution?

Thanks & regards,

sivani

|||

Did you find an anwer to your question?

Jay

|||

Sivani wrote:

Hi,

Thanks for your reply. i want it to do programmatically. Give me some sample code in vb.net to igmore the identity column.

regards,

sivani

I don't understand. Don't do anything with the identity column. Leave it out/alone.|||

Yes, Finally i found the solution

Jayanthi.

Import Flat file to sql server programmatically

Hi,

i want to import flat file data to sql server. i created a package in vb.net. if the import table column is identity means i got

Failure inserting into the read-only column "ID".
Column metadata validation failed.
"component "OLE DB Destination" (10)" failed validation and returned validation status "VS_ISBROKEN".
One or more component failed validation.
There were errors during task validation. error.

how can i rectify this error? or how can i ignore the identity column in coding.

thanks & regards,

sivani

Don't map a column to the identity column in the table. Leave it out.

For instance, in terms of SQL this won't work:

insert into table (ID, Description) values (1,'Row1');

But this will:

insert into table (Description) values ('Row1');

So in your package, don't map a data flow field to the identity column.|||

Hi,

Thanks for your reply. i want it to do programmatically. Give me some sample code in vb.net to igmore the identity column.

regards,

sivani

|||

Hi,

i have created a package successfully to import flat file. i got some error while i am importing fixedwidth textfiles. The error is,

No column was specified to allow the component to advance through the file.
component "Flat File Source" (1) failed the pre-execute phase and returned error code 0xC02020F0.

and my text file data like this,

00001234561195469 040000 0000 7674491234 47674491234 211252901209030000199804130128431998041301292900003800IXCRD1

00001234568002251234 0400234096321234 0000 8002251234 09782251234 111170401218830000199804130634501998041305345900000400IXCRD1

can anybody give me a solution?

Thanks & regards,

sivani

|||

Did you find an anwer to your question?

Jay

|||

Sivani wrote:

Hi,

Thanks for your reply. i want it to do programmatically. Give me some sample code in vb.net to igmore the identity column.

regards,

sivani

I don't understand. Don't do anything with the identity column. Leave it out/alone.|||

Yes, Finally i found the solution

Jayanthi.

Import Export Wizard and Unicode columns

Hello Folks,

This is my first real exposure to using SSIS's Import Export Tool.

I am trying to create a package to move the data from a number of tables in SQL Server into a duplicate database in Oracle. I am invoking the Import/Export WIzard from within the Management Studio. I am using the SQL Server native client on the SQL Server side and the Microsoft OLE DB driver for Oracle for the Oracle database. Both of my tables have unicode data types. On the SQL Server side I have columns defined as NVARCHAR and on Oracle the same column is defined as NVARCHAR2.

When I initially select the table for export the wizard assumes that I want to create a new table. The new table has the correct column name, column order and data types (NVARCHAR2). I cannot tell it to append the data. If I add the table owner to the detination table name or use the GUI to pick it with the table owner...the data types for the NVARCHAR2 columns go away. I cannot edit the data types at this point and if I continue, SSIS barks at me that it doesn't know the data types of those columns. The problem also occurs if you use the wizard from inside of the BIDS. It seems to be OK with the data type unless the table pre-exists. This seems un-useful to me.

Is there a way to avoid this in the Import/Export Wizard? Am I doing something wrong?

Any help would be appreciated.

Thanks, Mark

Hi Mark,

could you double check the metadata of your preexisting destination table matches the incoming metadata? Also, could you post the error the wizard reports?

Thanks,

Bob

|||Hi Bob,

Well, as I mentioned before if I try to continue SSIS barks and says:

TITLE: SQL Server Import and Export Wizard


Column information for the source and the destination data could not be retrieved, or the data types of source columns were not mapped correctly to those available on the destination provider.


[sql_dev_slove2].[dbo].[ACCESSPROFILE] -> "MARKT"."ACCESSPROFILE":

- The data type could not be assigned to the column "PROFILENM" in "Microsoft OLE DB Provider for Oracle".
- The data type could not be assigned to the column "PROFILEDSC" in "Microsoft OLE DB Provider for Oracle".

After this point, I can go no farther so (I think) I cannot see the actual meta data. If I do not change the owner (let SSIS think it needs to create the table) the input data type is DT_WSTR. The tables are defined as follows with the PROFILENM and PROFILEDSC columns being the troublesome ones:

SQL Server:
===========

CREATE TABLE ACCESSPROFILE
(
ACCESSPROFID INTEGER NOT NULL ,
PROFILENM NVARCHAR(50) NOT NULL ,
PROFILEDSC NVARCHAR(250) NULL ,
DEFAULTSW INTEGER DEFAULT 0 NULL ,
UPDATEDBYUSRACCTID INTEGER NOT NULL ,
UPDATEDTM DATETIME DEFAULT GETDATE() NOT NULL ,
VERSIONCNT INTEGER DEFAULT 1 NOT NULL ,
ALLOWALLSW INTEGER DEFAULT 0 NOT NULL
)
;

Oracle:
=======

CREATE TABLE ACCESSPROFILE
(
ACCESSPROFID NUMBER(10) NOT NULL ,
PROFILENM NVARCHAR2(50) NOT NULL ,
PROFILEDSC NVARCHAR2(250) NULL ,
DEFAULTSW NUMBER(10) DEFAULT 0 NULL ,
UPDATEDBYUSRACCTID NUMBER(10) NOT NULL ,
UPDATEDTM DATE DEFAULT SYSDATE NOT NULL ,
VERSIONCNT NUMBER(10) DEFAULT 1 NOT NULL ,
ALLOWALLSW NUMBER(10) DEFAULT 0 NOT NULL
)
/

Does this help?|||

Mark,

could you try the same thing using the Oracle's own OLE DB provider? Microsoft OLE DB provider for Oracle is pretty old one and I suspect it does not even know about nvarchar2 data type (I am not able to check this at the moment though).

There is a fundamental difference between transfering data into an existing or a new table. The existing table has the metadata already defined while the wizard generates new tables.

HTH,

Bob

Import Export Wizard and Unicode columns

Hello Folks,

This is my first real exposure to using SSIS's Import Export Tool.

I am trying to create a package to move the data from a number of tables in SQL Server into a duplicate database in Oracle. I am invoking the Import/Export WIzard from within the Management Studio. I am using the SQL Server native client on the SQL Server side and the Microsoft OLE DB driver for Oracle for the Oracle database. Both of my tables have unicode data types. On the SQL Server side I have columns defined as NVARCHAR and on Oracle the same column is defined as NVARCHAR2.

When I initially select the table for export the wizard assumes that I want to create a new table. The new table has the correct column name, column order and data types (NVARCHAR2). I cannot tell it to append the data. If I add the table owner to the detination table name or use the GUI to pick it with the table owner...the data types for the NVARCHAR2 columns go away. I cannot edit the data types at this point and if I continue, SSIS barks at me that it doesn't know the data types of those columns. The problem also occurs if you use the wizard from inside of the BIDS. It seems to be OK with the data type unless the table pre-exists. This seems un-useful to me.

Is there a way to avoid this in the Import/Export Wizard? Am I doing something wrong?

Any help would be appreciated.

Thanks, Mark

Hi Mark,

could you double check the metadata of your preexisting destination table matches the incoming metadata? Also, could you post the error the wizard reports?

Thanks,

Bob

|||Hi Bob,

Well, as I mentioned before if I try to continue SSIS barks and says:

TITLE: SQL Server Import and Export Wizard


Column information for the source and the destination data could not be retrieved, or the data types of source columns were not mapped correctly to those available on the destination provider.


[sql_dev_slove2].[dbo].[ACCESSPROFILE] -> "MARKT"."ACCESSPROFILE":

- The data type could not be assigned to the column "PROFILENM" in "Microsoft OLE DB Provider for Oracle".
- The data type could not be assigned to the column "PROFILEDSC" in "Microsoft OLE DB Provider for Oracle".

After this point, I can go no farther so (I think) I cannot see the actual meta data. If I do not change the owner (let SSIS think it needs to create the table) the input data type is DT_WSTR. The tables are defined as follows with the PROFILENM and PROFILEDSC columns being the troublesome ones:

SQL Server:
===========

CREATE TABLE ACCESSPROFILE
(
ACCESSPROFID INTEGER NOT NULL ,
PROFILENM NVARCHAR(50) NOT NULL ,
PROFILEDSC NVARCHAR(250) NULL ,
DEFAULTSW INTEGER DEFAULT 0 NULL ,
UPDATEDBYUSRACCTID INTEGER NOT NULL ,
UPDATEDTM DATETIME DEFAULT GETDATE() NOT NULL ,
VERSIONCNT INTEGER DEFAULT 1 NOT NULL ,
ALLOWALLSW INTEGER DEFAULT 0 NOT NULL
)
;

Oracle:
=======

CREATE TABLE ACCESSPROFILE
(
ACCESSPROFID NUMBER(10) NOT NULL ,
PROFILENM NVARCHAR2(50) NOT NULL ,
PROFILEDSC NVARCHAR2(250) NULL ,
DEFAULTSW NUMBER(10) DEFAULT 0 NULL ,
UPDATEDBYUSRACCTID NUMBER(10) NOT NULL ,
UPDATEDTM DATE DEFAULT SYSDATE NOT NULL ,
VERSIONCNT NUMBER(10) DEFAULT 1 NOT NULL ,
ALLOWALLSW NUMBER(10) DEFAULT 0 NOT NULL
)
/

Does this help?|||

Mark,

could you try the same thing using the Oracle's own OLE DB provider? Microsoft OLE DB provider for Oracle is pretty old one and I suspect it does not even know about nvarchar2 data type (I am not able to check this at the moment though).

There is a fundamental difference between transfering data into an existing or a new table. The existing table has the metadata already defined while the wizard generates new tables.

HTH,

Bob

sql

Import Excel with varying worksheet name using DTS

Hi Everyone,

I'm trying to create a DTS package that will let me import an Excel file. The user will be able to name the file the same name every time. But can the DTS package read a different worksheet name each time? Right now, if I use the Excel connection object in DTS designer, it wants to hard code the worksheet name.

Thanks,

Eric

You might want to post in the DTS forum:

http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.dts

Import excel file to database

Hai,

I am new to SSIS 2005.

Now i would like to use the foreach loop structure in an SSIS package to
loop through however many Excel files are placed in a directory and
then perform an import operation into a SQL table on each of these
files sequentially.

But i dont know how to get start?

Can anyone guide me on this task?

Thanks.

Step (1) and (2) used from http://rafael-salas.blogspot.com/

If Each Excel File has a Single Work Sheet, The following will be the method:

1) In the foreach loop properties use the following settings

In Collections tab,

Set Enumerator to "Foreach file Enumerator",

Under enumerator Configurations, Specify the Folder and Filter files with *.xls

In Variables Mapping tab,

Under Variable Column Select A Variable Name - User::ExcelFilePath(of String type, ForEachloop Scope) and Index Value 0.

2) Under the Dataflow, Under Source OLEDB Connection manager properties, use the expression builder to assign the Connection String Value to @.[User::ExcelfilePath].

Connect to source to your Destination SQL Server

For the rest of the steps, we pursue from step 11 given by DouglasL'S Answer

Import DT_DBDATE into a SQL TAble with datatype of datetime but without the timestamp

I created a SSIS package and creating a derived column named: Date...set datatype as DT_DBDATE....I do not want the timestamp on date...then I want to load this Date into a SQL server database table, with datatype of datetime, but it will load here with the timestamp which I do not want. Any ideas? I did change datatype of the SQL Server Destination datatype to DT_DBDATE but it will change it back to DT_DBTimestamp. thxSQL Server has no data type that contains just a date. If you insert just a date (insert into table (your_datefield) values ('07/16/2007')) you'll get a result stored in the database as '07/16/2007 00:00:00' or something like that. No way around that at the moment. Keep your eyes on SQL Server 2008 though.

Import data from SQL Server 2000 incredibly slow

I have a problem with bad perfomance with my import of data from a SQL Server 2000 database. I use an OLEDB datasource in my SSIS package to connect to the sql server 2000 database. My 2005 server runs 64bits but i dont think this is an issue. With this configuration the import is VERY slow, we are talking about 40+ minutes to get 3.5 million rows with about 20 columns. When i create a test DTS package on the SQL Server 2000 server itself and run it, its blazingly fast. Has anyone run into something similar?

Have you done any debuggin of where the bottleneck occurs? Donald Farmer explains some useful ways to do this, see here:

SSIS: Donald Farmer's Technet webcast
(http://blogs.conchango.com/jamiethomson/archive/2006/06/14/4076.aspx)

-Jamie

|||I had a look at the webcast. While it contains a lot of good stuff on optimization it doesnt really help me in this particular case. I tested the SSIS package against a mirror of the production server on another box, and it ran about 10X faster. So there has to be somenthing with the way the server is set up.

Import Data from SPSS (Statistical Package for the Social Sciences

hi to everyone. I'm developing a web application using C#.NET and MS SQL
Server 2000 database backend. But the user wants to transfer a huge data
from his SPSS application because it will take large amount of time to input
each of the item. My question is: Is there a way to import data from SPSS
application to the database using MS SQL Server 2000.
Specifications:
Import Data From a data file (SPSS Standard Version 11.0.0)
Hope you'll reply as soon as possible. You idea will greatly help me.
Thank you and God Bless.Hi
If this information is in a reasonably formatted file you can use BULK
INSERT command, DTS or the BCP utility to import the information. This can
be scheduled as a job and you can archive the file once loaded.
There is plenty of information on BULK INSERT, DTS and BCP in Books Online,
also check out http://www.sqldts.com/ for more DTS articles such as
http://www.sqldts.com/default.aspx?231
and http://www.sqldts.com/default.aspx?246
You will need some method of loading the data file onto your server or
somewhere accessable from the server. It may be necessary to hold the data
in a staging table(s) if it requires additional work before loading into
your destination table(s).
John
"rolly-hubport" <rollyhubport@.discussions.microsoft.com> wrote in message
news:231A4F51-B185-44D8-AE52-730275EC3C21@.microsoft.com...
> hi to everyone. I'm developing a web application using C#.NET and MS SQL
> Server 2000 database backend. But the user wants to transfer a huge data
> from his SPSS application because it will take large amount of time to
> input
> each of the item. My question is: Is there a way to import data from SPSS
> application to the database using MS SQL Server 2000.
> Specifications:
> Import Data From a data file (SPSS Standard Version 11.0.0)
>
> Hope you'll reply as soon as possible. You idea will greatly help me.
> Thank you and God Bless.
>
>|||Hi,
If u will use BCP or Bulk insert u have to manually define the table
name.
use dts package tranfer data from one server to another and u have
option to select the tables.
hope this helps u
from
doller

Import and Schedule DTS on MSDE

I have .dts file from another sql server. I would like to import and install
this file as a DTS package on MSDE and schedule it to run periodically.
How do I do this in MSDE 2000.
You can use DTSrun.exe
Look at
http://groups.google.it/groups?q=DTS...hx.gbl&rnum=18
"inquisite" <inquisite@.discussions.microsoft.com> wrote in message
news:392C38CD-4DB3-4D39-AF2A-0A14CED0D74F@.microsoft.com...
> I have .dts file from another sql server. I would like to import and
install
> this file as a DTS package on MSDE and schedule it to run periodically.
> How do I do this in MSDE 2000.
|||Will it be possible for anyone of you to help me with a example, for the syntax to use in command prompt with DTSrun.exe.
The situation is this. I need to uninstall SQL Server and install MSDE for my client. I have DTS running already in SQL Server. I need to take a back up, and make it run in MSDE. The package name is dtsPack.dts. I have it in the location "c:\"
Thanks for your help|||Here's the section from Books Online, including examples at the end:
The dtsrun utility executes a package created using Data Transformation Services (DTS). The DTS
package can be stored in the Microsoft SQL ServerT msdb database, a COM-structured storage file, or
SQL Server Meta Data Services.
Syntax
dtsrun
[/?] |
[
[
/[~]S server_name[\instance_name]
{ {/[~]U user_name [/[~]P password]} | /E }
]
{
{/[~]N package_name }
| {/[~]G package_guid_string}
| {/[~]V package_version_guid_string}
}
[/[~]M package_password]
[/[~]F filename]
[/[~]R repository_database_name]
[/A global_variable_name:typeid=value]
[/L log_file_name]
[/W NT_event_log_completion_status]
[/Z] [/!X] [/!D] [/!Y] [/!C]
]
Arguments/?
Displays the command prompt options.
~
Specifies that the parameter to follow is hexadecimal text representing the encrypted value of the
parameter. Can be used with the /S, /U, /P, /N, /G, /V, /M, /F, and /R options. Using encrypted
values increases the security of the command used to execute the DTS package because the server
name, password, and so on, are not visible. Use /!Y to determine the encrypted command.
/S server_name[\instance_name]
Specifies the instance of SQL Server to connect to. Specify server_name to connect to the default
instance of SQL Server on that server. Specify server_name\instance_name to connect to a named
instance of SQL Server 2000 on that server.
/U user_name
Is a login ID used to connect to an instance of SQL Server.
/P password
Is a user-specified password used with a login ID.
/E
Specifies a trusted connection (password not required).
/N package_name
Is the name of a DTS package assigned when the package was created.
/G package_guid_string
Is the package ID assigned to the DTS package when it was created. The package ID is a GUID.
/V package_version_guid_string
Is the version ID assigned to the DTS package when it was first saved or executed. A new version ID
is assigned to the DTS package each time it is modified. The version ID is a GUID.
/M package_password
Is an optional password assigned to the DTS package when it was created.
/F filename
Is the name of a structured storage file containing DTS packages. If server_name is also specified,
the DTS package retrieved from SQL Server is executed and that package is added to the structured
storage engine.
/R repository_database_name
Is the name of the repository database containing DTS packages. If no name is specified, the default
database name is used.
/A global_variable_name:typeid=value
Specifies a package global variable, where typeid = type identifier for the data type of the global
variable. The entire argument string can be quoted. This argument can be repeated to specify
multiple global variables. See the Remarks section for the different available type identifiers
available with global variables.
To set global variables with this command switch, you must have either Owner permission for the
package or the package must have been saved without DTS password protection enabled. If you do not
have Owner permission, you can specify global variables, but the values used will be those set in
the package, not those specified with the /A command switch.
/L log_file_name:
Specifies the name of the package log file.
/W Windows_Event_Log
Specifies whether or not to write the completion status of the package execution to the Windows
Application Log. Specify True or False.
/Z
Indicates that the command line for dtsrun is encrypted using SQL Server 2000 encryption.
/!X
Blocks execution of the selected DTS package. Use this command parameter when you want to create an
encrypted command line without executing the DTS package.
/!D
Deletes the DTS package from an instance of SQL Server. The package is not executed. It is not
possible to delete a specific DTS package from a structured storage file. The entire file needs to
be overwritten using the /F and /S options.
/!Y
Displays the encrypted command used to execute the DTS package without executing it.
/!C
Copies the command used to execute the DTS package to the Microsoft Windows clipboard. This option
can also be used in conjunction with /!X and /!Y.
Remarks
Security Note Batch files may contain credentials stored in plain text. Credentials may be echoed
to the user's screen during batch execution.
If you do not specify any command line switches, specify an incorrect command line switch, or your
command statement contains a syntax error, dtsrun returns error information and usage instructions.
If you enter dtsrunui on the command line without any command line switches, you will start the DTS
Run utility.
Spaces between command switches and values are optional. Embedded spaces in values must be embedded
between double quotation marks.
If an option is specified multiple times, the last occurrence takes precedence. One exception is the
/A command switch. Specifying more than one /A switch creates multiple global variables.
When specifying a global variable with the /A command switch, you must use a type identifier to
indicate the data type of the global variable.
A tilde (~) character after the forward slash (for example, /~Z) indicates that the parameter value
is encrypted and what follows is the hexadecimal text of the encrypted value.
The table shows the global variable data types and their IDs.
Data type Type ID
Integer (small) 2
Integer 3
Real (4-byte) 4
Real (8-byte) 5
Currency 6
Date 7
String 8
Boolean 11
Decimal 14
Integer (1-byte) 16
Unsigned int (1-byte) 17
Unsigned int (2-byte) 18
Unsigned int (4-byte) 19
Integer (8-byte) 20
Unsigned int (8-byte) 21
Int 22
Unsigned int 23
HRESULT 25
Pointer 26
LPSTR 30
LPWSTR 31
For information about where to find or how to run this utility, see Getting Started with Command
Prompt Utilities.
Examples
To execute a DTS package saved as a COM-structured storage file, use:
dtsrun /Ffilename /Npackage_name /Mpackage_password
To execute a DTS package saved in the SQL Server msdb database, use:
dtsrun /Sserver_name /Uuser_nName /Ppassword /Npackage_name /Mpackage_password
To execute a DTS package saved in Meta Data Services, use:
dtsrun /Sserver_name /Uuser_nrame /Ppassword /Npackage_name /Rrepository_name
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ngaya" <ngaya.1pivm3@.mail.mcse.ms> wrote in message news:ngaya.1pivm3@.mail.mcse.ms...
> Will it be possible for anyone of you to help me with a example, for the
> syntax to use in command prompt with DTSrun.exe.
> The situation is this. I need to uninstall SQL Server and install MSDE
> for my client. I have DTS running already in SQL Server. I need to take
> a back up, and make it run in MSDE. The package name is dtsPack.dts. I
> have it in the location "c:\"
> Thanks for your help
>
> --
> ngaya
> Posted via http://www.mcse.ms
> View this thread: http://www.mcse.ms/message1244207.html
>
|||THanks a lot for your time. I still have one more question to be clarified. Should i save the DTS in the database msdb? or will it automatically get saved? Ok if i save it, should i attach the database fresh to the MSDE?
Thanks in advance
Ngaya|||Thanks...I got it at last...I took a backup of msdb database and restored in MSDE, and tried
exec msdb..sp_enum_dtspackages
go
to see the list and it was there.
I was able to run the pacakge successfully using dtsrun.
Phew!...this took a lot of my timelah!... If i had known the simple thing that dts packages are stored in msdb database...this would have been over long back...thanks for that info..
ngaya|||You can also open a package on the originating server and save as file, copy file, open file on dest
server and save to msdb.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ngaya" <ngaya.1pm9u1@.mail.mcse.ms> wrote in message news:ngaya.1pm9u1@.mail.mcse.ms...
> Thanks...I got it at last...I took a backup of msdb database and
> restored in MSDE, and tried
> exec msdb..sp_enum_dtspackages
> go
> to see the list and it was there.
> I was able to run the pacakge successfully using dtsrun.
> Phew!...this took a lot of my timelah!... If i had known the simple
> thing that dts packages are stored in msdb database...this would have
> been over long back...thanks for that info..
> ngaya
>
> --
> ngaya
> Posted via http://www.mcse.ms
> View this thread: http://www.mcse.ms/message1244207.html
>

Import and Export Wizard: transferring multiple tables from SQL Server 2005 to SQL Server 2000

Hi!

I just used the SSIS Import and Export Wizard to copy 50+ tables from SS05 to SS2K.

I found that the wizard created a package that I could not figure out how to edit, e.g., to change whether or not it had to CREATE a table, or just use an existing one. (I created some problems by manually editing the receiving table names to be ones that already existed -- but the original names it had did not exist, so it knew it had to create them. What I should have done, and eventually ended up doing, was scroll through my list of tables in the "receiving" box; I just figured editing the name would be faster, not realizing what problems I would create for myself.)

Anyhow, now that I see the complex package that the wizard creates, with a LOOP over the 50+ tables, I would like to know how/where in the package it is storing the information about the tables to copy.

Basically the wizard creates the following Control Flow tab entries (in processing sequence order):

an Execute SQL Task: NonTransactableSql an Execute SQL Task: START TRANSACTION a Sequence Container: Transaction Scoping Sequence, which contains an Execute SQL Task: AllowedToFailPrologueSql an Execute SQL Task: PrologueSql a Foreach Loop Container, which contains a Transfer Task with an icon I did not notice in the Toolbox an Execute Package Task: Execute Inner Package an Execute SQL Task: EpilogueSql an "on success" arrow to an Execute SQL Task: COMMIT TRANSACTION an Execute SQL Task: PostTransaction Sql an "on failure" arrow to an Execute SQL Task: ROLLBACK TRANSACTION an Execute SQL Task: CompensatingSql

Where, and how, can I look within this package to see the details about the tables I am transferring? I see that one of the Connection Managers is "TableSchema.XML" -- but it points to a temporary file on my hard drive, that I presume is populated by the package. Where does it get its information?

This is certainly much more complex than the package I would have written, based on my limited knowledge of SSIS. I would have been inclined to create 50+ Data Flow tasks, one for each table.

So now I'm trying to understand why the Wizard created this more-complex package.

Any help will be appreciated, including references to non-Microsoft books/websites/etc.

Thanks in advance.

Dan

Hi Dan,

you can also have a package with 50 parallel data flows built if you uncheck the "Optimize for Many Tables" checkbox. That might be easier for you to edit. This solution does not scale too well with "really" too many tables so we had to emloy the complex package you are referring to. The metadata is stored in the XML file you mentioned and the transfer task goes through that file and generates simple data flows on the fly and executes them one by one.

HTH.

|||

Bob,

Thanks.

I also now know how to get the "50 parallel data flows" that is easier to edit.

I don't understand, though, how the metadata in the XML would "tag along" if I were to save the SSIS package to the File System and copy it to a network drive, where others could use it.

Dan

|||

You would need to copy all the associated files (like the XML with metadata) and tweak the connections to point to the new location.

Thanks.

|||

Bob,

Thanks again.

So it seems the Wizard creates an SSIS package, and also whatever files are needed to support that package, e.g., the XML file with the table names and structures (if a CREATE is necessary). The user of the Wizard must be smart enough to realize that anything in the Connection Manager must accompany the SSIS package -- and that such "temporary folder" files are essential to the task (so they should not be deleted by any "housekeeping" effort on the PC). That's good to know.

I would have expected such files to end up in someplace like the BIN folder found in the same folder where the SSIS package is created. If it isn't a long explanation, maybe you might share why the development team did not use the BIN folder for such files. (I am not wanting to be "nasty" -- just curious about how such decisions are made by the development team.)

Dan

|||

Hi Dan,

the transfer tables task was not initially designed to be used by the wizard. It was built by SMO team to allow copying tables using their APIs. When used that way the internal package is invisible and it makes sense to put the additional files to the temp folders. Later, we realized it might be suitable for our purpose. We weren't sure how often our users would actually want to preserve this package.

We realized it is a problem now and found some additional issues with the table transfer provider task, so we are looking into simplifying the wizard generated packages for Katmai.

Thanks,

-Bob

|||

Bob,

Thanks for the response. I truly appreciate the help the Wizard has given me on many occasions.

I have learned a lot from the Wizard, using it to convert my SQL Server 2000 DTS Packages to SSIS, and learning from the packages it created.

Since you mentioned some new features, is there any current plan to enhance the Execute Package Utility so that it can accommodate the movement of data from MS Access 2003 SP2 to SQL Server 2005? I can do that when I use Visual Studio 2005, but when I try to execute the package with the Execute Package Utility I get many errors of the form

Error: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "Data Conversion 1" (49).

The only data conversion I perform is double-byte characters to single-byte characters.

(Maybe I should ask this in a separate thread?)

Dan

|||

Are trying to run the package on the same machine it worked from the designer? What edition of the product you have installed?

It does not seem like new features are needed for this. You just need a proper edition of the product on the machine where you run the package.

Thanks.

|||

Hi Bob,

I'm having a similar issue as Dan, with two notable differences. First, I have over 300 tables to transfer and Second, most of the tables have identity columns.

When I went through the wizard, with "Optimize for Many Tables" checked, it ignored the adjustments to accept the identity values and created new values. Is there a way to adjust this script to accept the Identity values without setting up 300+ parallel data flows? The components are identical to what Dan described in the first posting.

If this can't be done, other suggestions are welcome.

Thanks - Gary

|||

Hi Gary,

unfortunately you have hit another issue with the transfer tables task I was referring to in the previous post. Currently, it is not possible to pass the identitty column settings when the "Optimize for many tables" option is selected.

The best workaround I can offer is to copy your tables in multiple batches (50 tables each should work, it might take up to 100 depending on your hardwear but you would need to test it) with unchecked "Optimize for many tables" option.

Thanks.

|||

Bob,

Thanks for your reply.

Yes, I am trying to run the package from the same machine it worked from the designer. But I am trying to run a "file system" copy of the package that I placed on the network drive. I am out of the office at the moment, so I cannot try running the exact copy on my PC hard drive -- but I will be back in the office in a few days, and can try doing so at that time.

We upgraded to SP2 a month or two ago, for SQL Server 2005. Did you need more "edition" information? If so, I will provide it on Friday, or so.

I just figured it (transfer from Access, and convert Access tables with single-byte characters to double-byte characters, as seem to be usual for SSIS input, then back to single-byte characters for placement in the SQL Server 2005 tables) was a capability that went beyond the intent of the "standalone" package runner (outside Visual Studio), Execute Package Utility.

I am pleased to learn that I may not need to use Visual Studio 2005 to perform this movement of data from Access to SQL Server 2005.

Dan

|||

Bob,

I am back at my desk, where I tried to run the SSIS package that moves approx. 50 tables from MS Access to SQL Server 2005.

The Access version is 2003 (11.6566.8132) SP2.

The SQL Server version is 9.0.3042

The only version information I see in the About box for "About DTExecUI" is "Version: 1.0". Is there some other place I should be seeking version information for this product?

Dan

|||

Dan,

I was asking about the edition of your SQL server instalation; is it Developer, Standard or Enterprise edition?

Thanks.

|||

Bob,

We have the Enterprise edition of SQL Server 2005 in the environment where I am trying to perform the task.

Dan

|||

Have you installed the entire SSIS module on all of those machines as well?

Thanks,

Bob

Import and Export SQL 2005 Maintenance Plan

I had created a maintenance plan and configure and scheduled my maintenace plan to run. I would like to save this package as a file into a source control. SO I use SSIS to export the package under stored packages->MSDB->Maintenace Plan. After that, I wanted to test my import process. So I deleted the packages under SSIS ->stored packages->MSDB->Maintenance plan and I use the import to add the package from my previously exported package stored in a file .dtxs extention.

So the problem is, after I imported my package. I lost the configured schedule and the job runs without doing anything. When I try to go in and make changes to the package, by adding a new schedule under SQL server -> management->maintenance plan. I receive a odd error message and it doesn't allow me to save the package.. The error message I got is...."GUID should contain 32 digits with 4 dashes (xxxxx-xxx-xx...)"

So my questions are: 1. why did the re-import loses the originally configure job run schedule. 2. why doesn't the re-import package works by backing up database as it was first setup. 3. why I cannot re-edit this package and saving the package error out?

Thank you for reading and for your helps! --Jon

I hope this should help. I have the same problem and working out on it

http://support.microsoft.com/default.aspx/kb/922651

http://sqlug.be/blogs/drivenbysql/archive/2006/10/21/374.aspx

|||I had similar issues. SSIS wasn't installed first, so I installed it then starting to get that GUID error. It was a simple fix actually. In studio, got to SQL Server Agent, Jobs and it had jobs for all my deleted plans. I cleared out the jobs, then created a brand new plan and it worked fine. I guess the errors created when I tried to create a plan prior to the SSIS installation caused it not to clear the jobs.

Though, this may or may not be what happened to you.

Import and Export SQL 2005 Maintenance Plan

I had created a maintenance plan and configure and scheduled my maintenace plan to run. I would like to save this package as a file into a source control. SO I use SSIS to export the package under stored packages->MSDB->Maintenace Plan. After that, I wanted to test my import process. So I deleted the packages under SSIS ->stored packages->MSDB->Maintenance plan and I use the import to add the package from my previously exported package stored in a file .dtxs extention.

So the problem is, after I imported my package. I lost the configured schedule and the job runs without doing anything. When I try to go in and make changes to the package, by adding a new schedule under SQL server -> management->maintenance plan. I receive a odd error message and it doesn't allow me to save the package.. The error message I got is...."GUID should contain 32 digits with 4 dashes (xxxxx-xxx-xx...)"

So my questions are: 1. why did the re-import loses the originally configure job run schedule. 2. why doesn't the re-import package works by backing up database as it was first setup. 3. why I cannot re-edit this package and saving the package error out?

Thank you for reading and for your helps! --Jon

I hope this should help. I have the same problem and working out on it

http://support.microsoft.com/default.aspx/kb/922651

http://sqlug.be/blogs/drivenbysql/archive/2006/10/21/374.aspx

|||I had similar issues. SSIS wasn't installed first, so I installed it then starting to get that GUID error. It was a simple fix actually. In studio, got to SQL Server Agent, Jobs and it had jobs for all my deleted plans. I cleared out the jobs, then created a brand new plan and it worked fine. I guess the errors created when I tried to create a plan prior to the SSIS installation caused it not to clear the jobs.

Though, this may or may not be what happened to you.

Import a DTS package?

Hi all,
I exported a dts package to a *.dts file, but now's
the question how do i import such a package in
SQL Server again...
Does anybody know that?
cheersOriginally posted by rgndrp
Hi all,
I exported a dts package to a *.dts file, but now's
the question how do i import such a package in
SQL Server again...

Does anybody know that?

cheers

In sql server you can make a right click on dts in Enterprise manager un select task open package.
The select the package.

If your are using ms sql 7 be sure that your server is up to date.|||thnx alot

import a dts package

Hi i was wodering how can i transfer a developed dts package from sqlserver
to an installed MSDE2000.
Hi Yaniv,
Easiest is probably to save it to a file, and run the file using dtsrun
(which does come with the MSDE).
HTH,
Greg Low [MVP]
MSDE Manager SQL Tools
www.whitebearconsulting.com
"yaniv" <yaniv@.discussions.microsoft.com> wrote in message
news:43B7014E-41A4-4534-8249-D2B32E6225C1@.microsoft.com...
> Hi i was wodering how can i transfer a developed dts package from
> sqlserver
> to an installed MSDE2000.
>
|||nevermind found a way.
a simple VBScript that imports the file.dts :
Const DTSSQLStgFlag_UseTrustedConnection = 256
dim ServerName,dtsFilePath,userPassword
Dim dtsp, dtsPkg
ServerName = "server"
dtsFilePath = "d:\path\File.dts"
userPassword = ""
Set dtsPkg = CreateObject("DTS.Package")
dtsp = dtsPkg.LoadFromStorageFile(dtsFilePath,userPasswor d)
dtsPkg.SaveToSQLServer ServerName, "","" ,DTSSQLStgFlag_UseTrustedConnection
|||Hi Greg,
I have installed latest MSDE 2000 Rel A.
I do not see and file named dtsrun.
What am I missing?
Thanks
"Greg Low [MVP]" wrote:

> Hi Yaniv,
> Easiest is probably to save it to a file, and run the file using dtsrun
> (which does come with the MSDE).
> HTH,
> --
> Greg Low [MVP]
> MSDE Manager SQL Tools
> www.whitebearconsulting.com
> "yaniv" <yaniv@.discussions.microsoft.com> wrote in message
> news:43B7014E-41A4-4534-8249-D2B32E6225C1@.microsoft.com...
>
>
|||Nevermind. My mistake. Thanks
"Subhojit Banerjee" wrote:
[vbcol=seagreen]
> Hi Greg,
> I have installed latest MSDE 2000 Rel A.
> I do not see and file named dtsrun.
> What am I missing?
> Thanks
>
> "Greg Low [MVP]" wrote: