create table(s) from xml schema

I'm using SQL 2005.

I have an XML schema that is maintained by an outside source and I'd like to use it to create the table structures. Is there a way to do this without manually creating the tables? I will be receiving data files that will need to be validated against the schema and then uploaded into a database.

I suppose I could just load the xml into a single field using the xml data type, but it seems like it may be easier to access the data if it was broken out.

Please let me know if anyone is aware of a tool that would automatically create the SQL tables from an XML schema.

Thanks,

John

You can use XML Bulk Load component to create your tables from xml schema file :

You can set the SchemaGen property to TRUE to create your tables.

Refer Books on line SQL Server 2005 :

SQL Server 2005 Books online ->

SQL Server Programming Reference ->

SQL XML 4.0 Programming ->

Performing Bulk Load of XML Data (SQLXML 4.0)

Refer the following links to know more about XmlBulkLoad component:

Performing Bulk Load of XML Data (SQLXML 4.0)

Using SQL Server's XML Support > XML Bulk Load

How to import XML into SQL Server with the XML Bulk Load component

Thanks

Naras.

sql

create table(s) from xml schema

I'm using SQL 2005.

I have an XML schema that is maintained by an outside source and I'd like to use it to create the table structures. Is there a way to do this without manually creating the tables? I will be receiving data files that will need to be validated against the schema and then uploaded into a database.

I suppose I could just load the xml into a single field using the xml data type, but it seems like it may be easier to access the data if it was broken out.

Please let me know if anyone is aware of a tool that would automatically create the SQL tables from an XML schema.

Thanks,

John

You can use XML Bulk Load component to create your tables from xml schema file :

You can set the SchemaGen property to TRUE to create your tables.

Refer Books on line SQL Server 2005 :

SQL Server 2005 Books online ->

SQL Server Programming Reference ->

SQL XML 4.0 Programming ->

Performing Bulk Load of XML Data (SQLXML 4.0)

Refer the following links to know more about XmlBulkLoad component:

Performing Bulk Load of XML Data (SQLXML 4.0)

Using SQL Server's XML Support > XML Bulk Load

How to import XML into SQL Server with the XML Bulk Load component

Thanks

Naras.

Create Subscription For Rpt Service with Windows integrated security !!!

hi All,
I m nicky here.
I have problem on Create Subscription for my report if my Data Source is use Windows Integrated Security, with this error message:

"Subscriptions cannot be created because the credentials used to run the report are not stored, or if a linked report , the linked is no longer valid"

Please Help ....

Thank you
From
nicky

For subscriptions to work the data source must either store credentials nor not require them (none option in the UI).

-Lukasz

|||Does this open the report so that anyone can see it?|||Yep, it sure does. A big pain. You could have multiple copies of the one report and change the data source of each report. Then secure the report via user groups. A pain, but if you only had a few users it might be ok. If you have hundreds however......|||

Not sure I understand. When you say multiple copies of a report, you lose me.

I have a report that has stored credentials so that the subscription will work. Unfortunately this also means that anyone can log on to the server and view the report. How do stop everyone from viewing the report?

|||

Hey,

Sorry about the delay in responding, Xmas and project work got in the way!

I mean that you publish the one copy of a report, several times to different folders. You don't use a shared data source, but instead use a data source specificially for each report. Now you edit the connection string of each report based data source to match each specific user. Finially, if it is possible, you have each of these reports in a unique folder that have security defined on it per user.

As I said, not pretty, but a possible solution!

Mark

|||Btw, is this report against an Analysis Service DB?|||

I got my issue fixed. The problem was that anonymous connections were set within IIS and this meant that RS security was rendered useless. Now that this issue is corrected, everything that I have read works correctly. (If not for the rock in the hoof of the lead horse...)

Thanks for all your help!

Create Subscription For Rpt Service with Windows integrated security !!!

hi All,
I m nicky here.
I have problem on Create Subscription for my report if my Data Source is use Windows Integrated Security, with this error message:

"Subscriptions cannot be created because the credentials used to run the report are not stored, or if a linked report , the linked is no longer valid"

Please Help ....

Thank you
From
nicky

