Marking posts to get faster attention from SSIS Team

Here is one practical initiative for improving the service SSIS team provides to this forum:

We asked our MVPs to mark posts that need attention from the SSIS team and built a system to fetch those messages and send them directly to our internal distribution list. The report will contain all currently marked messages in non-answered threads.That way, forum posts that MVPs target to us will be much easier for us to find, and the response time for them will be shorter.

The MVPs have accepted to help us with this, and we started extracting messages marked by selected users and sending daily reports to the team.

Please let us know what you think about this action and keep an eye on how we are doing with providing answers on marked threads.

Thank you,

Bob Bojanic, SSIS Team

Well done. I think this is a good idea. Is there any difference between this approach and the SSIS MSDN manged forum?|||

Thanks, Larry.

I am not sure what you are referring to as "MSDN managed forum".

|||http://msdn.microsoft.com/newsgroups/managed/default.aspx?dg=microsoft.public.sqlserver.dts|||

Pardon, I meant managed newsgroup.

http://msdn.microsoft.com/newsgroups/managed/default.aspx?dg=microsoft.public.sqlserver.dts

|||

Oh, I see.

This seems to be a web client to our newsgroup discussions. We kind of pre-oriented into using this new forum (forums.microsoft.com) in the course of the last couple of years. The newsgroups seem to be still pretty active but there is no as many MS answerers as you can find here. The groups appear to be self-sustained though, and if you prefer less traffic and more focused discussion you should keep visiting them.

This initiative of marking threads to be looked at by the SSIS team is going to be limited to this forum. We do not have access to the server where the newsgroup posts are kept.

Thanks.

|||

Sounds like a good idea, but how about getting the list more than once a day, maybe once an hour?

Gary

|||

Gary Watson wrote:

Sounds like a good idea, but how about getting the list more than once a day, maybe once an hour?

Gary

Well, likely because the Microsoft guys actually have work to do! Once a day is appropriate, I think. Microsoft employees are under no obligation to answer any question on this forum; certainly in no set time frame.|||

Phil is right; answering posts on this forum is not going to give us a good excuse for not finishing our daily tasks.

This initiative is an attempt to get the questions from here closer to potential answerers. Usually, when we (SSIS team members) read these threads we try to use our time efficiently; so if we do not know the answer or do not have time to do some follow up investigation we will just skip the question hoping somebody else would answer it. If a right person does not come across those hard yet important questions in a few days, they might be left unanswered.

Our tool will find such questions, if marked appropriately, and make them visible to the entire SSIS team. There is no time limit when we have to answer them. They are listed in our report until answered. Most of those questions get answered in 2-3 days (it depends on the time it gets into the report, time zones, weekends, etc). Some of them might even require more time for investigations.

It would not make sense to send the report every hour if we cannot commit to the turnaround time on the same scale. The most important aspect is to get them marked so they do not get lost under new piles of posts.

Thanks,

Bob

|||

Hi Bob,

I did not understand before the use of this forum, thanks for clearing that up for me.

I am grateful that you guys can help out whenever you can.

Regards

Gary

sql

Marking posts to get faster attention from SSIS Team

Here is one practical initiative for improving the service SSIS team provides to this forum:

We asked our MVPs to mark posts that need attention from the SSIS team and built a system to fetch those messages and send them directly to our internal distribution list. The report will contain all currently marked messages in non-answered threads.That way, forum posts that MVPs target to us will be much easier for us to find, and the response time for them will be shorter.

The MVPs have accepted to help us with this, and we started extracting messages marked by selected users and sending daily reports to the team.

Please let us know what you think about this action and keep an eye on how we are doing with providing answers on marked threads.

Thank you,

Bob Bojanic, SSIS Team

Well done. I think this is a good idea. Is there any difference between this approach and the SSIS MSDN manged forum?|||

Thanks, Larry.

I am not sure what you are referring to as "MSDN managed forum".

|||http://msdn.microsoft.com/newsgroups/managed/default.aspx?dg=microsoft.public.sqlserver.dts|||

Pardon, I meant managed newsgroup.

http://msdn.microsoft.com/newsgroups/managed/default.aspx?dg=microsoft.public.sqlserver.dts

|||

Oh, I see.

