Showing posts with label transformation. Show all posts
Showing posts with label transformation. Show all posts

Friday, March 30, 2012

Importing within SQL Server Express without Data Transformation Services

I'm sorry to repost this from microsoft.public.sqlserver.msde where I
had recently submitted this, if anyone would have noted, but I still
can't resolve the seemingly simple task of importing from one database
to another within even the same instance of SQL Server Express. I
would be so grateful if someone could help me:
The specific example I was working on involved an attempt to run an
update query to place data from a specific field in an older copy of an
otherwise identical database into a more recent copy. Ultimately I
just plugged the small data series in manually using side-by-side views
because I could not overcome the syntax errors generated in the effort
to refer to the "external" database.
This is the setup in question:
- An instance in the format of "MyComputerName\SQLExpress," and both an
- "OldDatabase" and
- "NewDatabase" attached and viewable from the Management Studio
Express, with identical fields
I tried a few different query techniques as suggested in historical
posts, but to no avail. Here is such an example that I tried:
In MSE I right clicked on the target table in question and chose:
"Script table as," then
"UPDATE to," then
"New Query Editor Window"
I removed all the fields except that in question and added this Where
clause:
UPDATE [NewDatabase].[dbo].[Table]
SET [Field] = [OldDatabase].[dbo].[Table].[Field]
WHERE [NewDatabase].[dbo].[Table].[PK] =
[OldDatabase].[dbo].[Table].[PK]
in which the primary key (an auto-increment integer) is identical
between the new and old tables.
This produces the error:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "OldDatabase.dbo.Table.PK" could not be
bound.
The same error occurs with different techniques (joins, subqueries,
etc.)
John Hackert wrote:

> I'm sorry to repost this from microsoft.public.sqlserver.msde where I
> had recently submitted this, if anyone would have noted, but I still
> can't resolve the seemingly simple task of importing from one database
> to another within even the same instance of SQL Server Express. I
> would be so grateful if someone could help me:
> The specific example I was working on involved an attempt to run an
> update query to place data from a specific field in an older copy of an
> otherwise identical database into a more recent copy. Ultimately I
> just plugged the small data series in manually using side-by-side views
> because I could not overcome the syntax errors generated in the effort
> to refer to the "external" database.
> This is the setup in question:
> - An instance in the format of "MyComputerName\SQLExpress," and both an
> - "OldDatabase" and
> - "NewDatabase" attached and viewable from the Management Studio
> Express, with identical fields
> I tried a few different query techniques as suggested in historical
> posts, but to no avail. Here is such an example that I tried:
> In MSE I right clicked on the target table in question and chose:
> "Script table as," then
> "UPDATE to," then
> "New Query Editor Window"
> I removed all the fields except that in question and added this Where
> clause:
> UPDATE [NewDatabase].[dbo].[Table]
> SET [Field] = [OldDatabase].[dbo].[Table].[Field]
> WHERE [NewDatabase].[dbo].[Table].[PK] =
> [OldDatabase].[dbo].[Table].[PK]
> in which the primary key (an auto-increment integer) is identical
> between the new and old tables.
> This produces the error:
> Msg 4104, Level 16, State 1, Line 1
> The multi-part identifier "OldDatabase.dbo.Table.PK" could not be
> bound.
> The same error occurs with different techniques (joins, subqueries,
> etc.)
try this
USE NewDatabase
UPDATE [NewDatabase].[dbo].[Table]
SET [Field] = Old.[Field]
from [NewDatabase].[dbo].[Table] New inner join
[OldDatabase].[dbo].[Table] Old
on
New.[PK] =
Old.[PK]
Regards
Amish Shah
|||Amish,
Thank you so much for your help. I set up two test databases,
"TestNew" and "TestOld" with parallel tables and test data. I found
that the syntax you proposed worked:
UPDATE [TestNew].[dbo].[tblProcedure]
SET [CPT] = TestOld.[CPT]
FROM [TestNew].[dbo].[tblProcedure] TestNew inner join
[TestOld].[dbo].[tblProcedure] TestOld
on
TestNew.[procedureID]=TestOld.[procedureID]
Whereas syntax such as this did not work:
UPDATE [TestNew].[dbo].[tblProcedure]
SET [CPT] = TestOld.[CPT]
FROM [TestNew].[dbo].[tblProcedure]
WHERE [Testnew].[dbo].[tblProcedure].procedureID
=[TestOld].[dbo].[tblProcedure].procedureID
The following error was generated:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "TestOld.dbo.tblProcedure.procedureID" could
not be bound.
SQL Server is relatively new to me, and the syntax/manipulations often
seem cryptic. May I ask you to help me understand the asterisked parts
of the construction:
FROM [TestNew].[dbo].[tblProcedure] *TestNew* inner join
[TestOld].[dbo].[tblProcedure] *TestOld*
on
TestNew.[procedureID]=TestOld.[procedureID]
Thanks,
John
amish wrote:
> John Hackert wrote:
>
> try this
> USE NewDatabase
> UPDATE [NewDatabase].[dbo].[Table]
> SET [Field] = Old.[Field]
> from [NewDatabase].[dbo].[Table] New inner join
> [OldDatabase].[dbo].[Table] Old
> on
> New.[PK] =
> Old.[PK]
>
> Regards
> Amish Shah
|||On Nov 5, 2:26 am, "John Hackert" <hackertjo...@.yahoo.com> wrote:[vbcol=seagreen]
> Amish,
> Thank you so much for your help. I set up two test databases,
> "TestNew" and "TestOld" with parallel tables and test data. I found
> that the syntax you proposed worked:
> UPDATE [TestNew].[dbo].[tblProcedure]
> SET [CPT] = TestOld.[CPT]
> FROM [TestNew].[dbo].[tblProcedure] TestNew inner join
> [TestOld].[dbo].[tblProcedure] TestOld
> on
> TestNew.[procedureID]=TestOld.[procedureID]
> Whereas syntax such as this did not work:
> UPDATE [TestNew].[dbo].[tblProcedure]
> SET [CPT] = TestOld.[CPT]
> FROM [TestNew].[dbo].[tblProcedure]
> WHERE [Testnew].[dbo].[tblProcedure].procedureID
> =[TestOld].[dbo].[tblProcedure].procedureID
> The following error was generated:
> Msg 4104, Level 16, State 1, Line 1
> The multi-part identifier "TestOld.dbo.tblProcedure.procedureID" could
> not be bound.
> SQL Server is relatively new to me, and the syntax/manipulations often
> seem cryptic. May I ask you to help me understand the asterisked parts
> of the construction:
> FROM [TestNew].[dbo].[tblProcedure] *TestNew* inner join
> [TestOld].[dbo].[tblProcedure] *TestOld*
> on
> TestNew.[procedureID]=TestOld.[procedureID]
> Thanks,
> John
> amish wrote:
>
>
>
>
>
>
John
Asterisk part is just an alias for the table.
Once you specify alias for the table in form clause of the query you
can refer it in other part of query instead of table name.
You have to add second table also in form clause.
You should change your query to
UPDATE [TestNew].[dbo].[tblProcedure]
SET [CPT] = TestOld.[CPT]
FROM [TestNew].[dbo].[tblProcedure] testnew,
[TestOld].[dbo].[tblProcedure] testold
WHERE [testnew].procedureID
=TestOld.procedureID
Regards
Amish Shah
|||You're correct, the use of 'AS' when identifying an alias is indeed
optional.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"John Hackert" <hackertjohnb@.yahoo.com> wrote in message
news:1162861737.778956.88230@.b28g2000cwb.googlegro ups.com...
> Amish,
> I've used aliases in other contexts, but always with the keyword "AS"
> - So the "AS" is an optional part of the syntax?
> I see now from your example that the failure of the previous queries
> related to not specifying the second table in the from clause.
> Once again I'm grateful for your help
> John
>
> amish wrote:
>

