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 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 data from SQL table to Excel File - How to delete rows before inserting new

Hi,

Question pls. I have an MS SQL local package where it exports data from SQL table to Excel file. My question is, how can I erase all the records in my excel file before i export the new data from SQL table?

What i want is to delete the rows in the destination file before inserting new records.

Note:
I cannot do the drop & recreate table because the users has no permission to do this in the database. Only the DB admin is allowed to drop & create table.

Thanks a lot.

Quote:

Originally Posted by LimaCharlie

Hi,

Question pls. I have an MS SQL local package where it exports data from SQL table to Excel file. My question is, how can I erase all the records in my excel file before i export the new data from SQL table?

What i want is to delete the rows in the destination file before inserting new records.

Note:
I cannot do the drop & recreate table because the users has no permission to do this in the database. Only the DB admin is allowed to drop & create table.

Thanks a lot.

Create a view that limits number of exported rows and use it in DTS package instead of a table.

Good Luck.|||hi iburyak,

just the same, the records are being appended to the excel file. what i want is to delete the rows in the destination file before inserting new records.

thanks.|||Did you create a view that removes unwanted records?
Execute a view on a Server side and make sure this is the data you want to export, don't create a view to select * from table_name it will not correct a problem.

You need to give me more information then just I want to delete records.
It is a wrong approach to load all records and delete on destination side you need to load only necessary records.|||Hi Iburyak,

The Question is... this excel file is getting populated everyday and everyday before porting data to the excel the old data has to be erased and new data filled up.

Any help on that.

Thanks in advance..

Exporting data from SQL table to Excel File - How to delete rows before insertin

Hi,

Question pls. I have an MS SQL local package where it exports data from SQL table to Excel file. My question is, how can erase all the records in my excel file before i export the new data from SQL table?

What i want is to delete the rows in the destination file before inserting new records.

Thanks a lot.Is it possibe to create a link to the Excel and run a delete statement from the SQL Server side?

http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=135&rl=1

ddave|||LimaCharlie, take a look at this post:

http://www.dbforums.com/showthread.php?t=981661

Cheers

kbk|||hi kbk,

that was my first design: i drop then recreate the table and it was working properly. but the problem is, the users are not permitted to drop & create tables in the database (only me & the DB admin are allowed to do this). so what i did was create a temporary table, from this temp table i export the data to excel. is there any other way so i can delete the rows before inserting new?

thanks a lot.|||What i want is to delete the rows in the destination file before inserting new records.

I sugggest delete the worksheet instead of delete all the rows. Then recreate the worksheet later.

1. Create an activeX script as below: -This will remove the worksheet.

Function Main()

Dim srccsvfile
Dim objExcel
Dim objWorkbook, objWorksheet

srccsvfile = "C:\Test.xls"

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
objExcel.displayalerts = False

Set objWorkbook = objExcel.Workbooks.open(srccsvfile)
Set objWorksheet = objWorkbook.WorkSheets("New_Table")
objWorksheet.Activate
objWorksheet.Delete 'this is removing the worksheet instead of rows
objWorkbook.Save 'you must save the change otherwise in trouble

objExcel.Workbooks.Close
Set objWorkbook = Nothing
objExcel.Quit
Set objExcel = Nothing
Main = DTSTaskExecResult_Success
End Function

2. Create Execute SQL task as below: - create the worksheet again
CREATE TABLE `New_Table` (
`OfficePK` VarChar (8) ,
`DealerGroupFK` VarChar (4) ,
`StateManagerGroupFK` VarChar (20) ,
`OfficeType` VarChar (1) ,
`OfficeName` VarChar (255) ,
`OfficeAddress1` VarChar (255) ,
`OfficeAddress2` VarChar (255) ,
`OfficeSuburb` VarChar (255) ,
`OfficeState` VarChar (255) ,
`OfficePostCode` VarChar (255) ,
`OfficeCountry` VarChar (255) ,
`OfficePhone` VarChar (255) ,
`RIOfficeID` VarChar (50) ,
`RIFranchiseName` VarChar (255) ,
`VPServerIP` VarChar (50) ,
`LastReceived` DateTime
)
3. Then use data transform as you are doing now.
Make sure that the destination table name is New_Table and the connection is Excel.|||Hi TerryP,
I'll try this.
Thank you very much. =)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

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

Exporting a DTS Package