This seems to be a web client to our newsgroup discussions. We kind of pre-oriented into using this new forum (forums.microsoft.com) in the course of the last couple of years. The newsgroups seem to be still pretty active but there is no as many MS answerers as you can find here. The groups appear to be self-sustained though, and if you prefer less traffic and more focused discussion you should keep visiting them.

This initiative of marking threads to be looked at by the SSIS team is going to be limited to this forum. We do not have access to the server where the newsgroup posts are kept.

Thanks.

|||

Sounds like a good idea, but how about getting the list more than once a day, maybe once an hour?

Gary

|||

Gary Watson wrote:

Sounds like a good idea, but how about getting the list more than once a day, maybe once an hour?

Gary

Well, likely because the Microsoft guys actually have work to do! Once a day is appropriate, I think. Microsoft employees are under no obligation to answer any question on this forum; certainly in no set time frame.|||

Phil is right; answering posts on this forum is not going to give us a good excuse for not finishing our daily tasks.

This initiative is an attempt to get the questions from here closer to potential answerers. Usually, when we (SSIS team members) read these threads we try to use our time efficiently; so if we do not know the answer or do not have time to do some follow up investigation we will just skip the question hoping somebody else would answer it. If a right person does not come across those hard yet important questions in a few days, they might be left unanswered.

Our tool will find such questions, if marked appropriately, and make them visible to the entire SSIS team. There is no time limit when we have to answer them. They are listed in our report until answered. Most of those questions get answered in 2-3 days (it depends on the time it gets into the report, time zones, weekends, etc). Some of them might even require more time for investigations.

It would not make sense to send the report every hour if we cannot commit to the turnaround time on the same scale. The most important aspect is to get them marked so they do not get lost under new piles of posts.

Thanks,

Bob

|||

Hi Bob,

I did not understand before the use of this forum, thanks for clearing that up for me.

I am grateful that you guys can help out whenever you can.

Regards

Gary

Marking posts to get faster attention from SSIS Team

Here is one practical initiative for improving the service SSIS team provides to this forum:

We asked our MVPs to mark posts that need attention from the SSIS team and built a system to fetch those messages and send them directly to our internal distribution list. The report will contain all currently marked messages in non-answered threads.That way, forum posts that MVPs target to us will be much easier for us to find, and the response time for them will be shorter.

The MVPs have accepted to help us with this, and we started extracting messages marked by selected users and sending daily reports to the team.

Please let us know what you think about this action and keep an eye on how we are doing with providing answers on marked threads.

Thank you,

Bob Bojanic, SSIS Team

Well done. I think this is a good idea. Is there any difference between this approach and the SSIS MSDN manged forum?|||

Thanks, Larry.

I am not sure what you are referring to as "MSDN managed forum".

|||http://msdn.microsoft.com/newsgroups/managed/default.aspx?dg=microsoft.public.sqlserver.dts|||

Pardon, I meant managed newsgroup.

http://msdn.microsoft.com/newsgroups/managed/default.aspx?dg=microsoft.public.sqlserver.dts

|||

Oh, I see.

This seems to be a web client to our newsgroup discussions. We kind of pre-oriented into using this new forum (forums.microsoft.com) in the course of the last couple of years. The newsgroups seem to be still pretty active but there is no as many MS answerers as you can find here. The groups appear to be self-sustained though, and if you prefer less traffic and more focused discussion you should keep visiting them.

This initiative of marking threads to be looked at by the SSIS team is going to be limited to this forum. We do not have access to the server where the newsgroup posts are kept.

Thanks.

|||

Sounds like a good idea, but how about getting the list more than once a day, maybe once an hour?

Gary

|||

Gary Watson wrote:

Sounds like a good idea, but how about getting the list more than once a day, maybe once an hour?

Gary

Well, likely because the Microsoft guys actually have work to do! Once a day is appropriate, I think. Microsoft employees are under no obligation to answer any question on this forum; certainly in no set time frame.|||

Phil is right; answering posts on this forum is not going to give us a good excuse for not finishing our daily tasks.

This initiative is an attempt to get the questions from here closer to potential answerers. Usually, when we (SSIS team members) read these threads we try to use our time efficiently; so if we do not know the answer or do not have time to do some follow up investigation we will just skip the question hoping somebody else would answer it. If a right person does not come across those hard yet important questions in a few days, they might be left unanswered.