Importing within SQL Server Express without Data Transformation Services

I'm sorry to repost this from microsoft.public.sqlserver.msde where I
had recently submitted this, if anyone would have noted, but I still
can't resolve the seemingly simple task of importing from one database
to another within even the same instance of SQL Server Express. I
would be so grateful if someone could help me:
The specific example I was working on involved an attempt to run an
update query to place data from a specific field in an older copy of an
otherwise identical database into a more recent copy. Ultimately I
just plugged the small data series in manually using side-by-side views
because I could not overcome the syntax errors generated in the effort
to refer to the "external" database.
This is the setup in question:
- An instance in the format of "MyComputerName\SQLExpress," and both an
- "OldDatabase" and
- "NewDatabase" attached and viewable from the Management Studio
Express, with identical fields
I tried a few different query techniques as suggested in historical
posts, but to no avail. Here is such an example that I tried:
In MSE I right clicked on the target table in question and chose:
"Script table as," then
"UPDATE to," then
"New Query Editor Window"
I removed all the fields except that in question and added this Where
clause:
UPDATE [NewDatabase].[dbo].[Table]
SET [Field] = [OldDatabase].[dbo].[Table].[Field]
WHERE [NewDatabase].[dbo].[Table].[PK] =
[OldDatabase].[dbo].[Table].[PK]
in which the primary key (an auto-increment integer) is identical
between the new and old tables.
This produces the error:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "OldDatabase.dbo.Table.PK" could not be
bound.
The same error occurs with different techniques (joins, subqueries,
etc.)John Hackert wrote:

> I'm sorry to repost this from microsoft.public.sqlserver.msde where I
> had recently submitted this, if anyone would have noted, but I still
> can't resolve the seemingly simple task of importing from one database
> to another within even the same instance of SQL Server Express. I
> would be so grateful if someone could help me:
> The specific example I was working on involved an attempt to run an
> update query to place data from a specific field in an older copy of an
> otherwise identical database into a more recent copy. Ultimately I
> just plugged the small data series in manually using side-by-side views
> because I could not overcome the syntax errors generated in the effort
> to refer to the "external" database.
> This is the setup in question:
> - An instance in the format of "MyComputerName\SQLExpress," and both an
> - "OldDatabase" and
> - "NewDatabase" attached and viewable from the Management Studio
> Express, with identical fields
> I tried a few different query techniques as suggested in historical
> posts, but to no avail. Here is such an example that I tried:
> In MSE I right clicked on the target table in question and chose:
> "Script table as," then
> "UPDATE to," then
> "New Query Editor Window"
> I removed all the fields except that in question and added this Where
> clause:
> UPDATE [NewDatabase].[dbo].[Table]
> SET [Field] = [OldDatabase].[dbo].[Table].[Field]
> WHERE [NewDatabase].[dbo].[Table].[PK] =
> [OldDatabase].[dbo].[Table].[PK]
> in which the primary key (an auto-increment integer) is identical
> between the new and old tables.
> This produces the error:
> Msg 4104, Level 16, State 1, Line 1
> The multi-part identifier "OldDatabase.dbo.Table.PK" could not be
> bound.
> The same error occurs with different techniques (joins, subqueries,
> etc.)
try this
USE NewDatabase
UPDATE [NewDatabase].[dbo].[Table]
SET [Field] = Old.[Field]
from [NewDatabase].[dbo].[Table] New inner join
[OldDatabase].[dbo].[Table] Old
on
New.[PK] =
Old.[PK]
Regards
Amish Shah|||Amish,
Thank you so much for your help. I set up two test databases,
"TestNew" and "TestOld" with parallel tables and test data. I found
that the syntax you proposed worked:
UPDATE [TestNew].[dbo].[tblProcedure]
SET [CPT] = TestOld.[CPT]
FROM [TestNew].[dbo].[tblProcedure] TestNew inner join
[TestOld].[dbo].[tblProcedure] TestOld
on
TestNew.[procedureID]=TestOld.[procedureID]
Whereas syntax such as this did not work:
UPDATE [TestNew].[dbo].[tblProcedure]
SET [CPT] = TestOld.[CPT]
FROM [TestNew].[dbo].[tblProcedure]
WHERE [Testnew].[dbo].[tblProcedure].procedureID
=[TestOld].[dbo].[tblProcedure].procedureID
The following error was generated:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "TestOld.dbo.tblProcedure.procedureID" could
not be bound.
SQL Server is relatively new to me, and the syntax/manipulations often
seem cryptic. May I ask you to help me understand the asterisked parts
of the construction:
FROM [TestNew].[dbo].[tblProcedure] *TestNew* inner join
[TestOld].[dbo].[tblProcedure] *TestOld*
on
TestNew.[procedureID]=TestOld.[procedureID]
Thanks,
John
amish wrote:
> John Hackert wrote:
>
> try this
> USE NewDatabase
> UPDATE [NewDatabase].[dbo].[Table]
> SET [Field] = Old.[Field]
> from [NewDatabase].[dbo].[Table] New inner join
> [OldDatabase].[dbo].[Table] Old
> on
> New.[PK] =
> Old.[PK]
>
> Regards
> Amish Shah|||On Nov 5, 2:26 am, "John Hackert" <hackertjo...@.yahoo.com> wrote:[vbcol=seagreen]
> Amish,
> Thank you so much for your help. I set up two test databases,
> "TestNew" and "TestOld" with parallel tables and test data. I found
> that the syntax you proposed worked:
> UPDATE [TestNew].[dbo].[tblProcedure]
> SET [CPT] = TestOld.[CPT]
> FROM [TestNew].[dbo].[tblProcedure] TestNew inner join
> [TestOld].[dbo].[tblProcedure] TestOld
> on
> TestNew.[procedureID]=TestOld.[procedureID]
> Whereas syntax such as this did not work:
> UPDATE [TestNew].[dbo].[tblProcedure]
> SET [CPT] = TestOld.[CPT]
> FROM [TestNew].[dbo].[tblProcedure]
> WHERE [Testnew].[dbo].[tblProcedure].procedureID
> =[TestOld].[dbo].[tblProcedure].procedureID
> The following error was generated:
> Msg 4104, Level 16, State 1, Line 1
> The multi-part identifier "TestOld.dbo.tblProcedure.procedureID" could
> not be bound.
> SQL Server is relatively new to me, and the syntax/manipulations often
> seem cryptic. May I ask you to help me understand the asterisked parts
> of the construction:
> FROM [TestNew].[dbo].[tblProcedure] *TestNew* inner join
> [TestOld].[dbo].[tblProcedure] *TestOld*
> on
> TestNew.[procedureID]=TestOld.[procedureID]
> Thanks,
> John
> amish wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
John
Asterisk part is just an alias for the table.
Once you specify alias for the table in form clause of the query you
can refer it in other part of query instead of table name.
You have to add second table also in form clause.
You should change your query to
UPDATE [TestNew].[dbo].[tblProcedure]
SET [CPT] = TestOld.[CPT]
FROM [TestNew].[dbo].[tblProcedure] testnew,
[TestOld].[dbo].[tblProcedure] testold
WHERE [testnew].procedureID
=TestOld.procedureID
Regards
Amish Shah|||Amish,
I've used aliases in other contexts, but always with the keyword "AS"
- So the "AS" is an optional part of the syntax?
I see now from your example that the failure of the previous queries
related to not specifying the second table in the from clause.
Once again I'm grateful for your help
John
amish wrote:
> On Nov 5, 2:26 am, "John Hackert" <hackertjo...@.yahoo.com> wrote:
> John
> Asterisk part is just an alias for the table.
> Once you specify alias for the table in form clause of the query you
> can refer it in other part of query instead of table name.
> You have to add second table also in form clause.
> You should change your query to
> UPDATE [TestNew].[dbo].[tblProcedure]
> SET [CPT] = TestOld.[CPT]
> FROM [TestNew].[dbo].[tblProcedure] testnew,
> [TestOld].[dbo].[tblProcedure] testold
> WHERE [testnew].procedureID
> =TestOld.procedureID
>
> Regards
> Amish Shah|||You're correct, the use of 'AS' when identifying an alias is indeed
optional.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"John Hackert" <hackertjohnb@.yahoo.com> wrote in message
news:1162861737.778956.88230@.b28g2000cwb.googlegroups.com...
> Amish,
> I've used aliases in other contexts, but always with the keyword "AS"
> - So the "AS" is an optional part of the syntax?
> I see now from your example that the failure of the previous queries
> related to not specifying the second table in the from clause.
> Once again I'm grateful for your help
> John
>
> amish wrote:
>sql

Wednesday, March 28, 2012

Importing within SQL Server Express without Data Transformation Services

