knowing which jobs/dts/SSIS affect which tables

hi, we were doing something and began to wonder which jobs/dts were
affecting a certain table (we use SQL 2000)... is there anyway to check
this?

and have the features for such dependency analysis been added for SQL
2005?I've never tried this, but you might be able to trap it using Profiler;
set up a log and filter on object_id.

http://www.microsoft.com/technet/ar...9.mspx?mfr=true

Post back and let us know if that works for you.

Stu

metaperl wrote:
> hi, we were doing something and began to wonder which jobs/dts were
> affecting a certain table (we use SQL 2000)... is there anyway to check
> this?
> and have the features for such dependency analysis been added for SQL
> 2005?sql

Keyboard not working (backspace and arrow keys) when viewing 2000 DTS packages from 2005 (ba

I know my problem is not directly associated w/ SSIS, so please forgive the post here. I figured this would be the "most" appropriate place to post this challenge.

We're attempting to edit a 2000 DTS package in Design mode from within the 2005 SQL Management Studio. To do this we downloaded/installed the following packages from the MS download site:

Microsoft SQL Server 2000 DTS Designer Components
Microsoft SQL Server 2005 Backward Compatibility Components

We're able to open the package in design mode from the Management Studio (Server > Management > Legacy > Data Transformation Services > DTS Package Name). But, when the DTS designer is open, the backspace and arrow keys won't respond in the Management Studio. They begin responding when we close the DTS designer. There are no error messages when we close the DTS designer.

Does anyone know of a fix or a workaround? Any help would be greatly appreciated.

Thanks, Mike

Do you have the Logitech MX5000 BlueTooth keyboard/mouse? I encountered this problem just yesterday and assumed it was my bluetooth connection going in and out.

That is very strange...

|||Nope, I have a wired Compaq keyboard. My colleague has the same problem w/ a different brand of wired keyboard.|||If you do a search there are a lot of people that have had problems with this component. It hosed enterprised manager on my machine and one of my co-workers. Even when it installed correctly a bunch of us still got errors opening packages. So far the only solution I've heard is to grin and bear it.|||

Thanks for the feedback, this was identified as a bug and was fixed. The fix will be in the next release after SP1

wenyang

|||

Is this fix out yet?

|||

I saw a reference to this recently which indicated it was fixed in SP2, or the associated feature pack. That is currently released as a CTP, so you may wish to risk it. I cannot find the source niow, which is rather annoying...

Download details: Feature Pack for SQL Server 2005 SP2 CTP
(http://www.microsoft.com/downloads/details.aspx?FamilyID=7A9AD90F-7F95-4369-A206-E84053D63FD3&displaylang=en)

|||

Thanks,

but unfortunately, the same behavior is happening with the CTP version of the dts designer :(

Kerberos delegation don''t work with SSIS ?

Hello,

I have configured Kerberos delegation for several web services. One of the web service calls SSIS packages, but the packages don't run with the expected impersonate user : the package starts with the imporsonate user, but continue with ASPNET user (which is not allowed to execute SSIS and connect to DB).

If the web service is called directly (no delegation), SSIS packages run with the correct user. It looks like than there is an autenthicate issue, but kerberos is configured and web services can run from one to another with the impersonate user. The issue occured only when I call SSIS packages.

Here is a extract of the SSIS log file :

Code Snippet

<dtslog>
<record>
<event>PackageStart</event>
<message>Beginning of package execution.
</message>
<computer>WKS-GE-BRAZILIA</computer>
<operator>WKS-GE-BRAZILIA\Pascal.Brun</operator>
<source>ImportMonthlyCSV</source>
<sourceid>{D053CB99-FDE4-492D-83BC-821E1B34704B}</sourceid>
<executionid>{EA9C1929-4131-4FDD-A6FC-560E01A65536}</executionid>
<starttime>09.08.2007 17:31:02</starttime>
<endtime>09.08.2007 17:31:02</endtime>
<datacode>0</datacode>
<databytes>0x</databytes>
</record>
<record>
<event>OnError</event>
<message>SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Data Warehouse" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
</message>
<computer>WKS-GE-BRAZILIA</computer>
<operator>WKS-GE-BRAZILIA\ASPNET</operator>
<source>Import CSV</source>
<sourceid>{284D3166-F372-4B03-86C1-75A4D8DC9A5C}</sourceid>
<executionid>{EA9C1929-4131-4FDD-A6FC-560E01A65536}</executionid>
<starttime>09.08.2007 17:31:02</starttime>
<endtime>09.08.2007 17:31:02</endtime>
<datacode>-1071611876</datacode>
<databytes>0x</databytes>
</record>
...

Any help is required.

Thanks in advance.

You might take a look at this thread, which discusses the issue.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1044994&SiteID=1

|||

This post show workarround to this issue. This can help me temporary, but I configured Kerberos delegation on my application. This post explain why SSIS packages call by ASP.Net is considered as double-hop, but double-hop should not be a problem when delegation is enable.

Why Kerberos don't work with SSIS ?

|||

If you take a close look at the thread, you'll see that the problem is that the initial thread does impersonate the account, but subsequent threads that SSIS creates internally do not. That is why it doesn't work.

If you would like to suggest a change in this, you should create a defect/request on connect.microsoft.com.

Keeping track of last time of extract

I am building an SSIS package in which I extract data from a number of transaction tables. The rows in each transaction table contain a timestamp. I would like to do the following.

* Every time I extract rows from a table I want to extract only the records added since last extract (no rows are modified or deleted - only added)
* In the extract process I want to find the maximum timestamp in a given table. This timestamp should be stored and updated in a table on the SQL Server. This way, the next time the extraction process is run only "new" rows are extracted

How would you go about doing this in SSIS? I have thought of the following approach, but I am unsure whether it is too cumbersome.

* A package variable is defined for each of the transaction tables
* A series of Execute SQL Tasks populates each of these variables with the timestamps from the table in SQL Server
* A series of data flow tasks extract the data (using the variables populated above in the WHERE-condition). Contained in the data flow tasks is a script component which records the maximum timestamp extracted and places this in yet another package variable (I am unsure how to do this, by the way...)
* A series of Execute SQL Tasks updates the table in SQL Server with the new timestamps

How would you do this?

Thanx! This is how I'd do it. It isn't particularly a SSIS solution...just the way I'd do it!

Have 2 values stored in a table called (e.g.) tblConfig. They are:
LastExtractDate
ThisExtractDate

Step 1) UPDATE tblConfig SET LastExtractDate=ThisExtractDate, ThisExtractDate = GETDATE()

Step 2) SELECT * FROM <source_table> WHERE <some_tstamp_value> >= LastExtractDate AND <some_tstamp_value> < ThisExtractDate> (this would be inside a data-flow source adapter)

OK, column names may change etc...but you get the idea!!!

-Jamie|||Very nice... And simple. One question though (for doing this in SSIS): Do you enlist the two tasks in the same transaction (using a sequence container for instance) so that if the data flow tasks fails, you will not "miss" any rows on the next run?

/Michael|||

Reckless wrote:

Very nice... And simple. One question though (for doing this in SSIS): Do you enlist the two tasks in the same transaction (using a sequence container for instance) so that if the data flow tasks fails, you will not "miss" any rows on the next run?

/Michael

Yeah. Good idea!

-Jamie|||Hi, folks:
Exactly how do we do this. Do we do a OLE data source > something > OLE data destination. Thanks in advance.|||

Al_chan wrote:

Hi, folks:
Exactly how do we do this. Do we do a OLE data source > something > OLE data destination. Thanks in advance.

Hi Al,
Step 1 I would do in an Exec SQL Task.
Step 2 most likely in a data flow - yes, using an OLE DB Data Source.

-Jamie

Keeping SSAS Projects in the dark ?

The feature with SSIS packages to "keep them in the dark" with configurationfiles and the use of variables. How does or does that apply for SSAS projects ?

Since we keep dev, test and production sepperate, the cubes datasource differ according to witch enviroment they are running on. Is there a way to fix this, or what is best practice on this front ?

Whe you talk about "keep them in the dark" are you referring to the following article : http://www.sqlmag.com/Articles/ArticleID/47688/pg/2/2.html ?

I guess this is not widely accepted terminology.

For being able to change some project properties while deploying you can take a look at the Deployment Wizard utility. It gives you abilty to change connection strings during deployment.

Keep package alive forever

Hi there,

Does anyone know how i can keep an ssis package used for real time reporting alive no matter the amount of errors it gets? So for instance the server im streaming to is shutdown for maintenance, and the connection dies, its needs to just keep re-trying. In other words the maximum error count is infinite. i dont just want to set max err count high, i want it out of the picture all together.

Thanks

are you talking about a SSIS packages which do stuff inside a infinite loop?

|||yup thats the plan. i want the package to stay alive rather than calling it from a job over and over because of the validation time incurred in having the package start and end all the time|||

ssis might not be the one you need.

how about replication

|||We currently replicate. but due to performance issues starting to rear their heads, we have changed our structures on our ODS to include only data marts - therefore we need to transform all that gets replicated to our ODS|||

replication should be able to catch up with that change. anyway. just stay for more suggestions.

how about scheduling the package to run regularly using a Sql jobs or windows task scheduler

|||Thanks for the reply. Yup gonna go with scheduling it. slower than i initially wanted but will prob be the only way