Data Flow Task Problem: flat

Showing posts with label flat. Show all posts

Sunday, March 25, 2012

data export to flat file

Hi, I hope you can help, and thank you.
SQL 2000. I need to generate a flat file export from a single table. I need
a line feed between select fields. I've tried using bcp and Bulk Insert with
a format file, but I've never used either, and I must be missing a critical
step somewhere.
The flat file format for a single record would be similar to the following:
Field1, Field2, Field3, Field4, Field5, Image Path1
Image Path2
Image Path3
I appreciate any assistance you can provide. Thanks!
Did you try the DTS (data transfomation service)tool in SQL Server 2000?
Easy to use for transforming data to export files.
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/dtssql2k.mspx
Good luck.
sql

data export to flat file

Hi, I hope you can help, and thank you.
SQL 2000. I need to generate a flat file export from a single table. I nee
d
a line feed between select fields. I've tried using bcp and Bulk Insert with
a format file, but I've never used either, and I must be missing a critical
step somewhere.
The flat file format for a single record would be similar to the following:
Field1, Field2, Field3, Field4, Field5, Image Path1
Image Path2
Image Path3
I appreciate any assistance you can provide. Thanks!Did you try the DTS (data transfomation service)tool in SQL Server 2000?
Easy to use for transforming data to export files.
http://www.microsoft.com/technet/pr...y/dtssql2k.mspx
Good luck.

Monday, March 19, 2012

Data coversion or Derived column?

I have a numeric column with the following sample values in a source flat file:

240

310

I would like to dump them in a table (destination) as string with the length only 3 and in the following format "xxx" .

Data in the destination column will look like this after the transformation:

240

006

048

310

055

Thanks for your help!

milton06 wrote:

I have a numeric column with the following sample values in a source flat file:

240

6

48

310

55

I would like to dump them in a table (destination) as string with the length only 3 and in the following format "xxx" .

Data in the destination column will look like this after the transformation:

240

006

048

310

055

Thanks for your help!

You'll need a derived column component for that. Here's the expression

RIGHT("000" + (DT_STR, 3, 1252)[columnname], 3)

-Jamie

|||Excellent Jamie.|||

milton06 wrote:
Excellent Jamie.

Please mark his post as the answer if that solved your problem.

Thanks,
Phil

Sunday, March 11, 2012

Data Conversion Error

Hi,

I am transferring data from a Flat file source to SQL Database. Initially I got validation error saying

"Validation error. Import Employee Information: [DataFlowTask] [6384]: The column "[ColumnName] " can't be inserted because the conversion between types DT_STR and DT_DBTIMESTAMP is not supported." ( i have 6 date fields)

The destination in SQL has the field in "datetime" format.

So i used a "Data Conversion" task to transform the fields, thereby resolving the validation errors. But when i Execute the dtsx I get the following error :

[Data Conversion [322]] Error: Data conversion failed while converting column "[ColumnName]" (79) to column "[Modified_ColumnName]" (346). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

[Data Conversion [322]] Error: The "output column "[Modified_ColumnName]" (346)" failed because error code 0xC020907F occurred, and the error row disposition on "output column "[Modified_ColumnName]" (346)" specifies failure on error. An error occurred on the specified object of the specified component.

[DTS.Pipeline] Error: The ProcessInput method on component "Data Conversion" (322) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0209029.

["InputFileName" Input [1]] Error: Setting the end of rowset for the buffer failed with error code 0xC0047020.

[DTS.Pipeline] Error: The PrimeOutput method on component "InputFileName Input" (1) returned error code 0xC0209017. 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.

[DTS.Pipeline] Error: Thread "SourceThread0" has exited with error code 0xC0047038.

Can anybody help me with this?

The "The value could not be converted because of a potential loss of data" error generally occurs when you're trying to convert a wider data type value into a data type too narrow to hold it. Can you look at the metadata in your data flow to see what the exact data types are for the source ("[ColumnName]") and target ("[Modified_ColumnName]") columns?|||Can you post some of your sample data in the [ColumnName] column?|||

Hi,

I am transferring data from .dat file to tables in SQL DB. The output from .dat file is in [DT_STR] datatype.

[ColumnName1] [ColumnName2]

04/27/1987 0

05/20/1958 0

03/12/1982 1

05/04/1957 2

Error 23 Validation error. [TaskName]: [TableName] [9]: The column "[ColumnName1]" can't be inserted because the conversion between types DT_STR and DT_DBTIMESTAMP is not supported.

The table in the DB has "datetime" as its datatype