I'm sorry to repost this from microsoft.public.sqlserver.msde where I
had recently submitted this, if anyone would have noted, but I still
can't resolve the seemingly simple task of importing from one database
to another within even the same instance of SQL Server Express. I
would be so grateful if someone could help me:
The specific example I was working on involved an attempt to run an
update query to place data from a specific field in an older copy of an
otherwise identical database into a more recent copy. Ultimately I
just plugged the small data series in manually using side-by-side views
because I could not overcome the syntax errors generated in the effort
to refer to the "external" database.
This is the setup in question:
- An instance in the format of "MyComputerName\SQLExpress," and both an
- "OldDatabase" and
- "NewDatabase" attached and viewable from the Management Studio
Express, with identical fields
I tried a few different query techniques as suggested in historical
posts, but to no avail. Here is such an example that I tried:
In MSE I right clicked on the target table in question and chose:
"Script table as," then
"UPDATE to," then
"New Query Editor Window"
I removed all the fields except that in question and added this Where
clause:
UPDATE [NewDatabase].[dbo].[Table]
SET [Field] = [OldDatabase].[dbo].[Table].[Field]
WHERE [NewDatabase].[dbo].[Table].[PK] = [OldDatabase].[dbo].[Table].[PK]
in which the primary key (an auto-increment integer) is identical
between the new and old tables.
This produces the error:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "OldDatabase.dbo.Table.PK" could not be
bound.
The same error occurs with different techniques (joins, subqueries,
etc.)John Hackert wrote:
> I'm sorry to repost this from microsoft.public.sqlserver.msde where I
> had recently submitted this, if anyone would have noted, but I still
> can't resolve the seemingly simple task of importing from one database
> to another within even the same instance of SQL Server Express. I
> would be so grateful if someone could help me:
> The specific example I was working on involved an attempt to run an
> update query to place data from a specific field in an older copy of an
> otherwise identical database into a more recent copy. Ultimately I
> just plugged the small data series in manually using side-by-side views
> because I could not overcome the syntax errors generated in the effort
> to refer to the "external" database.
> This is the setup in question:
> - An instance in the format of "MyComputerName\SQLExpress," and both an
> - "OldDatabase" and
> - "NewDatabase" attached and viewable from the Management Studio
> Express, with identical fields
> I tried a few different query techniques as suggested in historical
> posts, but to no avail. Here is such an example that I tried:
> In MSE I right clicked on the target table in question and chose:
> "Script table as," then
> "UPDATE to," then
> "New Query Editor Window"
> I removed all the fields except that in question and added this Where
> clause:
> UPDATE [NewDatabase].[dbo].[Table]
> SET [Field] = [OldDatabase].[dbo].[Table].[Field]
> WHERE [NewDatabase].[dbo].[Table].[PK] => [OldDatabase].[dbo].[Table].[PK]
> in which the primary key (an auto-increment integer) is identical
> between the new and old tables.
> This produces the error:
> Msg 4104, Level 16, State 1, Line 1
> The multi-part identifier "OldDatabase.dbo.Table.PK" could not be
> bound.
> The same error occurs with different techniques (joins, subqueries,
> etc.)
try this
USE NewDatabase
UPDATE [NewDatabase].[dbo].[Table]
SET [Field] = Old.[Field]
from [NewDatabase].[dbo].[Table] New inner join
[OldDatabase].[dbo].[Table] Old
on
New.[PK] =Old.[PK]
Regards
Amish Shah|||Amish,
Thank you so much for your help. I set up two test databases,
"TestNew" and "TestOld" with parallel tables and test data. I found
that the syntax you proposed worked:
UPDATE [TestNew].[dbo].[tblProcedure]
SET [CPT] = TestOld.[CPT]
FROM [TestNew].[dbo].[tblProcedure] TestNew inner join
[TestOld].[dbo].[tblProcedure] TestOld
on
TestNew.[procedureID]=TestOld.[procedureID]
Whereas syntax such as this did not work:
UPDATE [TestNew].[dbo].[tblProcedure]
SET [CPT] = TestOld.[CPT]
FROM [TestNew].[dbo].[tblProcedure]
WHERE [Testnew].[dbo].[tblProcedure].procedureID
=[TestOld].[dbo].[tblProcedure].procedureID
The following error was generated:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "TestOld.dbo.tblProcedure.procedureID" could
not be bound.
SQL Server is relatively new to me, and the syntax/manipulations often
seem cryptic. May I ask you to help me understand the asterisked parts
of the construction:
FROM [TestNew].[dbo].[tblProcedure] *TestNew* inner join
[TestOld].[dbo].[tblProcedure] *TestOld*
on
TestNew.[procedureID]=TestOld.[procedureID]
Thanks,
John
amish wrote:
> John Hackert wrote:
> > I'm sorry to repost this from microsoft.public.sqlserver.msde where I
> > had recently submitted this, if anyone would have noted, but I still
> > can't resolve the seemingly simple task of importing from one database
> > to another within even the same instance of SQL Server Express. I
> > would be so grateful if someone could help me:
> >
> > The specific example I was working on involved an attempt to run an
> > update query to place data from a specific field in an older copy of an
> > otherwise identical database into a more recent copy. Ultimately I
> > just plugged the small data series in manually using side-by-side views
> > because I could not overcome the syntax errors generated in the effort
> > to refer to the "external" database.
> >
> > This is the setup in question:
> > - An instance in the format of "MyComputerName\SQLExpress," and both an
> > - "OldDatabase" and
> > - "NewDatabase" attached and viewable from the Management Studio
> > Express, with identical fields
> >
> > I tried a few different query techniques as suggested in historical
> > posts, but to no avail. Here is such an example that I tried:
> >
> > In MSE I right clicked on the target table in question and chose:
> > "Script table as," then
> > "UPDATE to," then
> > "New Query Editor Window"
> >
> > I removed all the fields except that in question and added this Where
> > clause:
> > UPDATE [NewDatabase].[dbo].[Table]
> > SET [Field] = [OldDatabase].[dbo].[Table].[Field]
> > WHERE [NewDatabase].[dbo].[Table].[PK] => > [OldDatabase].[dbo].[Table].[PK]
> >
> > in which the primary key (an auto-increment integer) is identical
> > between the new and old tables.
> >
> > This produces the error:
> > Msg 4104, Level 16, State 1, Line 1
> > The multi-part identifier "OldDatabase.dbo.Table.PK" could not be
> > bound.
> >
> > The same error occurs with different techniques (joins, subqueries,
> > etc.)
> try this
> USE NewDatabase
> UPDATE [NewDatabase].[dbo].[Table]
> SET [Field] = Old.[Field]
> from [NewDatabase].[dbo].[Table] New inner join
> [OldDatabase].[dbo].[Table] Old
> on
> New.[PK] => Old.[PK]
>
> Regards
> Amish Shah|||On Nov 5, 2:26 am, "John Hackert" <hackertjo...@.yahoo.com> wrote:
> Amish,
> Thank you so much for your help. I set up two test databases,
> "TestNew" and "TestOld" with parallel tables and test data. I found
> that the syntax you proposed worked:
> UPDATE [TestNew].[dbo].[tblProcedure]
> SET [CPT] = TestOld.[CPT]
> FROM [TestNew].[dbo].[tblProcedure] TestNew inner join
> [TestOld].[dbo].[tblProcedure] TestOld
> on
> TestNew.[procedureID]=TestOld.[procedureID]
> Whereas syntax such as this did not work:
> UPDATE [TestNew].[dbo].[tblProcedure]
> SET [CPT] = TestOld.[CPT]
> FROM [TestNew].[dbo].[tblProcedure]
> WHERE [Testnew].[dbo].[tblProcedure].procedureID
> =[TestOld].[dbo].[tblProcedure].procedureID
> The following error was generated:
> Msg 4104, Level 16, State 1, Line 1
> The multi-part identifier "TestOld.dbo.tblProcedure.procedureID" could
> not be bound.
> SQL Server is relatively new to me, and the syntax/manipulations often
> seem cryptic. May I ask you to help me understand the asterisked parts
> of the construction:
> FROM [TestNew].[dbo].[tblProcedure] *TestNew* inner join
> [TestOld].[dbo].[tblProcedure] *TestOld*
> on
> TestNew.[procedureID]=TestOld.[procedureID]
> Thanks,
> John
> amish wrote:
> > John Hackert wrote:
> > > I'm sorry to repost this from microsoft.public.sqlserver.msde where I
> > > had recently submitted this, if anyone would have noted, but I still
> > > can't resolve the seemingly simple task of importing from one database
> > > to another within even the same instance of SQL Server Express. I
> > > would be so grateful if someone could help me:
> > > The specific example I was working on involved an attempt to run an
> > > update query to place data from a specific field in an older copy of an
> > > otherwise identical database into a more recent copy. Ultimately I
> > > just plugged the small data series in manually using side-by-side views
> > > because I could not overcome the syntax errors generated in the effort
> > > to refer to the "external" database.
> > > This is the setup in question:
> > > - An instance in the format of "MyComputerName\SQLExpress," and both an
> > > - "OldDatabase" and
> > > - "NewDatabase" attached and viewable from the Management Studio
> > > Express, with identical fields
> > > I tried a few different query techniques as suggested in historical
> > > posts, but to no avail. Here is such an example that I tried:
> > > In MSE I right clicked on the target table in question and chose:
> > > "Script table as," then
> > > "UPDATE to," then
> > > "New Query Editor Window"
> > > I removed all the fields except that in question and added this Where
> > > clause:
> > > UPDATE [NewDatabase].[dbo].[Table]
> > > SET [Field] = [OldDatabase].[dbo].[Table].[Field]
> > > WHERE [NewDatabase].[dbo].[Table].[PK] => > > [OldDatabase].[dbo].[Table].[PK]
> > > in which the primary key (an auto-increment integer) is identical
> > > between the new and old tables.
> > > This produces the error:
> > > Msg 4104, Level 16, State 1, Line 1
> > > The multi-part identifier "OldDatabase.dbo.Table.PK" could not be
> > > bound.
> > > The same error occurs with different techniques (joins, subqueries,
> > > etc.)
> > try this
> > USE NewDatabase
> > UPDATE [NewDatabase].[dbo].[Table]
> > SET [Field] = Old.[Field]
> > from [NewDatabase].[dbo].[Table] New inner join
> > [OldDatabase].[dbo].[Table] Old
> > on
> > New.[PK] => > Old.[PK]
> > Regards
> > Amish Shah
John
Asterisk part is just an alias for the table.
Once you specify alias for the table in form clause of the query you
can refer it in other part of query instead of table name.
You have to add second table also in form clause.
You should change your query to
UPDATE [TestNew].[dbo].[tblProcedure]
SET [CPT] = TestOld.[CPT]
FROM [TestNew].[dbo].[tblProcedure] testnew,
[TestOld].[dbo].[tblProcedure] testold
WHERE [testnew].procedureID
=TestOld.procedureID
Regards
Amish Shah|||Amish,
I've used aliases in other contexts, but always with the keyword "AS"
- So the "AS" is an optional part of the syntax?
I see now from your example that the failure of the previous queries
related to not specifying the second table in the from clause.
Once again I'm grateful for your help
John
amish wrote:
> On Nov 5, 2:26 am, "John Hackert" <hackertjo...@.yahoo.com> wrote:
> > Amish,
> >
> > Thank you so much for your help. I set up two test databases,
> > "TestNew" and "TestOld" with parallel tables and test data. I found
> > that the syntax you proposed worked:
> >
> > UPDATE [TestNew].[dbo].[tblProcedure]
> > SET [CPT] = TestOld.[CPT]
> > FROM [TestNew].[dbo].[tblProcedure] TestNew inner join
> > [TestOld].[dbo].[tblProcedure] TestOld
> > on
> > TestNew.[procedureID]=TestOld.[procedureID]
> >
> > Whereas syntax such as this did not work:
> >
> > UPDATE [TestNew].[dbo].[tblProcedure]
> > SET [CPT] = TestOld.[CPT]
> > FROM [TestNew].[dbo].[tblProcedure]
> > WHERE [Testnew].[dbo].[tblProcedure].procedureID
> > =[TestOld].[dbo].[tblProcedure].procedureID
> >
> > The following error was generated:
> > Msg 4104, Level 16, State 1, Line 1
> > The multi-part identifier "TestOld.dbo.tblProcedure.procedureID" could
> > not be bound.
> >
> > SQL Server is relatively new to me, and the syntax/manipulations often
> > seem cryptic. May I ask you to help me understand the asterisked parts
> > of the construction:
> >
> > FROM [TestNew].[dbo].[tblProcedure] *TestNew* inner join
> > [TestOld].[dbo].[tblProcedure] *TestOld*
> > on
> > TestNew.[procedureID]=TestOld.[procedureID]
> >
> > Thanks,
> >
> > John
> >
> > amish wrote:
> > > John Hackert wrote:
> >
> > > > I'm sorry to repost this from microsoft.public.sqlserver.msde where I
> > > > had recently submitted this, if anyone would have noted, but I still
> > > > can't resolve the seemingly simple task of importing from one database
> > > > to another within even the same instance of SQL Server Express. I
> > > > would be so grateful if someone could help me:
> >
> > > > The specific example I was working on involved an attempt to run an
> > > > update query to place data from a specific field in an older copy of an
> > > > otherwise identical database into a more recent copy. Ultimately I
> > > > just plugged the small data series in manually using side-by-side views
> > > > because I could not overcome the syntax errors generated in the effort
> > > > to refer to the "external" database.
> >
> > > > This is the setup in question:
> > > > - An instance in the format of "MyComputerName\SQLExpress," and both an
> > > > - "OldDatabase" and
> > > > - "NewDatabase" attached and viewable from the Management Studio
> > > > Express, with identical fields
> >
> > > > I tried a few different query techniques as suggested in historical
> > > > posts, but to no avail. Here is such an example that I tried:
> >
> > > > In MSE I right clicked on the target table in question and chose:
> > > > "Script table as," then
> > > > "UPDATE to," then
> > > > "New Query Editor Window"
> >
> > > > I removed all the fields except that in question and added this Where
> > > > clause:
> > > > UPDATE [NewDatabase].[dbo].[Table]
> > > > SET [Field] = [OldDatabase].[dbo].[Table].[Field]
> > > > WHERE [NewDatabase].[dbo].[Table].[PK] => > > > [OldDatabase].[dbo].[Table].[PK]
> >
> > > > in which the primary key (an auto-increment integer) is identical
> > > > between the new and old tables.
> >
> > > > This produces the error:
> > > > Msg 4104, Level 16, State 1, Line 1
> > > > The multi-part identifier "OldDatabase.dbo.Table.PK" could not be
> > > > bound.
> >
> > > > The same error occurs with different techniques (joins, subqueries,
> > > > etc.)
> >
> > > try this
> >
> > > USE NewDatabase
> >
> > > UPDATE [NewDatabase].[dbo].[Table]
> > > SET [Field] = Old.[Field]
> > > from [NewDatabase].[dbo].[Table] New inner join
> > > [OldDatabase].[dbo].[Table] Old
> > > on
> > > New.[PK] => > > Old.[PK]
> >
> > > Regards
> > > Amish Shah
> John
> Asterisk part is just an alias for the table.
> Once you specify alias for the table in form clause of the query you
> can refer it in other part of query instead of table name.
> You have to add second table also in form clause.
> You should change your query to
> UPDATE [TestNew].[dbo].[tblProcedure]
> SET [CPT] = TestOld.[CPT]
> FROM [TestNew].[dbo].[tblProcedure] testnew,
> [TestOld].[dbo].[tblProcedure] testold
> WHERE [testnew].procedureID
> =TestOld.procedureID
>
> Regards
> Amish Shah|||You're correct, the use of 'AS' when identifying an alias is indeed
optional.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"John Hackert" <hackertjohnb@.yahoo.com> wrote in message
news:1162861737.778956.88230@.b28g2000cwb.googlegroups.com...
> Amish,
> I've used aliases in other contexts, but always with the keyword "AS"
> - So the "AS" is an optional part of the syntax?
> I see now from your example that the failure of the previous queries
> related to not specifying the second table in the from clause.
> Once again I'm grateful for your help
> John
>
> amish wrote:
>> On Nov 5, 2:26 am, "John Hackert" <hackertjo...@.yahoo.com> wrote:
>> > Amish,
>> >
>> > Thank you so much for your help. I set up two test databases,
>> > "TestNew" and "TestOld" with parallel tables and test data. I found
>> > that the syntax you proposed worked:
>> >
>> > UPDATE [TestNew].[dbo].[tblProcedure]
>> > SET [CPT] = TestOld.[CPT]
>> > FROM [TestNew].[dbo].[tblProcedure] TestNew inner join
>> > [TestOld].[dbo].[tblProcedure] TestOld
>> > on
>> > TestNew.[procedureID]=TestOld.[procedureID]
>> >
>> > Whereas syntax such as this did not work:
>> >
>> > UPDATE [TestNew].[dbo].[tblProcedure]
>> > SET [CPT] = TestOld.[CPT]
>> > FROM [TestNew].[dbo].[tblProcedure]
>> > WHERE [Testnew].[dbo].[tblProcedure].procedureID
>> > =[TestOld].[dbo].[tblProcedure].procedureID
>> >
>> > The following error was generated:
>> > Msg 4104, Level 16, State 1, Line 1
>> > The multi-part identifier "TestOld.dbo.tblProcedure.procedureID" could
>> > not be bound.
>> >
>> > SQL Server is relatively new to me, and the syntax/manipulations often
>> > seem cryptic. May I ask you to help me understand the asterisked parts
>> > of the construction:
>> >
>> > FROM [TestNew].[dbo].[tblProcedure] *TestNew* inner join
>> > [TestOld].[dbo].[tblProcedure] *TestOld*
>> > on
>> > TestNew.[procedureID]=TestOld.[procedureID]
>> >
>> > Thanks,
>> >
>> > John
>> >
>> > amish wrote:
>> > > John Hackert wrote:
>> >
>> > > > I'm sorry to repost this from microsoft.public.sqlserver.msde where
>> > > > I
>> > > > had recently submitted this, if anyone would have noted, but I
>> > > > still
>> > > > can't resolve the seemingly simple task of importing from one
>> > > > database
>> > > > to another within even the same instance of SQL Server Express. I
>> > > > would be so grateful if someone could help me:
>> >
>> > > > The specific example I was working on involved an attempt to run an
>> > > > update query to place data from a specific field in an older copy
>> > > > of an
>> > > > otherwise identical database into a more recent copy. Ultimately I
>> > > > just plugged the small data series in manually using side-by-side
>> > > > views
>> > > > because I could not overcome the syntax errors generated in the
>> > > > effort
>> > > > to refer to the "external" database.
>> >
>> > > > This is the setup in question:
>> > > > - An instance in the format of "MyComputerName\SQLExpress," and
>> > > > both an
>> > > > - "OldDatabase" and
>> > > > - "NewDatabase" attached and viewable from the Management Studio
>> > > > Express, with identical fields
>> >
>> > > > I tried a few different query techniques as suggested in historical
>> > > > posts, but to no avail. Here is such an example that I tried:
>> >
>> > > > In MSE I right clicked on the target table in question and chose:
>> > > > "Script table as," then
>> > > > "UPDATE to," then
>> > > > "New Query Editor Window"
>> >
>> > > > I removed all the fields except that in question and added this
>> > > > Where
>> > > > clause:
>> > > > UPDATE [NewDatabase].[dbo].[Table]
>> > > > SET [Field] = [OldDatabase].[dbo].[Table].[Field]
>> > > > WHERE [NewDatabase].[dbo].[Table].[PK] =>> > > > [OldDatabase].[dbo].[Table].[PK]
>> >
>> > > > in which the primary key (an auto-increment integer) is identical
>> > > > between the new and old tables.
>> >
>> > > > This produces the error:
>> > > > Msg 4104, Level 16, State 1, Line 1
>> > > > The multi-part identifier "OldDatabase.dbo.Table.PK" could not be
>> > > > bound.
>> >
>> > > > The same error occurs with different techniques (joins, subqueries,
>> > > > etc.)
>> >
>> > > try this
>> >
>> > > USE NewDatabase
>> >
>> > > UPDATE [NewDatabase].[dbo].[Table]
>> > > SET [Field] = Old.[Field]
>> > > from [NewDatabase].[dbo].[Table] New inner join
>> > > [OldDatabase].[dbo].[Table] Old
>> > > on
>> > > New.[PK] =>> > > Old.[PK]
>> >
>> > > Regards
>> > > Amish Shah
>> John
>> Asterisk part is just an alias for the table.
>> Once you specify alias for the table in form clause of the query you
>> can refer it in other part of query instead of table name.
>> You have to add second table also in form clause.
>> You should change your query to
>> UPDATE [TestNew].[dbo].[tblProcedure]
>> SET [CPT] = TestOld.[CPT]
>> FROM [TestNew].[dbo].[tblProcedure] testnew,
>> [TestOld].[dbo].[tblProcedure] testold
>> WHERE [testnew].procedureID
>> =TestOld.procedureID
>>
>> Regards
>> Amish Shah
>

