Create Report Snapshot from SSIS

I need to create a SSRS report snapshot at the end of an SSIS package (seems like this would be a common task). I tried connecting to the ReportingService web service to do this but I get the following error when after I select the web service in the Web Service Task:

This version of the Web Services Description Language (WSDL) is not supported.

Is there as simple way to do what I want?

The Web Service task has some limitations in respect to what WSDL it can consume. Put bluntly it is not much use in practice.

The easiest way to do this would be to write a custom task. You could do this in a script task, but generating the proxy class from WSDL and installing it for use by the Script Task would be almost as much work as just doing it all in a custom task. There are only a few lines of code required to actually call the methods, it is not hard.

|||

I'm interested in doing the exact same thing. Can you provide a sample or template for how to do this? By writting a custom task, do I still have to create the proxy? What would the custom task look like?

Thanks for your help!

|||

See the ReportingService2005.UpdateReportExecutionSnapshot method.

The proxy class is just WS wrapper class, if you write a custom task, then I would expect you to add the web reference directly into you project, there is no point in creating a proxy. The Script task does not allow you to add web references, so it is a must in that case.

Create Report Snapshot from SSIS

I need to create a SSRS report snapshot at the end of an SSIS package (seems like this would be a common task). I tried connecting to the ReportingService web service to do this but I get the following error when after I select the web service in the Web Service Task:

This version of the Web Services Description Language (WSDL) is not supported.

Is there as simple way to do what I want?

The Web Service task has some limitations in respect to what WSDL it can consume. Put bluntly it is not much use in practice.

The easiest way to do this would be to write a custom task. You could do this in a script task, but generating the proxy class from WSDL and installing it for use by the Script Task would be almost as much work as just doing it all in a custom task. There are only a few lines of code required to actually call the methods, it is not hard.

|||

I'm interested in doing the exact same thing. Can you provide a sample or template for how to do this? By writting a custom task, do I still have to create the proxy? What would the custom task look like?

Thanks for your help!

|||

See the ReportingService2005.UpdateReportExecutionSnapshot method.

The proxy class is just WS wrapper class, if you write a custom task, then I would expect you to add the web reference directly into you project, there is no point in creating a proxy. The Script task does not allow you to add web references, so it is a must in that case.

Create Report Snapshot from SSIS

I need to create a SSRS report snapshot at the end of an SSIS package (seems like this would be a common task). I tried connecting to the ReportingService web service to do this but I get the following error when after I select the web service in the Web Service Task:

This version of the Web Services Description Language (WSDL) is not supported.

Is there as simple way to do what I want?

The Web Service task has some limitations in respect to what WSDL it can consume. Put bluntly it is not much use in practice.

The easiest way to do this would be to write a custom task. You could do this in a script task, but generating the proxy class from WSDL and installing it for use by the Script Task would be almost as much work as just doing it all in a custom task. There are only a few lines of code required to actually call the methods, it is not hard.

|||

I'm interested in doing the exact same thing. Can you provide a sample or template for how to do this? By writting a custom task, do I still have to create the proxy? What would the custom task look like?

Thanks for your help!

|||

See the ReportingService2005.UpdateReportExecutionSnapshot method.

The proxy class is just WS wrapper class, if you write a custom task, then I would expect you to add the web reference directly into you project, there is no point in creating a proxy. The Script task does not allow you to add web references, so it is a must in that case.

Create N rows from 1 Row?

Hello...

I have a small question about how to realize something in SSIS.

We are rewriting an Application and we will be normalizing a table. The current Table has Data in the Format:

ID - Name - Type500 - Type1000 - Type2000

1 - Test - 2 - 1 - 0

2 - Test 2 - 0 - 2 - 1

The Targets would be:

ID - Name

1 - Test

2 - Test

TargetID , Type_ID , Date (+ ID Field omitted here)

1 - 1 - 1.1.1900

1 - 1 - 1.1.1900

1 - 2 - 1.1.1900

2 - 2 - 1.1.1900

2 - 2 - 1.1.1900

2 - 3 - 1.1.1900

So basically we need to generate N type rows for each "Count" in the Type Fields. So whats the best aproach to convert this data in an SSIS Package? Currently I am thinking about calling a SP to split the rows, but I dont "like" this aproach since it would place import/migration logic into the database and I would have to "clean up" later.

I can see two choices-

1 - Use a script component.

2 - Write a custom component.

For information, this would be an asynchronous component, as discussed in Books Online.

There is a similar problem covered here http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=511608&SiteID=1

|||

Thanks...

The link is exactly what I was looking for...

Create MSI Installer for SSIS Package

Can anyone sugest me steps to create the MSI Installer for SSIS packages

That is a bit of an open question.

I would use WIX to create the MSI. All you need to do is copy the SSIS package file to a folder location.

How you specify the location is up to you, and really depends on yoru requirements.

You could also create a Setup project in VS 2005, if you have it, or use one of the many other third party MSI creation tools.

If you wanted to install the packages in a manged location that would be harder. For the "SSIS Package Store" folder, then just copy the files to the folder. It may get a bit mnessy, but some points to help find that location. Try the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTS\Setup\DTSPath key to get the root folder. By default the sub-folder you want is Packages, but to be sure you would have to read the SSIS Service config file. To find this look in HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTS\ServiceConfigFile

You may have to write a custom action to handle the manged stores, so for that reason I'd say go with files!

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 database within SSIS package

I want to create a package that imports data from a Visual Foxpro database to SQL Server 2005 Express database. I used the wizard in BI Development Studio (similar to the DTS in SQL Server 2000) to create a package and noticed that the SQL statements created in the Preparation SQL Task only has code for creating tables. I want to make the package such that it first creates the destination database before creating all the related tables in it! When I tried to edit the SQL code to include DROP DATABASE and/or CREATE DATABASE statements, these were rejected.

Is it possible to do this or do I have to first create the database outside the package and then call the package? I want to make this a seamless process for clients who do not have the know-how of SQL Server database administration.

HELP!!!!!!

Can't you just run a CREATE DATABASE DDL statement in a execute sql task?

|||

Peter K wrote:

Can't you just run a CREATE DATABASE DDL statement in a execute sql task?

yes. i'm afraid that the import/export wizard won't be sufficient to solve this problem.