For subscriptions to work the data source must either store credentials nor not require them (none option in the UI).

-Lukasz

|||Does this open the report so that anyone can see it?|||Yep, it sure does. A big pain. You could have multiple copies of the one report and change the data source of each report. Then secure the report via user groups. A pain, but if you only had a few users it might be ok. If you have hundreds however......|||

Not sure I understand. When you say multiple copies of a report, you lose me.

I have a report that has stored credentials so that the subscription will work. Unfortunately this also means that anyone can log on to the server and view the report. How do stop everyone from viewing the report?

|||

Hey,

Sorry about the delay in responding, Xmas and project work got in the way!

I mean that you publish the one copy of a report, several times to different folders. You don't use a shared data source, but instead use a data source specificially for each report. Now you edit the connection string of each report based data source to match each specific user. Finially, if it is possible, you have each of these reports in a unique folder that have security defined on it per user.

As I said, not pretty, but a possible solution!

Mark

|||Btw, is this report against an Analysis Service DB?|||

I got my issue fixed. The problem was that anonymous connections were set within IIS and this meant that RS security was rendered useless. Now that this issue is corrected, everything that I have read works correctly. (If not for the rock in the hoof of the lead horse...)

Thanks for all your help!

Create Subscription For Rpt Service with Windows integrated security !!!

hi All,
I m nicky here.
I have problem on Create Subscription for my report if my Data Source is use Windows Integrated Security, with this error message:

"Subscriptions cannot be created because the credentials used to run the report are not stored, or if a linked report , the linked is no longer valid"

Please Help ....

Thank you
From
nicky

For subscriptions to work the data source must either store credentials nor not require them (none option in the UI).

-Lukasz

|||Does this open the report so that anyone can see it?|||Yep, it sure does. A big pain. You could have multiple copies of the one report and change the data source of each report. Then secure the report via user groups. A pain, but if you only had a few users it might be ok. If you have hundreds however......|||

Not sure I understand. When you say multiple copies of a report, you lose me.

I have a report that has stored credentials so that the subscription will work. Unfortunately this also means that anyone can log on to the server and view the report. How do stop everyone from viewing the report?

|||

Hey,

Sorry about the delay in responding, Xmas and project work got in the way!

I mean that you publish the one copy of a report, several times to different folders. You don't use a shared data source, but instead use a data source specificially for each report. Now you edit the connection string of each report based data source to match each specific user. Finially, if it is possible, you have each of these reports in a unique folder that have security defined on it per user.

As I said, not pretty, but a possible solution!

Mark

|||Btw, is this report against an Analysis Service DB?|||

I got my issue fixed. The problem was that anonymous connections were set within IIS and this meant that RS security was rendered useless. Now that this issue is corrected, everything that I have read works correctly. (If not for the rock in the hoof of the lead horse...)

Thanks for all your help!

sql

Create Subscription For Rpt Service with Windows integrated security !!!

hi All,
I m nicky here.
I have problem on Create Subscription for my report if my Data Source is use Windows Integrated Security, with this error message:

"Subscriptions cannot be created because the credentials used to run the report are not stored, or if a linked report , the linked is no longer valid"

Please Help ....

Thank you
From
nicky

For subscriptions to work the data source must either store credentials nor not require them (none option in the UI).

-Lukasz

|||Does this open the report so that anyone can see it?|||Yep, it sure does. A big pain. You could have multiple copies of the one report and change the data source of each report. Then secure the report via user groups. A pain, but if you only had a few users it might be ok. If you have hundreds however......|||

Not sure I understand. When you say multiple copies of a report, you lose me.

I have a report that has stored credentials so that the subscription will work. Unfortunately this also means that anyone can log on to the server and view the report. How do stop everyone from viewing the report?

|||

Hey,

Sorry about the delay in responding, Xmas and project work got in the way!

I mean that you publish the one copy of a report, several times to different folders. You don't use a shared data source, but instead use a data source specificially for each report. Now you edit the connection string of each report based data source to match each specific user. Finially, if it is possible, you have each of these reports in a unique folder that have security defined on it per user.

As I said, not pretty, but a possible solution!

Mark

|||Btw, is this report against an Analysis Service DB?|||