Our tool will find such questions, if marked appropriately, and make them visible to the entire SSIS team. There is no time limit when we have to answer them. They are listed in our report until answered. Most of those questions get answered in 2-3 days (it depends on the time it gets into the report, time zones, weekends, etc). Some of them might even require more time for investigations.

It would not make sense to send the report every hour if we cannot commit to the turnaround time on the same scale. The most important aspect is to get them marked so they do not get lost under new piles of posts.

Thanks,

Bob

|||

Hi Bob,

I did not understand before the use of this forum, thanks for clearing that up for me.

I am grateful that you guys can help out whenever you can.

Regards

Gary

Mapping Package Variables to a SQL Query in an OLEDB Source Component

Learning how to use SSIS...

I have a data flow that uses an OLEDB Source Component to read data from a table. The data access mode is SQL Command. The SQL Command is:

select lpartid, iCallNum, sql_uid_stamp
from call where sql_uid_stamp not in (select sql_uid_stamp from import_callcompare)

I wanted to add additional clauses to the where clause.

The problem is that I want to add to this SQL Command the ability to have it use a package variable that at the time of the package execution uses the variable value.

The package variable is called [User::Date_BeginningYesterday]

select lpartid, iCallNum, sql_uid_stamp
from call where sql_uid_stamp not in (select sql_uid_stamp from import_callcompare) and record_modified < [User::Date_BeginningYesterday]

I have looked at various forum message and been through the BOL but seem to missing something to make this work properly.

http://msdn2.microsoft.com/en-us/library/ms139904.aspx

The article, is the closest I have (what I belive) come to finding a solution. I am sure the solution is so easy that it is staring me in the face and I just don't see it. Thank you for your assistance.

...cordell...

Not sure what your problem is; but the solution is to create a variable to hold your query, let's say [User::SQLStatement] and use as value your query. Then set EvaluateAsExpression property of the variable to true. In the expression property, create an expression that will be evaluate at run time where you concatenate your query with the [User::Date_BeginningYesterday] variable. Back in your OLE DB Component you need to choose 'SQL Statement from variable' and then choose [User::SQLStatement] from the list.

Notice that you need to cast the value of [User::Date_BeginningYesterday] to string in the expression builder before concatenating its value to the sql statement.

Rafael Salas

select lpartid, iCallNum, sql_uid_stamp
from call where sql_uid_stamp not in (select sql_uid_stamp from import_callcompare) and record_modified < [User::Date_BeginningYesterday]

|||

The question that I have is: Can one embed a package variable into a sql statement while selecting "SQL Statement" from the data access mode. If so how would would go about that?

...cordell...

|||

The short answer is no. you cannot reference a SSIS variable directly in your sql statement. You need to use '?' and then use the parameter mapping in your OLE DB source OR, to concatenate it within a second varibale as I described in the previous post.

Rafael Salas

sql

Mapping Output Parameter to a variable!

Hi there,

I am working on SSIS package that gets data from SQL 2005 Database and writes that to a flat file. But I need to write the count of records as part of the header.

Here is what i am trying:

The OLE DB Source is calling a stored procedure and returning two things i.e. a resultset and an output parameter. The data access mode is SQL Command.

Code Snippet

EXEC [Get_logins] ?, ?, ? OUTPUT
In the Set Query Parameters dialogbox, all the three patameters are mapped to three different user variables.

What is happening is that the user variable that is mapped to output parameter is never updated. The header property expression is written as follows

Code Snippet

RIGHT("0000000000" + (DT_STR, 10, 1252)@.LoginCount, 10)

I tried to watch the variable in watch window but to no avail. Any guidance if it is bug or I am missing some thing? Any thoughts, how can I accomplish this? I have also tried adding Row Count Transformation but its variable has the same behaviour. If I set the value of @.LoginCount variable to some value, this initially set value is successfully written to the file header.

Thanks

Paraclete

No bug, the OLE DB Source just doesn't support output parameters from stored procedures. The Execute SQL Task does, though. You could execute that one in your control flow and put your resultset in a variable. A script source component can shred the resultset into rows in your Data Flow.

Or you could issue two queries. One to count the rows and put that value in a variable in the Control Flow, and then another one in the Data Flow to produce the rows.
|||

Hi,

