How to make SSIS 2005 work like DTS 2000?

Hi!
Is it possible to make data flow in SQL Server 2005 Integration Services
looks like in SQL Server 2000 DTS?
I need to acive 2 goals:
1. If bcp fails on INSERT statement, for example, on primary key validation,
it must go on to the next row, but not to break SQL threat and stop.
2. I need log for failed errors. Not the log about which process failed, but
just the error source line.
And the final question: in SQL Server 2000 it was no probem to do it, so why
it has been removed from SQL Server 2005?
Thank you
Igor A. ChechetHi,
Igor A. Chechet wrote:
> Is it possible to make data flow in SQL Server 2005 Integration Services
> looks like in SQL Server 2000 DTS?
> I need to acive 2 goals:
> 1. If bcp fails on INSERT statement, for example, on primary key validatio
n,
> it must go on to the next row, but not to break SQL threat and stop.
> 2. I need log for failed errors. Not the log about which process failed, b
ut
> just the error source line.
>
your goals are easy to achieve with SIIS, too:
You have to open the editor of the task producing the error and
hit the "configure error output" button. There you have three
options:
- fail component
default, causes the package to stop
- ignore error
errors are ignored (see goal 1)
- redirect row
data is redirected to the error output (red arrow)
you can attach another Data Flow Destination component here
to write the failed rows to another table (see goal 2)
In the books online there's a tutorial for SIIS which shows the
most important features like logging, variables and configuration
files.
HTH,
Gerald|||Gerald, thank you for your advise!
I think that your suported method will help, but the problem is that the
error occured on the SQL Server Destination Task, and that's not possible to
create error output for it. BCP fails always on this task. Any ideas how to
handle it?
Thank you
Igor A. Chechet
"Gerald Aichholzer" <gerald.aichholzer@.gmx.net> '?/'' ? ''
'?: news:eYFYlFRpGHA.504@.TK2MSFTNGP05.phx.gbl...
> Hi,
> Igor A. Chechet wrote:
> your goals are easy to achieve with SIIS, too:
> You have to open the editor of the task producing the error and
> hit the "configure error output" button. There you have three
> options:
> - fail component
> default, causes the package to stop
> - ignore error
> errors are ignored (see goal 1)
> - redirect row
> data is redirected to the error output (red arrow)
> you can attach another Data Flow Destination component here
> to write the failed rows to another table (see goal 2)
> In the books online there's a tutorial for SIIS which shows the
> most important features like logging, variables and configuration
> files.
>
> HTH,
> Gerald|||Hi Igor,
Igor A. Chechet wrote:
> Gerald, thank you for your advise!
> I think that your suported method will help, but the problem is that the
> error occured on the SQL Server Destination Task, and that's not possible
to
> create error output for it. BCP fails always on this task. Any ideas how t
o
> handle it?
>
I'm not quite sure about this (I started with SIIS only two weeks ago),
but AFAIR you have to disable bulk insert to be able to create error
output. I think you have to select "table or view" (without "fast load")
in "data access mode".
HTH,
Gerald|||to log the rows in error, you have to disable the fast load option (like in
SQL 2000)
but this greatly slowdown the loading process.
regarding the size of the file you have to load, it could be bettter to load
the file into a temporary table (with no PK constraint)
then execute some SQL statement to identify duplicated rows and remove these
duplicated (or moving these rows into another table)
another option is to try to play with the sort task in the dataflow (there
is an option to remove duplicated rows)
or the aggregate flow to count the rows in double, then split the stream
into 2 streams (1 with unique rows (count=1), 1 with duplicated rows
(count>1))
finally the stream 1 with the unique rows will be inserted into the
destination using the fast load option and duplicated rows moved to the
error log table.
and another option...
simply insert the rows using the fast load option and use only 1 transaction
(no batch size)
if the insertion failed, then load again the rows but with a destination
where the fastload option is disabled and where the error output can be
used.
(solution not tested)
"Igor A. Chechet" <ichechet@.mail.ru> wrote in message
news:eMksqaPpGHA.4424@.TK2MSFTNGP05.phx.gbl...
> Hi!
> Is it possible to make data flow in SQL Server 2005 Integration Services
> looks like in SQL Server 2000 DTS?
> I need to acive 2 goals:
> 1. If bcp fails on INSERT statement, for example, on primary key
> validation, it must go on to the next row, but not to break SQL threat and
> stop.
> 2. I need log for failed errors. Not the log about which process failed,
> but just the error source line.
> And the final question: in SQL Server 2000 it was no probem to do it, so
> why it has been removed from SQL Server 2005?
> Thank you
> Igor A. Chechet
>