I got my issue fixed. The problem was that anonymous connections were set within IIS and this meant that RS security was rendered useless. Now that this issue is corrected, everything that I have read works correctly. (If not for the rock in the hoof of the lead horse...)

Thanks for all your help!

Create row from number range

Greetings:

I would like to create a row for each number range in a table and add its associated fields with it.

Source Table has:

BEG_NUM END_NUM PRINTER USER DATE
150 153 P01 Ed 6-1-07

I would like to convert that to a table which consists of:
Number Printer User Date
150 P01 Ed 6-1-07
151 P01 Ed 6-1-07
152 P01 Ed 6-1-07
153 P01 Ed 6-1-07

How can I create this using SQL? The source table has lots of rows.

Thanks,
Ericselect min(Number) as BEG_NUM
, max(Number) as END_NUM
, Printer as PRINTER
, User as USER
, Date as DATE
from Source
group
by Printer
, User
, Date|||r937,

Thank you for your reply.

Your solution combines the rows. I want to make a row for each number between the beginning and ending number, so if beginning is 150 and ending is 153 i would have 4 rows listed from that one row. 150,151,152,153.

My goal is to have one row for each series of numbers.|||aaargh, i misunderstood, i got your tables backwards, didn't i

you will need an integers table:create table integers (i integer not null primary key);
insert into integers (i) values
(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)then you can generate your range of numbers like this:select BEG_NUM + i as Number
, PRINTER as Printer
, USER as User
, DATE as Date
from integers
inner
join Source
on BEG_NUM + i <= END_NUM|||When I do the 2nd part, I get invalid column name for all except i because I am selecting fields from integer table that don't exist.

I tried adding the correct table names and selecting from both tables and then joining but it doesn't work. It says tables have the same exposed names.

When I tried another way, it gave me 10 rows for each number, which wasnt what i want either :(|||i tested my query and it works

probably your table is actually different from what you posted, or you made an error in your query

can't help you any further unless you show your query|||I apologize, Its working now.

Thank you for all your help!!|||The "filling holes in a table" problem is a well-known "difficult" one in relational databases.
If your database system supports it, you should use recursive SQL for the "integers table" part of the story:WITH integers(i) AS (VALUES (0)
UNION ALL
SELECT i+1 FROM integers
WHERE i < 9)
SELECT ... <Rudy's query>
Now this query is much more flexible in case there are bigger holes than width 10: just replace the "9" by, say, "99".
Or better yet, replace it by (SELECT MAX(End_Num-Beg_Num) FROM Source) or something like that, so that the query becomes completely unparametrized and will work on any table with any numeric column pair.
(Or with a date pair, for that matter.)|||nice trick, peter, and i mean that as a sincere compliment

it is a true "hack" and that is a good thing

:)

Create Report with Credentials

