Floating Point Numbers in BCP File

Hi,
I am BCPing in a tab-delimited text file and I am getting this error
message:
Starting copy...
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for cast
specification
The text file records look like below:
200509 67195 M12AB00 67195 SSL TEST RECORD 7217 240.41
200509 67338 DNMXAED 67338 CTA TEST RECORD 50 237.5
Not sure if that floating point number at the end of the file is the cause
of this problem or not.
Any ideas on how to solve the problem?
JDJoe Delphi wrote:
> Hi,
> I am BCPing in a tab-delimited text file and I am getting this
> error message:
> Starting copy...
> SQLState = 22005, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]Invalid character value
> for cast specification
> The text file records look like below:
> 200509 67195 M12AB00 67195 SSL TEST RECORD 7217 240.41
> 200509 67338 DNMXAED 67338 CTA TEST RECORD 50 237.5
> Not sure if that floating point number at the end of the file is the
> cause of this problem or not.
> Any ideas on how to solve the problem?
> JD
Have you verified there are actually TAB characters in the file where
they should be? You might try using the DTS Import Wizard to see if that
works. The wizard will show you the parsed data based on your
delimiters, so that might clue you in to the problem.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Joe Delphi (delphi561@.nospam.cox.net) writes:
> I am BCPing in a tab-delimited text file and I am getting this error
> message:
> Starting copy...
> SQLState = 22005, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for
> cast specification
> The text file records look like below:
> 200509 67195 M12AB00 67195 SSL TEST RECORD 7217 240.41
> 200509 67338 DNMXAED 67338 CTA TEST RECORD 50 237.5
> Not sure if that floating point number at the end of the file is the cause
> of this problem or not.
> Any ideas on how to solve the problem?
Please post the CREATE TABLE statement for the table. It's impossible to
tell without that information what is going on.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Flight Sample Debate

Hi All,

I found the Flight Sample Description at http://msdn2.microsoft.com/en-us/library/ms160946(d=ide).aspx .

I installed the file SQLServerSamples.msi which is in C:\Program

Files\Microsoft SQL Server\90\Tools\Samples by default.

I followed the steps strictly.

Every thing was OK except:

- I didn't have any response in the Notifications folder which by

default resides in C:\Program Files\Microsoft SQL Server\90\Samples\Notification

Services\Flight

- I didn't understand what happened exactly

So:

- What are the probable causes of not getting a result?

- I want some documentation about what happens in the example.

Thanks in advance,

Bishoy

Anything in the Event Viewer?

Try turning on Verbose logging to see if that helps point you in the right direction.

|||

1- That all what is found in Event Viewer:

Event Type: Information
Event Source: Service Control Manager
Event

Category: None
Event ID: 7035
Date: 8/11/2006
Time: 6:13:43

PM
User: BISHOYLAPTOP\Bishoy
Computer: BISHOYLAPTOP
Description:
The

NS$FlightInstance service was successfully sent a start control.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

> but still there is nothing appeared in the Notifications Folder

2- What is Verbose login

3- I want documentation source about that Flight Sample.

Thank you.

Bishoy

|||

I found where the error was:

There should be one strong name key for both the

AddSubscribers and AddSubscription projects.

The signing for both projects is ..\..\..\SampleKey.snk which I could never

make myself.

Now I have the following questions or inquires:

1- I want some documentations about what happened in that example.

2- How to sign a project to present key in above folder like

..\..\..\SampleKey.snk (because browse button copy the key to the project

folder)?

3- What is the output meaning in the Notifications folder which is

FileNotifications.txt file?

Many Thanks and Best Regards.

Bishoy

|||

Hi Bishoy -

Rather than continuing with two parallel conversations, I've posted replies to the NewsGroup.

http://groups.google.com/group/microsoft.public.sqlserver.notificationsvcs/browse_frm/thread/24e8f2b998118394/013718ab4d063ff3?hl=en#013718ab4d063ff3

HTH...

Joe

Flight Recorder in SSAS

Hi -

I am trying to use fight recorder in analysis services. But when I replay a trace file I shows only very few columns in output.

Is there any way to see all the coulums when you replay the trace?Any kind of setting that I need to modify.

Thanks,

Deepak,

The events and columns captured by the Flight Recorder are controlled by a trace definition file. The advanced server property named Log\FlightRecorder\TraceDefinitionFile controls the definition file used by the Fligth Recorder. By default, the property is blank, which means the Flight Recorder defaults to using the trace file definition stored in the file flightrecordertracedef.xml, which is stored in the ..\OLAP\bin directory wherever SSAS is installed.

Patrice Truong does a nice job of explaining how to modify the trace definition file in the following blog entry:

http://patrice-truong.spaces.live.com/blog/cns!62BC7696F42B5!181.entry

One thing I'd likely recommend is saving the modified flightrecordertracedef.xml file as a new name, then update the server property listed above to reflect that new file name (to keep the original file in tact just in case you need to revert to it for some reason).

HTH,

Dave Fackler

sql

Flatten File

I try to flatten a table.
in Access I would do this: iif(sequence=1,[Technology],Null) AS Tech1, iif(sequence=2,[Technology],Null) AS Tech2 FROM tblTechnologies GROUP BY Application
How do I flatten a file in SQL Server?
Thankslook up CASE in Books OnLine -- it does the same as the access IIF

rudy
http://rudy.ca/|||Great, Thanks. Step 1 completed.

Now I try to group the records so they all show up as 1 record with the primary key, but SQL want me to show list all fields i.e. GROUP BY itemID, sequence,Technology
In this case the fields will never show on one line.

Any suggestions?
Thanks..|||Hi Torgue