Hi,
I have a Database on one server and the same Database on
another server.
One Database has a DTS Package that I need.
Can I export this DTS package to my Database that does not
have it without doing a backup of the one that has it and
restoring it to the one that does'nt?
CHi
You van save them as files and transfer , another option is to move from
sysdtspackages table in msdb database.
"C" <anonymous@.discussions.microsoft.com> wrote in message
news:0b5d01c3b3fb$5c516ff0$a401280a@.phx.gbl...
> Hi,
> I have a Database on one server and the same Database on
> another server.
> One Database has a DTS Package that I need.
> Can I export this DTS package to my Database that does not
> have it without doing a backup of the one that has it and
> restoring it to the one that does'nt?
> C|||The easiest way of moving DTS packages between servers is to Design the DTS
package on the source server and simply say Save As and specify the other
server name.
If the servers can't see one another on the network save the package to a
structured file copy to other server then open
--
HTH
Ryan Waight, MCDBA, MCSE
"C" <anonymous@.discussions.microsoft.com> wrote in message
news:0b5d01c3b3fb$5c516ff0$a401280a@.phx.gbl...
> Hi,
> I have a Database on one server and the same Database on
> another server.
> One Database has a DTS Package that I need.
> Can I export this DTS package to my Database that does not
> have it without doing a backup of the one that has it and
> restoring it to the one that does'nt?
> C|||We use both of Ryan's methods all the time and either way
is probably your best way. Using the structured file
also allows you to utilize versioning software such as
Source Safe.
One thing to remember when you move DTS packages to a new
server is to verify all of your Data Connections within
the packages, especially if you have paths specified to
text files or MS Access files.
Matthew Bando
BandoM@.CSCTechnologies (remove this).com
>--Original Message--
>The easiest way of moving DTS packages between servers
is to Design the DTS
>package on the source server and simply say Save As and
specify the other
>server name.
>If the servers can't see one another on the network save
the package to a
>structured file copy to other server then open
>--
>HTH
>Ryan Waight, MCDBA, MCSE
>"C" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0b5d01c3b3fb$5c516ff0$a401280a@.phx.gbl...
>> Hi,
>> I have a Database on one server and the same Database
on
>> another server.
>> One Database has a DTS Package that I need.
>> Can I export this DTS package to my Database that does
not
>> have it without doing a backup of the one that has it
and
>> restoring it to the one that does'nt?
>> C
>
>.
>

Export with DTS to a dbf file

Hello,

I'm trying to export data from sql server2000 to a dbf file
When creating the dts package, i've got the following error (when creating the table in the dbf file):
"HResult of 2147467259 returned. Unexpected error occurred..."

The table i try to export is like:
Table1 (field1 int, field2 int...)

Does anyone have any idea?

Thanks in advance

IsabelleDo not forget that length of name field for DBF is only 10 ...
Check name of columns in your table.

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 file

I've created a dts package that exports data to an excel. The data exports okay, but I'd like to make a few changes on how the data is exported.

First, when the data exports to the excel file, it always creates a new excel sheet. How can I map the data to the existing sheet in the Excel file? I have an existing sheet named 'Sheet1'. I've deleted the other two sheets in the excel file, so there is only one sheet. When I created the destination table in the dts package for the data transformation task, I specified the name as 'Sheet1', but when it exports, it creates a new sheet named 'Sheet11'. I haven't been able to correct this.

Second, I'd like to either ammend data to the last row in the excel file, which I'd have to know the last record that was inserted from the SQL table. Or probably much easier, simply drop the existing sheet in the excel file and simply re-create and insert.

How can I do this?

Thanks,
-D-I figured it out...please disregard.

Export to CSV/Excel

How can I export data from a table to a CSV file? I am working with an
Account package with multiple companies. Each company has its own database
and all the tables are named the same thing. I want to export all the data i
n
a table called Account for each company to a CSV file. There are about 20
companies and I do not want to manually do this using DTS. Is there a way I
could write a script to do this?
Thanks
EmmaIf you do not want to use DTS, you can use BCP. It is a command line
directive that allows you to export to a file directly from a table, view or
a query.
Rather than me explaining more about it, you can check look the BCP usage in
SQL Server Books Online.
Let me know if it helps.
"Emma" wrote:

> How can I export data from a table to a CSV file? I am working with an
> Account package with multiple companies. Each company has its own database
> and all the tables are named the same thing. I want to export all the data
in
> a table called Account for each company to a CSV file. There are about 20
> companies and I do not want to manually do this using DTS. Is there a way
I
> could write a script to do this?
> Thanks
> Emma
>|||Huge ask for a simple manual task.
one question, hope its a one time request. If so the time scripting can get
you around 100 tables to CSV using DTS.
Again in my mind i was going wild like, using osql put it in batchfile and
input account name to script it to csv. Again for such things you need to
provide accountname hardcoded.
--
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"Emma" wrote:

> How can I export data from a table to a CSV file? I am working with an
> Account package with multiple companies. Each company has its own database
> and all the tables are named the same thing. I want to export all the data
in
> a table called Account for each company to a CSV file. There are about 20
> companies and I do not want to manually do this using DTS. Is there a way
I
> could write a script to do this?
> Thanks
> Emma
>|||Thanks Edgardo. BCP did the job.
Emma
"Edgardo Valdez, MCSD, MCDBA" wrote:
> If you do not want to use DTS, you can use BCP. It is a command line
> directive that allows you to export to a file directly from a table, view
or
> a query.
> Rather than me explaining more about it, you can check look the BCP usage
in
> SQL Server Books Online.
> Let me know if it helps.
> "Emma" wrote:
>

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