I have a report with a data source that requires credentials to be stored in
reporting service system.
I use the CreateReport function to deploy this report but it doesn't store
the credentials and these credentials are requested when it is about to
render.
How to store these credentials in order to avoid them to be asked during
rendering'
ThanksIn our case, we use script based on the scripting samples (in the RS install
folder), but link the reports to a shared data source created with the
credentials specfied:
Public Sub CreateSQLDataSource(ByVal serverName as String, ByVal
databaseName as String, ByVal userName as String, ByVal password as String)
Dim name As String = databaseName
Dim parent As String = "/"
'Define the data source definition.
Dim definition As New DataSourceDefinition()
definition.CredentialRetrieval = CredentialRetrievalEnum.Integrated
definition.ConnectString = "data source=" & serverName & ";initial
catalog=" & databaseName
definition.Enabled = True
definition.EnabledSpecified = True
definition.Extension = "SQL"
definition.ImpersonateUser = False
definition.ImpersonateUserSpecified = True
definition.Username = userName
definition.Password = password
'Use the default prompt string.
definition.Prompt = Nothing
definition.WindowsCredentials = False
Try
rs.CreateDataSource(name, parent, False, definition, Nothing)
Catch e As Exception
Console.WriteLine(e.Message)
End Try
End Sub
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Ale Dori" <AleDori@.discussions.microsoft.com> wrote in message
news:B8D09C36-79B2-4027-A6B5-E49DE21B460B@.microsoft.com...
>I have a report with a data source that requires credentials to be stored
>in
> reporting service system.
> I use the CreateReport function to deploy this report but it doesn't store
> the credentials and these credentials are requested when it is about to
> render.
> How to store these credentials in order to avoid them to be asked during
> rendering'
> Thanks
>|||Is There a way to not create a shared data source?
I would the result obtained by the Deploy of Visual Studio.
"Jeff A. Stucker" wrote:
> In our case, we use script based on the scripting samples (in the RS install
> folder), but link the reports to a shared data source created with the
> credentials specfied:
> Public Sub CreateSQLDataSource(ByVal serverName as String, ByVal
> databaseName as String, ByVal userName as String, ByVal password as String)
> Dim name As String = databaseName
> Dim parent As String = "/"
> 'Define the data source definition.
> Dim definition As New DataSourceDefinition()
> definition.CredentialRetrieval = CredentialRetrievalEnum.Integrated
> definition.ConnectString = "data source=" & serverName & ";initial
> catalog=" & databaseName
> definition.Enabled = True
> definition.EnabledSpecified = True
> definition.Extension = "SQL"
> definition.ImpersonateUser = False
> definition.ImpersonateUserSpecified = True
> definition.Username = userName
> definition.Password = password
> 'Use the default prompt string.
> definition.Prompt = Nothing
> definition.WindowsCredentials = False
> Try
> rs.CreateDataSource(name, parent, False, definition, Nothing)
> Catch e As Exception
> Console.WriteLine(e.Message)
> End Try
> End Sub
>
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "Ale Dori" <AleDori@.discussions.microsoft.com> wrote in message
> news:B8D09C36-79B2-4027-A6B5-E49DE21B460B@.microsoft.com...
> >I have a report with a data source that requires credentials to be stored
> >in
> > reporting service system.
> >
> > I use the CreateReport function to deploy this report but it doesn't store
> > the credentials and these credentials are requested when it is about to
> > render.
> >
> > How to store these credentials in order to avoid them to be asked during
> > rendering'
> >
> > Thanks
> >
>
>|||Look in RS Books Online for GetReportDataSources and DataSource.Item
property
Hopefully that points you in the right direction.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Ale Dori" <AleDori@.discussions.microsoft.com> wrote in message
news:8D611134-94B3-4937-9372-61A00D6F6573@.microsoft.com...
> Is There a way to not create a shared data source?
> I would the result obtained by the Deploy of Visual Studio.
> "Jeff A. Stucker" wrote:
>> In our case, we use script based on the scripting samples (in the RS
>> install
>> folder), but link the reports to a shared data source created with the
>> credentials specfied:
>> Public Sub CreateSQLDataSource(ByVal serverName as String, ByVal
>> databaseName as String, ByVal userName as String, ByVal password as
>> String)
>> Dim name As String = databaseName
>> Dim parent As String = "/"
>> 'Define the data source definition.
>> Dim definition As New DataSourceDefinition()
>> definition.CredentialRetrieval = CredentialRetrievalEnum.Integrated
>> definition.ConnectString = "data source=" & serverName & ";initial
>> catalog=" & databaseName
>> definition.Enabled = True
>> definition.EnabledSpecified = True
>> definition.Extension = "SQL"
>> definition.ImpersonateUser = False
>> definition.ImpersonateUserSpecified = True
>> definition.Username = userName
>> definition.Password = password
>> 'Use the default prompt string.
>> definition.Prompt = Nothing
>> definition.WindowsCredentials = False
>> Try
>> rs.CreateDataSource(name, parent, False, definition, Nothing)
>> Catch e As Exception
>> Console.WriteLine(e.Message)
>> End Try
>> End Sub
>>
>> --
>> Cheers,
>> '(' Jeff A. Stucker
>> \
>> Business Intelligence
>> www.criadvantage.com
>> ---
>> "Ale Dori" <AleDori@.discussions.microsoft.com> wrote in message
>> news:B8D09C36-79B2-4027-A6B5-E49DE21B460B@.microsoft.com...
>> >I have a report with a data source that requires credentials to be
>> >stored
>> >in
>> > reporting service system.
>> >
>> > I use the CreateReport function to deploy this report but it doesn't
>> > store
>> > the credentials and these credentials are requested when it is about to
>> > render.
>> >
>> > How to store these credentials in order to avoid them to be asked
>> > during
>> > rendering'
>> >
>> > Thanks
>> >
>>

