| Author |
Topic |
|
Michael71
Posting Yak Master
126 Posts |
 Posted - 2006-12-26 : 13:23:26
|
| How do I tranfer a flat file (text) to SQL Server using DTS? Please point me in the right direction. |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-12-26 : 13:28:59
|
| http://msdn2.microsoft.com/en-us/library/aa298646(SQL.80).aspx |
 |
|
|
Michael71
Posting Yak Master
126 Posts |
Posted - 2006-12-26 : 13:39:13
|
| Can you give me a little more please? |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-12-26 : 13:53:33
|
| That's the full docs, so not sure what more you want, right below that in the contents is thishttp://msdn2.microsoft.com/en-us/library/aa177546(SQL.80).aspxThat gives you step by step instructions, it is for a database connection, but just select Flat File instead of SQL Server in the source drop down and you should be able to follow from there.Do it yourself and if you get stuck then ask more specific questions, just saying "point me in the right direction" isn't going to get you much more than links to the documentation, which after all is the "right direction". |
|
|
|
Michael71
Posting Yak Master
126 Posts |
Posted - 2006-12-26 : 14:20:36
|
| Okay...I have a question. If my SQL Server is on the Server, but my flat file is on my client computer. Will I be able to access it and complete the process? |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-12-26 : 14:30:24
|
| That depends, DTS can run on the server or the client. If you have Enterprise Manager installed on your client and you edit and run the DTS package on your computer then yes, you can have the file on your computer. Once you have the package saved, you could run it on your computer with the command line utility too, but unless you want to schedule it, it's probably easier to just open it in Enterprise Manager. |
|
|
|
Michael71
Posting Yak Master
126 Posts |
Posted - 2006-12-26 : 15:04:42
|
| The Flat file is on my Computer and the SQL Server is on the Server. It's like I can't reach the Flat file at all. Then when I try to down load the flat file through email on my server, it gets blocked by security. Please help. |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-12-26 : 15:18:20
|
| You'll have to describe what you've done and what error/s you get, just saying your computer can't reach the flat file doesn't help us to help you. |
|
|
|
Michael71
Posting Yak Master
126 Posts |
Posted - 2006-12-26 : 16:31:16
|
| I got a error of this sort......Error 0xc02020a1: Data Flow Task: Data conversion failed. The data conversion for column "Column 48" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.". (SQL Server Import and Export Wizard) Error 0xc020902a: Data Flow Task: The "output column "Column 48" (154)" failed because truncation occurred, and the truncation row disposition on "output column "Column 48" (154)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component. (SQL Server Import and Export Wizard) |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-12-26 : 16:43:43
|
| Now we're getting somewhere - your error is not at all that you "can't reach the Flat file at all". You're reaching the flat file just fine, but you have data in your flat file that is either wider/longer than the column you're importing into or that contains foreign characters (exactly what the error says). So look at "Column 48" and either fix the data you're importing or import into a wider column.Basically SQL Server is saying it cannot import the data you gave it without losing some of it, so it is aborting the import. |
|
|
|
Michael71
Posting Yak Master
126 Posts |
Posted - 2006-12-26 : 17:01:43
|
| I gave the column a varChar(MAX) and it still didn't work any ideas. Maybe the best avenue is to fix the column I'm importing from. |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-12-26 : 17:37:26
|
| How about international characters? Try changing it to nvarchar(max). |
|
|
|
Michael71
Posting Yak Master
126 Posts |
Posted - 2006-12-26 : 21:18:32
|
| Thanks, I'll try this when I get to work in the morning. |
|
|
|
Michael71
Posting Yak Master
126 Posts |
Posted - 2006-12-27 : 09:39:05
|
| I still get these errors....Error 0xc02020ed: Data Flow Task: Columns "Column 0" and "itemID" cannot convert between unicode and non-unicode string data types. (SQL Server Import and Export Wizard) Error 0xc02020ed: Data Flow Task: Columns "Column 1" and "sessionID" cannot convert between unicode and non-unicode string data types. (SQL Server Import and Export Wizard) Error 0xc02020ed: Data Flow Task: Columns "Column 2" and "vendor" cannot convert between unicode and non-unicode string data types. (SQL Server Import and Export Wizard) Error 0xc004706b: Data Flow Task: "component "Destination - items" (301)" failed validation and returned validation status "VS_ISBROKEN". (SQL Server Import and Export Wizard) Error 0xc004700c: Data Flow Task: One or more component failed validation. (SQL Server Import and Export Wizard) Error 0xc0024107: Data Flow Task: There were errors during task validation. (SQL Server Import and Export Wizard) |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-12-27 : 11:02:59
|
| What are the data types of itemID, sessionID and vendor in your table? |
|
|
|
Michael71
Posting Yak Master
126 Posts |
Posted - 2006-12-27 : 12:55:16
|
| text nchar(4000) float |
|
|
|
Michael71
Posting Yak Master
126 Posts |
Posted - 2006-12-27 : 13:18:32
|
| Now I'm getting this....Error 0xc02020a1: Data Flow Task: Data conversion failed. The data conversion for column "Column 94" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.". (SQL Server Import and Export Wizard) Error 0xc020902a: Data Flow Task: The "output column "Column 94" (292)" failed because truncation occurred, and the truncation row disposition on "output column "Column 94" (292)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component. (SQL Server Import and Export Wizard) Error 0xc0202092: Data Flow Task: An error occurred while processing file "C:\Documents and Settings\mwilliams\Desktop\items.txt" on data row 103. (SQL Server Import and Export Wizard) Error 0xc0047038: Data Flow Task: The PrimeOutput method on component "Source - items_txt" (1) returned error code 0xC0202092. 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. (SQL Server Import and Export Wizard) Error 0xc0047021: Data Flow Task: Thread "SourceThread0" has exited with error code 0xC0047038. (SQL Server Import and Export Wizard) Error 0xc0047039: Data Flow Task: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. (SQL Server Import and Export Wizard) Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0047039. (SQL Server Import and Export Wizard) |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-12-27 : 13:19:32
|
| ItemID is text? That doesn't seem right? What it comes down to is that whatever values are in the text file for a given column must be able to be converted to the target type in the table and right now that isn't the case in your data. |
|
|
|
Michael71
Posting Yak Master
126 Posts |
Posted - 2006-12-27 : 13:35:46
|
| What should I change the data type to in SQL server? |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-12-27 : 14:17:00
|
| I don't know, I tried guessing what your data looks like in your flat file, but my telekinetic powers aren't what they used to be! |
|
|
|
|
Transfering a flat text file in DTS to SQL Server