I just have a question. How can many records be grouped together and shown to all have the same Primary Key? If five records are grouped together (on sequence,Technology) each with there own Primary Key (itemID), which one of the five (itemID) do you choose to show when grouping?|||I'm sorry for the confusion. It is actually the Foreigh Key on the N-end of a 1:N relationship. The main table lists the applications and the N-side lists the technologies used for the application development.

In an Excel export I need to display the application with all technologies.

Like:
Application1 Tech1 Tech2 Tech2
Application2 Tech1 Tech2 Tech3

Thx..|||why don't you define the sql/server table in access as a linked table via odbc, and then write a crosstab query in access? i think you can easily export that to excel, too

in any case, if you do want to run this right in sql/server, you need
select application
, case when sequence=1
then technology else null end as tech1
, case when sequence=2
then technology else null end as tech2
from ... or something similar

rudy|||Thanks very much for your help.

Unfortunately I am using SQL Server as a web Backend, so I need to stick to SQL Server.

I will see what other solutions are available to me. Perhaps I can work with codes (numbers) and then do a sum to group, then link this code to a lookup table.|||I will see what other solutions are available to me. Perhaps I can work with codes (numbers) and then do a sum to group, then link this code to a lookup table.if you have already tried my CASE example, you will notice that each of the 1:N rows is present, and i apologize for not having shown how to summarize select application
, max( case when sequence=1
then technology else '' end ) as tech1
, max( case when sequence=2
then technology else '' end ) as tech2
from ...
group by applicationthe problem is, i don't know if you are doing any summing (is "technology" a char field?)

if you still cannot figure it out, please show your two table layouts

rudy|||works great, thank you very much.

technology is a character field, but the max takes the populated fields over the null fields.sql

Flatfiles with garbage data

I was testing my packages today and my packages were running sucessfully when I didnt have any valid data in the flat file. One the reason was as not rows were returned from the flat file none of the validation script components returned any err.

How do I count the # of rows which were read from flat file from the package and continue only if there is more than one row.

I tried using conditional split but as I wont have the row count value availble till the dataflow task runs this didnt help.

Is it best for me to have two dataflow tasks one resturns the count of records from flat file and the other starts if there are any rows. Now my problem is if I have rows to process how to I transfer the flatfile data to validate from DataFlowTask1 to DataFlowTask2?

I have a script task whcih counts the rows and decides to the TaskResult but once the TaskResult is sucess how do I use the values read in DataFlowTask1?

Appreciate ur help in advance

ray_newbie_SSIS wrote:

I was testing my packages today and my packages were running sucessfully when I didnt have any valid data in the flat file. One the reason was as not rows were returned from the flat file none of the validation script components returned any err.

How do I count the # of rows which were read from flat file from the package and continue only if there is more than one row.

I tried using conditional split but as I wont have the row count value availble till the dataflow task runs this didnt help.

Is it best for me to have two dataflow tasks one resturns the count of records from flat file and the other starts if there are any rows. Now my problem is if I have rows to process how to I transfer the flatfile data to validate from DataFlowTask1 to DataFlowTask2?

You are on the right track with using two data-flows. the answer to your predicament is to use raw files to transfer data between the data flows.

ray_newbie_SSIS wrote:

I have a script task whcih counts the rows and decides to the TaskResult but once the TaskResult is sucess how do I use the values read in DataFlowTask1?

Appreciate ur help in advance

Have you considered using the RowCount component to count the number of rows?

-Jamie

|||Have your first data flow have a flat file input and a rowcount operator, and then have the second data flow have all the processing. If the reading of the flat file is very expensive, i.e. over a slow network, you could store the data read in data flow 1 to a raw file and then use the raw file as the source in data flow 2. You can then put a conditional clause between the two data flows based on the variable used in the rowcount in the first data flow.|||

This is what I have as of now

DataFlowtask1 has a FlatFileSource and RowCount Component

Script Task which will check the value of Row count from DataFlowTask1 and decide is the PackageResult Sucess or Failure

If Sucess I have to start validating the Flat file, and blah blah

So you guys suggest read the data into Rawdata files and use it in DataFlowTask2. Never knew I could do that,I will research on this...

IS this possible?

In one of my packages where I am doing similar flat file processing but with one dataflow

I have Flat file Source and script component and olddb destination

In scriptcomponent can i not declare a variable in preexecute and increment in Input0Buffer and check this val in post execute and if this value is 0 then raise an err....dont u think this is much simpler for me than using two dataflows...Need ur suggestion
Regards

|||

ray_newbie_SSIS wrote:

Script Task which will check the value of Row count from DataFlowTask1 and decide is the PackageResult Sucess or Failure

Don't do that. Instead, put a conditional precedence constraint between DF1 and DF2 that checks the rowcount value. If its greater than 0, DF2 executes.

Precedence Constraints: http://www.sqlis.com/default.aspx?306

-Jamie

|||

The sugestion basically is to read the file twice. Once to find out how many rows are in the file; the second to actually move the data.

I would recommend you to use an expression in the precedence constraint that goes to the 2nd dataflow (using the variable) that holds the number of rows in the file to decide whether to execute the 2nd dataflow or not; as opposed of using the script task to fail the package.

http://msdn2.microsoft.com/en-US/library/ms140153.aspx

Rafael Salas

|||

Rafael Salas wrote:

The sugestion basically is to read the file twice. Once to find out how many rows are in the file; the second to actually move the data.

Rafael,

That's not quite true. The suggestion is to read the file once and pass the data between data-flows using raw files.

-Jamie

|||

Jamie,

I don't understand how to 'pass the data between data-flows using raw files'...at some point the data gets read a second time right? Could you please clarify?

Rafael Salas

|||

Rafael Salas wrote:

Jamie,

I don't understand how to 'pass the data between data-flows using raw files'...at some point the data gets read a second time right? Could you please clarify?

Rafael Salas

