DataType change

I recieve a text file with a field that has a text format of 20010910. I import this data into sql server 7.0 utilizing the import wizard. (The wizard will not convert the data to datetime format) I need to know how to convert this data into date format of 2001/09/10. How do I do this in sql.

Thank You,
David A. FullertonYou can add a string column to your table and CONVERT in a post-importing execution or use Visual Basic CDATE in Transformation tab in DTS just before click OK in usual IMPORT... dialog|||CDATE will not work in this example. However, I agree with Cesar's recommendations. In this case, since using vbscript will be more complicated than using cdate you can try the following after the data is loaded:

select convert(varchar(10), cast('20010920' as datetime), 111)|||Thank You That worked..Have a great day!|||Happy to help and good luck.

DataSizer for 2000

Hi,
I'm trying to estimate the amount of space that will be required for several
import tables.
I've run the DataSizer tool and it calculates the following for a table with
3 INT fields and one REAL field (prec - 24).
Heap Table Data Sizer
Rows in table 137,242,307
Data Row Fixed Len Col Size 16
Number of Columns in data row 4
Number of Variable Length Columns in data row 0
Max Size of Variable Length Data in data row 0
Index Key Fixed Len Col Size 0
Number of Columns in Index Key 0
Number of Variable Length Columns in index key 0
Max Size of Variable Length Data in index key 0
Page size (not configurable) 8192
Page Hdr Size (not configurable) 96
Data Row Size Calculation
Data Row Header 4
Null Bitmap 3
Variable Length Columns Total Size 0
Minimum Row Length 16
Total Row Size 23
Data rows per page 324
Data pages 423588
Table Size 3,470,032,896
This example happens to be an existing table (heap) that reports the
following information via sp_spaceused
rows = 137242307
space used = 3569632 KB
There is about a 100MB difference between the two values returned (table
size est and table size actual). Is the DataSizer tool accurate for SQL
2000? Am I performing the calculation incorrectly?
Thanks
JerrySp_spaceused might use stale information (see the @.updateusage parameter to sp_spaceused and also
DBCC UPDATEUSAGE). Also, you might have less than 100% full pages (see DBCC SHOWCONTIG).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23yVzzudoFHA.1444@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I'm trying to estimate the amount of space that will be required for several import tables.
> I've run the DataSizer tool and it calculates the following for a table with 3 INT fields and one
> REAL field (prec - 24).
> Heap Table Data Sizer
> Rows in table 137,242,307
> Data Row Fixed Len Col Size 16
> Number of Columns in data row 4
> Number of Variable Length Columns in data row 0
> Max Size of Variable Length Data in data row 0
> Index Key Fixed Len Col Size 0
> Number of Columns in Index Key 0
> Number of Variable Length Columns in index key 0
> Max Size of Variable Length Data in index key 0
> Page size (not configurable) 8192
> Page Hdr Size (not configurable) 96
> Data Row Size Calculation
> Data Row Header 4
> Null Bitmap 3
> Variable Length Columns Total Size 0
> Minimum Row Length 16
> Total Row Size 23
> Data rows per page 324
> Data pages 423588
> Table Size 3,470,032,896
>
> This example happens to be an existing table (heap) that reports the following information via
> sp_spaceused
>
> rows = 137242307
> space used = 3569632 KB
> There is about a 100MB difference between the two values returned (table size est and table size
> actual). Is the DataSizer tool accurate for SQL 2000? Am I performing the calculation
> incorrectly?
> Thanks
> Jerry
>|||Hey Tibor.
I did use the updateusage param of sp_spaceused.
Yea I thought it might be due to fragmentation so I created a clustered
index on the table then dropped the clustered index to recompact the heap
pages. The 137 million rows now consume 3,399,200 KB (apx 70 MB less than
the estimated and about 170 MB less than before with the fragmentation).
Interesting ;-)
Thanks again for the reply.
Jerry
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eTrz2aeoFHA.3448@.TK2MSFTNGP12.phx.gbl...
> Sp_spaceused might use stale information (see the @.updateusage parameter
> to sp_spaceused and also DBCC UPDATEUSAGE). Also, you might have less than
> 100% full pages (see DBCC SHOWCONTIG).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23yVzzudoFHA.1444@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> I'm trying to estimate the amount of space that will be required for
>> several import tables.
>> I've run the DataSizer tool and it calculates the following for a table
>> with 3 INT fields and one REAL field (prec - 24).
>> Heap Table Data Sizer
>> Rows in table 137,242,307
>> Data Row Fixed Len Col Size 16
>> Number of Columns in data row 4
>> Number of Variable Length Columns in data row 0
>> Max Size of Variable Length Data in data row 0
>> Index Key Fixed Len Col Size 0
>> Number of Columns in Index Key 0
>> Number of Variable Length Columns in index key 0
>> Max Size of Variable Length Data in index key 0
>> Page size (not configurable) 8192
>> Page Hdr Size (not configurable) 96
>> Data Row Size Calculation
>> Data Row Header 4
>> Null Bitmap 3
>> Variable Length Columns Total Size 0
>> Minimum Row Length 16
>> Total Row Size 23
>> Data rows per page 324
>> Data pages 423588
>> Table Size 3,470,032,896
>>
>> This example happens to be an existing table (heap) that reports the
>> following information via sp_spaceused
>>
>> rows = 137242307
>> space used = 3569632 KB
>> There is about a 100MB difference between the two values returned (table
>> size est and table size actual). Is the DataSizer tool accurate for SQL
>> 2000? Am I performing the calculation incorrectly?
>> Thanks
>> Jerry
>|||Hi Jerry,
If I take the numbers from your calculation (3,470,032,896 bytes) and compare to the numbers from
sp_spaceused (I assume) (3,399,200 KB) and convert properly to MB etc, I get:
SELECT KB, KB/1024 AS MB, KB/(1024*1024) AS GB
FROM
(
SELECT 3470032896/1024 AS KB
UNION
SELECT 3399200 AS KB
) AS i
KB MB GB
-- -- --
3388704.000000 3309.28125000000 3.23171997070312500
3399200.000000 3319.53125000000 3.24172973632812500
I.e. a difference of 10 MB, which for a 3.2GB table is pretty close, IMO :-).
Btw, I always select from sysindexes (after updateusage) when I want to get these values. I never
remember whether sp_spaceused uses reserved, dpages or the used column. To be honest, I don't
remember the difference between dpages and used by heart either, so I always look up sysindexes in
BOL.
I should also say that I didn't actually look at your formula/calculation. I was initially focused
on the stale information in sysindexes.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23CmI1geoFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Hey Tibor.
> I did use the updateusage param of sp_spaceused.
> Yea I thought it might be due to fragmentation so I created a clustered index on the table then
> dropped the clustered index to recompact the heap pages. The 137 million rows now consume
> 3,399,200 KB (apx 70 MB less than the estimated and about 170 MB less than before with the
> fragmentation).
> Interesting ;-)
> Thanks again for the reply.
> Jerry
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:eTrz2aeoFHA.3448@.TK2MSFTNGP12.phx.gbl...
>> Sp_spaceused might use stale information (see the @.updateusage parameter to sp_spaceused and also
>> DBCC UPDATEUSAGE). Also, you might have less than 100% full pages (see DBCC SHOWCONTIG).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>> news:%23yVzzudoFHA.1444@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> I'm trying to estimate the amount of space that will be required for several import tables.
>> I've run the DataSizer tool and it calculates the following for a table with 3 INT fields and
>> one REAL field (prec - 24).
>> Heap Table Data Sizer
>> Rows in table 137,242,307
>> Data Row Fixed Len Col Size 16
>> Number of Columns in data row 4
>> Number of Variable Length Columns in data row 0
>> Max Size of Variable Length Data in data row 0
>> Index Key Fixed Len Col Size 0
>> Number of Columns in Index Key 0
>> Number of Variable Length Columns in index key 0
>> Max Size of Variable Length Data in index key 0
>> Page size (not configurable) 8192
>> Page Hdr Size (not configurable) 96
>> Data Row Size Calculation
>> Data Row Header 4
>> Null Bitmap 3
>> Variable Length Columns Total Size 0
>> Minimum Row Length 16
>> Total Row Size 23
>> Data rows per page 324
>> Data pages 423588
>> Table Size 3,470,032,896
>>
>> This example happens to be an existing table (heap) that reports the following information via
>> sp_spaceused
>>
>> rows = 137242307
>> space used = 3569632 KB
>> There is about a 100MB difference between the two values returned (table size est and table size
>> actual). Is the DataSizer tool accurate for SQL 2000? Am I performing the calculation
>> incorrectly?
>> Thanks
>> Jerry
>>
>

