Flat file source not communicating with script component properly

Hi there,

I have a flat file source in my data flow that is suppose to feed through a couple of columns to a script component. All of the columns have data but when I debug in my script component (with the aid of messageboxes) I can see that one of then is empty.

This is wrong because when I preview that data in the flat file source, I can see that the columns are all populated.

Has anyone ever encountered that a flat file source doesn't communicate with a script component properly? I mean it does pass through the data of all other columns except for one.

I have replaced the components in case it had become corrupt and I even rebuild the package but the problem still persists.

Any advice would be welcome

Regards
Mike

Put data viewer between the Flat File Source and the Script component and check what data actually enters your script.

HTH,

Bob

|||Thanks for the reply,

I put a viewer on and saw that the script gets all the populated columns except for the one I mentioned earlier. Thus the problem seem to be with the flat file source not passing the data in that column through to the script.
But why would it do that even though it shows all of the columns populated when I click on the preview button on the flat file source?

Regards

**EDITED**

I have also noted that when I check the "retail null values from row as null values in data flow" checked box, then it does populate all columns correctly but I cant have that checked box checked since it does not go to the correct path on my workflow with the nulls. When I uncheck it, it misses the one column again but not the other ones. This is puzzling me very much
|||

What version of the SSIS do you have installed (RTM, SP1, SP2)? I think there was a flat file parsing bug fixed in one of SP releases that would potentialy cause this behavior.

Thanks,

Bob

|||Hi Bob,

I have recently (last week) upgraded to service pack 2 and also updated my ssis from Version 9.00.20(something) to Version 9.00.3042.00.

The package was built on an older version and pre service pack 2. I wonder if that is not the case here.

Thanks for the reply

Regards
Mike
|||

Would it be possible for you to isolate a few rows that fail, create another simple file with only those two rows, obfuscate your data and post your file here?

Just make sure the newly created file still fails the same way as the original one.

Thanks,

Bob

|||Hi Bob,

Just for some extra clarity, none of the rows are actually failing. They all pass through successfully except for one of the columns (SiteCode) in the file does not get read properly by the flat file source. It does not fail or warn me about anything, it just doesn't detect the data in this specific column. It passes an empty string through for all rows in that column.

Like I mentioned before, the funny thing is that when I preview the file in the flat file source in the data flow task, it does actually show that the column is populated. All of the SiteCodes are populated in the preview window but that preview data just doesn't get passed through to the other data flow component.

Would it still be usefull for me to post the file?

Regards
Mike
|||

Yes, by "failing" I meant not being parsed properly.

If you can provide the sample of data I can try to debug it here and eventually find out what is causing the behavior you are seeing.

Thanks,

Bob

|||Hi,

I hope this will be sufficient
The column with the 'BAL' data doesn't get read properly
Regards

FMC|FMCG|22110|FMCG Warh|BAL|Balance Sheet|BAL|Balance Sheet|22110|FMCG Warehouse Balance sheet|10030||Cost - Comp Equip 3yrs|2006|10|144.00|AA|General Ledger||
FMC|FMCG|22110|FMCG Warh|BAL|Balance Sheet|BAL|Balance Sheet|22110|FMCG Warehouse Balance sheet|10050||Cost - Comp Equip 10yrs|2006|10|233.00|AA|General Ledger||

|||

I tried to parse these two rows using Flat File Source and could not see any problem with it.

Could you tell me again what is not read properly? I have seen "BAL" values read in both; 5th and 7th columns.

|||Hi Bob,

I managed to find a workaround on this problem. When I click the "retain null values from source as null values in data flow" check box, the data does get passed on correctly but to the wrong exit point (error exit point).

I did further investigation as to why it would go to the error exit point and realized that it actually picks up on a null value and does not know how to handle it. I got the "object reference not set as an instance of a ...etc" error.

Adding code to check for these nulls and setting it to dbnull.value helped with my problem and things are working fine now. I also believe that my problem might also be because we did not use text qualifiers in this project but this is only speculation.

From now on I will know what to do with custom scripts reading and processing text files so that I don't leave the system open for these possibly unhandled situations.