Error 26 Validation error. [TaskName]: [TableName] [9]: The column "[ColumnName2]" can't be inserted because the conversion between types DT_STR and DT_I4 is not supported.

The table in the DB has "int" as its datatype

So i used the "data conversion" task in SSIS and resolved the validation error.

But when i executed the dtsx i got the error mentioned in my previous post

|||I'd add a derived column transformation between the source and the destination.

In that derived column, you'd cast the values appropriately.

Expressions below:
NewOutputColumnName1: (DT_DBTIMESTAMP)[ColumnName1]
NewOutputColumnName2: (DT_I4)[ColumnName2]

Data Conversion - NULL datetime causes failure

Hi,

I have a flat file source and am bringing it into an OLE DB Destination connected to a SQL Server 2005 database. Since I am bringing in the data via a flat file, I have a conversion step in the data flow, which uses a "Data Conversion" transformation to convert each column to the appropriate destination data type.

When I run my SSIS package, if the datetime column in the flatfile is NULL, then the Data Conversion step fails. I have my Data COnversion step set to redirect error rows to an error output. So, I get all the rows with populated datetime values in the intended destination, and none of the rows with null datetime values (these go to the error destination.)

This is a problem! I need to make sure all valid rows fall through. It seems very odd to me that a NULL datetime could not be converted to a datetime data type. This makes no sense, since a datetime column can naturally hold NULL, just like any other type. So, my question is: is this a bug? It seems to me it is. I can't believe this would be the desired behavior?!

Microsoft: please let us know whether this is expected behavior. If so, then what would the desired workaround be?

Thanks

Do you have the retain nulls option checked in the Flat File Source?

If you are treating the column as a string in your flat file source, you may need to use a Derived Column transform to handle the conversion. Use an expression like:

Code Snippet

([YourDateColumn]=="")|| (ISNULL([YourDateColumn])) ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)[YourDateColumn]

|||Thanks much - the checkbox in the flatfile source did the trick. Never noticed that before, but it worked great. Thanks again.

Data Conversion

I have single letters in a Flat file and it is in the string format,but now i want to convert it in to int format.I have tried doing this by SSIS but it is not working.
I used data conversion and copy column transformation.

I got a error message.

it automatically correct the meta data mismatch. But when i run the package, it gives the following error message.

Error: 0xC0209029 at Data Flow Task, Data Conversion [75]: The "output column "ReceiptType" (112)" failed because error code 0xC020907F occurred, and the error row disposition on "output column "ReceiptType" (112)" specifies failure on error. An error occurred on the specified object of the specified component.

This is just one error message.

Please can you tell me the steps to do this correctly.

Also I need to know how to run a SSIS package through command line?

Thanks

Nishan

Did you say you were trying to convert letters to int? Can't do that. Is there any more info?|||

I have a flat file which has string types of data. I want to convert them into int type before I load them into destination table. I used data convertion and derived column but neither worked for me.

Ex;

Source file (String type);

column 1

Destination file format should look like this. B - > 1 , C - > 2

Destination File ( int Type);

Column 1

Can you explain the setps to do this conversion using SSIS

Thanks

|||The Derived Column task should work for you: Use the conditional operator|||Shamen,
Please keep your posts together in one thread.

This can be done in a lookup transformation. Use the following SQL for the lookup:

select "B",1
union all
select "C",2
union all
select "D",3"
....

Then hook up your source to the lookup component, selecting the second column as the return value. Coming out of the lookup component, you'll have the number associated with the letter and can then go into a flat file destination.|||

Thanks Pill and SQL Pro...I will try..at the same time I want to convert the data type too.

Yes I will keep all my posting together in one thread...

Also I have another question...Once I create the package can I run it on command line? How can I do that?

Thanks

|||Thanks I will try|||

shamen wrote:

Also I have another question...Once I create the package can I run it on command line? How can I do that?

DTEXEC runs packages from the command line.

|||

Thanks....Still I'm having a problem with data conversion...I tried using both derived column and data conversion transformation....but still no luck...

This is the error message I got when I tried with data conversion transformation.

SSIS package "Package.dtsx" starting.

Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.

Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning.

Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning.

Information: 0x402090DC at Data Flow Task, Flat File Source [1]: The processing of file "C:\p4_coventry1666_DEV02\Docs\ThirdParty\EPICWare\EPICWARE Flat File.txt" has started.

Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning.

Error: 0xC02020C5 at Data Flow Task, Data Conversion [75]: Data conversion failed while converting column "ReceiptType" (67) to column "ReceiptType" (112). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