DataReader Source and ODBC connection to PostgresSQL

Hi,

I am trying to use the DataReader Source to import a table from a PostgresSQL database into a new table in SQL 2005 database. It works for all tables except one, which has over 80,000 records with long text columns. When I limit the import to fraction of records (3,000 to 4,000 records) it works fine but when I try to get all it generates the following errors:

Source: DataReader using ADO.NET and ODBC driver to access PostgresSQL table
Destination: OLE DB Destination - new table in SQL 2005
(BTW - successful import with DTS packagein SQL 2000)

Errors
Error: 0x80070050 at Import File, DTS.Pipeline: The file exists.

Error: 0xC0048019 at Import File, DTS.Pipeline: The buffer manager could not get a temporary file name. The call to GetTempFileName failed.

Error: 0xC0048013 at Import File, DTS.Pipeline: The buffer manager could not create a temporary file on the path "C:\Documents and Settings\michaelsh\Local Settings\Temp". The path will not be considered for temporary storage again.

Error: 0xC0047070 at Import File, DTS.Pipeline: The buffer manager cannot create a file to spool a long object on the directories named in the BLOBTempStoragePath property. Either an incorrect file name was provided, or there are no permissions.

Error: 0xC0209029 at Import File, DataReader Source - Articles [1]: The "component "DataReader Source - Articles" (1)" failed because error code 0x80004005 occurred, and the error row disposition on "output column "probsumm" (1639)" specifies failure on error. An error occurred on the specified object of the specified component.