How to make SSIS 2005 work like DTS 2000?

Hi!
Is it possible to make data flow in SQL Server 2005 Integration Services
looks like in SQL Server 2000 DTS?
I need to acive 2 goals:
1. If bcp fails on INSERT statement, for example, on primary key validation,
it must go on to the next row, but not to break SQL threat and stop.
2. I need log for failed errors. Not the log about which process failed, but
just the error source line.
And the final question: in SQL Server 2000 it was no probem to do it, so why
it has been removed from SQL Server 2005?
Thank you
Igor A. ChechetHi,
Igor A. Chechet wrote:
> Is it possible to make data flow in SQL Server 2005 Integration Services
> looks like in SQL Server 2000 DTS?
> I need to acive 2 goals:
> 1. If bcp fails on INSERT statement, for example, on primary key validation,
> it must go on to the next row, but not to break SQL threat and stop.
> 2. I need log for failed errors. Not the log about which process failed, but
> just the error source line.
>
your goals are easy to achieve with SIIS, too:
You have to open the editor of the task producing the error and
hit the "configure error output" button. There you have three
options:
- fail component
default, causes the package to stop
- ignore error
errors are ignored (see goal 1)
- redirect row
data is redirected to the error output (red arrow)
you can attach another Data Flow Destination component here
to write the failed rows to another table (see goal 2)
In the books online there's a tutorial for SIIS which shows the
most important features like logging, variables and configuration
files.
HTH,
Gerald|||Gerald, thank you for your advise!
I think that your suported method will help, but the problem is that the
error occured on the SQL Server Destination Task, and that's not possible to
create error output for it. BCP fails always on this task. Any ideas how to
handle it?
Thank you
Igor A. Chechet
"Gerald Aichholzer" <gerald.aichholzer@.gmx.net> '?/'' ? ''
'?: news:eYFYlFRpGHA.504@.TK2MSFTNGP05.phx.gbl...
> Hi,
> Igor A. Chechet wrote:
>> Is it possible to make data flow in SQL Server 2005 Integration Services
>> looks like in SQL Server 2000 DTS?
>> I need to acive 2 goals:
>> 1. If bcp fails on INSERT statement, for example, on primary key
>> validation, it must go on to the next row, but not to break SQL threat
>> and stop.
>> 2. I need log for failed errors. Not the log about which process failed,
>> but just the error source line.
> your goals are easy to achieve with SIIS, too:
> You have to open the editor of the task producing the error and
> hit the "configure error output" button. There you have three
> options:
> - fail component
> default, causes the package to stop
> - ignore error
> errors are ignored (see goal 1)
> - redirect row
> data is redirected to the error output (red arrow)
> you can attach another Data Flow Destination component here
> to write the failed rows to another table (see goal 2)
> In the books online there's a tutorial for SIIS which shows the
> most important features like logging, variables and configuration
> files.
>
> HTH,
> Gerald|||Hi Igor,
Igor A. Chechet wrote:
> Gerald, thank you for your advise!
> I think that your suported method will help, but the problem is that the
> error occured on the SQL Server Destination Task, and that's not possible to
> create error output for it. BCP fails always on this task. Any ideas how to
> handle it?
>
I'm not quite sure about this (I started with SIIS only two weeks ago),
but AFAIR you have to disable bulk insert to be able to create error
output. I think you have to select "table or view" (without "fast load")
in "data access mode".
HTH,
Gerald|||to log the rows in error, you have to disable the fast load option (like in
SQL 2000)
but this greatly slowdown the loading process.
regarding the size of the file you have to load, it could be bettter to load
the file into a temporary table (with no PK constraint)
then execute some SQL statement to identify duplicated rows and remove these
duplicated (or moving these rows into another table)
another option is to try to play with the sort task in the dataflow (there
is an option to remove duplicated rows)
or the aggregate flow to count the rows in double, then split the stream
into 2 streams (1 with unique rows (count=1), 1 with duplicated rows
(count>1))
finally the stream 1 with the unique rows will be inserted into the
destination using the fast load option and duplicated rows moved to the
error log table.
and another option...
simply insert the rows using the fast load option and use only 1 transaction
(no batch size)
if the insertion failed, then load again the rows but with a destination
where the fastload option is disabled and where the error output can be
used.
(solution not tested)
"Igor A. Chechet" <ichechet@.mail.ru> wrote in message
news:eMksqaPpGHA.4424@.TK2MSFTNGP05.phx.gbl...
> Hi!
> Is it possible to make data flow in SQL Server 2005 Integration Services
> looks like in SQL Server 2000 DTS?
> I need to acive 2 goals:
> 1. If bcp fails on INSERT statement, for example, on primary key
> validation, it must go on to the next row, but not to break SQL threat and
> stop.
> 2. I need log for failed errors. Not the log about which process failed,
> but just the error source line.
> And the final question: in SQL Server 2000 it was no probem to do it, so
> why it has been removed from SQL Server 2005?
> Thank you
> Igor A. Chechet
>sql