Correct. When you said "The sugestion basically is to read the file " I read that to mean you read the same file twice. I wanted to clarify for the original poster that you read the sourcec file once and the raw file once.

Sorry for the confusion.

-Jamie

|||

It is clear now. Thanks for the clarification and sorry for the confusion

Rafael Salas

Flatfile Destination Variable Filename

Why does the raw file have an option for a variable path and the flat file destination does not? Not having this feature makes it impossible to work with variable environments. Please add this option to the Flatfile Destination.You can use expressions on the flat file destination to change the location of the file.|||Thank you so much for your quick reply. I have the flat file destination highlighted but there are no properties for Expressions. I also looked in the connection properties also and could not locate a place to do this. I ended up writing a vb script but I would like to have the ability to do it graphically. The SSIS team definately needs to set up a way to set Global Variables (which could contain file path settings) via a sql statement. They also need variable file pathes exactly like the raw file.|||When you right-click on the flat file connection manager, you can select properties. In there is an Expressions property. You'll want to set the ConnectionString property to the full path containing the file.|||Oh awesome! Thanks Phil.

FlatFile connection and security

My DTS package, deployed and run from the file system, works just fine for me, but fails when someone else runs it. The only explicit error from the dtexec command is:

Error: 2005-06-24 12:35:03.33
Code: 0xC0016016
Source:
Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.
End Error

This error just started when we tried to point to a file on a network server instead of a file local to the machine running the package. The other person definitely has access rights to that network server, but the package fails anyway.

One possible solution might be to specify a username/password for the file, but I don't see how to include that in the connection string. Alternatively, this may be a more general security issue with the whole package. I first turned to BOL for some explanations and answers, but wasn't able to find anything specific to my problem.

Any help or suggestions will be greatly appreciated. Thanks!
Phil
This is because the default protection level is to encrypt with a user key. That means only a user on his/her machine can open the package.

You probably want to use Package Password here instead.|||

I am getting a similar error on a DTS package created by me and migrated by me and executed by me via Dtexec. Shouldn't ssql 2000 ownership carry over to 2005? The package is not a flat file. And, by the way, where does the "DTS Property" come in?

thanks

Ted

Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
NULL
Started: 9:24:56 AM
Error: 2006-02-08 09:24:56.50
Code: 0xC0016016
Source:
Description: Failed to decrypt protected XML node "DTS:Property" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that
the correct key is available.
End Error
Error: 2006-02-08 09:24:56.51
Code: 0xC0016016
Source:
Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that
the correct key is available.
End Error

|||

SSIS is a completely new product so I don't think it follows that DTS behaviour should make it into SSIS. Furthermore, there's been a very high profile tightening up of security within Microsoft products and the behaviour you're seeing here is probably as a result of that.

The reason you see references to "DTS" in various places is for legacy reasons. Alot of the product had already been built before the name change came about.

-Jamie

sql

FlatFile connection and security

My DTS package, deployed and run from the file system, works just fine for me, but fails when someone else runs it. The only explicit error from the dtexec command is:

Error: 2005-06-24 12:35:03.33
Code: 0xC0016016
Source:
Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.
End Error

This error just started when we tried to point to a file on a network server instead of a file local to the machine running the package. The other person definitely has access rights to that network server, but the package fails anyway.

One possible solution might be to specify a username/password for the file, but I don't see how to include that in the connection string. Alternatively, this may be a more general security issue with the whole package. I first turned to BOL for some explanations and answers, but wasn't able to find anything specific to my problem.

Any help or suggestions will be greatly appreciated. Thanks!
PhilThis is because the default protection level is to encrypt with a user key. That means only a user on his/her machine can open the package.

You probably want to use Package Password here instead.|||

I am getting a similar error on a DTS package created by me and migrated by me and executed by me via Dtexec. Shouldn't ssql 2000 ownership carry over to 2005? The package is not a flat file. And, by the way, where does the "DTS Property" come in?

thanks

Ted

Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
NULL
Started: 9:24:56 AM
Error: 2006-02-08 09:24:56.50
Code: 0xC0016016
Source:
Description: Failed to decrypt protected XML node "DTS:Property" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that
the correct key is available.
End Error
Error: 2006-02-08 09:24:56.51
Code: 0xC0016016
Source:
Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that
the correct key is available.
End Error

|||

SSIS is a completely new product so I don't think it follows that DTS behaviour should make it into SSIS. Furthermore, there's been a very high profile tightening up of security within Microsoft products and the behaviour you're seeing here is probably as a result of that.

The reason you see references to "DTS" in various places is for legacy reasons. Alot of the product had already been built before the name change came about.

-Jamie

Flat Files having Only Column Names in One file and the Rows in the other

Sorry if this question had already been answered previously. I was unable search the forum on this topic. How will I merge these and then configure the first row as Column names (As this helps to map to the destination column names automatically)

As far as I know, you can't. Why would you? A quick mapping exercise, name the columns etc, it not that much of a job...

You might do it by merging your files using a DOS command. Google merging text files in dos.|||

Hi Crispin,

Thanks for your reply

Unfortunately, We have hundreds of columns for each of the Flat File Sources among 20 in each Package. Naming them will obviously take ages.

Thanks

Subhash Subramanyam

|||I've had the same situation.

If you specify the file with the column names as the source, it'll do it for you. After that, you give it the files with the actual data in. Once columns are named, you do not need to do anything else with it. Unless the column order changes - in which case, you have a problem...

Otherwise, look at merging the files using DOS.

Another option you could try is to use Data Defractor for text files. Might be overkill for text but works non the less...

Flat Files Containing Dates

