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 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 File Option In File Connection Manager

May be it's too late, but I think this requests could be scheduled at least
for a SP1 if it's not possible for the RTM.

1) Execute Task without debugger: it would be very nice to be able to execute
a single task without going in debugging mode. Just as you would ask "Start
Without Debugging CTRL+F5" but for a single task

2) Customize default properties for task and component: when you drag a task
on the package you get a default value for the properties that you could want
to change; often I need to change the same property in the same way each time
(for example I'd like to set the Batch Size for a OLE DB destination to 1000
instead than 0)

3) If you open a package and connections to data source are not available,
propose to "work offline" at the first failed connection.

IMHO, these features would be very important for developer productivity.

Marco Russo
http://sqljunkies.com/weblog/sqlbi

Hi Marco,

All great suggestions. Can you open them in BetaPlace? Unfortunately they're too late for SQL Server 2005 but we'd love to revisit them for the future.

For #2, Copy/Paste might be a short term solution.

regards,
ash|||I cannot find the thread where someone from Microsoft solicited suggestions for changes; I thought it was in a thread by Jamie Thompson, but somehow I am now overlooking it (or misremembering).
In any case, in the hopes that someone relevant sees this, I have three more.
* In any editor for any component, have a visible indicator on all properties which are supplanted at run-time by expressions. For example, have the values in red. This is to indicate that what you are seeing is not what will be used.
* Mark all the boxes which have event handlers attached. As above, this is to inform the human that there is more here than is apparent, and that the human should go track down the "more" (in this case, event handlers), to really find out what is happening.
* Have a list, or tree view, of all the event handlers. I've not figured out anyway to find, say 20 event handlers scattered across 500 boxes in many packages, except by the slowly going through and double-clicking on every box looking for event handlers. This seems to me a terrible way to find event handlers; I don't know if I'm overlooking something obvious (I hope), but in case not, and perhaps in any case?, this request for enhancement.

(I cannot log in to betaplace; I spent some time trying to do so, and waving my mouse around clicking on invisible buttons, and I never got past a page saying that my account would be activated someday, I think, and I cannot even remember the sequence of steps to get there again now.)
|||Great ideas Perry, I second all of them. The one about indicating in the control-flow which tasks/containers have eventhandlers on them is inspired.

Your idea about a visual representation of which properties have expressions on them has already been raised. Hopefully we'll see it in the next version!

-Jamie|||Yes, I third them! In addition, it would be nice to see the ability to copy/paste/modify multiple variables. Managing variables and managing parent variables in package configurations is not easily done incurrent state, especially when you are dealing with 100+ packages all sharing same/similar variables.|||How about something that shows underlying execution plan (akin to query plan) for the entire package with cost weightage?

regards,
Nitesh|||If you've been using Integration Services and have some feedback for how to make it better, we'd love to hear more.

Please add to this thread what you'd like to see added, fixed, changed, tweeked, or removed from Integration Services.

Your feedback is valuable.
We can't promise we'll be able to make it all happen, but certainly the guidance you give here will influence planning for the next version of Integration services.

Thanks,
|||The biggest pains for me so far in designing our ETL for our warehouse have been:

- Reusing data flows, I am doing a hack that lets the data flows run over a set of tables, performing work on the common columns. What would be useful is if you can define a "table set" within SSIS and then bind a data flow to the table set (where the table set is limited to the columns/types common across all tables.) I don't know if this would have to fit into the foreach stuff, or if it would be all within the data flow itself.
- Working with tables with LOTs of columns. I have a table with about 200 columns or so that I need to do a slowly changing dimension transform on. I also need to write script components that output 200 columns for inserting into the table. The script task input/output dialog makes it painful to enter the variables one by one, and the SCD wizard makes it too painful to do it by hand, so I actually went into the XML itself and changed the stuff (carefully :)) Not sure how to address this, but another major thing that's probably more of an issue to fix is that the SCD component goes insanely slow when you double click on it if you have a whole lot of columns like me. (Takes a good 3-5 minutes to come up.)
- I posted a thread earlier, but to re-iterate -- since we can't reuse data flows most of the time nor script tasks, cut and pasting should be cleaned up a bit so the formatting doesn't get completely destroyed when you paste in a huge block of data flow/control flow tasks.
- Undo! :)
- Another small feature suggestion would be a more complex lookup task that had inherently a built in behavior for when the lookup fails. I have an "Unknown" member for each dimension, and if my lookup fails for a certain member of a fact table I need to link it to the Unknown member. What this translates to are a conditional split for if the key being looked up is NULL (or 0) and then setting it to zero if it was NULL or actually doing the lookup, and then doing a union of the rows again. I realize I could just rely upon the error output of the Lookup, but that seems broken to me since "Unknown" is an expected behavior. The ideal situation is for the Lookup Task to have an optional default value to use if the lookup fails and/or if the column being looked up is NULL.