Error: 0xC02090F5 at Import File, DataReader Source - Articles [1]: The component "DataReader Source - Articles" (1) was unable to process the data.

Error: 0xC0047038 at Import File, DTS.Pipeline: The PrimeOutput method on component "DataReader Source - Articles" (1) returned error code 0xC02090F5. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
End

Any idea why it can't create a temp file or why it complains about the "The File exists", which file, where, etc. Any help or alternative suggestions are greatly appreciated. What I am missing or doing wrong here?

Best,

Michael Sh

I am definitely receiving the exact same error, with the exact same specifications- everything is working fine until I hit 19,932 - it sounds like it is some kind of error (I looked in the folder it was referencing) where there are too many temporary files- basically the directory gets mega huge with a ton of temporary filenames - and i guess it runs out of temporary file names to use -I think that is what stems the "The File Exists" error.

Right now, I am trying to see if I can just do a data conversion from a text string into a varchar(8000) field - i'm just going to truncate the column if needed -

Have you made any progress?

-robert

|||

After exploring many differenet options, the only way I could get it to work on the original machine (desktop with 2 processor and 2 GB RAM) was to create multiple dataflow tasks, limit each to about 5,000 records then connect them to each other. Not a pretty solution but once working, I could eliminate data in records as culprit.

Ironically once I've installed SQL 2005 on my laptop, the same exact package worked without a hitch on first run going through 80,000 + records. Both machines run on XP Pro, the only difference is my laptop is single processor using slightly older version of Postgres ODBC driver, which I can't locate any more to see if that is the culprit or not.

Best,

Michael Sh

|||

My first guess is that the pipeline is trying to create a file to spool the long columns due to memory limits, and it is unable to create that either because of permissions, or because it does not know where to put it.

Do you have file write permissions where this package is running?

What is the enivironment TEMP or TMP variable value set to?

Thanks

Mark

|||

You can tell that the temp directory is located at:

C:\Documents and Settings\<username>\Local Settings\Temp

Basically what I did, was I emptied out this folder, and then ran my package- as soon as it started running- it started adding thousands of files named "DTS####.tmp"

It seems that after some point in time it stopped and said it could not find another filename to use because it was already created.

I'm sure we have write permissions to this folder - it just gets filled. To me it is a flaw of SSIS -

I did a test and I created a similar package in DTS - it ran quickly and effortlessly.

|||

