how to treat truncations as a warning

I'm using SSIS to migrate data from one system to another. This is a usual extract, transform, cleanse and load type task.

The error handling is critical to get right. E.g. truncation of data on one column should stop that row being loaded but for other columns I might be happy to carry on loading the row but record a warning.

I'm finding the error disposition a bit limiting. I really feel the need for an 'Issue Warning' disposition which will act the same way as 'Ignore Error' in that the row continues being processed but will in addition copy a row to a warning output so that I can write a message to a log file for someone to manually investigate and correct that item of data post the conversion. Alternatively it would be useful to specify a severity (at a column level) when redirecting error output. This way I can put logic into a downstream component which would treat the error row differently depending on the severity of the error.

Am I missing a trick?

There's no built-in switch for enabling this behavior, but you can accomplish it with creative use of error redirection. I use error redirection or a conditional split to identify rows that either cause warnings or errors, flag them appropriately, then send the errors to a logging table. I send the warnings to a multicast that outputs the rows to both a logging table and to a Union All to put them back into the main flow.|||this will work for me, though it's a shame there isn't a built-in feature...|||

Nick Corrie wrote:

this will work for me, though it's a shame there isn't a built-in feature...

Nick,

That could be a good suggestion to make; you can post it at the connect site: http://connect.microsoft.com/VisualStudio/Feedback

How to translate DTSStepScriptResult_DontExecuteTask into SSIS?

Hi everyone,

We're struggling ourselves with this and we are stuck.

Which is the equivalent for a SSIS in a Script Task component on CONTROL FLOW layer?

Main = DTSStepScriptResult_ExecuteTask

Thanks for your input and regards,

Set the script result to a variable and then use that variable in a precedence constraint on the Execute XXX Task.|||

To try and expand on Phil's reply. The constant you mention does not exist, because you no longer have workflow scripts as you did in DTS. What you do have is a much richer set of precedence constraint functionality. As well as having the normal succes and failure stype stuff, you can also set an expression on the constraint. The expression uses a new syntax, which is quite simple, but maybe you could replace your script logic with an expression. Your logic may be too complicated for this, so you would have to use an Script Task and from that your result in variable. The variable can easily be referenced in an expressionon the constraint.

I would avoid any ActiveX Script in SSIS, use the new features, including the Script Task if required. The support for ActiveX is not good, things like error information are very poor now.

How to tranfer excel data to sqlserver2000 ?

In our project we r having a task such that to convert the excel data to sqlserver2000 . what is the procedure ? (Bulk amount of data )

Try the thread below for code to create a linked server with Excel. Hope this helps.
http://forums.asp.net/926047/ShowPost.aspx|||If you want to convert excel data permanently into SQL server
Right Click database -> All Task -> Import data ...-> Choose data source to be Excel, specify file path, choose destination, new table name .. map columns if necessary... just follow the wizard.
Happy programming!

How to tell if a table is being used.

Currently I've been assigned the glorious task of cleaning up our database. I've gone ahead and marked a sizable list of tables that I don't believe are accessed anymore. Now, I need to find out whether or not they are being accessed. I've ran sp_depends on all of them to find out what stored procedures/triggers may be accessing them, and the plate is clear. But, there are many individual programs, reports, etc... running against this database, so I'm going to need more proof than that. My boss suggested some sort of SELECT trigger, but that doesn't exist (to what I've found) for SQL Server 2000.

So, does anyone know how to determine if a table is being accessed or not (other than manually looking through thousands of lines of code)?

Thanks for any help you can give me.If you happen to have a test system, go ahead and rename the tables, and do a round of testing. Good luck.|||Originally posted by MCrowley
If you happen to have a test system, go ahead and rename the tables, and do a round of testing. Good luck.
I wish it were that simple. There is no test environment that tests all the applications/reports/etc... that are accessing this database. And, if I rename the tables, I've been told that the stored procedures will continue to work properly, while programs executing miscellaneous queries will fail. My initial idea has been to create copies of all the tables that I want to delete (named tablename_DELETE) and drop the original tables. Then if "shit hits the fan", I can rename the backup copy table back to its original form.

This still puts me in a situation where I have a lot of uncertainty about what I will be affecting. That's why I wanted to find some sort of an access log, or SELECT triger, or something I could use to see if these tables were being accessed.|||You can periodically look into syscacheobjects table and see if helps.|||Originally posted by rdjabarov
You can periodically look into syscacheobjects table and see if helps.
Good idea. I'm formulating some sort of a job to check this now, but I really curious to know what happens on instances where a table is accessed sparingly. In this case, would an entry exist in the syscacheobjects?|||What about using SQL Profiler and running a trace?|||Also you could use Profiler for saving tables activity (only selects, inserts, deletes and updates) and analyzing.|||Originally posted by Brett Kaiser
What about using SQL Profiler and running a trace?
You are reading my mind... ;)|||The profiler idea is a good one. I'm going to try that out.

