How to set sheetname on an Excel destination component ?

Hello, I am trying to create a simple package programmatically. I am following the examples in the BOL, and from some advice here. I am getting stuck at creating an Excel Destination and setting its sheetname. Everything works fine, including setting the output Excel filename. I get a runtime exception when I try to set the sheetname via SetComponentProperty. Is there another way, or am I doing something wrong? Thanks for any info you may have.

' Create and configure an OLE DB destination.

Dim conDest As ConnectionManager = package.Connections.Add("Excel")

conDest.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _

Dts.Variables("User::gsExcelFile").Value.ToString & ";Extended Properties=""Excel 8.0;HDR=YES"""

conDest.Name = "Excel File"

conDest.Description = "Excel File"

Dim destination As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New

destination.ComponentClassID = "DTSAdapter.ExcelDestination"

' Create the design-time instance of the destination.

Dim destDesignTime As CManagedComponentWrapper = destination.Instantiate

' The ProvideComponentProperties method creates a default input.

destDesignTime.ProvideComponentProperties()

destination.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(conDest)

destDesignTime.SetComponentProperty("AccessMode", 0)

'runtime Exception here

destDesignTime.SetComponentProperty("OpenRowSet", "functions")

Guess time!

If you post the error details, it normally helps. I'll guess at error HResult 0xC0204006, some notes - http://wiki.sqlis.com/default.aspx/SQLISWiki/0xC0204006.html

Properties are case sensitive, and it is called OpenRowset not OpenRowSet.

How did i do?

|||Yep, the case sensitivity was it. Thanks!

How to set sheetname on an Excel destination component ?

Hello, I am trying to create a simple package programmatically. I am following the examples in the BOL, and from some advice here. I am getting stuck at creating an Excel Destination and setting its sheetname. Everything works fine, including setting the output Excel filename. I get a runtime exception when I try to set the sheetname via SetComponentProperty. Is there another way, or am I doing something wrong? Thanks for any info you may have.

' Create and configure an OLE DB destination.

Dim conDest As ConnectionManager = package.Connections.Add("Excel")

conDest.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _

Dts.Variables("User::gsExcelFile").Value.ToString & ";Extended Properties=""Excel 8.0;HDR=YES"""

conDest.Name = "Excel File"

conDest.Description = "Excel File"

Dim destination As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New

destination.ComponentClassID = "DTSAdapter.ExcelDestination"

' Create the design-time instance of the destination.

Dim destDesignTime As CManagedComponentWrapper = destination.Instantiate

' The ProvideComponentProperties method creates a default input.

destDesignTime.ProvideComponentProperties()

destination.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(conDest)

destDesignTime.SetComponentProperty("AccessMode", 0)

'runtime Exception here

destDesignTime.SetComponentProperty("OpenRowSet", "functions")

Guess time!

If you post the error details, it normally helps. I'll guess at error HResult 0xC0204006, some notes - http://wiki.sqlis.com/default.aspx/SQLISWiki/0xC0204006.html

Properties are case sensitive, and it is called OpenRowset not OpenRowSet.

How did i do?

|||Yep, the case sensitivity was it. Thanks!

How to Set Record Length for the Flatfiles in SSIS

I am trying to transfer the data from OLEDB source to Flatfile destination. My Client need the flatfile of Record Length 80 Bytes. What does this means? Does it mean the output width or does it mean the flatfile format should be fixed length? please explain?It means that the sum of the individual column lengths must add up to 80. No less, no more.

You need to find out, though, if there is supposed to be a line feed at the end of each row, in which case you'd want to use a "ragged-right" format. Otherwise, fixed width would work.|||so if i set the output column width of each column to 80, it is all set, as the client dont have further data available.....he does not knw about the line feed......|||No, the columns must ALL add up, in total, to 80.|||great thanks phil.....I think this answers my question for now....i will keep the thread open as i might come up with more questions on this topic|||

one thing more is, if have set the record length for all the columns (total = 80 bytes), now when i name the columns should that also comply with number of bits i have set for each column

for eg

if columnname is MyNumber itz datataype is Varchar and Length is 10 than do MyNumber also be set within 10 Bits limit

|||

B.Chintan wrote:

one thing more is, if have set the record length for all the columns (total = 80 bytes), now when i name the columns should that also comply with number of bits i have set for each column

