"EMartinez" wrote:

> On Sep 19, 9:14 pm, Jonathan <Jonat...@discussions.microsoft.com>
> wrote:
> > Hi, I'm definitely a newbie to SQL 2005. I'm importing data from MS Access
> > 2003 and several tables have a status of Error.
> >
> > The error messages in SQL 2000 DTS were helpful as they included table name,
> > column name and description of data problem that caused the error. This
> > enabled me to identify records that needed correcting.
> >
> > When using SSIS, is the somewhere I can refer to learn what the actual
> > problem is as I find the list of errors in the report very unhelpful?
> >
> > Many thanks, Jonathan
>
>
> One option is to create an Excel/Flat file/Csv destination for the
> errors and add one of these destination controls to the error branch
> of each SSIS control. That way you can determine at what point the
> error is occurring. Also, you can add a dataviewer on the connections
> between the SSIS controls (via right-clicking the connector and
> selecting the add dataviewer option (not sure of the verbiage)). Also,
> there are several other options available in the way of error
> reporting and controls. Hope this helps.
>
> Regards,
>
> Enrique Martinez
> Sr. Software Consultant
>

wrote:
> "EMartinez" wrote:
> > On Sep 19, 9:14 pm, Jonathan <Jonat...@discussions.microsoft.com>
> > wrote:
> > > Hi, I'm definitely a newbie to SQL 2005. I'm importing data from MS Access
> > > 2003 and several tables have a status of Error.
>
> > > The error messages in SQL 2000 DTS were helpful as they included table name,
> > > column name and description of data problem that caused the error. This
> > > enabled me to identify records that needed correcting.
>
> > > When using SSIS, is the somewhere I can refer to learn what the actual
> > > problem is as I find the list of errors in the report very unhelpful?
>
> > > Many thanks, Jonathan
>
> > One option is to create an Excel/Flat file/Csv destination for the
> > errors and add one of these destination controls to the error branch
> > of each SSIS control. That way you can determine at what point the
> > error is occurring. Also, you can add a dataviewer on the connections
> > between the SSIS controls (via right-clicking the connector and
> > selecting the add dataviewer option (not sure of the verbiage)). Also,
> > there are several other options available in the way of error
> > reporting and controls. Hope this helps.
>
> > Regards,
>
> > Enrique Martinez
> > Sr. Software Consultant
>
> Hi Enrique, thanks for you suggestions. How do you add an error branch?
>
> Jonathan- Hide quoted text -
>
> - Show quoted text -

wrote:
> Hi, I'm definitely a newbie to SQL 2005. I'm importing data from MS Access
> 2003 and several tables have a status of Error.
>
> The error messages in SQL 2000 DTS were helpful as they included table name,
> column name and description of data problem that caused the error. This
> enabled me to identify records that needed correcting.
>
> When using SSIS, is the somewhere I can refer to learn what the actual
> problem is as I find the list of errors in the report very unhelpful?
>
> Many thanks, Jonathan

"jhofmeyr@googlemail.com" wrote:

> On Sep 20, 4:28 am, Jonathan <Jonat...@discussions.microsoft.com>
> wrote:
> > "EMartinez" wrote:
> > > On Sep 19, 9:14 pm, Jonathan <Jonat...@discussions.microsoft.com>
> > > wrote:
> > > > Hi, I'm definitely a newbie to SQL 2005. I'm importing data from MS Access
> > > > 2003 and several tables have a status of Error.
> >
> > > > The error messages in SQL 2000 DTS were helpful as they included table name,
> > > > column name and description of data problem that caused the error. This
> > > > enabled me to identify records that needed correcting.
> >
> > > > When using SSIS, is the somewhere I can refer to learn what the actual
> > > > problem is as I find the list of errors in the report very unhelpful?
> >
> > > > Many thanks, Jonathan
> >
> > > One option is to create an Excel/Flat file/Csv destination for the
> > > errors and add one of these destination controls to the error branch
> > > of each SSIS control. That way you can determine at what point the
> > > error is occurring. Also, you can add a dataviewer on the connections
> > > between the SSIS controls (via right-clicking the connector and
> > > selecting the add dataviewer option (not sure of the verbiage)). Also,
> > > there are several other options available in the way of error
> > > reporting and controls. Hope this helps.
> >
> > > Regards,
> >
> > > Enrique Martinez
> > > Sr. Software Consultant
> >
> > Hi Enrique, thanks for you suggestions. How do you add an error branch?
> >
> > Jonathan- Hide quoted text -
> >
> > - Show quoted text -
>
> Hi Jonathan,
>
> It might help if you give more details about what you're trying to do
> - there are a number of ways to deal with errors in SSIS :)
>
> For starters, while in a Data Flow most tasks have an Error Output
> (the red arrow coming from the task) that you can connect to an task
> to handle errors, in the Control Flow all connections default to
> OnSuccess (the green arrows coming from tasks) - these need to be
> changed to OnError, OnCompletion, etc. as required.
> You can also define Event Handlers for OnError events applying to
> various points of the package.
>
> So as you see - which method you use depends a lot on what you are
> trying to achieve.
> Good Luck
> J
>