Error: 0xC0047022 at Data Flow Task, DTS.Pipeline: The ProcessInput method on component "Data Conversion" (75) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

Error: 0xC02020C4 at Data Flow Task, Flat File Source [1]: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0209029.

Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: The PrimeOutput method on component "Flat File Source" (1) returned error code 0xC02020C4. 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.

Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.

Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning.

Information: 0x402090DD at Data Flow Task, Flat File Source [1]: The processing of file "C:\p4_coventry1666_DEV02\Docs\ThirdParty\EPICWare\EPICWARE Flat File.txt" has ended.

Information: 0x402090DF at Data Flow Task, OLE DB Destination [9]: The final commit for the data insertion has started.

Information: 0x402090E0 at Data Flow Task, OLE DB Destination [9]: The final commit for the data insertion has ended.

Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.

Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "OLE DB Destination" (9)" wrote 0 rows.

Task failed: Data Flow Task

Warning: 0x80019002 at Package: The Execution method succeeded, but the number of errors raised (7) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "Package.dtsx" finished: Failure.

The program '[4360] Package.dtsx: DTS' has exited with code 0 (0x0).

|||

You can use the CODEPOINT function to return a numeric value for a character. That means you can use an expression in a Derived Column transform to do the work:

Expression: CODEPOINT(UPPER(YourColumn))-64

(the 64 is there because CODEPOINT("A") = 65)

This will work nicely where you have a one-character value to transform.

You can craft a more elaborate expression for multiple characters if required.

Dylan.

|||

Thanks Dylan...But at the same time I want to convert data type from String to Int.....

I'm supposed to get specific value for specific letters. As a example for letter B I'm supposed to get 1.

B - > 1

C- > 2

But first of all I have to figure it out how to convert data from string to Int.

Thanks

|||

Use the Lookup as Phil suggested. It is by far the simplest solution to this problem.

|||

OK, but as pointed out above, you are on a fruitless quest.

A letter is not a number, so it cannot be converted to an int.

What you are saying is like asking how mainy legs a daisy has, or how many teeth in an elephant's trunk. It just doesn't make sense to talk about converting a letter into an int. By definition an int is a number, which is not a letter.

That said, what you CAN talk about is removing the column with the letter and replacing it with a corresponding column containing an int.

Then it is just a matter of defining the rule by which you convert your letter into a number.

One such rule is to say there is a specific mapping. So B=2, F=61, R=12.

In this case the select statement above is a good way of performing that mapping. However you can't deal with unknown values. This is like saying "African elephants have 6 teeth in their trunk. Asian elephants have 9 teeth." Using this rule, you can answer the question "How many teeth in an African elephant's trunk?" but not "How many teeth in an Australian elephant's trunk?" because you have no information about Australian elephants.

The other approach is to say there is a formula, so B=2, C=3, D=4, E=5. In this case you can say that even if you have never used an "F", if one were to be used for some reason then it would be equal to 6. That is where functions like CODEPOINT can help.

This is like saying "All daisys have twice as many legs as petals." Now you can look at any daisy, and answer the question "How many legs on my daisy?"

Notice that what we have done is create two different methods for answering questions that do not make sense in the real world. And likewise, we have revealed two different methods for turning a letter into an int, even though doing so does not make sense.

You should stop thinking about turning your letter into an int, and focus instead on dropping the column and replacing it with a derived int column. You are not converting anything, you are transforming and replacing. If it looks like "converting" to your pointy-haired managers, then that's fine, but you will know better on the inside.

Good luck,

Dylan.

|||

jwelch wrote:

Use the Lookup as Phil suggested. It is by far the simplest solution to this problem.

That would depend on context and maintainability requirements.

If the letter value can be derived by formula rather than by lookup, that would be more appropriate in my opinion. If you are hardcoding values in a lookup, then you are creating a potential maintenance problem.

In addition, creating a complex union query where there are only ever two values to look up would be overcomplicated when an IF expression would be simpler.

Without knowing more about the requirements and implementation specifics, it is risky to talk about which solution is simplest. Normally I wouldn't care, but I've taken issue in this case because I've had to maintain a fair amount of work recently that was done using this "simplest" approach. Two years later, it has turned out to be a major pain.

|||

OK...Well I will try...

Thanks all

Data Conversion

Hi,

I have a flat file with over 300 fields and need to do a data conversion before SQL final destination, most fields are DT_STRING, is there an easier way of converting the data without having to manualy do the 300 fields.