|||Great suggestions!
Keep them coming!
K|||On the note of the Lookup Task, I think it's probably an extremely common use case where you have to translate a set of fact table business keys to surrogate dimension keys. (Project REAL, for example, seems to have a huge data flow to do this, and so do I.) With this in mind, it might be useful to have one single lookup task to translate all the keys (my current package has like 15 lookup tasks and a whole lot of conditionals for the aforementioned "Unknown" behavior checking.) Having one task that has a series of "table, join key, lookup value, lookup column, default value if null or not found" would consolidate my 40-50 tasks into a single one (which probably could internally do the lookups in parallel, increasing performance.)

|||

Ok here's my wish list,

1. Advanced Editor support for >1 input. (This should enable the script component with > 1 input)

2. Read only access to the whole package from componentmetadata, not just that related to the component.

3. Parallel For each loops. Performance.

4. Option on Raw file to create once per package. This allows the same raw destination to be used in a loop

5. Debug support for script component (not just the task)

6. Parallel multicast. Says it all really performance (I know the memory issue but it should be an option. Allows for the creation of a new execution tree. It would be great if the compiler (process that produces execution tree) could figure this out. This would probably need to now the distribution of data being processed.

7. Suggest Types for flat files to provide the option of reading a whole file. This is to avoid encountering bugs during run time, which is very time consuming.

8. Suggest types for flat files to all for data to be just strings, rather than convert data to proper types. This is for performance

9. IIS Log file connection both source and destination would be good. But would settle for source.

10. Multiple data readers out of package. This would enable a package to produce multiple summaries and have them consumed by a report or other application.

11. Be able to drag a connection from one component to another. Its a real pain to delete one connection to be recreate it to the other component. This looses any data viewers

That'll do for now.

|||Thanks Simon. Excellent input. Thanks!
Anyone else?
K|||

I would like to see 3 big key improvements within SSIS. I have raised this before, Kirk asked me to send him a mail, which I never got around to do it. Sorry Kirk.

1. Data Profiler. This is quite crucial when you analyse the data to determine how bad the data is etc. Yes I know, the feature is sort of there but it is not good enough. It need to be improved considerably. We should be able to put any type of files and profile it before we start the work. Saves lot of time. It should be quick and simple to do, in the meantime it should be powerful.

2. Meta Data Management Tool. This can be web based tool / something along those line, which can be given to the business users to indetify for example, how we derive Net Sales column in the fact table. From my own experience, spent hours / days explaining how we derive each column. In a huge data warehousing environment it is very time consuming. This is not fun, i rather be writing SSIS package instead .

3. Dependancy Analysis. I would like to see a tool that would do the dependancy analysis on the fly, if I specify, that I am going to drop column A, it should run some kind of routine and tells me if you drop this column from your SSIS package, it will affect this table, cube and package etc. Run the check against the metadata only, therefore it should be quick. Save lots of time and avoid mistakes happening.

These are my requests. I know they are big requests, but I think we do need them in Microsoft environment as other competitors got similar products.

What everyone else think about these features.

Thanks
Sutha

|||I've already fed alot of stuff back to Kirk offline but just for the edification of everyone else, here are some ideas:
http://blogs.conchango.com/jamiethomson/archive/2005/05/09/1398.aspx
http://blogs.conchango.com/jamiethomson/archive/2005/05/16/1419.aspx
http://blogs.conchango.com/jamiethomson/archive/2005/02/05/929.aspx
http://blogs.conchango.com/jamiethomson/archive/2005/05/26/1470.aspx
http://blogs.conchango.com/jamiethomson/archive/2005/09/07/2130.aspx

-Jamie|||Sometimes .dtsx files get corrupted. Don't know why...don't know how!

It would be useful to have a tool to analyse a corrupt .dtsx file to tell you exactly what's wrong with it, how to fix it, possibly even fix it for you etc.... The error messages you get when trying to load it aren't really useful.

-Jamie

Create File Option In File Connection Manager

May be it's too late, but I think this requests could be scheduled at least
for a SP1 if it's not possible for the RTM.

1) Execute Task without debugger: it would be very nice to be able to execute
a single task without going in debugging mode. Just as you would ask "Start
Without Debugging CTRL+F5" but for a single task