"jhofmeyr@googlemail.com" wrote:

> On Sep 20, 9:34 pm, Jonathan <Jonat...@discussions.microsoft.com>
> wrote:
> > "jhofm...@googlemail.com" wrote:
> > > On Sep 20, 4:28 am, Jonathan <Jonat...@discussions.microsoft.com>
> > > wrote:
> > > > "EMartinez" wrote:
> > > > > On Sep 19, 9:14 pm, Jonathan <Jonat...@discussions.microsoft.com>
> > > > > wrote:
> > > > > > Hi, I'm definitely a newbie to SQL 2005. I'm importing data from MS Access
> > > > > > 2003 and several tables have a status of Error.
> >
> > > > > > The error messages in SQL 2000 DTS were helpful as they included table name,
> > > > > > column name and description of data problem that caused the error. This
> > > > > > enabled me to identify records that needed correcting.
> >
> > > > > > When using SSIS, is the somewhere I can refer to learn what the actual
> > > > > > problem is as I find the list of errors in the report very unhelpful?
> >
> > > > > > Many thanks, Jonathan
> >
> > > > > One option is to create an Excel/Flat file/Csv destination for the
> > > > > errors and add one of these destination controls to the error branch
> > > > > of each SSIS control. That way you can determine at what point the
> > > > > error is occurring. Also, you can add a dataviewer on the connections
> > > > > between the SSIS controls (via right-clicking the connector and
> > > > > selecting the add dataviewer option (not sure of the verbiage)). Also,
> > > > > there are several other options available in the way of error
> > > > > reporting and controls. Hope this helps.
> >
> > > > > Regards,
> >
> > > > > Enrique Martinez
> > > > > Sr. Software Consultant
> >
> > > > Hi Enrique, thanks for you suggestions. How do you add an error branch?
> >
> > > > Jonathan- Hide quoted text -
> >
> > > > - Show quoted text -
> >
> > > Hi Jonathan,
> >
> > > It might help if you give more details about what you're trying to do
> > > - there are a number of ways to deal with errors in SSIS :)
> >
> > > For starters, while in a Data Flow most tasks have an Error Output
> > > (the red arrow coming from the task) that you can connect to an task
> > > to handle errors, in the Control Flow all connections default to
> > > OnSuccess (the green arrows coming from tasks) - these need to be
> > > changed to OnError, OnCompletion, etc. as required.
> > > You can also define Event Handlers for OnError events applying to
> > > various points of the package.
> >
> > > So as you see - which method you use depends a lot on what you are
> > > trying to achieve.
> > > Good Luck
> > > J
> >
> > Thanks J. I was hoping that it was going to be a simple case of stepping
> > through the import wizard. Unfortunitely I now have the impression that I
> > will have to learn how to use SSIS design tools (which is probably a good
> > thing to do anyway...).
> >
> > Regards, Jonathan- Hide quoted text -
> >
> > - Show quoted text -
>
> Hi Jonathan,
>
> Re-reading you post it is now apparent that you were simply running
> the import wizard, not developing the packages yourself .. sorry for
> the misunderstanding :)
>
> If you are doing a simple import it should be as easy as running the
> import wizard. You say the error message you're getting isn't very
> helpful - what exactly is it saying? Also - are you trying to import
> multiple tables, or just 1 table? Are you importing into existing
> tables, or letting the wizard generate the tables for you?
>
> One thing you could try is to save the package created by the wizard
> instead of choosing the "run now" option - this would fast-track the
> package creation and also provide you with a base package to look at
> while you're familiarizing yourself with the tools.
>
> If you decide to delve deeping into the capabilities of SSIS, the MSDN
> website has lots of useful information. I'd recommend starting out
> with the Project Real case study. While it may be a bit dated now (it
> was done on the beta release iirc), it has loads of useful hints and
> best-practice suggestions.
>
> Good Luck!
> J
>