Monday, March 12, 2012

Importing Dates using derived column transformation

Hi All

I've got a flatfile which contains a column SALEDATE with this data

20070802

''

20070804

''

20070805

20070901

I've got a table with a column SALEDATE datetime.

I use a derived column with this expression

SALEDATE <add as new column> (DT_DBDATE)(SUBSTRING(SaleDate,1,4) + "-" + SUBSTRING(SaleDate,5,2) + "-" + SUBSTRING(SaleDate,7,2)) database date [DT_DBDATE]

This expression fails whenever it comes across a blank field.

How can evaluate my expression to insert a null if it comes across a blank field?

Thank You

SaleDate == "" || ISNULL(SaleDate) ? NULL(DT_DBDATE) : (DT_DBDATE)(SUBSTRING(SaleDate,1,4) + "-" + SUBSTRING(SaleDate,5,2) + "-" + SUBSTRING(SaleDate,7,2))|||

Phil thank you for replying.

There are some records that don't even have anything for SALEDATE

20070801

''

20070802

20070901

20070902

The package fails at the row 627 when the empty field comes across.

How should i modify the expression?

|||The package doesn't fail because of the expression -- check your logs -- it likely fails because of an invalid row (missing delimiter?). SSIS expects the metadata to be the same across the whole file.|||You're right