Thanks for the help!|||My only qualm about Profiler is that it may not be able to pick up views an/or procedures that access this table. I know sysdepends did not show anything, but how accurate is that?|||I sometimes log procedure execution by creating the following table:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ProcedureLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ProcedureLog]
GO
CREATE TABLE [dbo].[ProcedureLog] (
[ProcessName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ProcessTime] [datetime] NULL ,
[Application] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CurrentUser] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SystemUser] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ProcedureLog] WITH NOCHECK ADD
CONSTRAINT [DF_ProcedureLog_ProcessTime] DEFAULT (getdate()) FOR [ProcessTime]
GO
setuser
GO
EXEC sp_bindefault N'[dbo].[DF_SYS_APPNAME]', N'[ProcedureLog].[Application]'
GO
EXEC sp_bindefault N'[dbo].[DF_SYS_USERNAME]', N'[ProcedureLog].[CurrentUser]'
GO
EXEC sp_bindefault N'[dbo].[DF_STRING_EMPTY]', N'[ProcedureLog].[Notes]'
GO
EXEC sp_bindefault N'[dbo].[DF_STRING_EMPTY]', N'[ProcedureLog].[ProcessName]'
GO
EXEC sp_bindefault N'[dbo].[DF_SYS_SUSERSNAME]', N'[ProcedureLog].[SystemUser]'
GO
setuser
GO

...and then putting this code at the start of each procedure I want to monitor:

--Record the event
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ProcedureLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
insert into dbo.ProcedureLog (ProcessName) values ('YourProcedureName')

This monitors who, what, and when a procedure is called.

You could put the same code in a trigger on a table.

Actually, I also include a notes field where I pass the values of all the parameters submitted to the procedure, but this may not be necessary for what you want to do.|||Yeah, except as the poster said, - there are no dependent ptocedures found for the tables that he wants to research the usage of.

You could put the same code in a trigger on a table.

Have they come up with a trigger for SELECT already? Man, the technology is just leaping into the future, leaving us behind every day :)|||Old technology, actually.

Just because a relationship doesn't show up in sysdepends doesn't mean it doesn't exist. Dynamic dependencies are not documented in sysdepends.

And as far as searching for references, maybe you should just script out your database and application code and do string searches for the suspect objects.|||Originally posted by blindman
Old technology, actually.

Just because a relationship doesn't show up in sysdepends doesn't mean it doesn't exist. Dynamic dependencies are not documented in sysdepends.

And as far as searching for references, maybe you should just script out your database and application code and do string searches for the suspect objects.

Nah...he's worried about applcation connecting to the server...

There are no TRIGGERS for SELECT btw

bol

{ [DELETE] [,] [INSERT] [,] [UPDATE] }

Are keywords that specify which data modification statements, when attempted against this table or view, activate the trigger. At least one option must be specified. Any combination of these in any order is allowed in the trigger definition. If more than one option is specified, separate the options with commas.

For INSTEAD OF triggers, the DELETE option is not allowed on tables that have a referential relationship specifying a cascade action ON DELETE. Similarly, the UPDATE option is not allowed on tables that have a referential relationship specifying a cascade action ON UPDATE.|||Thanks, Brett, that's what I thought :)|||Originally posted by Brett Kaiser
Nah...he's worried about applcation connecting to the server...

There are no TRIGGERS for SELECT btw

bol
Yes, I was more worried about external programs running adhoc queries than I was about stored procedures accessing the tables. I felt reasonably confident with the sp_depends, and I even searched the syscomments system table for references to the tables in quetion in the text field. So, basically a SELECT trigger would be an ideal option, but the profiler is serving its purpose for now. I'm going to give it a week, and if I haven't seen any activity by then, I'm smoking the tables.

Thanks again, for all the help

How to tell how much memory is actually needed by my SQL Server 2000?

The easiest way of doing it is to go onto your server,
doing a CTR ALT DEL, bringing up task manager, then
looking for SQL Server under the processors list.
Peter
"I'm just preparing my impromptu remarks."
Winston Churchill

>--Original Message--
>I've been doing a lot of reading on this and my head is
starting to
>hurt! It seems to be quite a feat to work out how much
memory is
>actually being used by our server.
>I'm running W2K advanced server with SQL 2000 EE, 8GB of
RAM, a min of
>4GB and a max of 6GB is assigned to SQL server.
>I'm trying to work out whether we've assigned enough or
too
>much/little memory to SQL server. My first thought was to
let SQL
>dymanically manage its own memory and see how much it
uses, of course
>when AWE (/3GB /PAE) is enabled it will just use all that
is
>available.
>In perfmon "target server memory" = 6.1GB, "total server
memory" =
>6.1GB, "total pages" = 768000 ( x 8KB = 6.1GB).
>My second thought was to use "total pages" - the
average "free pages"
>= average mem used, therefore giving me the average
amount of memory
>used by SQL. I found out that SQL uses a min of 4GB (the
min we
>assigned) and the max of all the memory, 6GB.
>Is there an easier way of finding out how much memory is
actually used
>in this situation or is going by the above average the
best way?
>What i'm unsure about is if, for example, we made 1TB of
RAM
>available, will SQL just use all memory assigned to it
until it has
>the whole DB in memory? If not, what's the cut off? Or is
the fact
>that the memory usage of our instance of sql peaks at
using all the
>memory an indication that we do not have enough available?
>Any help would be greatly apprechiated.
>Thanks.
>.
>
I don't know if that method will work.
We are running SQL 2k EE on Win2k AS. We are using /3GB /PAE and SQL Server
is configured to use 6144 MB.
Task manager reports that sqlservr.exe is using 115,524K on the process tab
however on the performance tab the physical memory is reported as
Total 7863624
Available 1184100
Keith
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:15d501c4bc26$57a92ae0$a601280a@.phx.gbl...[vbcol=seagreen]
> The easiest way of doing it is to go onto your server,
> doing a CTR ALT DEL, bringing up task manager, then
> looking for SQL Server under the processors list.
> Peter
> "I'm just preparing my impromptu remarks."
> Winston Churchill
>
> starting to
> memory is
> RAM, a min of
> too
> let SQL
> uses, of course
> is
> memory" =
> average "free pages"
> amount of memory
> min we
> actually used
> best way?
> RAM
> until it has
> the fact
> using all the
|||Keith,
I think you have a point there.
However Task Manager will always show the amount of memory
that has been allocated to a process, and is very accurate
(as far as I know)
I suppose the question(s) is more of
1. How much memory has been allocated
2. How much of that memory is in use
3. How much more memory can be accessed
Anyway as it piked my interest I dug out my copy of
Performance Monitoring with SQL Server 2000 and set up the
counters Total Server Memory and Target Server Memory.
As you know it will show percentage of the actual memory
useage on the server.
Currently our server is set to 48% of all server memory,
which translates roughly to the amount of memory shown as
used in Task Manager, though I will freely admit its not
completely accurate.
I think the answer then is there is no way of totally been
able to predict the amount of memory being used, as both
things give different figures.
So I think I will change my mind and go down your route
using the profiler if nothing more than the fact you take
the findings at a regular time and do something with them.
Anyway thanks for that.
Peter
"Action speaks louder than words but not nearly as often"
Mark Twain