Hi everyone.
I'm trying to use a Flat File Connector to read in a fixed field width file that contains some date columns.
The problem is that the date column is in a CCYYMMDD format (with no delimiters) so that todays date, as an example, would be 20050711.
When it attempts to import the file it fails due to a "Data Conversion Failed" error. I can't find any way to specify the format of the column in the FFC dialog so my only option appears to be read in the column as a string and transform it later.
Is that correct?
Steve
Steve,
It sounds like it is, yes. Your other option is to write a custom connection manager and source component but that's like using a sledgehammer to crack a nut.

-Jamie|||Thanks Jamie, that's just what I was expecting.
Steve
|||

Jamie Thomson wrote:

Steve,
It sounds like it is, yes. Your other option is to write a custom connection manager and source component but that's like using a sledgehammer to crack a nut.

-Jamie

Or you could also use a script component as a source. Again, it may be overkill!

-Jamie|||Looks like ISO 8601 sans the '-' character. You can write a simple derived column expression to parse this out and convert it to a date. Like you say, just retrieve it as a string the new column will be a date.

Here's one way to do it:

(DT_DATE)(SUBSTRING(Date,6,2) + "-" + SUBSTRING(Date,8,2) + "-" + SUBSTRING(Date,1,5))

That will convert a string date column like this:

Date Derived Column 1 20050112 1/12/05 20031122 11/22/03 20050509 5/9/05 20010101 1/1/01 20000301 3/1/00 20021003 10/3/02 20022002 2/20/02 19631003 10/3/63 19621002 10/2/62 20051111 11/11/05

|||Thanks for those replies guys.
I'd like to create a derived column transform programmatically using the SSIS object model. I can't find any help in BOL regarding this - but I've managed to get this so far, which creates the derived column transformation object (the dataFlow object is a MainPipe object created elsewhere):

DTSComponentMetaData90 DerivedColumn;
DerivedColumn = dataFlow.ComponentMetaDataCollection.New();
DerivedColumn.Name = "DateTransform";
DerivedColumn.ComponentClassID = "DTSTransform.DerivedColumn.1";
CManagedComponentWrapper instance = DerivedColumn.Instantiate();
instance.ProvideComponentProperties();
instance.AcquireConnections(null);
instance.ReinitializeMetaData();
instance.ReleaseConnections();

The problem I have now is that I don't know how to create new columns from old columns ( as I will need to do in my case ). I have used other components which have mapped the virtual columns from the input to the output, so I'm assuming it's something similar, but I can't get it to work.
I've even tried creating a transform in the BIDS and then opening the package in code to see what the object looks like, but some of the properties were read-only and must be set another way. I'm really stuck now so any help would be really appreciated.
Thanks.
Steve
|||Steve,