wrote:
> "jhofm...@googlemail.com" wrote:
> > On Sep 20, 9:34 pm, Jonathan <Jonat...@discussions.microsoft.com>
> > wrote:
> > > "jhofm...@googlemail.com" wrote:
> > > > On Sep 20, 4:28 am, Jonathan <Jonat...@discussions.microsoft.com>
> > > > wrote:
> > > > > "EMartinez" wrote:
> > > > > > On Sep 19, 9:14 pm, Jonathan <Jonat...@discussions.microsoft.com>
> > > > > > wrote:
> > > > > > > Hi, I'm definitely a newbie to SQL 2005. I'm importing data from MS Access
> > > > > > > 2003 and several tables have a status of Error.
>
> > > > > > > The error messages in SQL 2000 DTS were helpful as they included table name,
> > > > > > > column name and description of data problem that caused the error. This
> > > > > > > enabled me to identify records that needed correcting.
>
> > > > > > > When using SSIS, is the somewhere I can refer to learn what the actual
> > > > > > > problem is as I find the list of errors in the report very unhelpful?
>
> > > > > > > Many thanks, Jonathan
>
> > > > > > One option is to create an Excel/Flat file/Csv destination for the
> > > > > > errors and add one of these destination controls to the error branch
> > > > > > of each SSIS control. That way you can determine at what point the
> > > > > > error is occurring. Also, you can add a dataviewer on the connections
> > > > > > between the SSIS controls (via right-clicking the connector and
> > > > > > selecting the add dataviewer option (not sure of the verbiage)). Also,
> > > > > > there are several other options available in the way of error
> > > > > > reporting and controls. Hope this helps.
>
> > > > > > Regards,
>
> > > > > > Enrique Martinez
> > > > > > Sr. Software Consultant
>
> > > > > Hi Enrique, thanks for you suggestions. How do you add an error branch?
>
> > > > > Jonathan- Hide quoted text -
>
> > > > > - Show quoted text -
>
> > > > Hi Jonathan,
>
> > > > It might help if you give more details about what you're trying to do
> > > > - there are a number of ways to deal with errors in SSIS :)
>
> > > > For starters, while in a Data Flow most tasks have an Error Output
> > > > (the red arrow coming from the task) that you can connect to an task
> > > > to handle errors, in the Control Flow all connections default to
> > > > OnSuccess (the green arrows coming from tasks) - these need to be
> > > > changed to OnError, OnCompletion, etc. as required.
> > > > You can also define Event Handlers for OnError events applying to
> > > > various points of the package.
>
> > > > So as you see - which method you use depends a lot on what you are
> > > > trying to achieve.
> > > > Good Luck
> > > > J
>
> > > Thanks J. I was hoping that it was going to be a simple case of stepping
> > > through the import wizard. Unfortunitely I now have the impression that I
> > > will have to learn how to use SSIS design tools (which is probably a good
> > > thing to do anyway...).
>
> > > Regards, Jonathan- Hide quoted text -
>
> > > - Show quoted text -
>
> > Hi Jonathan,
>
> > Re-reading you post it is now apparent that you were simply running
> > the import wizard, not developing the packages yourself .. sorry for
> > the misunderstanding :)
>
> > If you are doing a simple import it should be as easy as running the
> > import wizard. You say the error message you're getting isn't very
> > helpful - what exactly is it saying? Also - are you trying to import
> > multiple tables, or just 1 table? Are you importing into existing
> > tables, or letting the wizard generate the tables for you?
>
> > One thing you could try is to save the package created by the wizard
> > instead of choosing the "run now" option - this would fast-track the
> > package creation and also provide you with a base package to look at
> > while you're familiarizing yourself with the tools.
>
> > If you decide to delve deeping into the capabilities of SSIS, the MSDN
> > website has lots of useful information. I'd recommend starting out
> > with the Project Real case study. While it may be a bit dated now (it
> > was done on the beta release iirc), it has loads of useful hints and
> > best-practice suggestions.
>
> > Good Luck!
> > J
>
> Hi J. I was doing the same thing that I did with sql2000 DTS. That is I was
> attempting to import every table in the MS Access database into an existing
> SQL database. When going from the step to select tables, the system message
> informed me that this was too many. There did not seem to be an optimum
> number (apart from 1!). Sometimes 64 tables were successfully imported and
> other times only 5. In the end I found that the tables prompting an error
> message could be successfully imported when imported individually. There has
> got to be a better way to import a large number of tables in one go.
>
> Thanks for the learning tips. I will apply these as the problem that I'm
> having is likely to be from my lack of knowledge.
>
> Many thanks, Jonathan- Hide quoted text -
>
> - Show quoted text -