Regards

Flat file source not communicating with script component properly

Hi there,

I have a flat file source in my data flow that is suppose to feed through a couple of columns to a script component. All of the columns have data but when I debug in my script component (with the aid of messageboxes) I can see that one of then is empty.

This is wrong because when I preview that data in the flat file source, I can see that the columns are all populated.

Has anyone ever encountered that a flat file source doesn't communicate with a script component properly? I mean it does pass through the data of all other columns except for one.

I have replaced the components in case it had become corrupt and I even rebuild the package but the problem still persists.

Any advice would be welcome

Regards
Mike

Put data viewer between the Flat File Source and the Script component and check what data actually enters your script.

HTH,

Bob

|||Thanks for the reply,

I put a viewer on and saw that the script gets all the populated columns except for the one I mentioned earlier. Thus the problem seem to be with the flat file source not passing the data in that column through to the script.
But why would it do that even though it shows all of the columns populated when I click on the preview button on the flat file source?

Regards

**EDITED**

I have also noted that when I check the "retail null values from row as null values in data flow" checked box, then it does populate all columns correctly but I cant have that checked box checked since it does not go to the correct path on my workflow with the nulls. When I uncheck it, it misses the one column again but not the other ones. This is puzzling me very much
|||

What version of the SSIS do you have installed (RTM, SP1, SP2)? I think there was a flat file parsing bug fixed in one of SP releases that would potentialy cause this behavior.

Thanks,

Bob

|||Hi Bob,

I have recently (last week) upgraded to service pack 2 and also updated my ssis from Version 9.00.20(something) to Version 9.00.3042.00.

The package was built on an older version and pre service pack 2. I wonder if that is not the case here.

Thanks for the reply

Regards
Mike
|||

Would it be possible for you to isolate a few rows that fail, create another simple file with only those two rows, obfuscate your data and post your file here?

Just make sure the newly created file still fails the same way as the original one.

Thanks,

Bob

|||Hi Bob,

Just for some extra clarity, none of the rows are actually failing. They all pass through successfully except for one of the columns (SiteCode) in the file does not get read properly by the flat file source. It does not fail or warn me about anything, it just doesn't detect the data in this specific column. It passes an empty string through for all rows in that column.

Like I mentioned before, the funny thing is that when I preview the file in the flat file source in the data flow task, it does actually show that the column is populated. All of the SiteCodes are populated in the preview window but that preview data just doesn't get passed through to the other data flow component.

Would it still be usefull for me to post the file?

Regards
Mike
|||

Yes, by "failing" I meant not being parsed properly.

If you can provide the sample of data I can try to debug it here and eventually find out what is causing the behavior you are seeing.

Thanks,

Bob

|||Hi,

I hope this will be sufficient
The column with the 'BAL' data doesn't get read properly
Regards

FMC|FMCG|22110|FMCG Warh|BAL|Balance Sheet|BAL|Balance Sheet|22110|FMCG Warehouse Balance sheet|10030||Cost - Comp Equip 3yrs|2006|10|144.00|AA|General Ledger||
FMC|FMCG|22110|FMCG Warh|BAL|Balance Sheet|BAL|Balance Sheet|22110|FMCG Warehouse Balance sheet|10050||Cost - Comp Equip 10yrs|2006|10|233.00|AA|General Ledger||

|||

I tried to parse these two rows using Flat File Source and could not see any problem with it.

Could you tell me again what is not read properly? I have seen "BAL" values read in both; 5th and 7th columns.

|||Hi Bob,

I managed to find a workaround on this problem. When I click the "retain null values from source as null values in data flow" check box, the data does get passed on correctly but to the wrong exit point (error exit point).

I did further investigation as to why it would go to the error exit point and realized that it actually picks up on a null value and does not know how to handle it. I got the "object reference not set as an instance of a ...etc" error.

Adding code to check for these nulls and setting it to dbnull.value helped with my problem and things are working fine now. I also believe that my problem might also be because we did not use text qualifiers in this project but this is only speculation.