how to loop through several excel sheets in a file in integration services

Hello, I'm new at Integration services and I have an excel file with information in several worksheets. I want to loop through some specific sheets to retrieve the data and save it in a database table. I know how to retrieve the data from one sheet, but I don't know how to do it for several sheets. Any ideas?...I would appreciate any help.

Moving thread to the Intergration Services forum. They may be able to help out.

|||

Trisha1802 wrote:

Hello, I'm new at Integration services and I have an excel file with information in several worksheets. I want to loop through some specific sheets to retrieve the data and save it in a database table. I know how to retrieve the data from one sheet, but I don't know how to do it for several sheets. Any ideas?...I would appreciate any help.

Unfortunately its not possible to loop through sheets unless you know the names of them and you know that they won't change. If those conditions are satisfied then you can use the ForEach item enumerator in the Foreach Loop.

An Excel Sheet enumerator would be a nice addition. Perhaps you could request it at Microsoft Connect?

-Jamie

|||

Interesting situation. In SSIS Excel Source BOL section (http://msdn2.microsoft.com/en-us/library/ms141683.aspx) there is a link "HOWTO: Use ADO with Excel Data from Visual Basic or VBA" that has some sample code to "Enumerate Tables and Fields and Their Properties". I wonder if you could use some of that code inside of a script task to loop through all the sheet in your file and write their name in a flat file. Then it would be pretty easy to use a ForEach loop container with a data flow that connects to the excel file and runs a query like 'Select * from <SSIS variable with the sheet name>'

I have no idea how to write such script component and don't know how feasible that could be; so you do the research.

Rafael Salas

|||

Thank you very much for your reply Jamie.

I haven't used the Foreach item enumerator...could you please explain to me a little bit more how to pass the sheet name to the container?. I saw that I have to add some columns and I have the ones I need...but I don't know how to do the next part.

|||Is there a link where I can see the use of the Foreach item enumerator?|||

Trisha1802 wrote:

Thank you very much for your reply Jamie.

I haven't used the Foreach item enumerator...could you please explain to me a little bit more how to pass the sheet name to the container?. I saw that I have to add some columns and I have the ones I need...but I don't know how to do the next part.

You have to type them in at design-time basically. That's why I said you need to know them. Its not particularly clever or dynamic but it'll work.

-Jamie

|||

Just to add my 2cents to the request list -- I am trying to export from SQL Server to a single workbook with about 100 worksheets. I was hoping to be able to loop through a two-column recordset from a static SQL Server table with the names of the worksheets and the SQL command to extract/export data into each worksheet. The name and path of the workbook remain constant. However, it appears that the "Excel Destination" Data Flow does not accept variables for either the name of the worksheet (or workbook). That means I have to hard-code everything related to the export to 100 worksheets/workbooks. I was hoping to avoid needing to create 100 little export file tasks in the package, but that appears to be my only recourse. Any other ideas?

-Andre Chan

|||

You don't need to create 100 litle exports; you need to change the data access mode of your Excel Destination component to 'Table Name or View Name variable". Then you need to use a SSIS variable to hold the Excel sheet name you want to write into. You can use a forErachLoop container in the control flow to go over the 100 sheet names.

Rafael Salas

|||

To clarify a bit more -- within my For Each loop container, I could always use Excel Destination task to write to a single "dummy" filename, and then use the File System task within the same For Each loop container to re-name the file to whatever I want (using my variable?). In this case, I end up with 100 workbooks rather than 1 workbook with 100 worksheets, but that is better than nothing. Note that I need these worksheets because they are referenced via VLOOKUP from another well-formatted workbook.

The bigger problem I am having now is trying to populate these workbook/worksheets using a dynamic variable SQL command. I want to use a single stored procedure with specified parameters to fill each workbook/worksheet, to make it easier to manage in the database. These SQL commands are loaded into a SQL Server table. It seems built-in options in SSIS are to use a specific pre-defined, pre-created view or table name. But in this case then I need to create 100 different tables or views in the database, and that is what I am trying to avoid, because editing the logic would require updating all 100 views (and perhaps creating/updating all 100 tasks in the package if the view/table names change).

-Andre Chan

|||

Andre Chan wrote:

To clarify a bit more -- within my For Each loop container, I could always use Excel Destination task to write to a single "dummy" filename, and then use the File System task within the same For Each loop container to re-name the file to whatever I want (using my variable?). In this case, I end up with 100 workbooks rather than 1 workbook with 100 worksheets, but that is better than nothing. Note that I need these worksheets because they are referenced via VLOOKUP from another well-formatted workbook.

The bigger problem I am having now is trying to populate these workbook/worksheets using a dynamic variable SQL command. I want to use a single stored procedure with specified parameters to fill each workbook/worksheet, to make it easier to manage in the database. These SQL commands are loaded into a SQL Server table. It seems built-in options in SSIS are to use a specific pre-defined, pre-created view or table name. But in this case then I need to create 100 different tables or views in the database, and that is what I am trying to avoid, because editing the logic would require updating all 100 views (and perhaps creating/updating all 100 tasks in the package if the view/table names change).

-Andre Chan

I see the difference now. I still think you can write all inside of a single workbook, and you are very close to make it. So, if you already succeed looping through the 100 data sets; then trick I think is to make the package to write into the same file but different sheet each time. For that, try to create the 100 sheets in your excel destination file; add a variable to the package called, let's say DestinationSheet, and for each iteration change the value of that variable to one of the 100 sheet your destination file has (according with the set of data you are processing of course).You will not use the FileSystemTask to rename any files; because you are writing into a single file.

Rafael Salas

|||

Thanks, I did get my package finally to run, but I do have one last problem specified below in the last paragraph. As you might expect, my problems were somewhat "trivial" and/or only remotely related. First, I had to specify the worksheet name with $ attached at the end. Perhaps because I had renamed the worksheets within the workbook along the way? That's a software artifact that would be nice to clear up, as there was no helpful error message provided by SSIS, only that the "database" could not be found. That's where I got confused by the wording of the error message -- it never said "worksheet". Second, my source table data had columns specified as "varchar" but Excel Destination task widget only accepts "nvarchar". Changing the datatypes in the source columns to nvarchar fixed that problem. At least SSIS did give an obliquely useful error message in this case (but didn't explicitly tell me to go change the source table datatypes, only that varchar and nvarchar did not "match").

There was also a bit of necessary work-around because, within the ForEach container, in order to specify the source column mappings returned via my stored procedure from the "SQLCommand" variable, or for Excel to recognize its worksheet name/column mappings via its "WorksheetName" variable, I had to pre-define default values for the variables. It would have been nice (but quite advanced) for SSIS to anticipate the first row from the initial SQL recordset that fills the ForEach command.

I have one last problem. If the Excel worksheet already contains data, SSIS appears to be insert new rows for the data (or append it to the existing data -- I haven't figured that out yet). Is there any way to get SSIS to clear the worksheet before dumping data into the worksheet. For example, when setting up a PivotTable or other query from within Excel itself, there are multiple options for completely clearing the existing data first, inserting new rows and clearing rows, or just inserting new rows. I would just like the data cleared, because I don't want to have any VLOOKUP references to this spreadsheet to get corrupted. Rright now, each worksheet has a few hundred rows, but the VLOOKUP references rows 2 through 10000 (ignoring header row), just to ensure it gets the whole data range.

Thanks,

Andre

|||

The last bit of pre-processing I want to do in Excel, I know how to do in an Excel macro, but not in SSIS. Can anyone adapt this into an SSIS Script task? Here is the macro code snippet:

Sub ClearWorksheets()

With Application

.Calculation = xlManual

.EnableEvents = False

Worksheets("MyData").Range("A:H").Clear

.EnableEvents = True

.Calculation = xlAutomatic

End With

End Sub

After the range is cleared, then I can fill that same range. Ideally, the worksheet name and workbook name would both come from a variable.

Thanks,

Andre

|||

Andre,

I actually don't know if SSIS can 'delete' or 'truncate' an excel sheet; What i know though, is SSIS is accessing the excel file using and OLE DB provider for Microsoft Jet; so each sheet is treated as table. With this said; do yourself a favor, research arround Microsoft Jet 4.0, its OLE DB provider and may be you will see your options around what you want to acomplish. that may help you also to understand the limitations of the data types.

If truncating or deleting the existing rows before loading the new data is not possible; you could have an empty excel file with the required structure in a specifc location and then you just create a new copy to be used as the target every time the package runs.

I hope this takes you one step further on all this.

Rafael Salas

|||

Hi Rafael,

Trying to use a DELETE statement against the Excel file yields an error message (the syntax varies based on where I click OK), but generally all say something to the effect of "Deleting data in a linked table is not supported by this ISAM. (Microsoft JET Database Engine)".

So my only recourse seems to be, as you suggested, to (1) create a "blank" Excel template with all the worksheets I need, (2) when my package runs, create a working copy of the template, (3) fill the copy with data on each worksheet, (4) archive the "published" Excel file, if it exists, and (5) copy/rename the working copy to the published filename.

Thanks for your comments, your support and encouragement are appreciated; and if any Microsoft people are following this, it would be nice to address all of the SSIS to Excel issues raised in this thread.

Regards,

Andre Chan

how to loop through several excel sheets in a file in integration services

Hello, I'm new at Integration services and I have an excel file with information in several worksheets. I want to loop through some specific sheets to retrieve the data and save it in a database table. I know how to retrieve the data from one sheet, but I don't know how to do it for several sheets. Any ideas?...I would appreciate any help.

Moving thread to the Intergration Services forum. They may be able to help out.

|||

Trisha1802 wrote:

Hello, I'm new at Integration services and I have an excel file with information in several worksheets. I want to loop through some specific sheets to retrieve the data and save it in a database table. I know how to retrieve the data from one sheet, but I don't know how to do it for several sheets. Any ideas?...I would appreciate any help.

Unfortunately its not possible to loop through sheets unless you know the names of them and you know that they won't change. If those conditions are satisfied then you can use the ForEach item enumerator in the Foreach Loop.

An Excel Sheet enumerator would be a nice addition. Perhaps you could request it at Microsoft Connect?

-Jamie

|||

Interesting situation. In SSIS Excel Source BOL section (http://msdn2.microsoft.com/en-us/library/ms141683.aspx) there is a link "HOWTO: Use ADO with Excel Data from Visual Basic or VBA" that has some sample code to "Enumerate Tables and Fields and Their Properties". I wonder if you could use some of that code inside of a script task to loop through all the sheet in your file and write their name in a flat file. Then it would be pretty easy to use a ForEach loop container with a data flow that connects to the excel file and runs a query like 'Select * from <SSIS variable with the sheet name>'

I have no idea how to write such script component and don't know how feasible that could be; so you do the research.

Rafael Salas

|||

Thank you very much for your reply Jamie.

I haven't used the Foreach item enumerator...could you please explain to me a little bit more how to pass the sheet name to the container?. I saw that I have to add some columns and I have the ones I need...but I don't know how to do the next part.

|||Is there a link where I can see the use of the Foreach item enumerator?|||

Trisha1802 wrote:

Thank you very much for your reply Jamie.

I haven't used the Foreach item enumerator...could you please explain to me a little bit more how to pass the sheet name to the container?. I saw that I have to add some columns and I have the ones I need...but I don't know how to do the next part.

You have to type them in at design-time basically. That's why I said you need to know them. Its not particularly clever or dynamic but it'll work.

-Jamie

|||

Just to add my 2cents to the request list -- I am trying to export from SQL Server to a single workbook with about 100 worksheets. I was hoping to be able to loop through a two-column recordset from a static SQL Server table with the names of the worksheets and the SQL command to extract/export data into each worksheet. The name and path of the workbook remain constant. However, it appears that the "Excel Destination" Data Flow does not accept variables for either the name of the worksheet (or workbook). That means I have to hard-code everything related to the export to 100 worksheets/workbooks. I was hoping to avoid needing to create 100 little export file tasks in the package, but that appears to be my only recourse. Any other ideas?

-Andre Chan

|||

You don't need to create 100 litle exports; you need to change the data access mode of your Excel Destination component to 'Table Name or View Name variable". Then you need to use a SSIS variable to hold the Excel sheet name you want to write into. You can use a forErachLoop container in the control flow to go over the 100 sheet names.

Rafael Salas

|||

To clarify a bit more -- within my For Each loop container, I could always use Excel Destination task to write to a single "dummy" filename, and then use the File System task within the same For Each loop container to re-name the file to whatever I want (using my variable?). In this case, I end up with 100 workbooks rather than 1 workbook with 100 worksheets, but that is better than nothing. Note that I need these worksheets because they are referenced via VLOOKUP from another well-formatted workbook.

The bigger problem I am having now is trying to populate these workbook/worksheets using a dynamic variable SQL command. I want to use a single stored procedure with specified parameters to fill each workbook/worksheet, to make it easier to manage in the database. These SQL commands are loaded into a SQL Server table. It seems built-in options in SSIS are to use a specific pre-defined, pre-created view or table name. But in this case then I need to create 100 different tables or views in the database, and that is what I am trying to avoid, because editing the logic would require updating all 100 views (and perhaps creating/updating all 100 tasks in the package if the view/table names change).

-Andre Chan

|||

Andre Chan wrote:

To clarify a bit more -- within my For Each loop container, I could always use Excel Destination task to write to a single "dummy" filename, and then use the File System task within the same For Each loop container to re-name the file to whatever I want (using my variable?). In this case, I end up with 100 workbooks rather than 1 workbook with 100 worksheets, but that is better than nothing. Note that I need these worksheets because they are referenced via VLOOKUP from another well-formatted workbook.

The bigger problem I am having now is trying to populate these workbook/worksheets using a dynamic variable SQL command. I want to use a single stored procedure with specified parameters to fill each workbook/worksheet, to make it easier to manage in the database. These SQL commands are loaded into a SQL Server table. It seems built-in options in SSIS are to use a specific pre-defined, pre-created view or table name. But in this case then I need to create 100 different tables or views in the database, and that is what I am trying to avoid, because editing the logic would require updating all 100 views (and perhaps creating/updating all 100 tasks in the package if the view/table names change).

-Andre Chan

I see the difference now. I still think you can write all inside of a single workbook, and you are very close to make it. So, if you already succeed looping through the 100 data sets; then trick I think is to make the package to write into the same file but different sheet each time. For that, try to create the 100 sheets in your excel destination file; add a variable to the package called, let's say DestinationSheet, and for each iteration change the value of that variable to one of the 100 sheet your destination file has (according with the set of data you are processing of course).You will not use the FileSystemTask to rename any files; because you are writing into a single file.

Rafael Salas

|||

Thanks, I did get my package finally to run, but I do have one last problem specified below in the last paragraph. As you might expect, my problems were somewhat "trivial" and/or only remotely related. First, I had to specify the worksheet name with $ attached at the end. Perhaps because I had renamed the worksheets within the workbook along the way? That's a software artifact that would be nice to clear up, as there was no helpful error message provided by SSIS, only that the "database" could not be found. That's where I got confused by the wording of the error message -- it never said "worksheet". Second, my source table data had columns specified as "varchar" but Excel Destination task widget only accepts "nvarchar". Changing the datatypes in the source columns to nvarchar fixed that problem. At least SSIS did give an obliquely useful error message in this case (but didn't explicitly tell me to go change the source table datatypes, only that varchar and nvarchar did not "match").

There was also a bit of necessary work-around because, within the ForEach container, in order to specify the source column mappings returned via my stored procedure from the "SQLCommand" variable, or for Excel to recognize its worksheet name/column mappings via its "WorksheetName" variable, I had to pre-define default values for the variables. It would have been nice (but quite advanced) for SSIS to anticipate the first row from the initial SQL recordset that fills the ForEach command.

I have one last problem. If the Excel worksheet already contains data, SSIS appears to be insert new rows for the data (or append it to the existing data -- I haven't figured that out yet). Is there any way to get SSIS to clear the worksheet before dumping data into the worksheet. For example, when setting up a PivotTable or other query from within Excel itself, there are multiple options for completely clearing the existing data first, inserting new rows and clearing rows, or just inserting new rows. I would just like the data cleared, because I don't want to have any VLOOKUP references to this spreadsheet to get corrupted. Rright now, each worksheet has a few hundred rows, but the VLOOKUP references rows 2 through 10000 (ignoring header row), just to ensure it gets the whole data range.

Thanks,

Andre

|||

The last bit of pre-processing I want to do in Excel, I know how to do in an Excel macro, but not in SSIS. Can anyone adapt this into an SSIS Script task? Here is the macro code snippet:

Sub ClearWorksheets()

With Application

.Calculation = xlManual

.EnableEvents = False

Worksheets("MyData").Range("A:H").Clear

.EnableEvents = True

.Calculation = xlAutomatic

End With

End Sub

After the range is cleared, then I can fill that same range. Ideally, the worksheet name and workbook name would both come from a variable.

Thanks,

Andre

|||

Andre,

I actually don't know if SSIS can 'delete' or 'truncate' an excel sheet; What i know though, is SSIS is accessing the excel file using and OLE DB provider for Microsoft Jet; so each sheet is treated as table. With this said; do yourself a favor, research arround Microsoft Jet 4.0, its OLE DB provider and may be you will see your options around what you want to acomplish. that may help you also to understand the limitations of the data types.

If truncating or deleting the existing rows before loading the new data is not possible; you could have an empty excel file with the required structure in a specifc location and then you just create a new copy to be used as the target every time the package runs.

I hope this takes you one step further on all this.

Rafael Salas

|||

Hi Rafael,

Trying to use a DELETE statement against the Excel file yields an error message (the syntax varies based on where I click OK), but generally all say something to the effect of "Deleting data in a linked table is not supported by this ISAM. (Microsoft JET Database Engine)".

So my only recourse seems to be, as you suggested, to (1) create a "blank" Excel template with all the worksheets I need, (2) when my package runs, create a working copy of the template, (3) fill the copy with data on each worksheet, (4) archive the "published" Excel file, if it exists, and (5) copy/rename the working copy to the published filename.

Thanks for your comments, your support and encouragement are appreciated; and if any Microsoft people are following this, it would be nice to address all of the SSIS to Excel issues raised in this thread.

Regards,

Andre Chan

sql

How to load .dbf files in Sql Server 2005

hi

i have a dbf file which i need to import in sql server 2005. \

anyone having any idea to import these files either through integration services or any other tool ?

SSIS will do it. Please search this forum for "dbf" and you'll get plenty of results/examples.|||

Hi Salman,

To add to what Phil said, you will want to use the FoxPro and Visual FoxPro OLE DB data provider. The download link has changed in the past few months. You can find it at http://msdn2.microsoft.com/en-us/vfoxpro/bb190232.aspx.