wrote:
> "jhofm...@googlemail.com" wrote:
> > On Sep 20, 4:28 am, Jonathan <Jonat...@discussions.microsoft.com>
> > wrote:
> > > "EMartinez" wrote:
> > > > On Sep 19, 9:14 pm, Jonathan <Jonat...@discussions.microsoft.com>
> > > > wrote:
> > > > > Hi, I'm definitely a newbie to SQL 2005. I'm importing data from MS Access
> > > > > 2003 and several tables have a status of Error.
>
> > > > > The error messages in SQL 2000 DTS were helpful as they included table name,
> > > > > column name and description of data problem that caused the error. This
> > > > > enabled me to identify records that needed correcting.
>
> > > > > When using SSIS, is the somewhere I can refer to learn what the actual
> > > > > problem is as I find the list of errors in the report very unhelpful?
>
> > > > > Many thanks, Jonathan
>
> > > > One option is to create an Excel/Flat file/Csv destination for the
> > > > errors and add one of these destination controls to the error branch
> > > > of each SSIS control. That way you can determine at what point the
> > > > error is occurring. Also, you can add a dataviewer on the connections
> > > > between the SSIS controls (via right-clicking the connector and
> > > > selecting the add dataviewer option (not sure of the verbiage)). Also,
> > > > there are several other options available in the way of error
> > > > reporting and controls. Hope this helps.
>
> > > > Regards,
>
> > > > Enrique Martinez
> > > > Sr. Software Consultant
>
> > > Hi Enrique, thanks for you suggestions. How do you add an error branch?
>
> > > Jonathan- Hide quoted text -
>
> > > - Show quoted text -
>
> > Hi Jonathan,
>
> > It might help if you give more details about what you're trying to do
> > - there are a number of ways to deal with errors in SSIS :)
>
> > For starters, while in a Data Flow most tasks have an Error Output
> > (the red arrow coming from the task) that you can connect to an task
> > to handle errors, in the Control Flow all connections default to
> > OnSuccess (the green arrows coming from tasks) - these need to be
> > changed to OnError, OnCompletion, etc. as required.
> > You can also define Event Handlers for OnError events applying to
> > various points of the package.
>
> > So as you see - which method you use depends a lot on what you are
> > trying to achieve.
> > Good Luck
> > J
>
> Thanks J. I was hoping that it was going to be a simple case of stepping
> through the import wizard. Unfortunitely I now have the impression that I
> will have to learn how to use SSIS design tools (which is probably a good
> thing to do anyway...).
>
> Regards, Jonathan- Hide quoted text -
>
> - Show quoted text -

"jhofmeyr@googlemail.com" wrote:

> On Sep 21, 4:04 am, Jonathan <Jonat...@discussions.microsoft.com>
> wrote:
> > "jhofm...@googlemail.com" wrote:
> > > On Sep 20, 9:34 pm, Jonathan <Jonat...@discussions.microsoft.com>
> > > wrote:
> > > > "jhofm...@googlemail.com" wrote:
> > > > > On Sep 20, 4:28 am, Jonathan <Jonat...@discussions.microsoft.com>
> > > > > wrote:
> > > > > > "EMartinez" wrote:
> > > > > > > On Sep 19, 9:14 pm, Jonathan <Jonat...@discussions.microsoft.com>
> > > > > > > wrote:
> > > > > > > > Hi, I'm definitely a newbie to SQL 2005. I'm importing data from MS Access
> > > > > > > > 2003 and several tables have a status of Error.
> >
> > > > > > > > The error messages in SQL 2000 DTS were helpful as they included table name,
> > > > > > > > column name and description of data problem that caused the error. This
> > > > > > > > enabled me to identify records that needed correcting.
> >
> > > > > > > > When using SSIS, is the somewhere I can refer to learn what the actual
> > > > > > > > problem is as I find the list of errors in the report very unhelpful?
> >
> > > > > > > > Many thanks, Jonathan
> >
> > > > > > > One option is to create an Excel/Flat file/Csv destination for the
> > > > > > > errors and add one of these destination controls to the error branch
> > > > > > > of each SSIS control. That way you can determine at what point the
> > > > > > > error is occurring. Also, you can add a dataviewer on the connections
> > > > > > > between the SSIS controls (via right-clicking the connector and
> > > > > > > selecting the add dataviewer option (not sure of the verbiage)). Also,
> > > > > > > there are several other options available in the way of error
> > > > > > > reporting and controls. Hope this helps.
> >
> > > > > > > Regards,
> >
> > > > > > > Enrique Martinez
> > > > > > > Sr. Software Consultant
> >
> > > > > > Hi Enrique, thanks for you suggestions. How do you add an error branch?
> >
> > > > > > Jonathan- Hide quoted text -
> >
> > > > > > - Show quoted text -
> >
> > > > > Hi Jonathan,
> >
> > > > > It might help if you give more details about what you're trying to do
> > > > > - there are a number of ways to deal with errors in SSIS :)
> >
> > > > > For starters, while in a Data Flow most tasks have an Error Output
> > > > > (the red arrow coming from the task) that you can connect to an task
> > > > > to handle errors, in the Control Flow all connections default to
> > > > > OnSuccess (the green arrows coming from tasks) - these need to be
> > > > > changed to OnError, OnCompletion, etc. as required.
> > > > > You can also define Event Handlers for OnError events applying to
> > > > > various points of the package.
> >
> > > > > So as you see - which method you use depends a lot on what you are
> > > > > trying to achieve.
> > > > > Good Luck
> > > > > J
> >
> > > > Thanks J. I was hoping that it was going to be a simple case of stepping
> > > > through the import wizard. Unfortunitely I now have the impression that I
> > > > will have to learn how to use SSIS design tools (which is probably a good
> > > > thing to do anyway...).
> >
> > > > Regards, Jonathan- Hide quoted text -
> >
> > > > - Show quoted text -
> >
> > > Hi Jonathan,
> >
> > > Re-reading you post it is now apparent that you were simply running
> > > the import wizard, not developing the packages yourself .. sorry for
> > > the misunderstanding :)
> >
> > > If you are doing a simple import it should be as easy as running the
> > > import wizard. You say the error message you're getting isn't very
> > > helpful - what exactly is it saying? Also - are you trying to import
> > > multiple tables, or just 1 table? Are you importing into existing
> > > tables, or letting the wizard generate the tables for you?
> >
> > > One thing you could try is to save the package created by the wizard
> > > instead of choosing the "run now" option - this would fast-track the
> > > package creation and also provide you with a base package to look at
> > > while you're familiarizing yourself with the tools.
> >
> > > If you decide to delve deeping into the capabilities of SSIS, the MSDN
> > > website has lots of useful information. I'd recommend starting out
> > > with the Project Real case study. While it may be a bit dated now (it
> > > was done on the beta release iirc), it has loads of useful hints and
> > > best-practice suggestions.
> >
> > > Good Luck!
> > > J
> >
> > Hi J. I was doing the same thing that I did with sql2000 DTS. That is I was
> > attempting to import every table in the MS Access database into an existing
> > SQL database. When going from the step to select tables, the system message
> > informed me that this was too many. There did not seem to be an optimum
> > number (apart from 1!). Sometimes 64 tables were successfully imported and
> > other times only 5. In the end I found that the tables prompting an error
> > message could be successfully imported when imported individually. There has
> > got to be a better way to import a large number of tables in one go.
> >
> > Thanks for the learning tips. I will apply these as the problem that I'm
> > having is likely to be from my lack of knowledge.
> >
> > Many thanks, Jonathan- Hide quoted text -
> >
> > - Show quoted text -
>
> Hmm - sounds a bit strange. I have often used the import wizard to
> migrate data from multiple tables in one SQL server DB to another
> without any trouble - but haven't done so from Access. I'll keep my
> eyes and ears open and let you know if I run into similar issues
> importing data from Access.
>
> Good luck finding a solution.
> J
>
>