From now on I will know what to do with custom scripts reading and processing text files so that I don't leave the system open for these possibly unhandled situations.

Regards

FK Index Advice

I have a couple custom database that I am building. They will be 3NF with several many to many linking tables.

In general, both databases will have about 30 tables. MOST tables will have an autonumber PK, and two FK fields related to PK's on another table. My question is about indexing...

I will not be designing the application(s) using the DB's (as far as ASP code, VB script) etc, so I am not familiar with the SQL to be used, nor am I confident that my developers will run the code by me before the system is done.

Therefore, I am trying to create very general indexes on each table that will serve generic needs (my attempt at half-way optimizing the DB). So each table has a unique index on the PK, and each FK has it OWN non-unique index. Is this the correct way to do this? Or, should I have one index that covers both FK columns rather than two indexes? Any thoughts? Please provide some reasoning with any answers.

Thanks in advance!

Ryan HuntRun the CPU intensive statements through the Index Tuning Wizard (ITW) in order to get any index recommendations that may be necessary.

Review information from this http://www.sql-server-performance.com/mr_indexing.asp link on choosing indexes.

Fixed datafile suddenly change to max "automaticgrow"

Hi to all,
If I give in a fixed datafile size ( say 80000 Mb ) for a couple of
datafiles SQL server is capturing the size on the filesystem and after
finishing I asume this size is dedicated to this particular file(s) and
checking this it seems to be OK....
however. . .
Checking this same fixed filesize the next moring the original size ( before
yesterdays action ) has returned and the 80000 Mb has been MOVED to the
"automatic grow file" part as max extented restriction. ( this part was
grayed out and is still grayed out ).
it seems that I have a problem when SQL server needs to expand . . . . .
( the application running this database has a mechanism to stop when 80pct
has been reached of just one datafile ( of many ) - no alternative on the
application part !! )
Is this a known issue or is this a SQL server BUG and how to fixe this '
Please help me out.
GKramer
The NetherlandsHi
I think you are using SQL Server 2005 , right? I have seen this behaviour
when you detach/attach the database and it should be fixed in SP1 , however
someone else can provide more info.
"Guus Kramer" <GuusKramer@.discussions.microsoft.com> wrote in message
news:1B126CF6-8A66-429A-8D2E-82D730C9502F@.microsoft.com...
> Hi to all,
> If I give in a fixed datafile size ( say 80000 Mb ) for a couple of
> datafiles SQL server is capturing the size on the filesystem and after
> finishing I asume this size is dedicated to this particular file(s) and
> checking this it seems to be OK....
> however. . .
> Checking this same fixed filesize the next moring the original size (
> before
> yesterdays action ) has returned and the 80000 Mb has been MOVED to the
> "automatic grow file" part as max extented restriction. ( this part was
> grayed out and is still grayed out ).
> it seems that I have a problem when SQL server needs to expand . . . . .
> ( the application running this database has a mechanism to stop when 80pct
> has been reached of just one datafile ( of many ) - no alternative on the
> application part !! )
> Is this a known issue or is this a SQL server BUG and how to fixe this '
> Please help me out.
> GKramer
> The Netherlands
>|||Uri,
Thanks for the quick reply.
Unfortionately we still use SQL serve 2000 ( 2005 is still being tested ).
No attach or detach has been made. . . .it just happened overnight !!!!!
Guus
"Uri Dimant" wrote:

> Hi
> I think you are using SQL Server 2005 , right? I have seen this behaviour
> when you detach/attach the database and it should be fixed in SP1 , howeve
r
> someone else can provide more info.
>
>
> "Guus Kramer" <GuusKramer@.discussions.microsoft.com> wrote in message
> news:1B126CF6-8A66-429A-8D2E-82D730C9502F@.microsoft.com...
>
>|||Hi
Well, so perhaps someone has changed this feature and you did not know , did
you?
"Guus Kramer" <GuusKramer@.discussions.microsoft.com> wrote in message
news:83ECCAAA-3E98-4F88-AC2E-99AD3F9E93DB@.microsoft.com...[vbcol=seagreen]
> Uri,
> Thanks for the quick reply.
> Unfortionately we still use SQL serve 2000 ( 2005 is still being tested ).
> No attach or detach has been made. . . .it just happened overnight !!!!!
> Guus
> "Uri Dimant" wrote:
>|||Uri,
I was the only DBA for Europe last week and no-one other than me ( and our
DBA team ) has access !!!! ( or even has the knowledge on this )
Guus
"Uri Dimant" wrote:

> Hi
> Well, so perhaps someone has changed this feature and you did not know , d
id
> you?
>
>
> "Guus Kramer" <GuusKramer@.discussions.microsoft.com> wrote in message
> news:83ECCAAA-3E98-4F88-AC2E-99AD3F9E93DB@.microsoft.com...
>
>|||Hi
Then I have no idea. It could be that a script will be ran to change the
settings , you know, there is no magic here :-))))
"Guus Kramer" <GuusKramer@.discussions.microsoft.com> wrote in message
news:70F7163C-82F1-4625-955E-C15FEBDB1D51@.microsoft.com...[vbcol=seagreen]
> Uri,
> I was the only DBA for Europe last week and no-one other than me ( and our
> DBA team ) has access !!!! ( or even has the knowledge on this )
> Guus
> "Uri Dimant" wrote:
>

Fixed datafile suddenly change to max "automaticgrow"

Hi to all,
If I give in a fixed datafile size ( say 80000 Mb ) for a couple of
datafiles SQL server is capturing the size on the filesystem and after
finishing I asume this size is dedicated to this particular file(s) and
checking this it seems to be OK....
however. . .
Checking this same fixed filesize the next moring the original size ( before
yesterdays action ) has returned and the 80000 Mb has been MOVED to the
"automatic grow file" part as max extented restriction. ( this part was
grayed out and is still grayed out ).
it seems that I have a problem when SQL server needs to expand . . . . .
( the application running this database has a mechanism to stop when 80pct
has been reached of just one datafile ( of many ) - no alternative on the
application part !! )
Is this a known issue or is this a SQL server BUG and how to fixe this '
Please help me out.
GKramer
The NetherlandsHi
I think you are using SQL Server 2005 , right? I have seen this behaviour
when you detach/attach the database and it should be fixed in SP1 , however
someone else can provide more info.
"Guus Kramer" <GuusKramer@.discussions.microsoft.com> wrote in message
news:1B126CF6-8A66-429A-8D2E-82D730C9502F@.microsoft.com...
> Hi to all,
> If I give in a fixed datafile size ( say 80000 Mb ) for a couple of
> datafiles SQL server is capturing the size on the filesystem and after
> finishing I asume this size is dedicated to this particular file(s) and
> checking this it seems to be OK....
> however. . .
> Checking this same fixed filesize the next moring the original size (
> before
> yesterdays action ) has returned and the 80000 Mb has been MOVED to the
> "automatic grow file" part as max extented restriction. ( this part was
> grayed out and is still grayed out ).
> it seems that I have a problem when SQL server needs to expand . . . . .
> ( the application running this database has a mechanism to stop when 80pct
> has been reached of just one datafile ( of many ) - no alternative on the
> application part !! )
> Is this a known issue or is this a SQL server BUG and how to fixe this '
> Please help me out.
> GKramer
> The Netherlands
>|||Uri,
Thanks for the quick reply.
Unfortionately we still use SQL serve 2000 ( 2005 is still being tested ).
No attach or detach has been made. . . .it just happened overnight !!!!!
Guus
"Uri Dimant" wrote:
> Hi
> I think you are using SQL Server 2005 , right? I have seen this behaviour
> when you detach/attach the database and it should be fixed in SP1 , however
> someone else can provide more info.
>
>
> "Guus Kramer" <GuusKramer@.discussions.microsoft.com> wrote in message
> news:1B126CF6-8A66-429A-8D2E-82D730C9502F@.microsoft.com...
> > Hi to all,
> >
> > If I give in a fixed datafile size ( say 80000 Mb ) for a couple of
> > datafiles SQL server is capturing the size on the filesystem and after
> > finishing I asume this size is dedicated to this particular file(s) and
> > checking this it seems to be OK....
> > however. . .
> >
> > Checking this same fixed filesize the next moring the original size (
> > before
> > yesterdays action ) has returned and the 80000 Mb has been MOVED to the
> > "automatic grow file" part as max extented restriction. ( this part was
> > grayed out and is still grayed out ).
> >
> > it seems that I have a problem when SQL server needs to expand . . . . .
> > ( the application running this database has a mechanism to stop when 80pct
> > has been reached of just one datafile ( of many ) - no alternative on the
> > application part !! )
> >
> > Is this a known issue or is this a SQL server BUG and how to fixe this '
> >
> > Please help me out.
> >
> > GKramer
> > The Netherlands
> >
>
>|||Hi
Well, so perhaps someone has changed this feature and you did not know , did
you?
"Guus Kramer" <GuusKramer@.discussions.microsoft.com> wrote in message
news:83ECCAAA-3E98-4F88-AC2E-99AD3F9E93DB@.microsoft.com...
> Uri,
> Thanks for the quick reply.
> Unfortionately we still use SQL serve 2000 ( 2005 is still being tested ).
> No attach or detach has been made. . . .it just happened overnight !!!!!
> Guus
> "Uri Dimant" wrote:
>> Hi
>> I think you are using SQL Server 2005 , right? I have seen this
>> behaviour
>> when you detach/attach the database and it should be fixed in SP1 ,
>> however
>> someone else can provide more info.
>>
>>
>> "Guus Kramer" <GuusKramer@.discussions.microsoft.com> wrote in message
>> news:1B126CF6-8A66-429A-8D2E-82D730C9502F@.microsoft.com...
>> > Hi to all,
>> >
>> > If I give in a fixed datafile size ( say 80000 Mb ) for a couple of
>> > datafiles SQL server is capturing the size on the filesystem and after
>> > finishing I asume this size is dedicated to this particular file(s) and
>> > checking this it seems to be OK....
>> > however. . .
>> >
>> > Checking this same fixed filesize the next moring the original size (
>> > before
>> > yesterdays action ) has returned and the 80000 Mb has been MOVED to the
>> > "automatic grow file" part as max extented restriction. ( this part was
>> > grayed out and is still grayed out ).
>> >
>> > it seems that I have a problem when SQL server needs to expand . . . .
>> > .
>> > ( the application running this database has a mechanism to stop when
>> > 80pct
>> > has been reached of just one datafile ( of many ) - no alternative on
>> > the
>> > application part !! )
>> >
>> > Is this a known issue or is this a SQL server BUG and how to fixe this
>> > '
>> >
>> > Please help me out.
>> >
>> > GKramer
>> > The Netherlands
>> >
>>|||Uri,
I was the only DBA for Europe last week and no-one other than me ( and our
DBA team ) has access !!!! ( or even has the knowledge on this )
Guus
"Uri Dimant" wrote:
> Hi
> Well, so perhaps someone has changed this feature and you did not know , did
> you?
>
>
> "Guus Kramer" <GuusKramer@.discussions.microsoft.com> wrote in message
> news:83ECCAAA-3E98-4F88-AC2E-99AD3F9E93DB@.microsoft.com...
> > Uri,
> >
> > Thanks for the quick reply.
> >
> > Unfortionately we still use SQL serve 2000 ( 2005 is still being tested ).
> > No attach or detach has been made. . . .it just happened overnight !!!!!
> >
> > Guus
> >
> > "Uri Dimant" wrote:
> >
> >> Hi
> >> I think you are using SQL Server 2005 , right? I have seen this
> >> behaviour
> >> when you detach/attach the database and it should be fixed in SP1 ,
> >> however
> >> someone else can provide more info.
> >>
> >>
> >>
> >>
> >>
> >> "Guus Kramer" <GuusKramer@.discussions.microsoft.com> wrote in message
> >> news:1B126CF6-8A66-429A-8D2E-82D730C9502F@.microsoft.com...
> >> > Hi to all,
> >> >
> >> > If I give in a fixed datafile size ( say 80000 Mb ) for a couple of
> >> > datafiles SQL server is capturing the size on the filesystem and after
> >> > finishing I asume this size is dedicated to this particular file(s) and
> >> > checking this it seems to be OK....
> >> > however. . .
> >> >
> >> > Checking this same fixed filesize the next moring the original size (
> >> > before
> >> > yesterdays action ) has returned and the 80000 Mb has been MOVED to the
> >> > "automatic grow file" part as max extented restriction. ( this part was
> >> > grayed out and is still grayed out ).
> >> >
> >> > it seems that I have a problem when SQL server needs to expand . . . .
> >> > .
> >> > ( the application running this database has a mechanism to stop when
> >> > 80pct
> >> > has been reached of just one datafile ( of many ) - no alternative on
> >> > the
> >> > application part !! )
> >> >
> >> > Is this a known issue or is this a SQL server BUG and how to fixe this
> >> > '
> >> >
> >> > Please help me out.
> >> >
> >> > GKramer
> >> > The Netherlands
> >> >
> >>
> >>
> >>
>
>|||Hi
Then I have no idea. It could be that a script will be ran to change the
settings , you know, there is no magic here :-))))
"Guus Kramer" <GuusKramer@.discussions.microsoft.com> wrote in message
news:70F7163C-82F1-4625-955E-C15FEBDB1D51@.microsoft.com...
> Uri,
> I was the only DBA for Europe last week and no-one other than me ( and our
> DBA team ) has access !!!! ( or even has the knowledge on this )
> Guus
> "Uri Dimant" wrote:
>> Hi
>> Well, so perhaps someone has changed this feature and you did not know ,
>> did
>> you?
>>
>>
>> "Guus Kramer" <GuusKramer@.discussions.microsoft.com> wrote in message
>> news:83ECCAAA-3E98-4F88-AC2E-99AD3F9E93DB@.microsoft.com...
>> > Uri,
>> >
>> > Thanks for the quick reply.
>> >
>> > Unfortionately we still use SQL serve 2000 ( 2005 is still being
>> > tested ).
>> > No attach or detach has been made. . . .it just happened overnight
>> > !!!!!
>> >
>> > Guus
>> >
>> > "Uri Dimant" wrote:
>> >
>> >> Hi
>> >> I think you are using SQL Server 2005 , right? I have seen this
>> >> behaviour
>> >> when you detach/attach the database and it should be fixed in SP1 ,
>> >> however
>> >> someone else can provide more info.
>> >>
>> >>
>> >>
>> >>
>> >>
>> >> "Guus Kramer" <GuusKramer@.discussions.microsoft.com> wrote in message
>> >> news:1B126CF6-8A66-429A-8D2E-82D730C9502F@.microsoft.com...
>> >> > Hi to all,
>> >> >
>> >> > If I give in a fixed datafile size ( say 80000 Mb ) for a couple of
>> >> > datafiles SQL server is capturing the size on the filesystem and
>> >> > after
>> >> > finishing I asume this size is dedicated to this particular file(s)
>> >> > and
>> >> > checking this it seems to be OK....
>> >> > however. . .
>> >> >
>> >> > Checking this same fixed filesize the next moring the original size
>> >> > (
>> >> > before
>> >> > yesterdays action ) has returned and the 80000 Mb has been MOVED to
>> >> > the
>> >> > "automatic grow file" part as max extented restriction. ( this part
>> >> > was
>> >> > grayed out and is still grayed out ).
>> >> >
>> >> > it seems that I have a problem when SQL server needs to expand . . .
>> >> > .
>> >> > .
>> >> > ( the application running this database has a mechanism to stop when
>> >> > 80pct
>> >> > has been reached of just one datafile ( of many ) - no alternative
>> >> > on
>> >> > the
>> >> > application part !! )
>> >> >
>> >> > Is this a known issue or is this a SQL server BUG and how to fixe
>> >> > this
>> >> > '
>> >> >
>> >> > Please help me out.
>> >> >
>> >> > GKramer
>> >> > The Netherlands
>> >> >
>> >>
>> >>
>> >>
>>