Create New SQL Server Mobile Edition Database

Hi, I follow the instruction to install SQL Server 2005 Mobile Edition, but when I try to change the Data Source to add a new connection, I don't find "Create New SQL Server Mobile Edition Database".
Does anyone know how to fix this?

Thanks Paolo GiorgioThe following steps should give you a brand new Sql Mobile database

- Go to the new connection dialog. Quickest way is to right click on Data Connections in the Server Explorer and choosing to add a connection

- Make sure you have the Sql Mobile provider selected. The full name is "Microsoft SQL Server Mobile Edition (.NET Framework Data Provider for SQL Server CE)"

- Now you should see a 'Create' button which will let you create a new Sql Mobile database|||

I just follow your step, but when i have to choose the Sql Mobile provider, "Microsoft SQL Server Mobile Edition" is not available, evenif i installed .Net compact framework for Sql Mobile.

|||Do you see Sql Mobile in your Add/Remove programs list?|||Which Program List?|||In your Control Panel->Add or Remove programs, do you see an entry which says "Microsoft Sql Server 2005 Mobile [ENU] Developer Tools"?|||No i don't find in the List.
I find only "Microsoft Sql Server 2005 Mobile [ENU] Beta 3".|||I try to find "Microsoft Sql Server 2005 Mobile [ENU] Developer Tools" by Google but i don't find anything|||

In the early beta days of "Microsoft SQL Server 2005 Mobile [ENU] Developer Tools" was registered as "Microsoft SQL Server 2005 Mobile [ENU] Beta 3".

If you have uninstalled and reinstalled .NET Fx after VS Installation, this problem would arise. Can you please search for Data Providers in .NET Framework config file.

%SystemDrive%\WINDOWS\Microsoft.NET\Framework\v2.0.xxxxx\config\machine.config

Open this file in NOTEPAD, and search for "SQL Server CE Data Provider". If it is not there then, you may try adding the following line AS IT IS in Data Providers section.

<add name="SQL Server CE Data Provider" invariant="Microsoft.SqlServerCe.Client" description=".NET Framework Data Provider for Microsoft SQL Server 2005 Mobile Edition" type="Microsoft.SqlServerCe.Client.SqlCeClientFactory, Microsoft.SqlServerCe.Client, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />

You may need to restart the VS or even the PC to make VS detect this new setting.

Let us know if this has worked for you!

Thanks,
Laxmi NRO, SQL Mobile

|||

Thank you, but it still not working.
We follow your instruction, reboot the PC, but the Dataprovider still not in the list of the Data Sources.

Any other solution?
Thanks,
Paolo Giorgio

|||DEVTOOLS ==> Microsoft SQL Server 2005 Mobile [ENU] Beta 3 or Microsoft SQL Server 2005 Mobile [ENU] Developer Tools

Uninstall & Reinstall DEVTOOLS using the MSI sqlmobile30devtoolsenu.msi in wcu\SQLCE sub directory of your Whidbey Installation Disc.

Goto ADD/REMOVE programs to uninstall DEVTOOLS.

Thanks,
Laxmi NRO, MSFT, SQL Mobile, Microsoft Corporation

Create New SQL Server Mobile Edition Database

Hi, I follow the instruction to install SQL Server 2005 Mobile Edition, but when I try to change the Data Source to add a new connection, I don't find "Create New SQL Server Mobile Edition Database".
Does anyone know how to fix this?

Thanks Paolo GiorgioThe following steps should give you a brand new Sql Mobile database

- Go to the new connection dialog. Quickest way is to right click on Data Connections in the Server Explorer and choosing to add a connection

- Make sure you have the Sql Mobile provider selected. The full name is "Microsoft SQL Server Mobile Edition (.NET Framework Data Provider for SQL Server CE)"