Thanks for the extra information.

Could you share exactly how many files are in the temp directory at the point of failure?

Aslo, approximately how many columns do you have in the table, and how many of those are LOB columns?

Thanks

Mark

|||

I re-viewed the data files- and I found that they are formatted as such:

DTS####.tmp - where # is a hexadecimal character -

I noticed the files were as such:

DTSAAA0.TMP

DTSAAA1.TMP

DTSAAA2.TMP

DTSAAA3.TMP

.....

DTSAAAA.TMP

DTSAAAB.TMP

...

DTSAAAF.TMP

DTSAAB0.TMP

...

and so on- an so on- so you can see that there would definitely be a limit to this numbering scheme.

In my dataset, all I have are about 8 columns, only one of which is a TEXT field.

I have about 77,000 records - but the data in the TEXT column is quite large sometimes. (> 8000 characters)

-rob

|||

Yes, it does sound like you are running out of temp files. Now we need to figure out why so many are created.

At this point, i recommend that you go here:http://msdn.microsoft.com/sql/bi/integration/ and choose the MSDN Product Feedback link under Support, and select Report a Bug. This is likely something that will need to be reproduced and investigated by the development team.

Thanks
Mark

|||

A teammate suggested something that might help you work around this. You can have additional temp paths by setting the BLOBTempStoragePath to a semi-colon separated list of paths. This way, you will be able to create more unique temporary files.

Mark

|||

Great find - I actually took a different route in creating a temporary fix and created a DTS package (*gasp!*) to just jam the data into my table ...

But next time if it ever happens to me again, I will give it a try!

|||

Even after creating the semi colon delimited set in Temporrayblobtsorage it gives me the same error

Package Package

DataReader Source and ODBC connection to PostgresSQL

Hi,

I am trying to use the DataReader Source to import a table from a PostgresSQL database into a new table in SQL 2005 database. It works for all tables except one, which has over 80,000 records with long text columns. When I limit the import to fraction of records (3,000 to 4,000 records) it works fine but when I try to get all it generates the following errors:

Source: DataReader using ADO.NET and ODBC driver to access PostgresSQL table
Destination: OLE DB Destination - new table in SQL 2005
(BTW - successful import with DTS packagein SQL 2000)

Errors
Error: 0x80070050 at Import File, DTS.Pipeline: The file exists.

Error: 0xC0048019 at Import File, DTS.Pipeline: The buffer manager could not get a temporary file name. The call to GetTempFileName failed.

Error: 0xC0048013 at Import File, DTS.Pipeline: The buffer manager could not create a temporary file on the path "C:\Documents and Settings\michaelsh\Local Settings\Temp". The path will not be considered for temporary storage again.

Error: 0xC0047070 at Import File, DTS.Pipeline: The buffer manager cannot create a file to spool a long object on the directories named in the BLOBTempStoragePath property. Either an incorrect file name was provided, or there are no permissions.

Error: 0xC0209029 at Import File, DataReader Source - Articles [1]: The "component "DataReader Source - Articles" (1)" failed because error code 0x80004005 occurred, and the error row disposition on "output column "probsumm" (1639)" specifies failure on error. An error occurred on the specified object of the specified component.

Error: 0xC02090F5 at Import File, DataReader Source - Articles [1]: The component "DataReader Source - Articles" (1) was unable to process the data.

Error: 0xC0047038 at Import File, DTS.Pipeline: The PrimeOutput method on component "DataReader Source - Articles" (1) returned error code 0xC02090F5. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
End

Any idea why it can't create a temp file or why it complains about the "The File exists", which file, where, etc. Any help or alternative suggestions are greatly appreciated. What I am missing or doing wrong here?

Best,

Michael Sh

I am definitely receiving the exact same error, with the exact same specifications- everything is working fine until I hit 19,932 - it sounds like it is some kind of error (I looked in the folder it was referencing) where there are too many temporary files- basically the directory gets mega huge with a ton of temporary filenames - and i guess it runs out of temporary file names to use -I think that is what stems the "The File Exists" error.

Right now, I am trying to see if I can just do a data conversion from a text string into a varchar(8000) field - i'm just going to truncate the column if needed -

Have you made any progress?

-robert

|||