for eg

if columnname is MyNumber itz datataype is Varchar and Length is 10 than do MyNumber also be set within 10 Bits limit

It depends. If you are outputting column names to the file, then yes. (Even that might not be true, but in general, it is.) If you are not outputting file names to the file, then you can make them however long you need.

|||

I tried the above stated solution, when the column name is not specified it works fine and when column name is specified, it does not work as it wants the names to be within 80 bytes limit.

The problem now is when i try to put this flat files back to database using oledbsource and lookups, I am not able to do that. i encounter problem with format.

In this case i have set the format to fixed width...so when i try to put it in db.....i can see just one column whereas when i converted it to flatfile it had 61 columns and now while reversing it i c everything in one column.

Any step by step procedure to solve this tht is from db to flatfile with fixed width row dilimiter and vice versa

Chintan Shah

|||Then make your column names fit into the 80 bytes length restriction.

Also, use "ragged right" when loading the resulting flat file. But why are you doing this anyway? Just to test the file?|||i need to submit the flat files to an reporting agency now they have certain standards which my client need to follow, putting the flat file back to db is just a test I am doing as tht will be done by the agency when they receive the flat files from us. I will try using ragged right....hope tht works fine.|||

the best solution i did was to transform the db file in 3 format using this

OLEDBSource>>>>MultiCast>>>>3 Diff flatfiles in 3 diff formats.

One important thing to remember is

If the column headings are to be submitted than they should comply to the set Byte Limit. If they are not than column headings can be as long as desired.

Thanks phil for all help.

I would like to know if you have any Knowledge about Microsoft Dynamics and is it a good line to enter or SQL Server is the better option.

Chintan

How to Set Record Length for the Flatfiles in SSIS

I am trying to transfer the data from OLEDB source to Flatfile destination. My Client need the flatfile of Record Length 80 Bytes. What does this means? Does it mean the output width or does it mean the flatfile format should be fixed length? please explain?It means that the sum of the individual column lengths must add up to 80. No less, no more.

You need to find out, though, if there is supposed to be a line feed at the end of each row, in which case you'd want to use a "ragged-right" format. Otherwise, fixed width would work.|||so if i set the output column width of each column to 80, it is all set, as the client dont have further data available.....he does not knw about the line feed......|||No, the columns must ALL add up, in total, to 80.|||great thanks phil.....I think this answers my question for now....i will keep the thread open as i might come up with more questions on this topic|||

one thing more is, if have set the record length for all the columns (total = 80 bytes), now when i name the columns should that also comply with number of bits i have set for each column

for eg

if columnname is MyNumber itz datataype is Varchar and Length is 10 than do MyNumber also be set within 10 Bits limit

|||

B.Chintan wrote:

one thing more is, if have set the record length for all the columns (total = 80 bytes), now when i name the columns should that also comply with number of bits i have set for each column

for eg

if columnname is MyNumber itz datataype is Varchar and Length is 10 than do MyNumber also be set within 10 Bits limit

It depends. If you are outputting column names to the file, then yes. (Even that might not be true, but in general, it is.) If you are not outputting file names to the file, then you can make them however long you need.

|||

I tried the above stated solution, when the column name is not specified it works fine and when column name is specified, it does not work as it wants the names to be within 80 bytes limit.

The problem now is when i try to put this flat files back to database using oledbsource and lookups, I am not able to do that. i encounter problem with format.

In this case i have set the format to fixed width...so when i try to put it in db.....i can see just one column whereas when i converted it to flatfile it had 61 columns and now while reversing it i c everything in one column.

Any step by step procedure to solve this tht is from db to flatfile with fixed width row dilimiter and vice versa

Chintan Shah

|||Then make your column names fit into the 80 bytes length restriction.

Also, use "ragged right" when loading the resulting flat file. But why are you doing this anyway? Just to test the file?|||i need to submit the flat files to an reporting agency now they have certain standards which my client need to follow, putting the flat file back to db is just a test I am doing as tht will be done by the agency when they receive the flat files from us. I will try using ragged right....hope tht works fine.|||

the best solution i did was to transform the db file in 3 format using this

OLEDBSource>>>>MultiCast>>>>3 Diff flatfiles in 3 diff formats.

One important thing to remember is

If the column headings are to be submitted than they should comply to the set Byte Limit. If they are not than column headings can be as long as desired.

