Exporting multiple tables as flat files

Hi.

I've tried to create a SSIS package to simply export a bunch of tables as flat files, and am having troubles because when the for each loop hits the second table the column mappings in the flat file destination are not synchronised with its schema.

I created a for each loop with an enumerator that returns the table names and sets a user variable.

I created a data flow task which dynamically connects to the table name variable.

In the Flat File Destination there is a column mapping property, but I don't know how to reset these mappings on each iteration.

Any ideas?

Are all the tables you are trying to export have exactly same column metadata? If not, you can't do this using a for loop. This is because SSIS cannot dynamically update the column mapping/schema. If the tables have different metadata, you need to create separate data flow tasks for them.|||Thanks.

Exporting data to Flat File

I'm using SSIS package to export some data to a comma delimited CSV file. The problem is that some of the fields have commas in them. Is there a way to deal with this other to changing the delimiter?

It depends on your requirements. As I see it you have 2 options:

1) Change your delimiter

2) Change the commas in the data to something else.

-Jamie

|||

Use Text Qualifiers (for ex. double quotes ") when you export the data.
Each field will be enclosed within double quotes.

Thanks,
Loonysan

Exporting data to excel from T-SQL Resultset

I have started just using SSIS. I needed to transfer result set from SQL statements like SELECT * FROM Employee to excel spreadsheet. One way of getting this is using import and export wizard from management studio.

As my sql statement is complex joining many tables. I am wondering wheteher there are other best ways to deal such type of transformations.

I am sure this is very simple question but yours feedback will be much appreciated.

You can use a SQL statement in an OLE DB source and then you can hook that OLE DB source up to an Excel destination.|||

Notice that the Excel file must exists by the time the dataflow is reached and that if there is data on it; the new execution will append the new rows at the end of the existing ones; but youc an create a new excel file on each run (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=941982&SiteID=1)

There ahve been interesting discussion on this forum about diffrent escenarios involvong excel destination and source. make sure you use the search option at the top of this page

sql

Exporting data to Excel files

Hi everybody, i'm new to SSIS, so it's possible that mine is a very stupid question

I have to develop a simple ETL package that reads data from a csv file and writes them to an xls file; the problem is that when the number of rows exceeds the maximum number of rows allowed for an xls file i get an error.

There is a way to solve this problem? for example adding a new sheet or creating a new file?

Thanks in advance

You can do this, but you'll have to do a little extra work. Basically, you need to preprocess your CSV to get the number of rows that will fit in a sheet, write them, then process the next set.

These posts might help:

http://blogs.conchango.com/jamiethomson/archive/2005/12/04/SSIS-Nugget_3A00_-Splitting-a-file-into-multiple-files.aspx

http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-_116683388696570741.html

|||I don't see any other solution than create multiple sheets/files. That is an Excel limititaion, nothing that SSIS can do about it.|||seems like it may works, thanks!

Exporting Data From SSIS Package to Excel

I currently have a export that takes data from my SQL Server 2005 DB and exports it into Excel. This process works correctly. My excel template has the first row headers and the data is dumped in the row after the header. I would like to know if it is possible for me to add borders around my data without doing it within the template? I don't know how much data is going to be exported so I can't put borders within the template. I put borders around the headers to see if it will copy the formatting down to the data and it didn't. Thank You for any help.

I do not think you can do this using SSIS.

Thanks.

|||

I hope that isn't true I really have to find a way around that. Why does Microsoft limit the access to the Office Components being automated?

|||

Jerry8989 wrote:

Why does Microsoft limit the access to the Office Components being automated?

It doesn't, you just have to work a little harder.

http://sqljunkies.com/WebLog/knight_reign/archive/2005/07/07/16018.aspx
http://msdn2.microsoft.com/en-us/library/ms136007.aspx

I think automation is the only way you'll get this accomplished.
|||

Jerry8989 wrote:

I hope that isn't true I really have to find a way around that. Why does Microsoft limit the access to the Office Components being automated?

Well, for one SSIS is an ETL tool and as such isn't designed to be making fancy looking excel sheets, powerpoint presentations, etc... It's designed to read data, transform it, and load it somewhere. So the fact that you need pretty looking visual effects in your excel sheet, doesn't really warrant the need for that feature in SSIS.

Exporting a table with changing column names to an excel file

I'm trying to write an SSIS package that exports a table that has changing column names to an excel file. The column names change due to the fact that the table is created by a pivot daily. the only thing I'm missing is the ability to dynamically map the tables' columns to the excel destination. Is this possible?

I read in another thread that

"It is not possible to create packages or new objects within packages using SSIS."

I also read in the books online that "The input and the input columns of the Excel destination have no custom

properties." To me this means that I cannot programmatically create or remove columns in the excel destination. Please tell me I'm wrong.

So, to summarize my research so far. In writing an SSIS package, I cannot programmatically create a new excel destination object and I can't manipulate an existing one. I hope I'm wrong. Can anyone help me? (and please correct any wrong assumptions I may have stated)

I'm interested in finding this out as well. I need to basically take a dataset and cast it out to multiple pivot tables in Excel. If you find anything out about this, or find that you can't manage it; please let me know. Feel free to email!

thx

|||

It is not possible for a package to dynamically alter its pipeline(s) at execution-time. The metadata of the pipeline is set at design-time.

There are workarounds to this - but they're difficult. I have talked a little about this subject here:

But it used to work in DTS (1) - Modifing a Package in script
(http://blogs.conchango.com/jamiethomson/archive/2006/06/28/4156.aspx)

-Jamie

sql

Exporting a table with changing column names to an excel file

I'm trying to write an SSIS package that exports a table that has changing column names to an excel file. The column names change due to the fact that the table is created by a pivot daily. the only thing I'm missing is the ability to dynamically map the tables' columns to the excel destination. Is this possible?

I read in another thread that

"It is not possible to create packages or new objects within packages using SSIS."

I also read in the books online that "The input and the input columns of the Excel destination have no custom

properties." To me this means that I cannot programmatically create or remove columns in the excel destination. Please tell me I'm wrong.

So, to summarize my research so far. In writing an SSIS package, I cannot programmatically create a new excel destination object and I can't manipulate an existing one. I hope I'm wrong. Can anyone help me? (and please correct any wrong assumptions I may have stated)

I'm interested in finding this out as well. I need to basically take a dataset and cast it out to multiple pivot tables in Excel. If you find anything out about this, or find that you can't manage it; please let me know. Feel free to email!

thx

|||

It is not possible for a package to dynamically alter its pipeline(s) at execution-time. The metadata of the pipeline is set at design-time.

There are workarounds to this - but they're difficult. I have talked a little about this subject here:

But it used to work in DTS (1) - Modifing a Package in script
(http://blogs.conchango.com/jamiethomson/archive/2006/06/28/4156.aspx)

-Jamie

ExportConfigurationFile

Can any SSIS guru please shed some light on this issue?

Here is a posting that I am running into.

http://sqljunkies.com/Forums/Search/default.aspx?SearchFor=1&SearchText=neetash

Your comments are appreciated.

Steve

Any help please ?|||Can any expert or Microsoft SSIS team members please respond to this ?

Export to Excel Range

I have an SSIS package that exports data to a named range in Excel, except that instead of putting the data in the named range, it adds the first row of data to the first row below the range. The spreadsheet is not protected. Why doesn't the data get put into the named range?

When I'm done testing the SSIS package, I want to protect the spreadsheet and enable edit for the named range so that data can be written using the SSIS package. Will enabling the named range be necessary?

Dan

Dan:

I have not used named ranges in particular to populate data, but in general, when you use Excel as the destination you do not have the option to "overwrite" the data as you do in a Flat File destination. The problem you are experiencing could be because your package is updating the same Excel file during each run.

Does it always insert a new row one row below the named range? What happens when you run the package 2 or 3 times; each time it should be inserting new records at the n+1th row.

The issue of populating Excel destination has been discussed in previous threads and Rafael Salas apparently as a solution for it. I am not sure if his solution addresses populating Named Ranges particularly.

Rafael has a blog at blogspot.com, and is also listed in the Top Answerer box. When you click on his name it provides details of his profile.

|||The first step in the SSIS package is to execute a file server component that copies and renames a spreadsheet template to the spreadsheet I'm trying to populate. I've deleted the data range and removed any read-only settings. What has worked is to create and use a header range which then populates data in the first row immediately following. Thanks for the response and reference to Rafael Salas.

Export to Excel Range

I have an SSIS package that exports data to a named range in Excel, except that instead of putting the data in the named range, it adds the first row of data to the first row below the range. The spreadsheet is not protected. Why doesn't the data get put into the named range?

When I'm done testing the SSIS package, I want to protect the spreadsheet and enable edit for the named range so that data can be written using the SSIS package. Will enabling the named range be necessary?

Dan

Dan:

I have not used named ranges in particular to populate data, but in general, when you use Excel as the destination you do not have the option to "overwrite" the data as you do in a Flat File destination. The problem you are experiencing could be because your package is updating the same Excel file during each run.

Does it always insert a new row one row below the named range? What happens when you run the package 2 or 3 times; each time it should be inserting new records at the n+1th row.

The issue of populating Excel destination has been discussed in previous threads and Rafael Salas apparently as a solution for it. I am not sure if his solution addresses populating Named Ranges particularly.

Rafael has a blog at blogspot.com, and is also listed in the Top Answerer box. When you click on his name it provides details of his profile.

|||The first step in the SSIS package is to execute a file server component that copies and renames a spreadsheet template to the spreadsheet I'm trying to populate. I've deleted the data range and removed any read-only settings. What has worked is to create and use a header range which then populates data in the first row immediately following. Thanks for the response and reference to Rafael Salas.

export to excel - formatting number output

Hi all,

Once again, SSIS is giving me a 'F.U.N.' time (ask for definition of the F.U.N. acronym another time ).

I

have a relatively simple task - create an excel spreadsheet with 3

columns of data - Id, Description and Sales. ID and Description are

text, sales is int.

So my SP aggregates and creates my resultset

in my OLE DB Source in the Data Flow. It proceeds to the Excel

destination, and that all seems fine. My issue is that the data is

being written as text. Looking at the excel destination in Advanced

editor:
the Excel Destination Input, Input columns are formatted as

I expected: DT_WSTR 8 for the ID, DT_WSTR 100 for the Description and

DT_I4 for the Sales.
Excel Destination Input, External columns refuse to fall in line, though. They are all listed as DT_WSTR 255.

The

target excel spreadsheet is being created from a template file. That

template file has header columns. The target column for the Sales has

the entire column formatted to NUMBER (0 decimals). Yet to now avail.

When

I check the spreadsheet, the column has retained the cell formatting,

and I have a 'I' pop-up to inform me that 'someone' has inserted text

data into the number column (even though the data IS number).

Since

the SP spits out INT, it isn't a case of receiving a text value, imho.

While trying to change the external column data type in the advanced

editor, SSIS is quite happy to let me change the value for the Sales

output to DT_I4, apply, and ok. Then, when I open it immedaitely

aftgerwards, it has reverted to the DT_WSTR's! AArrgh. If is can't

handle it, at least tell me when I try and change it. don't let me

change it, and then revert back without telling me! Grumble grumble...

So - anyone know a way around this?

There are no perfect solutions for many behaviors of Jet with the Excel driver. However the driver is always happiest when the destination "table" has been created by the driver itself, which could be done through the Excel Destination or the Import and Export Wizard or ADO code. Excel column formatting seems to be disregarded completely.

The most comprehensive list of Excel driver-related issues is in 257819 How To Use ADO with Excel Data from Visual Basic or VBA
http://support.microsoft.com/default.aspx?scid=kb;EN-US;257819. Many of these will surface in SSIS in one form or another.

-Doug

|||

Hi Douglas,

As I read

the links I’ve found so far, IMEX=1 specifies text? Is there a way to have the

IMEX setting per column? If so, what would the correct IMEX setting for numeric

be?

TIA

|||

IMEX makes all data one type. By default that's Text (based on a Registry entry), and I've never heard of anyone changing it.

Please trust me on this -- if you want more predictable behavior, create the destination table in Excel by using the Excel driver. There appear to be hidden Excel column or cell settings that the driver is aware of but that are not exposed in the Excel UI (and not through cell formatting options).

-Doug

|||Thanks Douglas. I'll try that approach. It would seem to imply something like this:

CREATE TABLE `Sales Rank` (

`CatalogueItemReference` NVARCHAR(8),

`DescriptionText` NVARCHAR(100),

`TotalSalesForPeriod` INTEGER

)

that seem about right?|||

I'm not sure whether Jet SQL recognizes NVARCHAR but always have trouble finding the list. Somewhere on your disk there's a JETSQL40.CHM or SQLJET40.CHM or similar. I'd be more inclined to let the Import and Export Wizard or the Excel Destination write the statement for me.

-Doug

|||Thanks Doug, the sample I wrote was a slightly modified version of the

table create that the ssis excel destination task generated. I'll post

if it works :-)

Export SQL database Tables into HTML page

Hello,

I just want to know how can I create a SSIS package to export a few distinct tables into distinct HTML pages.

If anyone can help.

Thanks in advance.

Best regards...

Hi, are you looking for how to get distinct tables or the acutal HTML ouput? Is the idea that each table ends up as an HTML file with the data formatted with the proper tags of an HTML table?

For walking through the table list you may want to explore using a ForEach loop with a SMO enumerator to cylce through the tables. There is not built in feature/object for generating HTML though you may want to consider a script component, perhaps several, one as a transform and one as a custom destination.

|||

Thanks Craig for your reply,

Well, seams to me that export to HTML from Integration Services isn't a easy task... In my first aproach, I try using a query "Select * from table FOR XML AUTO" with output to a Flat destination file... I have some problems with types conversion... What I really wants is put each table content in a HTML file (or XML), and send all the files (ftp) to a specific site...

Best regards

Export sql data to text file

Hi all,

I am new to ssis. I try to create a package completely by vb.net to export a table in sql server to text file. i got the following error while i run the package,

An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for ODBC Drivers"
Hresult: 0x80004005 Description: "[Microsoft][ODBC Driver Manager]
Data source name not found and no default driver specified".
The AcquireConnection method call to the connection manager "OLEDBSrc"
failed with error code 0xC0202009.
component "OLE DB Source" (1) failed validation and returned error code 0xC020801C.
One or more component failed validation.
There were errors during task validation.

i have posted my code below,

Dim pkg As New Package

Dim OLEDBConMgr As ConnectionManager

Dim FileConMgr As ConnectionManager

Dim SrcComponent As IDTSComponentMetaData90

Dim SrcInstance As CManagedComponentWrapper

Dim DesComponent As IDTSComponentMetaData90

Dim DesInstance As CManagedComponentWrapper

pkg.PackageType = DTSPackageType.DTSDesigner90

Dim e As Executable = pkg.Executables.Add("DTS.Pipeline.1")

Dim thMainPipe As TaskHost = e 'as Task Host

Dim DataFlowTask As MainPipe = thMainPipe.InnerObject 'as MainPipe

'OLEDB Connection Manager

OLEDBConMgr = pkg.Connections.Add("OLEDB")

OLEDBConMgr.ConnectionString = "Data Source=srcServerName;Initial Catalog=srcDBName;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Auto Translate=False;"

OLEDBConMgr.Name = "OLEDBSrc"

OLEDBConMgr.Description = "OLEDB Connection to flightinfo database"

'FlatFile Connection Manager

FileConMgr = pkg.Connections.Add("FLATFILE")

FileConMgr.ConnectionString = "//FilePath"

FileConMgr.Name = "FLATFILE"

FileConMgr.Description = "Flat File Connection to the file"

FileConMgr.Properties("DataRowsToSkip").SetValue(FileConMgr, 0)

FileConMgr.Properties("Format").SetValue(FileConMgr, "Delimited")

FileConMgr.Properties("ColumnNamesInFirstDataRow").SetValue(FileConMgr, False)

FileConMgr.Properties("Unicode").SetValue(FileConMgr, False)

FileConMgr.Properties("RowDelimiter").SetValue(FileConMgr, vbCrLf)

FileConMgr.Properties("TextQualifier").SetValue(FileConMgr, "<none>")

FileConMgr.Properties("HeaderRowsToSkip").SetValue(FileConMgr, 0)

FileConMgr.Properties("HeaderRowDelimiter").SetValue(FileConMgr, vbCrLf)

FileConMgr.Properties("CodePage").SetValue(FileConMgr, 1252)

'Create Source Component

SrcComponent = DataFlowTask.ComponentMetaDataCollection.[New]

SrcComponent.ComponentClassID = "DTSAdapter.OLEDBSource"

SrcComponent.Name = "OLEDB"

'Get the Design time instance of the component

SrcInstance = SrcComponent.Instantiate

'Initialize the component

SrcInstance.ProvideComponentProperties()

'Specify the Connection Manager

If SrcComponent.RuntimeConnectionCollection.Count > 0 Then

SrcComponent.RuntimeConnectionCollection(0).ConnectionManagerID = OLEDBConMgr.ID

SrcComponent.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(OLEDBConMgr)

End If

'Set the Custom Properties

SrcInstance.SetComponentProperty("AccessMode", 0)

SrcInstance.SetComponentProperty("OpenRowset", "[dbo].[srcTableName]")

'ReInitialize the metadata

'SrcInstance.AcquireConnections(Nothing)

'SrcInstance.ReinitializeMetaData()

'SrcInstance.ReleaseConnections()

'Create Destination Component

DesComponent = DataFlowTask.ComponentMetaDataCollection.[New]

DesComponent.ComponentClassID = "DTSAdapter.FlatFileDestination"

DesComponent.Name = "FLATFILE"

'Get the Design time instance of the component

DesInstance = DesComponent.Instantiate

'Initialize the component

DesInstance.ProvideComponentProperties()

'Specify the Connection Manager

If DesComponent.RuntimeConnectionCollection.Count > 0 Then

DesComponent.RuntimeConnectionCollection(0).ConnectionManagerID = FileConMgr.ID

DesComponent.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(FileConMgr)

End If

'ReInitialize the metadata

'DesInstance.AcquireConnections(Nothing)

'DesInstance.ReinitializeMetaData()

'DesInstance.ReleaseConnections()

Dim path As IDTSPath90 = DataFlowTask.PathCollection.[New]

path.AttachPathAndPropagateNotifications(SrcComponent.OutputCollection(0), DesComponent.InputCollection(0))

' Get the destination's default input and virtual input.

Dim input As IDTSInput90 = DesComponent.InputCollection(0)

Dim vInput As IDTSVirtualInput90

vInput = input.GetVirtualInput()

'Iterate through the virtual column collection.

Dim vColumn As IDTSVirtualInputColumn90

' Iterate through the virtual input column collection.

For Each vColumn In vInput.VirtualInputColumnCollection

' Call the SetUsageType method of the destination

' to add each available virtual input column as an input column.

DesInstance.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY)

Next

' Verify that the columns have been added to the input.

For Each inputColumn As IDTSInputColumn90 In DesComponent.InputCollection(0).InputColumnCollection

MsgBox(inputColumn.Name)

Next

Dim pkgResult As DTSExecResult

pkgResult = pkg.Execute

Is there anybody know it? plz help me.

regards,

sivani

OLEDBConMgr.ConnectionString = "Data

Source=srcServerName;Initial

Catalog=srcDBName;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist

Security Info=False;Auto Translate=False;"

Might be something with your connection string, Try:

"Data Source=srcServerName;Initial Catalog=srcDBName;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;"|||

I changed the connection string but still i got the same error and in path creation,

Dim path As IDTSPath90 = DataFlowTask.PathCollection.[New]

path.AttachPathAndPropagateNotifications(SrcComponent.OutputCollection(0), DesComponent.InputCollection(0))

the following error occured at the endpoint while debugging

ErrorCode = -2147467259
Error HRESULT E_FAIL has been returned from a call to a COM component.

|||

Thanks a lot.

The connection string is the problem. i corrected the error but in flat file validation i got the following error, i think its related with column mapping but i dont know abt it. plz help me. the error is,

"component "FLATFILE" (31)" failed validation and returned validation status "VS_NEEDSNEWMETADATA".

One or more component failed validation.

There were errors during task validation.

|||

Hi,

Anybody has any idea to solve the "VS_NEEDNEWMETADATA" problem.

thanks

sivani.

|||What is the VS_NEEDNEWMETADATA problem?|||

Hi,

I already posted the problem in,

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

plz find it.

thanks

Sivani

|||

You are correct, the problem is related to the column mappings.

There are no external columns created in the above post for the flat file destination, nor are there any mapping to those columns. Hence, there is nothing to map the oledb sourced input columns into.

See the example (if you've downloaded the 2005 Samples) located by default at "%ProgramFiles%\Microsoft SQL Server\90\Samples\Integration Services\Programming Samples\Control Flow\CreatePackage Sample\VB" which does exactly what you're attempting here. The sample is a programmatic package builder which instantiates an OLEDB source that ends up in a flat file destination.

|||

Hi,

Thanks for ur suggestion. i solved the problem.

Thanks & Regards,

Sivani