After exploring many differenet options, the only way I could get it to work on the original machine (desktop with 2 processor and 2 GB RAM) was to create multiple dataflow tasks, limit each to about 5,000 records then connect them to each other. Not a pretty solution but once working, I could eliminate data in records as culprit.

Ironically once I've installed SQL 2005 on my laptop, the same exact package worked without a hitch on first run going through 80,000 + records. Both machines run on XP Pro, the only difference is my laptop is single processor using slightly older version of Postgres ODBC driver, which I can't locate any more to see if that is the culprit or not.

Best,

Michael Sh

|||

My first guess is that the pipeline is trying to create a file to spool the long columns due to memory limits, and it is unable to create that either because of permissions, or because it does not know where to put it.

Do you have file write permissions where this package is running?

What is the enivironment TEMP or TMP variable value set to?

Thanks

Mark

|||

You can tell that the temp directory is located at:

C:\Documents and Settings\<username>\Local Settings\Temp

Basically what I did, was I emptied out this folder, and then ran my package- as soon as it started running- it started adding thousands of files named "DTS####.tmp"

It seems that after some point in time it stopped and said it could not find another filename to use because it was already created.

I'm sure we have write permissions to this folder - it just gets filled. To me it is a flaw of SSIS -

I did a test and I created a similar package in DTS - it ran quickly and effortlessly.

|||

Thanks for the extra information.

Could you share exactly how many files are in the temp directory at the point of failure?

Aslo, approximately how many columns do you have in the table, and how many of those are LOB columns?

Thanks

Mark

|||

I re-viewed the data files- and I found that they are formatted as such:

DTS####.tmp - where # is a hexadecimal character -

I noticed the files were as such:

DTSAAA0.TMP

DTSAAA1.TMP

DTSAAA2.TMP

DTSAAA3.TMP

.....

DTSAAAA.TMP

DTSAAAB.TMP

...

DTSAAAF.TMP

DTSAAB0.TMP

...

and so on- an so on- so you can see that there would definitely be a limit to this numbering scheme.

In my dataset, all I have are about 8 columns, only one of which is a TEXT field.

I have about 77,000 records - but the data in the TEXT column is quite large sometimes. (> 8000 characters)

-rob

|||

Yes, it does sound like you are running out of temp files. Now we need to figure out why so many are created.

At this point, i recommend that you go here:http://msdn.microsoft.com/sql/bi/integration/ and choose the MSDN Product Feedback link under Support, and select Report a Bug. This is likely something that will need to be reproduced and investigated by the development team.

Thanks
Mark

|||

A teammate suggested something that might help you work around this. You can have additional temp paths by setting the BLOBTempStoragePath to a semi-colon separated list of paths. This way, you will be able to create more unique temporary files.

Mark

|||

Great find - I actually took a different route in creating a temporary fix and created a DTS package (*gasp!*) to just jam the data into my table ...

But next time if it ever happens to me again, I will give it a try!

|||

Even after creating the semi colon delimited set in Temporrayblobtsorage it gives me the same error

Package Package

DataReader Source and ODBC connection to PostgresSQL

Hi,

I am trying to use the DataReader Source to import a table from a PostgresSQL database into a new table in SQL 2005 database. It works for all tables except one, which has over 80,000 records with long text columns. When I limit the import to fraction of records (3,000 to 4,000 records) it works fine but when I try to get all it generates the following errors:

Source: DataReader using ADO.NET and ODBC driver to access PostgresSQL table
Destination: OLE DB Destination - new table in SQL 2005
(BTW - successful import with DTS packagein SQL 2000)

Errors
Error: 0x80070050 at Import File, DTS.Pipeline: The file exists.

Error: 0xC0048019 at Import File, DTS.Pipeline: The buffer manager could not get a temporary file name. The call to GetTempFileName failed.

Error: 0xC0048013 at Import File, DTS.Pipeline: The buffer manager could not create a temporary file on the path "C:\Documents and Settings\michaelsh\Local Settings\Temp". The path will not be considered for temporary storage again.

Error: 0xC0047070 at Import File, DTS.Pipeline: The buffer manager cannot create a file to spool a long object on the directories named in the BLOBTempStoragePath property. Either an incorrect file name was provided, or there are no permissions.

