Source cols disappear copying package to test server

Question

Source cols disappear copying package to test server

rschaeferhig

SSCrazy Eights

Points: 8776
More actions
June 24, 2005 at 8:20 am

#60772

Here's a wierd one:
I have a package that runs a bunch of transforms that take the output from a stored procedure and create flat files. Each step runs the same SP with a different parameter to create a different flat file. This job has been running fine in production for over a year.
I saved a copy of the job to our test SQL Server. The copy came over fine. I changed all the SQL connections to point to the test server which has a complete copy of the production DB. I changed the flat files to point to our test Windows server where the files should be built. When I go to the Data Transform task the source shows the SP (i.e. "EXEC dbo.Extract_Mainframe_LateStart 'HIG') just like in the production job. I change the destination to the UNC for the test server. When I click on the Transformations tab it tells me there are invalid transforms. If I click on Remove and Auto-Remap I end up with no columns listed in the Source for the Transform. I go back to the Source tab and click Preview and it shows me the 5 rows of data I expect with the appropriate column names.
What the heck is going on?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Richard Heim SSC Veteran Points: 250 More actions · Answer 1

I've never been able to use a proc as a datapump source without using disconnected edit. I use dummy columns that mirror what the proc returns when working on the transform.

ie...

select Cast('' as varchar(20)) as Col1, Cast(0 as int) as col2, etc...

Then, after the transform is set up, I use disconnected edit to replace the dummy query with the stored proc. If I need to edit the transform, I have to use the dummy query again. There may be a simpler way, but I've always used this method.

It sounds like you're manually changing the source and destination. You shouldn't have this issue if you use disconnected edit or an ActiveX script to change the source & destination.

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 2

The odd thing is I had no problem setting this up in production. I only had a problem when I tried to modify it for a new release.

Using an ActiveX or Dynamic Properties tasks to change all the destinations up front might be the way to go. I'll look at that.

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 3

I implemented a Dynamic Properties Task at the start of the job to change all the destinations to the test server. It worked like a champ!!!

Richard Heim SSC Veteran Points: 250 More actions · Answer 4

That works too. I forgot about that Task, as I don't use it much.