Thanks phil for all help.

I would like to know if you have any Knowledge about Microsoft Dynamics and is it a good line to enter or SQL Server is the better option.

Chintan

How to Set Record Lenght for the Flatfiles in SSIS

I am trying to transfer the data from OLEDB source to Flatfile destination. My Client need the flatfile of Record Length 80 Bytes. What does this means? Does it mean the output width or does it mean the flatfile format should be fixed length? please explain?It means that the sum of the individual column lengths must add up to 80. No less, no more.

You need to find out, though, if there is supposed to be a line feed at the end of each row, in which case you'd want to use a "ragged-right" format. Otherwise, fixed width would work.|||so if i set the output column width of each column to 80, it is all set, as the client dont have further data available.....he does not knw about the line feed......|||No, the columns must ALL add up, in total, to 80.|||great thanks phil.....I think this answers my question for now....i will keep the thread open as i might come up with more questions on this topic|||

one thing more is, if have set the record length for all the columns (total = 80 bytes), now when i name the columns should that also comply with number of bits i have set for each column

for eg

if columnname is MyNumber itz datataype is Varchar and Length is 10 than do MyNumber also be set within 10 Bits limit

|||

B.Chintan wrote:

one thing more is, if have set the record length for all the columns (total = 80 bytes), now when i name the columns should that also comply with number of bits i have set for each column

for eg

if columnname is MyNumber itz datataype is Varchar and Length is 10 than do MyNumber also be set within 10 Bits limit

It depends. If you are outputting column names to the file, then yes. (Even that might not be true, but in general, it is.) If you are not outputting file names to the file, then you can make them however long you need.

|||

I tried the above stated solution, when the column name is not specified it works fine and when column name is specified, it does not work as it wants the names to be within 80 bytes limit.

The problem now is when i try to put this flat files back to database using oledbsource and lookups, I am not able to do that. i encounter problem with format.

In this case i have set the format to fixed width...so when i try to put it in db.....i can see just one column whereas when i converted it to flatfile it had 61 columns and now while reversing it i c everything in one column.

Any step by step procedure to solve this tht is from db to flatfile with fixed width row dilimiter and vice versa

Chintan Shah

|||Then make your column names fit into the 80 bytes length restriction.

Also, use "ragged right" when loading the resulting flat file. But why are you doing this anyway? Just to test the file?|||i need to submit the flat files to an reporting agency now they have certain standards which my client need to follow, putting the flat file back to db is just a test I am doing as tht will be done by the agency when they receive the flat files from us. I will try using ragged right....hope tht works fine.|||

the best solution i did was to transform the db file in 3 format using this

OLEDBSource>>>>MultiCast>>>>3 Diff flatfiles in 3 diff formats.

One important thing to remember is

If the column headings are to be submitted than they should comply to the set Byte Limit. If they are not than column headings can be as long as desired.

Thanks phil for all help.

I would like to know if you have any Knowledge about Microsoft Dynamics and is it a good line to enter or SQL Server is the better option.

Chintan

How to set MS Access as Data Flow Destination

I can't seem to find a way to make the Data Flow Destination in a Business Intelligence Visual Studion Project output an MDB file for Microsoft Access.

Hi Bart,

Data flow destination can be used to output to MDB file for Microsoft Access.

In the OLE DB "Connection Manager: Select "Microsoft Jet 4.0 OLE DB Provider" for the Provider field.
Click on the "Browse" Button next for the "Database File Name" property and Select an existing Microsoft Access Database file. and close that window.

In the OLE DB Destination Editor - for the "Name of the table or the view" property click on "New" and it will show the table format for the imported table in access database.

Thanks,
Loonysan

|||

Yes - its possible.

In the "OLE DB Destination Editor" Window - Click "New" for OLEDB Connection Manager.
In the "Connection Manager" Window - Select "Microsoft Jet 4.0 OLE DB Provider".
Select an existing Access Database file using the "Browse" button for "Database File Name and close the "Configure OLE DB Connection Manager" window.

In the "OLE DB Destination Editor" - Click "New" for "Name of the table or the view" - it will show the table structure of the imported data. Make changes to the table / column names if required.

Clcik on "Mappings" and check the default mapping for the imported columns. Click OK.

You should be good to go from here

Thanks,
Loonysan