Error: 0xC0209029 at Import File, DataReader Source - Articles [1]: The "component "DataReader Source - Articles" (1)" failed because error code 0x80004005 occurred, and the error row disposition on "output column "probsumm" (1639)" specifies failure on error. An error occurred on the specified object of the specified component.

Error: 0xC02090F5 at Import File, DataReader Source - Articles [1]: The component "DataReader Source - Articles" (1) was unable to process the data.

Error: 0xC0047038 at Import File, DTS.Pipeline: The PrimeOutput method on component "DataReader Source - Articles" (1) returned error code 0xC02090F5. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
End

Any idea why it can't create a temp file or why it complains about the "The File exists", which file, where, etc. Any help or alternative suggestions are greatly appreciated. What I am missing or doing wrong here?

Best,

Michael Sh

I am definitely receiving the exact same error, with the exact same specifications- everything is working fine until I hit 19,932 - it sounds like it is some kind of error (I looked in the folder it was referencing) where there are too many temporary files- basically the directory gets mega huge with a ton of temporary filenames - and i guess it runs out of temporary file names to use -I think that is what stems the "The File Exists" error.

Right now, I am trying to see if I can just do a data conversion from a text string into a varchar(8000) field - i'm just going to truncate the column if needed -

Have you made any progress?

-robert

|||

After exploring many differenet options, the only way I could get it to work on the original machine (desktop with 2 processor and 2 GB RAM) was to create multiple dataflow tasks, limit each to about 5,000 records then connect them to each other. Not a pretty solution but once working, I could eliminate data in records as culprit.

Ironically once I've installed SQL 2005 on my laptop, the same exact package worked without a hitch on first run going through 80,000 + records. Both machines run on XP Pro, the only difference is my laptop is single processor using slightly older version of Postgres ODBC driver, which I can't locate any more to see if that is the culprit or not.

Best,

Michael Sh

|||

My first guess is that the pipeline is trying to create a file to spool the long columns due to memory limits, and it is unable to create that either because of permissions, or because it does not know where to put it.

Do you have file write permissions where this package is running?

What is the enivironment TEMP or TMP variable value set to?

Thanks

Mark

|||

You can tell that the temp directory is located at:

C:\Documents and Settings\<username>\Local Settings\Temp

Basically what I did, was I emptied out this folder, and then ran my package- as soon as it started running- it started adding thousands of files named "DTS####.tmp"

It seems that after some point in time it stopped and said it could not find another filename to use because it was already created.

I'm sure we have write permissions to this folder - it just gets filled. To me it is a flaw of SSIS -

I did a test and I created a similar package in DTS - it ran quickly and effortlessly.

|||

Thanks for the extra information.

Could you share exactly how many files are in the temp directory at the point of failure?

Aslo, approximately how many columns do you have in the table, and how many of those are LOB columns?

Thanks

Mark

|||

I re-viewed the data files- and I found that they are formatted as such:

DTS####.tmp - where # is a hexadecimal character -

I noticed the files were as such:

DTSAAA0.TMP

DTSAAA1.TMP

DTSAAA2.TMP

DTSAAA3.TMP

.....

DTSAAAA.TMP

DTSAAAB.TMP

...

DTSAAAF.TMP

DTSAAB0.TMP

...

and so on- an so on- so you can see that there would definitely be a limit to this numbering scheme.

In my dataset, all I have are about 8 columns, only one of which is a TEXT field.

I have about 77,000 records - but the data in the TEXT column is quite large sometimes. (> 8000 characters)

-rob

|||

Yes, it does sound like you are running out of temp files. Now we need to figure out why so many are created.

At this point, i recommend that you go here:http://msdn.microsoft.com/sql/bi/integration/ and choose the MSDN Product Feedback link under Support, and select Report a Bug. This is likely something that will need to be reproduced and investigated by the development team.

Thanks
Mark

|||

A teammate suggested something that might help you work around this. You can have additional temp paths by setting the BLOBTempStoragePath to a semi-colon separated list of paths. This way, you will be able to create more unique temporary files.

Mark

|||

Great find - I actually took a different route in creating a temporary fix and created a DTS package (*gasp!*) to just jam the data into my table ...

But next time if it ever happens to me again, I will give it a try!