627 is the last row in the flatfile. it fails on that row.

I checked the file the last record contains data 20070831

We get flatfiles in a RaggedRight format.

There are no text qualifiers and Header row delimiter is set to {CR}{LF}

Here is the error log:

[Transform Columns [1]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Transform Columns" (1)" failed because error code 0xC0049063 occurred, and the error row disposition on "output column "SALEDATE" (4334)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.

[DTS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Transform Columns" (1) 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. There may be error messages posted before this with more information about the failure.

[DTS.Pipeline] Error: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0209029. There may be error messages posted before this with more information on why the thread has exited.

I'll try different options and see what i can come up with.

Thanks for you help.

|||Sounds like a short row and it is missing its row terminator|||

I can't seem to get any rows into a table.

Still getting the same error.

I tried changing the (DT_DBDATE) to (DT_STR) but it turns red.

OnSaleDate == "" || ISNULL(OnSaleDate) ? NULL(DT_DBDATE) : (DT_DBDATE)(SUBSTRING(OnSaleDate,1,4) + "/" + SUBSTRING(OnSaleDate,5,2) + "/" + SUBSTRING(OnSaleDate,7,2))

Did anyone come across this problem?

|||Please share all of your errors.

There's nothing wrong with that expression, syntactically.|||

I got the same problem and I fixed cleaning garbage data in the column that caused the error. Ex I expected a number and I got a "-" character.

Good luck

|||

Here is how i solved it.

LEN(TRIM(OnSaleDate)) > 1 ? (DT_DBDATE)(SUBSTRING(OnSaleDate,1,4) + "-" + SUBSTRING(OnSaleDate,5,2) + "-" + SUBSTRING(OnSaleDate,7,2)) : NULL(DT_DATE)

Importing Dates using derived column transformation

Hi All

I've got a flatfile which contains a column SALEDATE with this data

20070802

''

20070804

''

20070805

20070901

I've got a table with a column SALEDATE datetime.

I use a derived column with this expression

SALEDATE <add as new column> (DT_DBDATE)(SUBSTRING(SaleDate,1,4) + "-" + SUBSTRING(SaleDate,5,2) + "-" + SUBSTRING(SaleDate,7,2)) database date [DT_DBDATE]

This expression fails whenever it comes across a blank field.

How can evaluate my expression to insert a null if it comes across a blank field?

Thank You

SaleDate == "" || ISNULL(SaleDate) ? NULL(DT_DBDATE) : (DT_DBDATE)(SUBSTRING(SaleDate,1,4) + "-" + SUBSTRING(SaleDate,5,2) + "-" + SUBSTRING(SaleDate,7,2))|||

Phil thank you for replying.

There are some records that don't even have anything for SALEDATE

20070801

''

20070802

20070901

20070902

The package fails at the row 627 when the empty field comes across.

How should i modify the expression?

|||The package doesn't fail because of the expression -- check your logs -- it likely fails because of an invalid row (missing delimiter?). SSIS expects the metadata to be the same across the whole file.|||You're right

627 is the last row in the flatfile. it fails on that row.

I checked the file the last record contains data 20070831

We get flatfiles in a RaggedRight format.

There are no text qualifiers and Header row delimiter is set to {CR}{LF}

Here is the error log:

[Transform Columns [1]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Transform Columns" (1)" failed because error code 0xC0049063 occurred, and the error row disposition on "output column "SALEDATE" (4334)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.

[DTS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Transform Columns" (1) 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. There may be error messages posted before this with more information about the failure.

[DTS.Pipeline] Error: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0209029. There may be error messages posted before this with more information on why the thread has exited.

I'll try different options and see what i can come up with.

Thanks for you help.

|||Sounds like a short row and it is missing its row terminator|||

I can't seem to get any rows into a table.

Still getting the same error.

I tried changing the (DT_DBDATE) to (DT_STR) but it turns red.

OnSaleDate == "" || ISNULL(OnSaleDate) ? NULL(DT_DBDATE) : (DT_DBDATE)(SUBSTRING(OnSaleDate,1,4) + "/" + SUBSTRING(OnSaleDate,5,2) + "/" + SUBSTRING(OnSaleDate,7,2))

Did anyone come across this problem?

|||Please share all of your errors.

There's nothing wrong with that expression, syntactically.|||

I got the same problem and I fixed cleaning garbage data in the column that caused the error. Ex I expected a number and I got a "-" character.

Good luck

|||

Here is how i solved it.

LEN(TRIM(OnSaleDate)) > 1 ? (DT_DBDATE)(SUBSTRING(OnSaleDate,1,4) + "-" + SUBSTRING(OnSaleDate,5,2) + "-" + SUBSTRING(OnSaleDate,7,2)) : NULL(DT_DATE)

Importing Dates using derived column transformation

Hi All

I've got a flatfile which contains a column SALEDATE with this data

20070802

''

20070804

''

20070805

20070901

I've got a table with a column SALEDATE datetime.

I use a derived column with this expression

SALEDATE <add as new column> (DT_DBDATE)(SUBSTRING(SaleDate,1,4) + "-" + SUBSTRING(SaleDate,5,2) + "-" + SUBSTRING(SaleDate,7,2)) database date [DT_DBDATE]

This expression fails whenever it comes across a blank field.

How can evaluate my expression to insert a null if it comes across a blank field?

Thank You

SaleDate == "" || ISNULL(SaleDate) ? NULL(DT_DBDATE) : (DT_DBDATE)(SUBSTRING(SaleDate,1,4) + "-" + SUBSTRING(SaleDate,5,2) + "-" + SUBSTRING(SaleDate,7,2))|||

Phil thank you for replying.

There are some records that don't even have anything for SALEDATE

20070801

''

20070802

20070901

20070902

The package fails at the row 627 when the empty field comes across.

How should i modify the expression?

|||The package doesn't fail because of the expression -- check your logs -- it likely fails because of an invalid row (missing delimiter?). SSIS expects the metadata to be the same across the whole file.|||You're right

627 is the last row in the flatfile. it fails on that row.

I checked the file the last record contains data 20070831

We get flatfiles in a RaggedRight format.

There are no text qualifiers and Header row delimiter is set to {CR}{LF}

Here is the error log:

[Transform Columns [1]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Transform Columns" (1)" failed because error code 0xC0049063 occurred, and the error row disposition on "output column "SALEDATE" (4334)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.

[DTS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Transform Columns" (1) 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. There may be error messages posted before this with more information about the failure.

[DTS.Pipeline] Error: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0209029. There may be error messages posted before this with more information on why the thread has exited.

I'll try different options and see what i can come up with.

Thanks for you help.

|||Sounds like a short row and it is missing its row terminator|||

I can't seem to get any rows into a table.

Still getting the same error.

I tried changing the (DT_DBDATE) to (DT_STR) but it turns red.

OnSaleDate == "" || ISNULL(OnSaleDate) ? NULL(DT_DBDATE) : (DT_DBDATE)(SUBSTRING(OnSaleDate,1,4) + "/" + SUBSTRING(OnSaleDate,5,2) + "/" + SUBSTRING(OnSaleDate,7,2))

Did anyone come across this problem?

|||Please share all of your errors.

There's nothing wrong with that expression, syntactically.|||

I got the same problem and I fixed cleaning garbage data in the column that caused the error. Ex I expected a number and I got a "-" character.

Good luck

|||

Here is how i solved it.

LEN(TRIM(OnSaleDate)) > 1 ? (DT_DBDATE)(SUBSTRING(OnSaleDate,1,4) + "-" + SUBSTRING(OnSaleDate,5,2) + "-" + SUBSTRING(OnSaleDate,7,2)) : NULL(DT_DATE)

Importing Dates using derived column transformation

Hi All

I've got a flatfile which contains a column SALEDATE with this data

20070802

''

20070804

''

20070805

20070901

I've got a table with a column SALEDATE datetime.

I use a derived column with this expression

SALEDATE <add as new column> (DT_DBDATE)(SUBSTRING(SaleDate,1,4) + "-" + SUBSTRING(SaleDate,5,2) + "-" + SUBSTRING(SaleDate,7,2)) database date [DT_DBDATE]

This expression fails whenever it comes across a blank field.

How can evaluate my expression to insert a null if it comes across a blank field?

Thank You

SaleDate == "" || ISNULL(SaleDate) ? NULL(DT_DBDATE) : (DT_DBDATE)(SUBSTRING(SaleDate,1,4) + "-" + SUBSTRING(SaleDate,5,2) + "-" + SUBSTRING(SaleDate,7,2))|||

Phil thank you for replying.

There are some records that don't even have anything for SALEDATE

20070801

''

20070802

20070901

20070902

The package fails at the row 627 when the empty field comes across.

How should i modify the expression?

|||The package doesn't fail because of the expression -- check your logs -- it likely fails because of an invalid row (missing delimiter?). SSIS expects the metadata to be the same across the whole file.|||You're right

627 is the last row in the flatfile. it fails on that row.

I checked the file the last record contains data 20070831

We get flatfiles in a RaggedRight format.

There are no text qualifiers and Header row delimiter is set to {CR}{LF}

Here is the error log:

[Transform Columns [1]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Transform Columns" (1)" failed because error code 0xC0049063 occurred, and the error row disposition on "output column "SALEDATE" (4334)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.

[DTS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Transform Columns" (1) 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. There may be error messages posted before this with more information about the failure.

[DTS.Pipeline] Error: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0209029. There may be error messages posted before this with more information on why the thread has exited.

I'll try different options and see what i can come up with.

Thanks for you help.

|||Sounds like a short row and it is missing its row terminator|||

I can't seem to get any rows into a table.

Still getting the same error.

I tried changing the (DT_DBDATE) to (DT_STR) but it turns red.

OnSaleDate == "" || ISNULL(OnSaleDate) ? NULL(DT_DBDATE) : (DT_DBDATE)(SUBSTRING(OnSaleDate,1,4) + "/" + SUBSTRING(OnSaleDate,5,2) + "/" + SUBSTRING(OnSaleDate,7,2))

Did anyone come across this problem?

|||Please share all of your errors.

There's nothing wrong with that expression, syntactically.|||

I got the same problem and I fixed cleaning garbage data in the column that caused the error. Ex I expected a number and I got a "-" character.

Good luck

|||

Here is how i solved it.

LEN(TRIM(OnSaleDate)) > 1 ? (DT_DBDATE)(SUBSTRING(OnSaleDate,1,4) + "-" + SUBSTRING(OnSaleDate,5,2) + "-" + SUBSTRING(OnSaleDate,7,2)) : NULL(DT_DATE)