I have all the data types i want STR converting to in a file.

Any info would be great,

Cheers,

Slash.

If they are strings, what are you converting to in SQL Server?|||?|||

Hi,

Lots of different data types the destination table fields are mailny int, bigint varchar, date and decimal

Thanks,

Dave.

|||

Its a lot of fields 300... but doing something flexible as you described I dont know without using code...

In your case I would convert the data directly in the SQL query using CONVERT or using the convertion transform of SSIS...

regards!

|||It's going to be manual somehow... Either you configure the flat file source with the correct data types, or you add a derived column/data conversion component, or you stage the data into SQL Server and write a SQL statement to convert the view.|||

Hi,

I think i've managed to do it, i manualy went through the import using the SQL wizzard and at the final stage saved to an SSIS package copied the data flow ammened it to fit into my project and its running now.

Thanks,

Slash.

Data Conversion

Hi,

I have all the data types i want STR converting to in a file.

Any info would be great,

Cheers,

Slash.

If they are strings, what are you converting to in SQL Server?|||?|||

Hi,

Lots of different data types the destination table fields are mailny int, bigint varchar, date and decimal

Thanks,

Dave.

|||

Its a lot of fields 300... but doing something flexible as you described I dont know without using code...

In your case I would convert the data directly in the SQL query using CONVERT or using the convertion transform of SSIS...

regards!

Hi,

Thanks,

Slash.

Data Conversion

Hi,

I have all the data types i want STR converting to in a file.

Any info would be great,

Cheers,

Slash.

If they are strings, what are you converting to in SQL Server?|||?|||

Hi,

Lots of different data types the destination table fields are mailny int, bigint varchar, date and decimal

Thanks,

Dave.

|||

Its a lot of fields 300... but doing something flexible as you described I dont know without using code...

In your case I would convert the data directly in the SQL query using CONVERT or using the convertion transform of SSIS...

regards!

Hi,

Thanks,

Slash.

Data Conversion

Hi,

I have all the data types i want STR converting to in a file.

Any info would be great,

Cheers,

Slash.

If they are strings, what are you converting to in SQL Server?|||?|||

Hi,

Lots of different data types the destination table fields are mailny int, bigint varchar, date and decimal

Thanks,

Dave.

|||

Its a lot of fields 300... but doing something flexible as you described I dont know without using code...

In your case I would convert the data directly in the SQL query using CONVERT or using the convertion transform of SSIS...

regards!

Hi,

Thanks,

Slash.

Data Conversion

Hi,

I have all the data types i want STR converting to in a file.

Any info would be great,

Cheers,

Slash.

If they are strings, what are you converting to in SQL Server?|||?|||

Hi,

Lots of different data types the destination table fields are mailny int, bigint varchar, date and decimal

Thanks,

Dave.

|||

Its a lot of fields 300... but doing something flexible as you described I dont know without using code...

In your case I would convert the data directly in the SQL query using CONVERT or using the convertion transform of SSIS...

regards!

Hi,

Thanks,

Slash.

Data Conversion

Hi,

I have all the data types i want STR converting to in a file.

Any info would be great,

Cheers,

Slash.

If they are strings, what are you converting to in SQL Server?|||?|||

Hi,

Lots of different data types the destination table fields are mailny int, bigint varchar, date and decimal

Thanks,

Dave.

|||

Its a lot of fields 300... but doing something flexible as you described I dont know without using code...

In your case I would convert the data directly in the SQL query using CONVERT or using the convertion transform of SSIS...

regards!

Hi,

Thanks,

Slash.

Data Conversion

Hi,

I have all the data types i want STR converting to in a file.

Any info would be great,

Cheers,

Slash.

If they are strings, what are you converting to in SQL Server?|||?|||

Hi,

Lots of different data types the destination table fields are mailny int, bigint varchar, date and decimal

Thanks,

Dave.

|||

Its a lot of fields 300... but doing something flexible as you described I dont know without using code...

In your case I would convert the data directly in the SQL query using CONVERT or using the convertion transform of SSIS...

regards!

Hi,

Thanks,

Slash.

Sunday, March 25, 2012

data export to flat file

data export to flat file

Monday, March 19, 2012

Data coversion or Derived column?

Sunday, March 11, 2012

Data Conversion Error

Data Conversion - NULL datetime causes failure

Data Conversion

Data Conversion

Data Conversion

Data Conversion

Data Conversion

Data Conversion

Data Conversion

Data Conversion

Data Flow Task Problem

Blog Archive

About Me