|||

Even after creating the semi colon delimited set in Temporrayblobtsorage it gives me the same error

Package Package

Datafile From Non-Windows Server

Can SQL Server 2000 import a flat data file directly from a mainframe
or other server that is not a Windows server or does the data file
need to be moved to a Windows server first?
Jerry
On Fri, 10 Aug 2007 18:18:01 -0000, Jerry <jerryalan@.gmail.com> wrote:

>Can SQL Server 2000 import a flat data file directly from a mainframe
>or other server that is not a Windows server or does the data file
>need to be moved to a Windows server first?
The first issue is whether the file can be "seen" and accessed. I
have seen AS/400 setups where that system had part of the disk visible
over the network so that a Windows box could access the files like any
other network drive. If the file is visible in that way something
should be possible. However I generally used a transfer utility
rather than a shared directory.
The next issue is whether the file is in a useable format. All the
files I ever loaded from a mainframe started on the mainframe end as
readable text, with no binary, float, packed, or zoned decimal fields,
likewise no "overpunch" where the sign is carried in half a byte. Then
it was a matter of using a transfer tool tool that converted IBM's
mainframe character set, EBCDIC, to the ASCII used by the rest of the
world.
A final aternative would be a driver that allowed ODBC or equivelent
access to mainframe data.
Roy Harvey
Beacon Falls, CT
|||In addition, the best approach here is to define your process:
1) dump the flat-file from the AS400 to a Windows machine or an FTP site
2) Use DTS to do ETL - there is a File Transfer Protocol Task if you intend
to read from an FTP site or a Text File Source if you intend to dump the
file on a Windows machine
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:s1gpb3prd16altj63k5qatvicte8vd2im7@.4ax.com...
> On Fri, 10 Aug 2007 18:18:01 -0000, Jerry <jerryalan@.gmail.com> wrote:
>
> The first issue is whether the file can be "seen" and accessed. I
> have seen AS/400 setups where that system had part of the disk visible
> over the network so that a Windows box could access the files like any
> other network drive. If the file is visible in that way something
> should be possible. However I generally used a transfer utility
> rather than a shared directory.
> The next issue is whether the file is in a useable format. All the
> files I ever loaded from a mainframe started on the mainframe end as
> readable text, with no binary, float, packed, or zoned decimal fields,
> likewise no "overpunch" where the sign is carried in half a byte. Then
> it was a matter of using a transfer tool tool that converted IBM's
> mainframe character set, EBCDIC, to the ASCII used by the rest of the
> world.
> A final aternative would be a driver that allowed ODBC or equivelent
> access to mainframe data.
> Roy Harvey
> Beacon Falls, CT

Datafile From Non-Windows Server

Can SQL Server 2000 import a flat data file directly from a mainframe
or other server that is not a Windows server or does the data file
need to be moved to a Windows server first?
--
JerryOn Fri, 10 Aug 2007 18:18:01 -0000, Jerry <jerryalan@.gmail.com> wrote:
>Can SQL Server 2000 import a flat data file directly from a mainframe
>or other server that is not a Windows server or does the data file
>need to be moved to a Windows server first?
The first issue is whether the file can be "seen" and accessed. I
have seen AS/400 setups where that system had part of the disk visible
over the network so that a Windows box could access the files like any
other network drive. If the file is visible in that way something
should be possible. However I generally used a transfer utility
rather than a shared directory.
The next issue is whether the file is in a useable format. All the
files I ever loaded from a mainframe started on the mainframe end as
readable text, with no binary, float, packed, or zoned decimal fields,
likewise no "overpunch" where the sign is carried in half a byte. Then
it was a matter of using a transfer tool tool that converted IBM's
mainframe character set, EBCDIC, to the ASCII used by the rest of the
world.
A final aternative would be a driver that allowed ODBC or equivelent
access to mainframe data.
Roy Harvey
Beacon Falls, CT|||In addition, the best approach here is to define your process:
1) dump the flat-file from the AS400 to a Windows machine or an FTP site
2) Use DTS to do ETL - there is a File Transfer Protocol Task if you intend
to read from an FTP site or a Text File Source if you intend to dump the
file on a Windows machine
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:s1gpb3prd16altj63k5qatvicte8vd2im7@.4ax.com...
> On Fri, 10 Aug 2007 18:18:01 -0000, Jerry <jerryalan@.gmail.com> wrote:
>>Can SQL Server 2000 import a flat data file directly from a mainframe
>>or other server that is not a Windows server or does the data file
>>need to be moved to a Windows server first?
> The first issue is whether the file can be "seen" and accessed. I
> have seen AS/400 setups where that system had part of the disk visible
> over the network so that a Windows box could access the files like any
> other network drive. If the file is visible in that way something
> should be possible. However I generally used a transfer utility
> rather than a shared directory.
> The next issue is whether the file is in a useable format. All the
> files I ever loaded from a mainframe started on the mainframe end as
> readable text, with no binary, float, packed, or zoned decimal fields,
> likewise no "overpunch" where the sign is carried in half a byte. Then
> it was a matter of using a transfer tool tool that converted IBM's
> mainframe character set, EBCDIC, to the ASCII used by the rest of the
> world.
> A final aternative would be a driver that allowed ODBC or equivelent
> access to mainframe data.
> Roy Harvey
> Beacon Falls, CT