- Now you should see a 'Create' button which will let you create a new Sql Mobile database|||

I just follow your step, but when i have to choose the Sql Mobile provider, "Microsoft SQL Server Mobile Edition" is not available, evenif i installed .Net compact framework for Sql Mobile.

|||Do you see Sql Mobile in your Add/Remove programs list?|||Which Program List?|||In your Control Panel->Add or Remove programs, do you see an entry which says "Microsoft Sql Server 2005 Mobile [ENU] Developer Tools"?|||No i don't find in the List.
I find only "Microsoft Sql Server 2005 Mobile [ENU] Beta 3".|||I try to find "Microsoft Sql Server 2005 Mobile [ENU] Developer Tools" by Google but i don't find anything|||

In the early beta days of "Microsoft SQL Server 2005 Mobile [ENU] Developer Tools" was registered as "Microsoft SQL Server 2005 Mobile [ENU] Beta 3".

If you have uninstalled and reinstalled .NET Fx after VS Installation, this problem would arise. Can you please search for Data Providers in .NET Framework config file.

%SystemDrive%\WINDOWS\Microsoft.NET\Framework\v2.0.xxxxx\config\machine.config

Open this file in NOTEPAD, and search for "SQL Server CE Data Provider". If it is not there then, you may try adding the following line AS IT IS in Data Providers section.

<add name="SQL Server CE Data Provider" invariant="Microsoft.SqlServerCe.Client" description=".NET Framework Data Provider for Microsoft SQL Server 2005 Mobile Edition" type="Microsoft.SqlServerCe.Client.SqlCeClientFactory, Microsoft.SqlServerCe.Client, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />

You may need to restart the VS or even the PC to make VS detect this new setting.

Let us know if this has worked for you!

Thanks,
Laxmi NRO, SQL Mobile

|||

Thank you, but it still not working.
We follow your instruction, reboot the PC, but the Dataprovider still not in the list of the Data Sources.

Any other solution?
Thanks,
Paolo Giorgio

|||DEVTOOLS ==> Microsoft SQL Server 2005 Mobile [ENU] Beta 3 or Microsoft SQL Server 2005 Mobile [ENU] Developer Tools

Uninstall & Reinstall DEVTOOLS using the MSI sqlmobile30devtoolsenu.msi in wcu\SQLCE sub directory of your Whidbey Installation Disc.

Goto ADD/REMOVE programs to uninstall DEVTOOLS.

Thanks,
Laxmi NRO, MSFT, SQL Mobile, Microsoft Corporation

Create login account

I have installed MSDE 2000 on my computer. When I try to use ODBC to connect
to the data source, it keeps asking me to enter User Name and Password. Can
someone please tell me how I can create an account to connect to the SQL data
source?
> I have installed MSDE 2000 on my computer. When I try to use ODBC to
connect
> to the data source, it keeps asking me to enter User Name and Password.
Can
> someone please tell me how I can create an account to connect to the SQL
data
> source?
Here is a good starting point for MSDE security:
http://support.microsoft.com/default...;en-us;325022.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com

Create Flat File source programatically

I created a SSIS package, added Script task. created data flow task programatically, trying to add a flat file source component programatically. stuck at this point.

my goal is to add flat file source component to the data flow task and insert into a table in sql server using oledb destination component all programatically.

any help is appreciated. thanks.

You have to build a Flat File Connection Manager first and then reference it in your Flat File Source.

Have you looked at the BOL and previous posts in this forum for guidance?

Thanks.

|||

Iam creating the connection, I want know how I can read the flat file, and insert into a table. he is the code Iam using....

' Add the component to the dataFlow metadata collection

flatfileSource = dataFlowTask.ComponentMetaDataCollection.New()

' Set the common properties

flatfileSource.ComponentClassID = "DTSAdapter.FlatFileSource"

flatfileSource.Name = "FlatFileSource"

flatfileSource.Description = "Flat file source"

' Create an instance of the component

Dim inst As CManagedComponentWrapper = flatfileSource.Instantiate()

inst.ProvideComponentProperties()

' Associate the runtime ConnectionManager with the component