Thanks for your response. Yes I can calculate the number of rows in a separate query, but some of the rows may have bad data. So in this case these rows will be ignored or sent to error output i.e. will not be written to the Flat File Destination. So the count taken in a separate query will be incorrect i.e. CountATStart-Errors not the CountAtstart. This may create problem becuase the header has count of records in the file. Any guidance/thoughts are wellcomed.

Thanks,

Paraclete

|||You were probably on the right track with the Row Count transformation, but it won't write to the variable until all the rows have been recieved, at which point you've already written your header. Try putting a Sort component after the Row Count. This will queue up the rows between the Row Count and the Destination and should allow Row Count to set the variable before the header gets created. By the way, how are you writing the header?

Mapping Columns Automatically?

New to SSIS...

I created a new package with a source and destination and manually created the output column with data type, etc. Works. The issue is say the table has 200 columns to export.. I dont want to create these by hand. How can I just say export them all to csv format and not have to specify and map each and every column?

Use the Export Data Wizard in SSMS.

-Jamie

|||Thats fine and dandy when starting from scratch. But if you have spent a lot of time building scripts and other actions in an existing package... it seems that it should be simple to add all columns to an existing text export. This seems like it would be such a common issue there has to be a solution.|||

You could replace your existing source adapter with a new one. The default behaviour is to select all columns which by the sound of it is what you want.

The new columns will automatically appear in the metadata of downstream components.

-Jamie

|||Thanks.. I will try that and see how it goes.

managing ssis behind a firewall

Just a comment for people who are experiencing problems.

We are managing our ssis servers from a subnet that is blocked off from our production network via a firewall as I'm sure many people do. The problem we were facing was that when we tried to connect to an ssis instance we could get to dcom through port 135 which BOL states should be open. What BOL does not say is that dcom then arbitrarily assigns a high numbered port for the management interface to connect to. This was not very desirable since we would have had to open a gaping hole in our firewall.

The solution we have come up with is in windows 2003 you can map a static port to a com server through a registry key.

First you must find the applicationid (guid) of MsDtsServer in the HKEY_CLASSES_ROOT\AppID\ registry hive. From what I can tell this is always {F38B7F09-979B-4241-80D9-2EADED02954F}.

You then need to specify a new REG_MULTI_SZ value named Endpoints with the value of ncacn_ip_tcp,0,<port number>. You can only set one port, not a range.

Now you should be able to restart SSIS and connect through the port you specified (you still need port 135 for dcom though).

The specific process is detailed in more in http://support.microsoft.com/default.aspx?scid=kb;en-us;Q312960

Let me know what you guys think, we haven't put this through the testing gauntlet yet

This was very helpful - wish MS provided this kind of answer. Thanks!

Managing Hierarchies in Dimension Tables

I am currently looking at the capabilities in SSIS from the point of view of an ETL developer who has worked with other products eg. Informatica, Cognos DecisionStream and one of things I note is a lack of support for dimensional hierarchies.

It appears that MS have assumed that SSIS users will automatically use SSAS.

We use Hyperion Essbase. Other sites have Cognos or Business Objects for their OLAP/BI.

I would like to be able build multi-level dimension hierarchies directly from within SSIS.

Has MS considered this for future versions?

We don't have any support for building hierarchies in these products simply becuase we do not connect directly to their metadata. However, it is possible to load hierarchies in SSIS - although again without direct metadata support.

Could you outline some features that you think would be useful? Or perhaps some issues you are currently facing in loading hierarchies?

Thanks

Donald Farmer

|||

Hi Donald,

Many enterprises are moving towards the concept of Master Data Management. This is the function of maintaining the reporting dimensions externally to any DW/BI system. There are specialised tools to do this but it can be managed in spreadsheets and fed into a dimension repository. This may be as simple as a few tables which store the attributes and parent/child relationships. The data in this repository has been validated.

As an ETL developer, when I wish to build dimensional tables, I can use the Master Data to source the hierarchies etc and then build the appropriate dimensional tables for either a star or snowflake schema.

Cognos 8 Data Integration (formerly the DecisionStream ETL tool) allows a developer to define a hierarchy. The GUI allows them to select the parent, child, description, and Top Parent Node of the hierarchy. It then generates a table containing chiild, description, parent, and level. From this, I can build a star schema dimension. One of the best features is that it checks the hierarchy for errors. The Developer can also view the hierarchy tree.

With SSIS, and I am a relative newbie, the only way to do the above is to too build a dimension in SSAS and then call that component from SSIS.