Datafile From Non-Windows Server

Can SQL Server 2000 import a flat data file directly from a mainframe
or other server that is not a Windows server or does the data file
need to be moved to a Windows server first?
JerryOn Fri, 10 Aug 2007 18:18:01 -0000, Jerry <jerryalan@.gmail.com> wrote:

>Can SQL Server 2000 import a flat data file directly from a mainframe
>or other server that is not a Windows server or does the data file
>need to be moved to a Windows server first?
The first issue is whether the file can be "seen" and accessed. I
have seen AS/400 setups where that system had part of the disk visible
over the network so that a Windows box could access the files like any
other network drive. If the file is visible in that way something
should be possible. However I generally used a transfer utility
rather than a shared directory.
The next issue is whether the file is in a useable format. All the
files I ever loaded from a mainframe started on the mainframe end as
readable text, with no binary, float, packed, or zoned decimal fields,
likewise no "overpunch" where the sign is carried in half a byte. Then
it was a matter of using a transfer tool tool that converted IBM's
mainframe character set, EBCDIC, to the ASCII used by the rest of the
world.
A final aternative would be a driver that allowed ODBC or equivelent
access to mainframe data.
Roy Harvey
Beacon Falls, CT|||In addition, the best approach here is to define your process:
1) dump the flat-file from the AS400 to a Windows machine or an FTP site
2) Use DTS to do ETL - there is a File Transfer Protocol Task if you intend
to read from an FTP site or a Text File Source if you intend to dump the
file on a Windows machine
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:s1gpb3prd16altj63k5qatvicte8vd2im7@.
4ax.com...
> On Fri, 10 Aug 2007 18:18:01 -0000, Jerry <jerryalan@.gmail.com> wrote:
>
> The first issue is whether the file can be "seen" and accessed. I
> have seen AS/400 setups where that system had part of the disk visible
> over the network so that a Windows box could access the files like any
> other network drive. If the file is visible in that way something
> should be possible. However I generally used a transfer utility
> rather than a shared directory.
> The next issue is whether the file is in a useable format. All the
> files I ever loaded from a mainframe started on the mainframe end as
> readable text, with no binary, float, packed, or zoned decimal fields,
> likewise no "overpunch" where the sign is carried in half a byte. Then
> it was a matter of using a transfer tool tool that converted IBM's
> mainframe character set, EBCDIC, to the ASCII used by the rest of the
> world.
> A final aternative would be a driver that allowed ODBC or equivelent
> access to mainframe data.
> Roy Harvey
> Beacon Falls, CT

Database Tuning Advisor DTA Import Session Results?

Hi,

I have ran the Tuning Advisor (DTA) on a .trc file. Recommendations and reports came back.

I would like to take my exported session results and import that file into a DTA on a different machine with the SQL Server 2005 developer tools installed.

The file is the following Job010704SessionResultsExported.xml

I see how to import a session definition. I do not see how to import 'session results.'

Any ideas oh how to do that?

Thanks,

AIMDBA

DTA does not have such feature. You can file a feature request here.
https://connect.microsoft.com/sqlserver