>--Original Message--
>I don't know if that method will work.
>We are running SQL 2k EE on Win2k AS. We are
using /3GB /PAE and SQL Server
>is configured to use 6144 MB.
>Task manager reports that sqlservr.exe is using 115,524K
on the process tab
>however on the performance tab the physical memory is
reported as
>Total 7863624
>Available 1184100
>--
>Keith
>
>"Peter The Spate" <anonymous@.discussions.microsoft.com>
wrote in message[vbcol=seagreen]
>news:15d501c4bc26$57a92ae0$a601280a@.phx.gbl...
of[vbcol=seagreen]
to[vbcol=seagreen]
that[vbcol=seagreen]
server[vbcol=seagreen]
(the[vbcol=seagreen]
is[vbcol=seagreen]
of[vbcol=seagreen]
is[vbcol=seagreen]
available?
>.
>
|||Spot on. Windows counters such as these in task manager and process
and working set in perfmon cannot monitor memory that is enabled by
AWE, 4GB+. I wish it were that easy.
In my situation, because the min and max memory is set to 4GB and 6GB
none of this can be monitored by the usual tools, it's all AWE memory.
What i need to know is;
-Should i be concerned that SQL memory usage peaks at using all memory
assigned to it?
-Should it peak at 100% mem usage, is this normal?
-If i assigned, for example, 40GB of memory would it just keep using
memory until it had the hole db in memory (20gb db).
-If that's not the case when does it stop using memory?
Your thoughts,
Thanks.
|||comments inline
Keith
"Tim Richardson" <tim@.specialmail.co.uk> wrote in message
news:25879432.0410280128.5080316a@.posting.google.c om...
> Spot on. Windows counters such as these in task manager and process
> and working set in perfmon cannot monitor memory that is enabled by
> AWE, 4GB+. I wish it were that easy.
> In my situation, because the min and max memory is set to 4GB and 6GB
> none of this can be monitored by the usual tools, it's all AWE memory.
> What i need to know is;
> -Should i be concerned that SQL memory usage peaks at using all memory
> assigned to it?
No

> -Should it peak at 100% mem usage, is this normal?
Yes
INF: SQL Server Memory Usage
http://support.microsoft.com/default...;en-us;q321363

> -If i assigned, for example, 40GB of memory would it just keep using
> memory until it had the hole db in memory (20gb db).
It might use slightly more RAM [than 20GB] because it would (could) have
data and query plans in cache. It might also decide that it wants
additional memory for SQL Agent, user connections, and so on.

> -If that's not the case when does it stop using memory?
When it does not need any more.

> Your thoughts,
> Thanks.

How to tell how much memory is actually needed by my SQL Server 2000?

The easiest way of doing it is to go onto your server,
doing a CTR ALT DEL, bringing up task manager, then
looking for SQL Server under the processors list.
Peter
"I'm just preparing my impromptu remarks."
Winston Churchill

>--Original Message--
>I've been doing a lot of reading on this and my head is
starting to
>hurt! It seems to be quite a feat to work out how much
memory is
>actually being used by our server.
>I'm running W2K advanced server with SQL 2000 EE, 8GB of
RAM, a min of
>4GB and a max of 6GB is assigned to SQL server.
>I'm trying to work out whether we've assigned enough or
too
>much/little memory to SQL server. My first thought was to
let SQL
>dymanically manage its own memory and see how much it
uses, of course
>when AWE (/3GB /PAE) is enabled it will just use all that
is
>available.
>In perfmon "target server memory" = 6.1GB, "total server
memory" =
>6.1GB, "total pages" = 768000 ( x 8KB = 6.1GB).
>My second thought was to use "total pages" - the
average "free pages"
>= average mem used, therefore giving me the average
amount of memory
>used by SQL. I found out that SQL uses a min of 4GB (the
min we
>assigned) and the max of all the memory, 6GB.
>Is there an easier way of finding out how much memory is
actually used
>in this situation or is going by the above average the
best way?
>What i'm unsure about is if, for example, we made 1TB of
RAM
>available, will SQL just use all memory assigned to it
until it has
>the whole DB in memory? If not, what's the cut off? Or is
the fact
>that the memory usage of our instance of sql peaks at
using all the
>memory an indication that we do not have enough available?
>Any help would be greatly apprechiated.
>Thanks.
>.
>I don't know if that method will work.
We are running SQL 2k EE on Win2k AS. We are using /3GB /PAE and SQL Server
is configured to use 6144 MB.
Task manager reports that sqlservr.exe is using 115,524K on the process tab
however on the performance tab the physical memory is reported as
Total 7863624
Available 1184100
Keith
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:15d501c4bc26$57a92ae0$a601280a@.phx.gbl...[vbcol=seagreen]
> The easiest way of doing it is to go onto your server,
> doing a CTR ALT DEL, bringing up task manager, then
> looking for SQL Server under the processors list.
> Peter
> "I'm just preparing my impromptu remarks."
> Winston Churchill
>
>
> starting to
> memory is
> RAM, a min of
> too
> let SQL
> uses, of course
> is
> memory" =
> average "free pages"
> amount of memory
> min we
> actually used
> best way?
> RAM
> until it has
> the fact
> using all the|||Keith,
I think you have a point there.
However Task Manager will always show the amount of memory
that has been allocated to a process, and is very accurate
(as far as I know)
I suppose the question(s) is more of
1. How much memory has been allocated
2. How much of that memory is in use
3. How much more memory can be accessed
Anyway as it piked my interest I dug out my copy of
Performance Monitoring with SQL Server 2000 and set up the
counters Total Server Memory and Target Server Memory.
As you know it will show percentage of the actual memory
useage on the server.
Currently our server is set to 48% of all server memory,
which translates roughly to the amount of memory shown as
used in Task Manager, though I will freely admit its not
completely accurate.
I think the answer then is there is no way of totally been
able to predict the amount of memory being used, as both
things give different figures.
So I think I will change my mind and go down your route
using the profiler if nothing more than the fact you take
the findings at a regular time and do something with them.
Anyway thanks for that.
Peter
"Action speaks louder than words but not nearly as often"
Mark Twain

>--Original Message--
>I don't know if that method will work.
>We are running SQL 2k EE on Win2k AS. We are
using /3GB /PAE and SQL Server
>is configured to use 6144 MB.
>Task manager reports that sqlservr.exe is using 115,524K
on the process tab
>however on the performance tab the physical memory is
reported as
>Total 7863624
>Available 1184100
>--
>Keith
>
>"Peter The Spate" <anonymous@.discussions.microsoft.com>
wrote in message
>news:15d501c4bc26$57a92ae0$a601280a@.phx.gbl...
of[vbcol=seagreen]
to[vbcol=seagreen]
that[vbcol=seagreen]
server[vbcol=seagreen]
(the[vbcol=seagreen]
is[vbcol=seagreen]
of[vbcol=seagreen]
is[vbcol=seagreen]
available?[vbcol=seagreen]
>.
>|||Spot on. Windows counters such as these in task manager and process
and working set in perfmon cannot monitor memory that is enabled by
AWE, 4GB+. I wish it were that easy.
In my situation, because the min and max memory is set to 4GB and 6GB
none of this can be monitored by the usual tools, it's all AWE memory.
What i need to know is;
-Should i be concerned that SQL memory usage peaks at using all memory
assigned to it?
-Should it peak at 100% mem usage, is this normal?
-If i assigned, for example, 40GB of memory would it just keep using
memory until it had the hole db in memory (20gb db).
-If that's not the case when does it stop using memory?
Your thoughts,
Thanks.|||comments inline
Keith
"Tim Richardson" <tim@.specialmail.co.uk> wrote in message
news:25879432.0410280128.5080316a@.posting.google.com...
> Spot on. Windows counters such as these in task manager and process
> and working set in perfmon cannot monitor memory that is enabled by
> AWE, 4GB+. I wish it were that easy.
> In my situation, because the min and max memory is set to 4GB and 6GB
> none of this can be monitored by the usual tools, it's all AWE memory.
> What i need to know is;
> -Should i be concerned that SQL memory usage peaks at using all memory
> assigned to it?
No

> -Should it peak at 100% mem usage, is this normal?
Yes
INF: SQL Server Memory Usage
http://support.microsoft.com/defaul...b;en-us;q321363

> -If i assigned, for example, 40GB of memory would it just keep using
> memory until it had the hole db in memory (20gb db).
It might use slightly more RAM [than 20GB] because it would (could) have
data and query plans in cache. It might also decide that it wants
additional memory for SQL Agent, user connections, and so on.

> -If that's not the case when does it stop using memory?
When it does not need any more.

> Your thoughts,
> Thanks.

How to take the database online/ bring it offline by command prompt?

Hi,
I now using MS SQL SERVER Desktop Edition, as I know there is no enterpirse
belongs with this edition. I would like to perform a task that is provided
in the enterprise manager.
The task is "select a database--rightclick->All Tasks-->Taks Offline/Bring
Online"
The database file will then disconnect and connect to the server.
Anybody know how to perform the same task by command prompt as there is no
enterprise manager?
Thanks
RC
Hi,
From OSQL use the below commands:-
For Offline
ALTER DATABASE <DBNAME> SET OFFLINE
For Online
ALTER DATABASE <DBNAME> SET ONLINE
Note:
You have many options in ALTER DATABASE command, refer books online for more
information.
THanks
Hari
SQL Server MVP
"RC" <rc@.gmail.com> wrote in message
news:u2q2ST%23PFHA.3560@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I now using MS SQL SERVER Desktop Edition, as I know there is no
> enterpirse
> belongs with this edition. I would like to perform a task that is provided
> in the enterprise manager.
> The task is "select a database--rightclick->All Tasks-->Taks Offline/Bring
> Online"
> The database file will then disconnect and connect to the server.
> Anybody know how to perform the same task by command prompt as there is no
> enterprise manager?
> Thanks
> RC
>

How to suppress warnings from a data flow task

Hi,

I am getting unusable warnings from dataflow task while running. Say for example I am getting column A, B, C from source and I am using only column C in destination. Whenever I run the package, I am getting a warning saying that

"The output column "A" (67) on output "Output0" (5) and component "Data Flow Task" (1) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance."

Can any one help me how to resolve these warnings?

Thanks in advance.

You can ignore the warning, or do what it says, and remove the unused column from the data flow.|||

Get rid of the unused columns by not having them present on a given output.

"Get rid of unused columns" means modifying components with async outputs (sources, union all, merge, merge join, sort, aggregation) by eliminating unused output columns. Classic example is to use a sproc or select statement rather than a Table/View, which will often clog the dataflow with unused output columns.

That "unused output columns" warning is a very useful, and use-able, for that matter, warning message.

|||

Dear Friend,

In order to improve the performance in your dataflow, it's better to clean all the warning that the system return.

So, in your case, in the transforme task after your datasource, uncheck the columns that will not be used in the dataflow. If you dont insert this columns on the destination, and dont have any transformations depending on these columns, you can delete it from teh dataflow..

Understood?

Regards!!!

|||

Thanks for ur reply.

But After the source component, I have only lookup component which will looks up for the value. If it finds a row, update will happen or else insert will happen. In lookup we can not unselect the columns.

Shall I delete the columns in the "Inputs and Outputs" editor for source component?

|||

Your datasource has a SQL Statment inside, no?

If yes, delete the fields in the SQL

If not, explain me how your data comes into dataflow!

Regards!

|||Our source does not have any query. It will just read the data from file. We have to read all columns. But in data flow I will just use only one column.|||

S Venkat wrote:

Our source does not have any query. It will just read the data from file. We have to read all columns. But in data flow I will just use only one column.

Then in the data source, only select the column you need.|||

So, select only the column you need to use in your dataflow...

Understood? If not, ahow us an image or something with more detail!

regards!

How to suppress warnings from a data flow task

Hi,

I am getting unusable warnings from dataflow task while running. Say for example I am getting column A, B, C from source and I am using only column C in destination. Whenever I run the package, I am getting a warning saying that

"The output column "A" (67) on output "Output0" (5) and component "Data Flow Task" (1) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance."

Can any one help me how to resolve these warnings?

Thanks in advance.

You can ignore the warning, or do what it says, and remove the unused column from the data flow.|||

Get rid of the unused columns by not having them present on a given output.

"Get rid of unused columns" means modifying components with async outputs (sources, union all, merge, merge join, sort, aggregation) by eliminating unused output columns. Classic example is to use a sproc or select statement rather than a Table/View, which will often clog the dataflow with unused output columns.

That "unused output columns" warning is a very useful, and use-able, for that matter, warning message.

|||

Dear Friend,

In order to improve the performance in your dataflow, it's better to clean all the warning that the system return.

So, in your case, in the transforme task after your datasource, uncheck the columns that will not be used in the dataflow. If you dont insert this columns on the destination, and dont have any transformations depending on these columns, you can delete it from teh dataflow..

Understood?

Regards!!!

|||

Thanks for ur reply.

But After the source component, I have only lookup component which will looks up for the value. If it finds a row, update will happen or else insert will happen. In lookup we can not unselect the columns.

Shall I delete the columns in the "Inputs and Outputs" editor for source component?

|||

Your datasource has a SQL Statment inside, no?

If yes, delete the fields in the SQL

If not, explain me how your data comes into dataflow!

Regards!

|||Our source does not have any query. It will just read the data from file. We have to read all columns. But in data flow I will just use only one column.|||

S Venkat wrote:

Our source does not have any query. It will just read the data from file. We have to read all columns. But in data flow I will just use only one column.

Then in the data source, only select the column you need.|||

So, select only the column you need to use in your dataflow...

Understood? If not, ahow us an image or something with more detail!

regards!

How to suppress warnings from a data flow task

Hi,

I am getting unusable warnings from dataflow task while running. Say for example I am getting column A, B, C from source and I am using only column C in destination. Whenever I run the package, I am getting a warning saying that

"The output column "A" (67) on output "Output0" (5) and component "Data Flow Task" (1) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance."

Can any one help me how to resolve these warnings?

Thanks in advance.

You can ignore the warning, or do what it says, and remove the unused column from the data flow.|||

Get rid of the unused columns by not having them present on a given output.

"Get rid of unused columns" means modifying components with async outputs (sources, union all, merge, merge join, sort, aggregation) by eliminating unused output columns. Classic example is to use a sproc or select statement rather than a Table/View, which will often clog the dataflow with unused output columns.

That "unused output columns" warning is a very useful, and use-able, for that matter, warning message.

|||

Dear Friend,

In order to improve the performance in your dataflow, it's better to clean all the warning that the system return.

So, in your case, in the transforme task after your datasource, uncheck the columns that will not be used in the dataflow. If you dont insert this columns on the destination, and dont have any transformations depending on these columns, you can delete it from teh dataflow..

Understood?

Regards!!!

|||

Thanks for ur reply.

But After the source component, I have only lookup component which will looks up for the value. If it finds a row, update will happen or else insert will happen. In lookup we can not unselect the columns.

Shall I delete the columns in the "Inputs and Outputs" editor for source component?

|||

Your datasource has a SQL Statment inside, no?

If yes, delete the fields in the SQL

If not, explain me how your data comes into dataflow!

Regards!

|||Our source does not have any query. It will just read the data from file. We have to read all columns. But in data flow I will just use only one column.|||

S Venkat wrote:

Our source does not have any query. It will just read the data from file. We have to read all columns. But in data flow I will just use only one column.

Then in the data source, only select the column you need.|||

So, select only the column you need to use in your dataflow...

Understood? If not, ahow us an image or something with more detail!

regards!

How to suppress warnings from a data flow task

Hi,

I am getting unusable warnings from dataflow task while running. Say for example I am getting column A, B, C from source and I am using only column C in destination. Whenever I run the package, I am getting a warning saying that

"The output column "A" (67) on output "Output0" (5) and component "Data Flow Task" (1) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance."

Can any one help me how to resolve these warnings?

Thanks in advance.

You can ignore the warning, or do what it says, and remove the unused column from the data flow.|||

Get rid of the unused columns by not having them present on a given output.

"Get rid of unused columns" means modifying components with async outputs (sources, union all, merge, merge join, sort, aggregation) by eliminating unused output columns. Classic example is to use a sproc or select statement rather than a Table/View, which will often clog the dataflow with unused output columns.

That "unused output columns" warning is a very useful, and use-able, for that matter, warning message.

|||

Dear Friend,

In order to improve the performance in your dataflow, it's better to clean all the warning that the system return.

So, in your case, in the transforme task after your datasource, uncheck the columns that will not be used in the dataflow. If you dont insert this columns on the destination, and dont have any transformations depending on these columns, you can delete it from teh dataflow..

Understood?

Regards!!!

|||

Thanks for ur reply.

But After the source component, I have only lookup component which will looks up for the value. If it finds a row, update will happen or else insert will happen. In lookup we can not unselect the columns.

Shall I delete the columns in the "Inputs and Outputs" editor for source component?

|||

Your datasource has a SQL Statment inside, no?

If yes, delete the fields in the SQL

If not, explain me how your data comes into dataflow!

Regards!

|||Our source does not have any query. It will just read the data from file. We have to read all columns. But in data flow I will just use only one column.|||

S Venkat wrote:

Our source does not have any query. It will just read the data from file. We have to read all columns. But in data flow I will just use only one column.

Then in the data source, only select the column you need.|||

So, select only the column you need to use in your dataflow...

Understood? If not, ahow us an image or something with more detail!

regards!

How to suppress warnings from a data flow task

Hi,

I am getting unusable warnings from dataflow task while running. Say for example I am getting column A, B, C from source and I am using only column C in destination. Whenever I run the package, I am getting a warning saying that

"The output column "A" (67) on output "Output0" (5) and component "Data Flow Task" (1) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance."

Can any one help me how to resolve these warnings?

Thanks in advance.

You can ignore the warning, or do what it says, and remove the unused column from the data flow.|||

Get rid of the unused columns by not having them present on a given output.

"Get rid of unused columns" means modifying components with async outputs (sources, union all, merge, merge join, sort, aggregation) by eliminating unused output columns. Classic example is to use a sproc or select statement rather than a Table/View, which will often clog the dataflow with unused output columns.

That "unused output columns" warning is a very useful, and use-able, for that matter, warning message.

|||

Dear Friend,

In order to improve the performance in your dataflow, it's better to clean all the warning that the system return.

So, in your case, in the transforme task after your datasource, uncheck the columns that will not be used in the dataflow. If you dont insert this columns on the destination, and dont have any transformations depending on these columns, you can delete it from teh dataflow..

Understood?

Regards!!!

|||

Thanks for ur reply.

But After the source component, I have only lookup component which will looks up for the value. If it finds a row, update will happen or else insert will happen. In lookup we can not unselect the columns.

Shall I delete the columns in the "Inputs and Outputs" editor for source component?

|||

Your datasource has a SQL Statment inside, no?

If yes, delete the fields in the SQL

If not, explain me how your data comes into dataflow!

Regards!

|||Our source does not have any query. It will just read the data from file. We have to read all columns. But in data flow I will just use only one column.|||

S Venkat wrote:

Our source does not have any query. It will just read the data from file. We have to read all columns. But in data flow I will just use only one column.

Then in the data source, only select the column you need.|||

So, select only the column you need to use in your dataflow...

Understood? If not, ahow us an image or something with more detail!

regards!

How to suppress warnings from a data flow task

Hi,

I am getting unusable warnings from dataflow task while running. Say for example I am getting column A, B, C from source and I am using only column C in destination. Whenever I run the package, I am getting a warning saying that

"The output column "A" (67) on output "Output0" (5) and component "Data Flow Task" (1) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance."

Can any one help me how to resolve these warnings?

Thanks in advance.

You can ignore the warning, or do what it says, and remove the unused column from the data flow.|||

Get rid of the unused columns by not having them present on a given output.

"Get rid of unused columns" means modifying components with async outputs (sources, union all, merge, merge join, sort, aggregation) by eliminating unused output columns. Classic example is to use a sproc or select statement rather than a Table/View, which will often clog the dataflow with unused output columns.

That "unused output columns" warning is a very useful, and use-able, for that matter, warning message.

|||

Dear Friend,

In order to improve the performance in your dataflow, it's better to clean all the warning that the system return.

So, in your case, in the transforme task after your datasource, uncheck the columns that will not be used in the dataflow. If you dont insert this columns on the destination, and dont have any transformations depending on these columns, you can delete it from teh dataflow..

Understood?

Regards!!!

|||

Thanks for ur reply.

But After the source component, I have only lookup component which will looks up for the value. If it finds a row, update will happen or else insert will happen. In lookup we can not unselect the columns.

Shall I delete the columns in the "Inputs and Outputs" editor for source component?

|||

Your datasource has a SQL Statment inside, no?

If yes, delete the fields in the SQL

If not, explain me how your data comes into dataflow!

Regards!

|||Our source does not have any query. It will just read the data from file. We have to read all columns. But in data flow I will just use only one column.|||

S Venkat wrote:

Our source does not have any query. It will just read the data from file. We have to read all columns. But in data flow I will just use only one column.

Then in the data source, only select the column you need.|||

So, select only the column you need to use in your dataflow...

Understood? If not, ahow us an image or something with more detail!

regards!

How to suppress warnings from a data flow task

Hi,

I am getting unusable warnings from dataflow task while running. Say for example I am getting column A, B, C from source and I am using only column C in destination. Whenever I run the package, I am getting a warning saying that

"The output column "A" (67) on output "Output0" (5) and component "Data Flow Task" (1) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance."

Can any one help me how to resolve these warnings?

Thanks in advance.

You can ignore the warning, or do what it says, and remove the unused column from the data flow.|||

Get rid of the unused columns by not having them present on a given output.

"Get rid of unused columns" means modifying components with async outputs (sources, union all, merge, merge join, sort, aggregation) by eliminating unused output columns. Classic example is to use a sproc or select statement rather than a Table/View, which will often clog the dataflow with unused output columns.

That "unused output columns" warning is a very useful, and use-able, for that matter, warning message.

|||

Dear Friend,

In order to improve the performance in your dataflow, it's better to clean all the warning that the system return.

So, in your case, in the transforme task after your datasource, uncheck the columns that will not be used in the dataflow. If you dont insert this columns on the destination, and dont have any transformations depending on these columns, you can delete it from teh dataflow..

Understood?

Regards!!!

|||

Thanks for ur reply.

But After the source component, I have only lookup component which will looks up for the value. If it finds a row, update will happen or else insert will happen. In lookup we can not unselect the columns.

Shall I delete the columns in the "Inputs and Outputs" editor for source component?

|||

Your datasource has a SQL Statment inside, no?

If yes, delete the fields in the SQL

If not, explain me how your data comes into dataflow!

Regards!

|||Our source does not have any query. It will just read the data from file. We have to read all columns. But in data flow I will just use only one column.|||

S Venkat wrote:

Our source does not have any query. It will just read the data from file. We have to read all columns. But in data flow I will just use only one column.

Then in the data source, only select the column you need.|||

So, select only the column you need to use in your dataflow...

Understood? If not, ahow us an image or something with more detail!

regards!

How to supply config file to Exec Package Task?

How do I do this ? Or is there any way to bundle Package with Config file so that they both are deployed in MSDB?If you are going to save to MSDB, you might want to use SQL Server based package configurations.|||SQL Based configurations ?
Whats that ? Any page to read about it ?|||

Fahad349 wrote:

SQL Based configurations ?
Whats that ? Any page to read about it ?

There are plenty of resources on this forum, BOL, and other places. This should get your started:

http://msdn2.microsoft.com/en-us/library/ms141682.aspx|||dwith's reply really helped me in this post

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=142920&SiteID=1

How to Store System Variables in DB?

Hi,

I want to store the System::StartTime in my Integration Table Log. Please guide how can I use this in my Execute SQL Task Block. I write this SQL Statement:

INSERT INTO CMN_tblIntegration VALUES ('HRM_tblParty', ?) Which the ? is for the parameter. I go to parameter mapping tab and map the System::SatrTime to parameter0, but the package fails to run. I also changed the ? to @.IntDate and also changed the parameter name but the error was the same.

Please help how to write this System Variable value in to that table.

Regards,
Samy

In this situation I recommend using expressions rather than parameters. This article talks about expressions to store a SQL statement for an OLE DB Source component but this principle can apply to Execute SQL Task as well: http://blogs.conchango.com/jamiethomson/archive/2005/12/09/2480.aspx

-Jamie

|||

I know Jamie is a fan of using expressions over parameters, but I disagree, and there are situations where parameters are still better. Security being the big issue with expressions.

Anyway, to get your parameter to work, make sure you have set mappend the parameter on the "Parameter Mapping" page of the Execute SQL Task. Select variable StartTime, direction is Input, Data Type is DATE, and Parameter name is 0.

For OLE-DB connections the parameter name is a zero based incrementing count of the parameters.

The Data Type is DATE, which seems rather confusing, as normally DBTIMESTAMP is the SSIS data type to use, however these are clearly not normal SSIS data types. We just do not have enough different types within SSIS!

|||

Dear Darren,

I did what you mentioned, but the package is not functioning:

SSIS package "HRM_tblParty.dtsx" starting.

Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "INSERT INTO CMN_tblIntegration VALUES ('HRM_tblParty', ?)" failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Task failed: Execute SQL Task

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

SSIS package "HRM_tblParty.dtsx" finished: Failure.

This is my Query:

INSERT INTO CMN_tblIntegration VALUES ('HRM_tblParty', ?)

and I mapped the System::StartTime to Parameter0 (Type: Date and Direction: Input)

Pleae help,
Sassan

|||

Dear Jamie,

Nice Solution with Nice blog.
But How do I call the other System Value in my SQL Variable. For example:

"SELECT * FROM CMN_tblParty WHERE CreationDateTime <= " + @.StartTime

or

"SELECT * FROM CMN_tblParty WHERE CreationDateTime <= " + @.System::StartTime

Both fails at defining the Data Flow Task.

Thanks.
Samy

|||

SamyLahur wrote:

Dear Jamie,

Nice Solution with Nice blog.
But How do I call the other System Value in my SQL Variable. For example:

"SELECT * FROM CMN_tblParty WHERE CreationDateTime <= " + @.StartTime

or

"SELECT * FROM CMN_tblParty WHERE CreationDateTime <= " + @.System::StartTime

Both fails at defining the Data Flow Task.

Thanks.
Samy

The OLE DB Source component can be set by selecting a table, entering a SQL statement, or taking a SQL statement that is stored in a variable. You need to select the 3rd of these options.

-Jamie

|||

The parameter name should be 0, nothing else just the character for zero.

For the expression option you need to specify the variable name correctly-

@.[System::StartTime]

You will also need to convert to date time valeu to a string to do the concatenation-

"SELECT * FROM CMN_tblParty WHERE CreationDateTime <= '" + (DT_WSTR, 20 )@.[System::StartTime] + "'"

Note the quick and dirty conversion I have used is open to date format misinterpretation issues DMY, MDY etc. You should break it down and convert to component parts into a string as an unambiguous format. For SQL Server yyyymmdd is unambiguous. Of course using parameter support avoids this issue as it is treated as a date type all the way through.

|||

I know I must choose the SQL Command by Variable, but when I choose the variable, which has the above expression it fails to show the Value and says:

The expression for variable "NewItemsSQL" failed evaluation. There was an error in the expression."

What is the problem with expression?

Samy

|||Have you read my last post? I appreciate that you may well have missed it due to the exceptionally well designed interface and threading capabilities of these forums.|||

SamyLahur wrote:

I know I must choose the SQL Command by Variable, but when I choose the variable, which has the above expression it fails to show the Value and says:

The expression for variable "NewItemsSQL" failed evaluation. There was an error in the expression."

What is the problem with expression?

Samy

My first guess would be that you need to cast the date as a string.

There is no expression editor on variable expressions which is (and I'm being polite here) very very BAD. It will appear in SP1 but in the meantime here's a tip for you. Build your expression against the "Description" property of the package. This will enable you to use the expression editor to build your expression and validate it within there. When you have it working, copy and paste the expression into the appropriate place for the variable.

Hope that makes sense.

-Jamie

How to stop execution of DTS package (during loop)

I have a MSSQL DTS Package where it needs to loop/execute 3 times because the main task is to import data from 3 excel files (different location) into 1 SQL table. I used a global variable vCounter and I use an ActiveX Script.

ActiveX Script 1

Option Explicit

Function Main()

Dim vDate, vCounter, vBranchCode, vPath

vDate="011207"

vCounter=DTSGlobalVariables("gVarCounter").Value

IF vCounter<=3 THEN

IF vCounter=1 THEN
vBranchCode="ALB"
vPath= "D:\PROJECTS\HRIS\ALB\"
ELSEIF vCounter=2 THEN
vBranchCode="MOA"
vPath= "D:\PROJECTS\HRIS\MOA\"
ELSEIF vCounter=3 THEN
vBranchCode="PSQ"
vPath= "D:\PROJECTS\HRIS\PSQ\"
END IF

DTSGlobalVariables("gVarPath").Value=vPath & vDate & "_" & vBranchCode & ".xls"

Main = DTSTaskExecResult_Success

ELSE
<This is where i will initialize the global variable gVarCounter, so in the next execution..the value should be back to 1>
DTSGlobalVariables("gVarCounter").Value=1
<DTS Process should stop execution...how is this?>
END IF

End Function

After excel to sql dts
ActiveX Script2

Function Main()

IF gVarCounter<=3 then
DTSGlobalVariables("gVarCounter").Value=DTSGlobalVariables("gVarCounter").Value+1
DTSGlobalVariables.Parent.Steps("DTSStep_DTSActiveScriptTask_1").ExecutionStatus=DTSStepExecStat_Waiting
Main = DTSTaskExecResult_Success
END IF

End Function

Thanks a lot.there are many ways to stop a DTS. u can simply write

Main = DTSTaskExecResult_Failure

in the ELSE part and link the next step with "on success" workflow. or u can even write a blank ActiveX step and redirect the flow to that step in the ELSE part with "...DTSStepExecStat_Waiting" as u r already doing|||sorry, i pressed the save button twice... and the same thing got posted twice... there sould be an option to delete a post....|||hi upalsen,

thanks a lot. it's now working. great!
god bless.|||hi LimaCharlie,

thanks for your acknowledgement. many here do not acknowledge the solution they have accepted. an acknowledgement is (1) a recognition for support (2) helps in closing the post (3) helps a third user who is viewing the post at a later date (or coming from search engine) to understand what the final solution could be.