flatfileSource.RuntimeConnectionCollection(0).ConnectionManagerID _

= package.Connections("FlatFileConnection").ID

flatfileSource.RuntimeConnectionCollection(0).ConnectionManager _

= DtsConvert.ToConnectionManager90( _

package.Connections("FlatFileConnection"))

|||

You need to configure your connection manager by defining the file to use, column formats, and all additional properties before setting up your source.

There are also three more methods to call on your flat file source after the code you displayed:

AcquireConnections()

ReinitializeMetadata()

ReleaseConnections()

After that, you should hook up your source with the rest of a data flow and try to execute it.

HTH.

|||

sorry, Iam not explaining the problem correctly.

I do have those 3 lines of code. Iam lot where I have to loop through the columns or rows of my flat file. and how to map the columns to the destination table columns. any good example or reference please. thanks.

|||

Iam using the CreatePackage example in the samples. Iam able to create flat file source connection. not able to read the file.

Private Sub MapFlatFileDestinationColumns()

Dim wrp As CManagedComponentWrapper = flatfileDestination.Instantiate()

Dim vInput As IDTSVirtualInput90 = flatfileDestination.InputCollection(0).GetVirtualInput()

For Each vColumn As IDTSVirtualInputColumn90 In vInput.VirtualInputColumnCollection

wrp.SetUsageType(flatfileDestination.InputCollection(0).ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY)

Next

' For each column in the input collection

' find the corresponding external metadata column.

Dim exCol As IDTSExternalMetadataColumn90

For Each col As IDTSInputColumn90 In flatfileDestination.InputCollection(0).InputColumnCollection

exCol = flatfileDestination.InputCollection(0).ExternalMetadataColumnCollection(col.Name)

wrp.MapInputColumn(flatfileDestination.InputCollection(0).ID, col.ID, exCol.ID)

Next

End Sub

trying use this code to map the columns, now my flat file connection is a source instead of destination. hope Iam explaining the problem.

thanks.

|||

External columns are automatically mapped to output columns when you call ReinitializeMetadata of your flat file source.

A good way for testing this would be to configure your package to some point (only a flat file source initially), save the package to a file and then open that file in the designer and inspect the metadata using the advanced component editor.

Thanks.

|||

Hi, i'm trying to 'emulate' a flat file source component using the script component in a data flow task. I need to be able to create a flat file connection and get the columns in the script (the columns change now and then, so i can't create a 'fixed' flatfile connection manager).

After getting the columns, i need to create output columns for the script component and to go through each row of the file and assign the column details.

I have derived column transformation after this source file step, do i need to make changes to the input / output columns of the derived column component in any way?

I also have an oledb destination, how can i generate an sql command to be used in the oledb destination? I know i can use sql from variable in an oledb source, but i can't find it in the oledb destination

I don't need real code examples, but i need some help on what objects need to be created, what functions need to be called etc... I'm very new to ssis programming.

Create Flat File source programatically

I created a SSIS package, added Script task. created data flow task programatically, trying to add a flat file source component programatically. stuck at this point.

my goal is to add flat file source component to the data flow task and insert into a table in sql server using oledb destination component all programatically.

any help is appreciated. thanks.

You have to build a Flat File Connection Manager first and then reference it in your Flat File Source.

Have you looked at the BOL and previous posts in this forum for guidance?

Thanks.

|||

Iam creating the connection, I want know how I can read the flat file, and insert into a table. he is the code Iam using....

' Add the component to the dataFlow metadata collection

flatfileSource = dataFlowTask.ComponentMetaDataCollection.New()

' Set the common properties

flatfileSource.ComponentClassID = "DTSAdapter.FlatFileSource"

flatfileSource.Name = "FlatFileSource"

flatfileSource.Description = "Flat file source"

' Create an instance of the component

Dim inst As CManagedComponentWrapper = flatfileSource.Instantiate()

inst.ProvideComponentProperties()

' Associate the runtime ConnectionManager with the component

flatfileSource.RuntimeConnectionCollection(0).ConnectionManagerID _

= package.Connections("FlatFileConnection").ID

flatfileSource.RuntimeConnectionCollection(0).ConnectionManager _