To create a new column from an existing column you need to add an output column to the derived column transform (InsertOutputColumAt) and then set the FriendlyExpression (or Expression) custom property on that column (SetOutputColumnProperty). The FriendlyExpression would be something like LEFT([oldcolname], 5) to take the left 5 chars of the [oldcolname] column (assuming the oldcolname column was a string or wstring). You could use the expression property but it isn't as obvious and you need to get the existing column's lineage id (e.g. LEFT(#27, 5) if 27 was oldcolname's lineageid). Additionally, you have to set the virtual input column's usage type (IDTSDesigntimeComponent90::SetUsageType) to read only to tell the dataflow that this component needs to use this column for reading.

HTH,|||I tried this but got following error:

Derived Column [2497]: An error occurred while attempting to perform a type cast.
thanks,
Nitesh Ambastha
nitesh.ambastha@.csfb.com

|||

KirkHaselden wrote:

Looks like ISO 8601 sans the '-' character. You can write a simple derived column expression to parse this out and convert it to a date. Like you say, just retrieve it as a string the new column will be a date.

Here's one way to do it:

(DT_DATE)(SUBSTRING(Date,6,2) + "-" + SUBSTRING(Date,8,2) + "-" + SUBSTRING(Date,1,5))

That will convert a string date column like this:

Date Derived Column 1 20050112 1/12/05 20031122 11/22/03 20050509 5/9/05 20010101 1/1/01 20000301 3/1/00 20021003 10/3/02 20022002 2/20/02 19631003 10/3/63 19621002 10/2/62 20051111 11/11/05

To be more specific, I used the above idea and wrote this expression:
(DT_DATE)(SUBSTRING((YYYYMM + "01"),6,2) + "-" + SUBSTRING((YYYYMM + "01"),8,2) + "-" + SUBSTRING((YYYYMM + "01"),1,5))

This throws a cast exception.
Any suggestions?

thanks,
Nitesh Ambastha
nitesh.ambastha@.csfb.com|||May be the cast error is due to the fact that input YYYYMM can be null or empty string. Can someone suggest a better expression? Or I have to write a script?|||

What do you mean when you say it "throws a cast exception"?

Have you tried entering this expression in the derived column UI to see if it gives an error message?

If you think the input column might be null or empty, you could check that with ISNULL() or LEN() calls first using a conditional operator.

sql

Flat File with random bad rows.

I have a text file that come from our client that is Column deliminated by ~ and row deliminated by {CR}{LF}.

There is a comment field that appearently is not cleaned up and has {CR}{LF} within the comment field.

I am new to SSIS and I'm wondering if there is a way to detect and correct the bad rows?

example file formet:

ORDERID~DATE~Comment~Address

1~2/3/2007~Some Comment~1234 oak st

2~2/3/2007~Some messed

up comment~345 oak st.

3~2/3/2007~Another comment~3214 asdf blvd.

Thank you.

You can use the Microsoft Visual Basic .NET RTrim function in a script run from the Script Component (configured as a transformation), to remove white space characters such as line feed and carriage return characters.

So the package data flow would include a Flat File Source connected to a Script Component. The output of the Script Component can then be sent to a destination or another transformation.

For information about the VB function, see "LTrim; RTrim; and Trim functions" at http://msdn2.microsoft.com/en-us/library/h9wz3dez(VS.71).aspx. For information about the Script Component, see "Extending the Data Flow with the Script Component" at http://msdn2.microsoft.com/en-us/library/ms136118.aspx.

|||If you do not want to mess with scripting you could use the REPLACE function in a Derived Column task and replace the space with another character.|||

How do you specify the line-feed character in the REPLACE function?

|||

Try

Code Snippet

\n

Generally, you use a \ character to escape special characters. \n indicates new line, \t indicates tab, etc.

|||

Thanks John, that works great

|||

If you enclose the escape character in quotes ("\n"), the expression will parse. For more information about using characters that require escape sequences in string literals, see "Literals (SSIS)" at http://msdn2.microsoft.com/en-us/library/ms141001.aspx.

Flat File with random bad rows.

I have a text file that come from our client that is Column deliminated by ~ and row deliminated by {CR}{LF}.

There is a comment field that appearently is not cleaned up and has {CR}{LF} within the comment field.

I am new to SSIS and I'm wondering if there is a way to detect and correct the bad rows?

example file formet:

ORDERID~DATE~Comment~Address

1~2/3/2007~Some Comment~1234 oak st

2~2/3/2007~Some messed

up comment~345 oak st.

3~2/3/2007~Another comment~3214 asdf blvd.

Thank you.

You can use the Microsoft Visual Basic .NET RTrim function in a script run from the Script Component (configured as a transformation), to remove white space characters such as line feed and carriage return characters.

So the package data flow would include a Flat File Source connected to a Script Component. The output of the Script Component can then be sent to a destination or another transformation.

For information about the VB function, see "LTrim; RTrim; and Trim functions" at http://msdn2.microsoft.com/en-us/library/h9wz3dez(VS.71).aspx. For information about the Script Component, see "Extending the Data Flow with the Script Component" at http://msdn2.microsoft.com/en-us/library/ms136118.aspx.

|||If you do not want to mess with scripting you could use the REPLACE function in a Derived Column task and replace the space with another character.

|||

How do you specify the line-feed character in the REPLACE function?

|||

Try

Code Snippet

\n

Generally, you use a \ character to escape special characters. \n indicates new line, \t indicates tab, etc.

|||

Thanks John, that works great

|||

If you enclose the escape character in quotes ("\n"), the expression will parse. For more information about using characters that require escape sequences in string literals, see "Literals (SSIS)" at http://msdn2.microsoft.com/en-us/library/ms141001.aspx.

Flat File with random bad rows.

I have a text file that come from our client that is Column deliminated by ~ and row deliminated by {CR}{LF}.

There is a comment field that appearently is not cleaned up and has {CR}{LF} within the comment field.

I am new to SSIS and I'm wondering if there is a way to detect and correct the bad rows?

example file formet:

ORDERID~DATE~Comment~Address

1~2/3/2007~Some Comment~1234 oak st

2~2/3/2007~Some messed

up comment~345 oak st.

3~2/3/2007~Another comment~3214 asdf blvd.

Thank you.

You can use the Microsoft Visual Basic .NET RTrim function in a script run from the Script Component (configured as a transformation), to remove white space characters such as line feed and carriage return characters.

So the package data flow would include a Flat File Source connected to a Script Component. The output of the Script Component can then be sent to a destination or another transformation.

For information about the VB function, see "LTrim; RTrim; and Trim functions" at http://msdn2.microsoft.com/en-us/library/h9wz3dez(VS.71).aspx. For information about the Script Component, see "Extending the Data Flow with the Script Component" at http://msdn2.microsoft.com/en-us/library/ms136118.aspx.

|||If you do not want to mess with scripting you could use the REPLACE function in a Derived Column task and replace the space with another character.|||

How do you specify the line-feed character in the REPLACE function?

|||

Try

Code Snippet

\n

Generally, you use a \ character to escape special characters. \n indicates new line, \t indicates tab, etc.

|||

Thanks John, that works great

|||

If you enclose the escape character in quotes ("\n"), the expression will parse. For more information about using characters that require escape sequences in string literals, see "Literals (SSIS)" at http://msdn2.microsoft.com/en-us/library/ms141001.aspx.

Flat File with Nested Data

I am looking to import data into SQL Server 2005 using SSIS. I want to take data that is contained in a flat file and place it into the various appropriate tables in my system. The flat file contains nested data. For example...

Bob,Smith,555-5555,123~3.33|245~1.99,Active

So I want to build a package that brings in the records as follows

Client Table: First Name, Last Name, Phone, and Status (Bob, Smith, 555-5555, Active)

Order Table: OrderID, Amount (ID 123 @. $3.33 and another row ID 245 @. $1.99). If possible I would also like to tie the orders to the client record that was inserted.

My first question is if SSIS supports nested fields as in my example. Can it break a file by commas, then within a field by other delimiters? If so how do I do this, and if not what is the recommend way to accomplish this sort of task.

My second quesiton is if it can do that, can it tie the Client and Order data on the fly?

Thanks.

Looks like you could get what you want using multicasts, conditional splits, merges/unions, etc...

That's terribly messy source data, and if you can normalize it before bringing it into SSIS, you should do so.|||

The main problem I see you having with correllating a client with an order is not having an ID available. At least you won't have a Client ID until after you insert the client. This limits your ability to insert both within the same Data Flow Task.

One way to solve this is to generate an ID for the client before inserting.

If you had an ID you could then just use the Multicast Shape to send each row to multiple destinations. One destination would be the Client table, and the other would be the Order table. Your Data Flow would then look something like this.

Flat File Source --> Derived Column to add ID --> Multicast --> Client table, Order table

|||

I do not have control of the data feed. It is coming from a provider and therefore is out of my control. I also simplified the data greatly as there are multiple nested fields and nested within nested. So my initial dilema is just how to parse the data. Presently I have looked at two options, but both seem to be non-optimal and trying to figure out a better way to do this.

1. I did this with BizTalk. The problem is BizTalk is horrible on the performance side. I was able to convert the nested files into an XML structure and then send an XML message to a web service using code to import the data. This is just not a good solution.

2. I can import the data once. Then re-run and loop over the columns that can be nested untill all that data is pulled out. This works at a decent speed, but complex, subject to issues with file changes, prone to errors, etc.

So I have not found a way to do this easier... I will look at multicasts, conditional splits, etc but not seeing how i could use them to get the initial file split properly. If you have specific informaiton, links, examples, etc of how i can handle a multi nested flat file please let me know.

Thanks again.

|||

This is easy enough... Bear with me as I try to explain for you:
Bring in your source using the Flat File Source. Delimit on ",". The field names I used were FirstName, LastName, Phone, OrderData, Active. All are strings.
Next, I chose to create a composite key because I'm assuming that first name, last name, and phone number ensures a unique record in your dataset. Creating our own counter on each load may not guarantee uniqueness when inserting into your destination tables. So, throw a derived column transformation onto the data flow. Connect it to your source and create a new column, Key. Its expression is "FirstName+LastName+Phone". I left its datatype as unicode string, although you can cast it back to string if you'd like to fit your destination.
Then, add a multicast to your data flow. Connect it to the derived column transformation.
Next, add a destination for your client table. Hook it into the multicast. Take the FirstName, LastName, Phone, Active, and Key fields.
Add a script component to another output of the multicast transform. Select two fields as input columns, OrderData and Key. Select "Inputs and Outputs". Click on Output 0 and change the property, SynchronousInputID to 0. Next, add three output columns, "order", "key", and "value". Set order to string. Set key to which ever datatype you chose in the derived column above. Set value to numeric (perferrably) and set the precision/scale appropriately. Next up is the script:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim arrayOrder As Array
Dim arrayOrderValues As Array
arrayOrder = Row.OrderData.ToString.Split("|"c)
For Each order As String In arrayOrder
arrayOrderValues = order.Split("~"c)
Output0Buffer.AddRow()
' I'm assuming that there is two and only two values for each order - ordernum, amount
' I perform no error checking of the data
Output0Buffer.order = arrayOrderValues.GetValue(0).ToString()
Output0Buffer.value = Decimal.Parse(arrayOrderValues.GetValue(1).ToString())
' Attach the key from the source row to each of the new rows we create from the nested data
Output0Buffer.key = Row.Key
Next
End Sub
End Class

Next, take the script, and hook it up to your second destination, which is for the order table. Take all three fields.
Done.
--Phil

|||I get where you are going with this and it may be my best solution. I think you answered my one thought, which is that with the given components available in SSIS it is not doable, it must be done with .NET or other code in the script component. I will try your sample code and work with it a little and see how it works. As i stated there is nested within nested, so I assume I can then repeat what you did and multicast a nested field to another .NET script that further processes the record?|||My code splits everything out...

An example source file I used:
Bob,Smith,555-5555,123~3.33|245~1.99,Active
Phil,Brammer,535-3333,347~2.14|671~5.14,Inactive
Test,Name,321-3211,347~2.14|671~5.14|127~1.26|876~4.20,Active

The output from what I documented above:
CLIENT
FirstName LastName Phone Active Key
Bob Smith 555-5555 Active BobSmith555-5555
Phil Brammer 535-3333 Inactive PhilBrammer535-3333
Test Name 321-3211 Active TestName321-3211

ORDER
order key value
123 BobSmith555-5555 3.330
245 BobSmith555-5555 1.990
347 PhilBrammer535-3333 2.140
671 PhilBrammer535-3333 5.140
347 TestName321-3211 2.140
671 TestName321-3211 5.140
127 TestName321-3211 1.260
876 TestName321-3211 4.200|||

I think this will be a solution... atleast for now. I am working with your sample code and testing some throughput... and working with the real data to see if I see problem, but it looks promising. Thank you for your help and suggestions.

One quick question is you mention to set "SynchronousInputID to 0." What is this and why? I am sure I could look it up and find info, but thought it might be easier coming straight from you. Thanks again.

|||Allows you to add rows to the data flow. This sets it to asynchronous.|||This all is working well. Thank you for your help... I think that this will server well to break the flat file feed and get it into the database. The last issue that I think I will face is I will not be using a composite key as you do to tie the records. The table is using an Identity column, so records that are inserted will get a new unique value. Is there an easy way to get the id out and tie it to the other inserts?|||Well, I hate identity columns, so...

You'll have to do a lookup task to lookup a record in the dataflow with it's associated record in the table. HOWEVER, you'll likely have to split the single dataflow I provided into two separate dataflows first.sql

Flat File With Fixed Length Header and No Delimeter

Hi,

I'm trying to extract data from a Flat File which is as fixed length as they come. The file has a header, which simply contains the number of records in the file, followed by the records, with no header delimeter (No CR/LF, nothing).

For example a file would look like the following:

00000003Name1Address1Name2Address2Name3Address3

So this has 3 records (indicated by the first 8 characters), each consisting of a Name and Address.

I can't see a way to extract the data using a flat file connection, unless we add a delimeter for the header (not possible at this stage). Am I wrong?

Any suggestions on possible solution would be much appreciated - I'm thinking Ill have to write a script to parse the file manually.

Thanks in advance,

Scott

Do you need the data in the first row?

You can just ignore it by setting the "Header rows to skip" setting to 1.

K

|||

Yes. Essentially the file is just one row..Which would include the header details (number fo records) then all of the fixed length records follow (on the same line)

Scott

|||

Scott,

Given the unstructured nature of this file I think you will have to parse it out yourself in a script task. This isn't as daunting as it sounds. First clue I can give you is that it will have to be an asynchronous script task.

You can still import it into the pipeline using a Flat File Connection Manager though. It'll be a 1-column, 1-row file that's all.

-Jamie

|||

Using a script component of type source should be easiest. A little example follows.

Here is my sample file, representing an 8 byte header, followed by three rows of two columns, 10 and 20 bytes respectively.

00000003A234567890B234567890C234567890D234567890E234567890F234567890G234567890H234567890I234567890

The results table will look a bit like this-

Name Address
A234567890 B234567890C234567890
D234567890 E234567890F234567890
G234567890 H234567890I234567890

You will need to create the two columns in the script component , Name and Address as DT_WSTR 10 and 20 in length.

Now the code-

Public Class ScriptMain

Inherits UserComponent

Private stream As StreamReader

Public Overrides Sub CreateNewOutputRows()

Dim headerRecordCount As Integer

Dim recordCount As Integer = 0

'// Get filename from connection, using full acquire method

Dim filename As String = CType(Me.Connections.Connection.AcquireConnection(Nothing), String)

'// Open source file

stream = New StreamReader(filename)

'// Reader header block, 8 characters

Dim headerBuffer(7) As Char

If stream.ReadBlock(headerBuffer, 0, 8) = 8 Then

'// Store record count for later use in validation

headerRecordCount = CType(New String(headerBuffer), Integer)

Else

Throw New Exception("Invalid file format, header not valid.")

End If

With Output0Buffer

While stream.Peek > 0

'// Add data rows

.AddRow()

.Name = ReadColumn(10)

.Address = ReadColumn(20)

recordCount = recordCount + 1

End While

'// Close down buffer

.SetEndOfRowset()

'// Check record count

If recordCount = headerRecordCount Then

Me.Log(String.Format("Header row count ({0}) matched toital rows found.", headerRecordCount), 1, Nothing)

Else

Throw New Exception(String.Format("Invalid file format, header row count ({0}) not equal to rows found ({1}).", headerRecordCount, recordCount))

End If

End With

End Sub

Private Function ReadColumn(ByVal length As Integer) As String

Dim buffer(length - 1) As Char

If stream.Read(buffer, 0, length) = length Then

Return New String(buffer)

Else

Throw New Exception("Invalid file format, full column length not found.")

End If

End Function

End Class

|||Thanks for the answers guys..

Have gone with using a script component of type source, as per code above. With one change...Just ensured that the stream is closed after processing to ensure the resources are released...

I also added an extra Output for the script which holds the header details - my real data file has extra (useful) details in the header.

Thanks again..

Scott

Flat file with a standard of 4

Hi

I am trying to import a flat file into a table, and from there select values from the table and insert the appropriate values into different tables

The flat file is pipe delimited. I.E

File Example:

01|Name|Surname|BenCode|Counter||||||DateTime

02|Name|Surname|BenCode|SchemeID|SchemeName|

03|Name|Surname|BenCode|ID||||Date_From|Date_To||||||||||

04|Name|Surname|BenCode|SchemeID|SchemeName||||CodeID|CodeDescription||

All these different fields are in one flat file. (It would be nice if they were in 4 seperate flat files but they're not)

I want to take the file, where the ID = 01 then the data must go into table Q1

WHERE the ID = 02 then the data must go into table Q2 and so on

When i tried to do it with SSIS, it started creating columns according to the file, but it takes the first row and counts only that rows fields and calculates the columns based on the firs record, but some of the records have more fields than that of the first row.

If i can just get this flat file imported into a single table then i can split the data up based on the table.

Any ideas will be welcome. I'm quite new to SSIS.

Kind Regards

Carel Greaves

To handle the varying number of columns, you can bring each row in as a single column, then parse it in a script component. By adding multiple outputs to the script task, you can send each record type to it's own unique output. Here's a few examples:

http://agilebi.com/cs/blogs/jwelch/archive/2007/05/08/handling-flat-files-with-varying-numbers-of-columns.aspx

http://agilebi.com/cs/blogs/jwelch/archive/2007/07/12/processing-a-flat-file-with-header-and-detail-rows.aspx

flat file to xml

Hey, all. Is it possible to read a flat file in and convert it to xml in SSIS? Xml is not listed as one of the destination types... OR, is there some easy way to take a fixed width flat file and convert it to xml?

Thanks for any insight!

Jim Work

SSIS does not include an XML destinaiton component. The explanation that I've heard is that this is because XML is too rich and complex a format to easily map to. At least in the first release.

You should be able to do this using a Script Component as a destination, if you have a known XML format to which you should be writing.

|||"if you have a known XML format to which you should be writing"

I can design the schema myself, and I know what I want to do with it. I've not messed with Script Components yet... as you may have noticed, I'm new.

Any idea if there's a tutorial out there that might help? Or even a simple example?

Thanks so much for your help today!

Jim
|||

It's my pleasure to help. It's always great to see how people are using SSIS, and always better to learn from others' pain than from my own.

Take a look at this page: http://msdn2.microsoft.com/en-us/sql/aa336314.aspx

There are sample component source code projects and tutorials/documentation on creating custom SSIS components available here for download.

|||

Jim Work wrote:

"if you have a known XML format to which you should be writing"

I can design the schema myself, and I know what I want to do with it. I've not messed with Script Components yet... as you may have noticed, I'm new.

Any idea if there's a tutorial out there that might help? Or even a simple example?

Thanks so much for your help today!

Jim

I've run into this a few times recently myself, so I created an example and posted it to my blog. Hope it helps.

http://agilebi.com/cs/blogs/jwelch/archive/2007/06/02/xml-destination-script-component.aspx

Flat file to table - rows out of order

Hi,

I noticed something strange today. I created a pkg that reads a flat file and writes the rows to a table.

In checking the data in the file against what's in the table, I noticed that the rows were inserted in a different order than they are in the file.

All the rows appear to be in the table correctly, but they're just not in the same order as in the file. I've never seen this before. But I checked very carefully, and this is indeed the case.

Is this normal?

Thanks

Is it normal? Well...its not not normal!

There is no concept of order in a database table. You should never assume that rows will get returned to you in the order that (you assume) they were inserted.

-Jamie

|||

That is not my understanding. For example, if you create a table, then insert a bunch of rows, one at a time, they will most definitely be returned in the order they were inserted. I have *never* seen an exception to this.

Perhaps the SSIS package is not inserting the rows in the order they are in the file?

Anyhow, I could be wrong, but this goes against my experience completely.

|||Not to sound mean or anything, but Jamie is absolutely right. There is no such thing as ordering in database land. Just because your experience "proves" otherwise, doesn't make it fact. The only way to guarantee order is to use an ORDER BY clause on your SQL statement which only controls the PRESENTATION of the data, not the way it's stored.

Do you have a situation that the records are out of order when ordering by an identity column, or are you merely using a "select * from table" statement without an ORDER BY clause?

This is perfectly normal behavior. You might want to add a sort transformation right before the destination. But still, there are no guarantees that the data will be stored "in order."|||By its definition, a database table is an unordered set of rows. While "most" of the time, a select without an ORDER BY clause will return the rows in the order they were entered, it is never guaranteed. The only way to guarantee retrieving rows in the order you want is with an ORDER BY clause on the query.|||

There are many factors that influence the order in which rows are returned. The most obvious being the presence of indexes.

Other possible causes may be the number of processors, what data is cached, datafile placement, datafile fill factors, hard drive configuration. There are a million and one things.

These same factors that affect the retrieval of data can also affect the insertion of data. Hopefully you can see how the order in which data is retrieved can be affected.

There is no concept of order in a database table. Period.

-Jamie

|||

Ok, ok - just had to make sure. As this goes against anything I have ever seen before. I've only been using SQL Server a couple years now, so there's a lot of things I haven't seen. This is one of them.

Anyways, thanks.

|||

sadie519590 wrote:

Ok, ok - just had to make sure. As this goes against anything I have ever seen before. I've only been using SQL Server a couple years now, so there's a lot of things I haven't seen. This is one of them.

Anyways, thanks.

No worries. All the training courses in the world wouldn't have taught you this. The only way you learn a product is by using it. I've been using this damn thing for seven years now and I only know a fraction of it

-Jamie

Flat file to table

Hi,

I have a set of flat files and transforming it to SQL server. If I do that in 2000 it was done with in 45 seconds for 1.5 M records. If I do the same in SSIS it takes 3 minutes. Why there is difference in time that too lower when compared to the previous version. I used the data access mode as "Fast load". Am I missing anything while doing through SSIS?

There's so many "it depends" answers to this its not really worth posting a possible reason.

What exactly is the data flow doing? Where is the bottleneck?

-Jamie

|||

Its a very straight transformation. CSV file to a table and all the fields are set as Varchar,

- No validations made on the transformation

- No Calculations.

- No aggregations

again its a very straight transformation.

|||one thing i forget to mention. In 2000 I am using the global variable for looping the source files. In SSIS i used "For each loop" container.|||

And where is the bottleneck? Is it in sourcing the data or loading it to the target?

Check this out for tips on diagnosing bottlenecks:

http://blogs.conchango.com/jamiethomson/archive/2006/06/14/SSIS_3A00_-Donald-Farmer_2700_s-Technet-webcast.aspx

-Jamie

|||

Jamie,

Thanks for sending the link, I will go through it in the evening as I am now in office. In the mean time I fixed and the performance is increased from 3 minutes to just 21 seconds (2000 took 45 seconds for the same transformation). The change I made is previously it was Native OLE DB but I changed it to MS OLE DB. If you find time could you please send any link or explain how this has created the dramatic change in performance.

Thanks for your time.

|||

I'm not sure what you mean by "native OLE DB". Can you send a link to the OLE DB driver that you were using?

-Jamie

|||

Jamie,

The link you provided was awesome. Thanks to Donald farmer for wonderful explanation and for you to identifing it to me on the right time.

Initially i had the provider as "Native OLE DB\SQL Native client" in the connection manager when it gives outpu on 3 minutes. When I changed this to "Native OLE DB \ Microsft OLE DB Provider for SQL server" it was processint the same task in less than 30 minutes. Is this due to the driver? how do i choose the best dirver?

|||

Dhanasu wrote:

...it was processint the same task in less than 30 minutes...

Based on your above comment, I'm assuming you mean "30 seconds" not 30 minutes.

|||Yes you're correct. it is 30 seconds.|||

That is an interesting observation. I would expect the opposite results, as SQL Native Client is the more recent provider.

It is almost certain that the difference lies in the used provider. I would try to ask why that is on the Data Access forum:

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=87&SiteID=1

Thanks.

sql

Flat File to SQL table

I am looking to evaluate a text field in the row and change it to an integer in the sql table based on the text.

What is the best data flow tranformation for me to use inbetween the flat file source and the ole db destination?

it depends on what logic you are using for your evaluation but Derived Column will probably do it. If not, the script component.

-Jamie

|||Can you help with an example If then expression?|||

With the information you have provided, no. What evaluation do you want to do?

-Jamie

|||

Something like:

If [Treatment] = "No Deposit Required" then 1 else 0

I'm not sure how to write this in an expression.

|||

OK

[Treatment] == "No Deposit Required" ? (DT_I4)1 : (DT_I4)0

-Jamie

|||Thanks. your great..