2) Customize default properties for task and component: when you drag a task
on the package you get a default value for the properties that you could want
to change; often I need to change the same property in the same way each time
(for example I'd like to set the Batch Size for a OLE DB destination to 1000
instead than 0)

3) If you open a package and connections to data source are not available,
propose to "work offline" at the first failed connection.

IMHO, these features would be very important for developer productivity.

Marco Russo
http://sqljunkies.com/weblog/sqlbi

Hi Marco,

All great suggestions. Can you open them in BetaPlace? Unfortunately they're too late for SQL Server 2005 but we'd love to revisit them for the future.

For #2, Copy/Paste might be a short term solution.

regards,
ash|||I cannot find the thread where someone from Microsoft solicited suggestions for changes; I thought it was in a thread by Jamie Thompson, but somehow I am now overlooking it (or misremembering).
In any case, in the hopes that someone relevant sees this, I have three more.
* In any editor for any component, have a visible indicator on all properties which are supplanted at run-time by expressions. For example, have the values in red. This is to indicate that what you are seeing is not what will be used.
* Mark all the boxes which have event handlers attached. As above, this is to inform the human that there is more here than is apparent, and that the human should go track down the "more" (in this case, event handlers), to really find out what is happening.
* Have a list, or tree view, of all the event handlers. I've not figured out anyway to find, say 20 event handlers scattered across 500 boxes in many packages, except by the slowly going through and double-clicking on every box looking for event handlers. This seems to me a terrible way to find event handlers; I don't know if I'm overlooking something obvious (I hope), but in case not, and perhaps in any case?, this request for enhancement.

(I cannot log in to betaplace; I spent some time trying to do so, and waving my mouse around clicking on invisible buttons, and I never got past a page saying that my account would be activated someday, I think, and I cannot even remember the sequence of steps to get there again now.)|||Great ideas Perry, I second all of them. The one about indicating in the control-flow which tasks/containers have eventhandlers on them is inspired.

Your idea about a visual representation of which properties have expressions on them has already been raised. Hopefully we'll see it in the next version!

-Jamie|||Yes, I third them! In addition, it would be nice to see the ability to copy/paste/modify multiple variables. Managing variables and managing parent variables in package configurations is not easily done incurrent state, especially when you are dealing with 100+ packages all sharing same/similar variables.|||How about something that shows underlying execution plan (akin to query plan) for the entire package with cost weightage?

regards,
Nitesh|||If you've been using Integration Services and have some feedback for how to make it better, we'd love to hear more.

Please add to this thread what you'd like to see added, fixed, changed, tweeked, or removed from Integration Services.

Your feedback is valuable.
We can't promise we'll be able to make it all happen, but certainly the guidance you give here will influence planning for the next version of Integration services.

Thanks,
|||The biggest pains for me so far in designing our ETL for our warehouse have been:

- Reusing data flows, I am doing a hack that lets the data flows run over a set of tables, performing work on the common columns. What would be useful is if you can define a "table set" within SSIS and then bind a data flow to the table set (where the table set is limited to the columns/types common across all tables.) I don't know if this would have to fit into the foreach stuff, or if it would be all within the data flow itself.
- Working with tables with LOTs of columns. I have a table with about 200 columns or so that I need to do a slowly changing dimension transform on. I also need to write script components that output 200 columns for inserting into the table. The script task input/output dialog makes it painful to enter the variables one by one, and the SCD wizard makes it too painful to do it by hand, so I actually went into the XML itself and changed the stuff (carefully :)) Not sure how to address this, but another major thing that's probably more of an issue to fix is that the SCD component goes insanely slow when you double click on it if you have a whole lot of columns like me. (Takes a good 3-5 minutes to come up.)
- I posted a thread earlier, but to re-iterate -- since we can't reuse data flows most of the time nor script tasks, cut and pasting should be cleaned up a bit so the formatting doesn't get completely destroyed when you paste in a huge block of data flow/control flow tasks.
- Undo! :)
- Another small feature suggestion would be a more complex lookup task that had inherently a built in behavior for when the lookup fails. I have an "Unknown" member for each dimension, and if my lookup fails for a certain member of a fact table I need to link it to the Unknown member. What this translates to are a conditional split for if the key being looked up is NULL (or 0) and then setting it to zero if it was NULL or actually doing the lookup, and then doing a union of the rows again. I realize I could just rely upon the error output of the Lookup, but that seems broken to me since "Unknown" is an expected behavior. The ideal situation is for the Lookup Task to have an optional default value to use if the lookup fails and/or if the column being looked up is NULL.|||Great suggestions!
Keep them coming!
K|||On the note of the Lookup Task, I think it's probably an extremely common use case where you have to translate a set of fact table business keys to surrogate dimension keys. (Project REAL, for example, seems to have a huge data flow to do this, and so do I.) With this in mind, it might be useful to have one single lookup task to translate all the keys (my current package has like 15 lookup tasks and a whole lot of conditionals for the aforementioned "Unknown" behavior checking.) Having one task that has a series of "table, join key, lookup value, lookup column, default value if null or not found" would consolidate my 40-50 tasks into a single one (which probably could internally do the lookups in parallel, increasing performance.)
|||

Ok here's my wish list,

1. Advanced Editor support for >1 input. (This should enable the script component with > 1 input)

2. Read only access to the whole package from componentmetadata, not just that related to the component.

3. Parallel For each loops. Performance.

4. Option on Raw file to create once per package. This allows the same raw destination to be used in a loop

5. Debug support for script component (not just the task)