= DtsConvert.ToConnectionManager90( _

package.Connections("FlatFileConnection"))

|||

You need to configure your connection manager by defining the file to use, column formats, and all additional properties before setting up your source.

There are also three more methods to call on your flat file source after the code you displayed:

AcquireConnections()

ReinitializeMetadata()

ReleaseConnections()

After that, you should hook up your source with the rest of a data flow and try to execute it.

HTH.

|||

sorry, Iam not explaining the problem correctly.

I do have those 3 lines of code. Iam lot where I have to loop through the columns or rows of my flat file. and how to map the columns to the destination table columns. any good example or reference please. thanks.

|||

Iam using the CreatePackage example in the samples. Iam able to create flat file source connection. not able to read the file.

Private Sub MapFlatFileDestinationColumns()

Dim wrp As CManagedComponentWrapper = flatfileDestination.Instantiate()

Dim vInput As IDTSVirtualInput90 = flatfileDestination.InputCollection(0).GetVirtualInput()

For Each vColumn As IDTSVirtualInputColumn90 In vInput.VirtualInputColumnCollection

wrp.SetUsageType(flatfileDestination.InputCollection(0).ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY)

Next

' For each column in the input collection

' find the corresponding external metadata column.

Dim exCol As IDTSExternalMetadataColumn90

For Each col As IDTSInputColumn90 In flatfileDestination.InputCollection(0).InputColumnCollection

exCol = flatfileDestination.InputCollection(0).ExternalMetadataColumnCollection(col.Name)

wrp.MapInputColumn(flatfileDestination.InputCollection(0).ID, col.ID, exCol.ID)

Next

End Sub

trying use this code to map the columns, now my flat file connection is a source instead of destination. hope Iam explaining the problem.

thanks.

|||

External columns are automatically mapped to output columns when you call ReinitializeMetadata of your flat file source.

A good way for testing this would be to configure your package to some point (only a flat file source initially), save the package to a file and then open that file in the designer and inspect the metadata using the advanced component editor.

Thanks.

|||

Hi, i'm trying to 'emulate' a flat file source component using the script component in a data flow task. I need to be able to create a flat file connection and get the columns in the script (the columns change now and then, so i can't create a 'fixed' flatfile connection manager).

After getting the columns, i need to create output columns for the script component and to go through each row of the file and assign the column details.

I have derived column transformation after this source file step, do i need to make changes to the input / output columns of the derived column component in any way?

I also have an oledb destination, how can i generate an sql command to be used in the oledb destination? I know i can use sql from variable in an oledb source, but i can't find it in the oledb destination

I don't need real code examples, but i need some help on what objects need to be created, what functions need to be called etc... I'm very new to ssis programming.

create data source, data source view from XML?

hi everybody,
i want to create data source and data source view for data mining, with using C Sharp.
i have create data source and data source view and export to XML file, but when i change to another computer, run those XML file, it return error, when i run statement to create and biuld mining model, what can i change on xml or how to run XML on another computer sucessfully,
and have i build data source and data source view, how to do it.?

thanks for you helps

Basically, you need to double check that you can the following: 1) You can connect to the server successfully; 2) The database for containing the target data source and data source view has been created, and you can access it; 3) You specify the connection appropriately.

Please double check your script against the following sample XMLA script for creating Data Source:

<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

<ParentObject>

<DatabaseID>Your Database ID</DatabaseID>

</ParentObject>

<ObjectDefinition>

<DataSource xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlnsdl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlnsdl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xsi:type="RelationalDataSource">

<ID>Your Data source ID</ID>

<Name>Your Data source name</Name>

<ConnectionString>Provider=SQLOLEDB.1;Data Source=Server Name;Integrated Security=SSPI;Initial Catalog= Your Database ID </ConnectionString>

<ImpersonationInfo>

<ImpersonationMode>Default</ImpersonationMode>

</ImpersonationInfo>

<Timeout>PT0S</Timeout>

</DataSource>

</ObjectDefinition>

</Create>

I have marked out the xml elements that you need to pay extra attention. As to the script for data source view, you also need to make sure the related data source exist on the server.

Good luck,