6. Parallel multicast. Says it all really performance (I know the memory issue but it should be an option. Allows for the creation of a new execution tree. It would be great if the compiler (process that produces execution tree) could figure this out. This would probably need to now the distribution of data being processed.

7. Suggest Types for flat files to provide the option of reading a whole file. This is to avoid encountering bugs during run time, which is very time consuming.

8. Suggest types for flat files to all for data to be just strings, rather than convert data to proper types. This is for performance

9. IIS Log file connection both source and destination would be good. But would settle for source.

10. Multiple data readers out of package. This would enable a package to produce multiple summaries and have them consumed by a report or other application.

11. Be able to drag a connection from one component to another. Its a real pain to delete one connection to be recreate it to the other component. This looses any data viewers

That'll do for now.

|||Thanks Simon. Excellent input. Thanks!
Anyone else?
K|||

I would like to see 3 big key improvements within SSIS. I have raised this before, Kirk asked me to send him a mail, which I never got around to do it. Sorry Kirk.

1. Data Profiler. This is quite crucial when you analyse the data to determine how bad the data is etc. Yes I know, the feature is sort of there but it is not good enough. It need to be improved considerably. We should be able to put any type of files and profile it before we start the work. Saves lot of time. It should be quick and simple to do, in the meantime it should be powerful.

2. Meta Data Management Tool. This can be web based tool / something along those line, which can be given to the business users to indetify for example, how we derive Net Sales column in the fact table. From my own experience, spent hours / days explaining how we derive each column. In a huge data warehousing environment it is very time consuming. This is not fun, i rather be writing SSIS package instead .

3. Dependancy Analysis. I would like to see a tool that would do the dependancy analysis on the fly, if I specify, that I am going to drop column A, it should run some kind of routine and tells me if you drop this column from your SSIS package, it will affect this table, cube and package etc. Run the check against the metadata only, therefore it should be quick. Save lots of time and avoid mistakes happening.

These are my requests. I know they are big requests, but I think we do need them in Microsoft environment as other competitors got similar products.

What everyone else think about these features.

Thanks
Sutha

|||I've already fed alot of stuff back to Kirk offline but just for the edification of everyone else, here are some ideas:
http://blogs.conchango.com/jamiethomson/archive/2005/05/09/1398.aspx
http://blogs.conchango.com/jamiethomson/archive/2005/05/16/1419.aspx
http://blogs.conchango.com/jamiethomson/archive/2005/02/05/929.aspx
http://blogs.conchango.com/jamiethomson/archive/2005/05/26/1470.aspx
http://blogs.conchango.com/jamiethomson/archive/2005/09/07/2130.aspx

-Jamie|||Sometimes .dtsx files get corrupted. Don't know why...don't know how!

It would be useful to have a tool to analyse a corrupt .dtsx file to tell you exactly what's wrong with it, how to fix it, possibly even fix it for you etc.... The error messages you get when trying to load it aren't really useful.

-Jamie

Create Directory with a File System Task

Hi!

I'm having a bit of a problem implementing a File System Task to Create a directory and would appreciate some help if possible.

I want to create a date directory so I can move files to once they are imported successfully. The date portion for the directory comes from the import file whose name is variable and in the format of PerfLog_<yyyymmdd>.aud. So, in essence, if I am processing a file named Perflog_20060913.aud, when I am done processing it I want to create a directory c:\myprog\20060913 and move my processed file there.

Can anyone help me? Please.

Here's how I did it:

1. Create your File System task

2. Set Operation to "Create Directory"

3. Under SourceConnection, select "<New Connection>"

4. Set Usage Type to "Create Folder"

5. Select any folder, or enter a dummy value for "File" - we'll be setting this with an expression

5. Click OK, and then select your new file connection in the Connection Managers window

6. On the properties window, bring up the Property Expression Editor

7. Select the "ConnectionString" property

8. For the expression, use this:

"c:\\myprog\\" + SUBSTRING( @.[User::filename], 9, 8 )

(replace User::filename with the name of your variable).

Hope that helps!

|||Thanks Matt! I believe this is what I want to create the directory.|||

There is one additional problem I'm having with this. My filename variable is perflog_*.aud, because it goes through multiple flat files, so the SUBSTRING(@.[user::filename,9,8) is picking up *.aud. Please tell me what I'm doing wrong...

Thanks

|||

Are you using a Foreach Loop Container to go through your flat files? If your variable contains the wildcard character, it sounds like you haven't setup the container to store the current filename properly. You might want to take a look at the help page for the Foreach Loop. Step 7 covers how to map the file to a variable.

|||Thanks again Matt, that was my problem!|||

I seem to be running into another problem with this.

The create directory works fine for the first file that is moved. In the second iteration of the for loop I get a warning that the the directory exists, which is fine and I have selected use directory if it exists. The problem is that I then get an error,

Error: 0xC002F304 at Create Directory Task, File System Task: An error occurred with the following error message: "The directory is not empty.

".

What am I missing?

Thanks

|||

I'm having the same problem that you have listed here where the first create directory call works fine but the second (ie. if the directory exists) fails even though I have specified that this should not be an error (ie. Set UseDirectoryIfExists = True on the task).

I was wondering if you've found a solution to this problem.

Thanks,

Matt

Create Directory with a File System Task

Hi!

I'm having a bit of a problem implementing a File System Task to Create a directory and would appreciate some help if possible.

I want to create a date directory so I can move files to once they are imported successfully. The date portion for the directory comes from the import file whose name is variable and in the format of PerfLog_<yyyymmdd>.aud. So, in essence, if I am processing a file named Perflog_20060913.aud, when I am done processing it I want to create a directory c:\myprog\20060913 and move my processed file there.

Can anyone help me? Please.

Here's how I did it:

1. Create your File System task

2. Set Operation to "Create Directory"

3. Under SourceConnection, select "<New Connection>"

4. Set Usage Type to "Create Folder"

5. Select any folder, or enter a dummy value for "File" - we'll be setting this with an expression

5. Click OK, and then select your new file connection in the Connection Managers window

6. On the properties window, bring up the Property Expression Editor

7. Select the "ConnectionString" property

8. For the expression, use this:

"c:\\myprog\\" + SUBSTRING( @.[User::filename], 9, 8 )

(replace User::filename with the name of your variable).

Hope that helps!

|||Thanks Matt! I believe this is what I want to create the directory.|||

There is one additional problem I'm having with this. My filename variable is perflog_*.aud, because it goes through multiple flat files, so the SUBSTRING(@.[user::filename,9,8) is picking up *.aud. Please tell me what I'm doing wrong...

Thanks

|||

Are you using a Foreach Loop Container to go through your flat files? If your variable contains the wildcard character, it sounds like you haven't setup the container to store the current filename properly. You might want to take a look at the help page for the Foreach Loop. Step 7 covers how to map the file to a variable.

|||Thanks again Matt, that was my problem!|||

I seem to be running into another problem with this.

The create directory works fine for the first file that is moved. In the second iteration of the for loop I get a warning that the the directory exists, which is fine and I have selected use directory if it exists. The problem is that I then get an error,

Error: 0xC002F304 at Create Directory Task, File System Task: An error occurred with the following error message: "The directory is not empty.

".

What am I missing?

Thanks

|||

I'm having the same problem that you have listed here where the first create directory call works fine but the second (ie. if the directory exists) fails even though I have specified that this should not be an error (ie. Set UseDirectoryIfExists = True on the task).

I was wondering if you've found a solution to this problem.

Thanks,

Matt

Create Directory with a File System Task

Hi!

I'm having a bit of a problem implementing a File System Task to Create a directory and would appreciate some help if possible.

I want to create a date directory so I can move files to once they are imported successfully. The date portion for the directory comes from the import file whose name is variable and in the format of PerfLog_<yyyymmdd>.aud. So, in essence, if I am processing a file named Perflog_20060913.aud, when I am done processing it I want to create a directory c:\myprog\20060913 and move my processed file there.

Can anyone help me? Please.

Here's how I did it:

1. Create your File System task

2. Set Operation to "Create Directory"

3. Under SourceConnection, select "<New Connection>"

4. Set Usage Type to "Create Folder"

5. Select any folder, or enter a dummy value for "File" - we'll be setting this with an expression

5. Click OK, and then select your new file connection in the Connection Managers window

6. On the properties window, bring up the Property Expression Editor

7. Select the "ConnectionString" property

8. For the expression, use this:

"c:\\myprog\\" + SUBSTRING( @.[User::filename], 9, 8 )

(replace User::filename with the name of your variable).

Hope that helps!

|||Thanks Matt! I believe this is what I want to create the directory.|||

There is one additional problem I'm having with this. My filename variable is perflog_*.aud, because it goes through multiple flat files, so the SUBSTRING(@.[user::filename,9,8) is picking up *.aud. Please tell me what I'm doing wrong...

Thanks

|||

Are you using a Foreach Loop Container to go through your flat files? If your variable contains the wildcard character, it sounds like you haven't setup the container to store the current filename properly. You might want to take a look at the help page for the Foreach Loop. Step 7 covers how to map the file to a variable.

|||Thanks again Matt, that was my problem!|||

I seem to be running into another problem with this.

The create directory works fine for the first file that is moved. In the second iteration of the for loop I get a warning that the the directory exists, which is fine and I have selected use directory if it exists. The problem is that I then get an error,

Error: 0xC002F304 at Create Directory Task, File System Task: An error occurred with the following error message: "The directory is not empty.

".

What am I missing?

Thanks

|||

I'm having the same problem that you have listed here where the first create directory call works fine but the second (ie. if the directory exists) fails even though I have specified that this should not be an error (ie. Set UseDirectoryIfExists = True on the task).

I was wondering if you've found a solution to this problem.

Thanks,

Matt