principal to proxy problem

I can successfully execute my package via Business Studio but when I schedule it in SQL Agent I get the error message, 'could not get proxy data for proxy id = 2.' I am trying to execute the scheduled job with that proxy (call it abcd_proxy) because I have a logon id (call it abcd) with access to a specific network drive folder, which holds a required source flat file. The abcd logon is defined as a sysadmin account. I am unable to see that the abcd logon id is an added principal within the abcd_proxy, which I think is because the abcd logon principal has a sysadmin role. My proxy id = 2 definition looks to be defined the same way that my first proxy id was.

Does anyone have an idea about what the message means and how I can fix it?

I had to create a proxy recently to have an SSIS package write a file to a network folder. I am by no means an expert on the subject, but here is what I had to do.

I had to first create a windows account with the right credentials. I then created a credential, a proxy for the credential, and of course set the "Run As" parameter for the step to the proxy. I made sure that the new account was added as a sysadmin in SQL Server, added the account to the "Users" group on the server, as well as to the Logon as batch security policy. When I still had problems with the package being able to read a file it was related to the credentials of the account, which I had the network admin fix. Everything now runs fine.

I apologize if I have stated the obvious to you, but just wanted to share with you all the steps I had to take to perform a task very similar to what you are trying to do. Of course, I had to also use an "absolute" network path and not a mapped drive letter.

Hope this helps.

Primary key violation

Hi,
I have got a very peculier kind of problem. My package is running on SQL 2000. There is a identity primary key in a table. Now when I submit the data from 2 different computer at the same time. Only one data is storing. The reason behind this is the primary key violation. as both the data are sending the request to the database at the same time.............n as the primary key is th identity column, it is storing one that value which is able to store the data at the forst hand.
Now plz help me out in this regard............. :confused:Help you do what?

Eliminate the dups, Or remove the constraint?|||The target table should have the original IDENTITY field and a LOCATION field as primary key. Make sure that the field does not have IDENTITY property on the target column. The process should be modified to change data retrieval from a table to a view where an artificial LOCATION column is added. That's at least how I'd do it. Give us more details maybe someone will come up with something better.|||The target table should have the original IDENTITY field and a LOCATION field as primary key. Make sure that the field does not have IDENTITY property on the target column. The process should be modified to change data retrieval from a table to a view where an artificial LOCATION column is added. That's at least how I'd do it. Give us more details maybe someone will come up with something better.

Really...man I hate surrogates....|||rdajabarov's solution is the cleanest, but tsk, tsk,... should'a used GUIDs... ;)

Gotta love those surrogate (GUID) keys!|||I'm confused, are you inserting the values into the identity column on the target table or letting target table generate the identity value?

Blindman what's the storage size for a GUID?|||binary(16)|||bm - you're right, GUID would be perfect for this implementation. 2 things that I have against it as far as the original posting goes:


1. Will have to completely redesign the db, and what's most painful, - redesign the app.
2. As I posted before, it's easier to type a number in the search by key field, than a GUID value.|||CREATE TABLE [dbo].[vfar_Bact_phylum_tb] (
[Bact_sr_phylum] [int] IDENTITY (1, 1) NOT NULL ,
[Bact_phylum] [varchar] (30) NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[vfar_Bact_phylum_tb] WITH NOCHECK ADD
CONSTRAINT [PK_vfar_Bact_phylum_tb] PRIMARY KEY CLUSTERED
(
[Bact_sr_phylum]
) ON [PRIMARY]
GO

This is my table structure. Now from two different computers i'm sending the data to be submitted to this table at the same time. But unfortunately only single data is being saved.

All i want is to save both the data, no matter.........how many simultaneous request is going to the DB.

No, not at all.........its not at all possible to change the table design at this point of time.|||Then get rid of the constraint|||...and of IDENTITY property.|||bm - you're right, GUID would be perfect for this implementation. 2 things that I have against it as far as the original posting goes:


1. Will have to completely redesign the db, and what's most painful, - redesign the app.
2. As I posted before, it's easier to type a number in the search by key field, than a GUID value.

1) Even worse, will have to redesign source apps.
2) GUIDS are a bitch to type, but users shouldn't be entering them anyway. I think surrogate keys should be absolutely invisible to the users.

But yeah, it's too late for this guy's purpose.sql

Primary Key Constraint Violation

I need to insert a text file where might contains duplicate data into
a table with primary keys using a DTS package. is there anyone out
there could help me? I have tried couple way to get around, but still
doesn't work.I would load trhe text file in to a table of the correct structure, but =with no PK constraint. Then use SELECT DISTINCT -- to retrieve the =data you need and insert into final table. (INSERT INTO -- SELECT =DISTINCT -- FROM can by helpful for that part).
Mike John
"Matt" <tkiansoon@.yahoo.com> wrote in message =news:7a4ed84d.0308270840.25fe7496@.posting.google.com...
> I need to insert a text file where might contains duplicate data into
> a table with primary keys using a DTS package. is there anyone out
> there could help me? I have tried couple way to get around, but still
> doesn't work.|||You could load it into a staging table first and then de-dupe it or have a
index with ignore duplicate key on the staging table (but this would slow
the load unless the text file is ordered the same as the index which would
need to be unique clustered) and assumes that the entire row is a duplicate
if the key is.
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Matt" <tkiansoon@.yahoo.com> wrote in message
news:7a4ed84d.0308270840.25fe7496@.posting.google.com...
I need to insert a text file where might contains duplicate data into
a table with primary keys using a DTS package. is there anyone out
there could help me? I have tried couple way to get around, but still
doesn't work.

'PRIMARY' filegroup is full (yet another)

Hello,
I am getting this error on the database msdb when trying to update a DTS
package.
When I look at msdb, it is set to automatically grow and autoshrink is off.
I changed the auto-grow on the MDF file to 10% but that didn't help.
I then tried changing the space allocated - on the Data Files tab in the
Properties menu after right-clicking the database in Enterprise Manager. As
soon as I hit "OK", the size changes back to the original (too small) value
and nothing has changed. What else could be wrong, and why can't I change
the size'
Thanks
LesTry changing the size using ALTER DATABASE and see if you get any error message. Also see if you
have autoshrink turned on for the database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Les Russell" <LesRussell@.discussions.microsoft.com> wrote in message
news:4D994768-4737-4CA9-9817-BFE42C083317@.microsoft.com...
> Hello,
> I am getting this error on the database msdb when trying to update a DTS
> package.
> When I look at msdb, it is set to automatically grow and autoshrink is off.
> I changed the auto-grow on the MDF file to 10% but that didn't help.
> I then tried changing the space allocated - on the Data Files tab in the
> Properties menu after right-clicking the database in Enterprise Manager. As
> soon as I hit "OK", the size changes back to the original (too small) value
> and nothing has changed. What else could be wrong, and why can't I change
> the size'
> Thanks
> Les|||With an 'is it plugged in question' (because all your factors were within
SS)--is there enough room on the disk?
--
Joseph R.P. Maloney, CSP,CCP,CDP
"Tibor Karaszi" wrote:
> Try changing the size using ALTER DATABASE and see if you get any error message. Also see if you
> have autoshrink turned on for the database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Les Russell" <LesRussell@.discussions.microsoft.com> wrote in message
> news:4D994768-4737-4CA9-9817-BFE42C083317@.microsoft.com...
> > Hello,
> >
> > I am getting this error on the database msdb when trying to update a DTS
> > package.
> >
> > When I look at msdb, it is set to automatically grow and autoshrink is off.
> > I changed the auto-grow on the MDF file to 10% but that didn't help.
> >
> > I then tried changing the space allocated - on the Data Files tab in the
> > Properties menu after right-clicking the database in Enterprise Manager. As
> > soon as I hit "OK", the size changes back to the original (too small) value
> > and nothing has changed. What else could be wrong, and why can't I change
> > the size'
> >
> > Thanks
> > Les
>|||Hello,
I used the ALTER DATABASE command and it increased the PHYSICAL size of the
file on disk. However it had no effect on the "Space Allocated (MS)" figure
and the database still refuses to expand. Autoshrink os OFF and always has
been.
I tried increasing the MAXSIZE - this also had no effect. This is extremely
frustrating - the server is basically ignoring my instructions. Could it be
a security issue? (although I am getting no warning or error messages). The
owner of the database is sa.
There is 50GB of space left on the disk - no problem there.
We are now stuck in a situation where we cannot modify our DTS packages.
Any help appreciated.
Les
"Tibor Karaszi" wrote:
> Try changing the size using ALTER DATABASE and see if you get any error message. Also see if you
> have autoshrink turned on for the database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Les Russell" <LesRussell@.discussions.microsoft.com> wrote in message
> news:4D994768-4737-4CA9-9817-BFE42C083317@.microsoft.com...
> > Hello,
> >
> > I am getting this error on the database msdb when trying to update a DTS
> > package.
> >
> > When I look at msdb, it is set to automatically grow and autoshrink is off.
> > I changed the auto-grow on the MDF file to 10% but that didn't help.
> >
> > I then tried changing the space allocated - on the Data Files tab in the
> > Properties menu after right-clicking the database in Enterprise Manager. As
> > soon as I hit "OK", the size changes back to the original (too small) value
> > and nothing has changed. What else could be wrong, and why can't I change
> > the size'
> >
> > Thanks
> > Les
>|||I seemed to have got this to work by creating a second file to the PRIMARY
filegroup for the msdb database. However I would still be interested in any
theories as to why I couldn't get the single file to work.
I should mention we are having software RAID problems on this server - I
don't think they are a factor because I am getting no related error messages.
Les
"Les Russell" wrote:
> Hello,
> I used the ALTER DATABASE command and it increased the PHYSICAL size of the
> file on disk. However it had no effect on the "Space Allocated (MS)" figure
> and the database still refuses to expand. Autoshrink os OFF and always has
> been.
> I tried increasing the MAXSIZE - this also had no effect. This is extremely
> frustrating - the server is basically ignoring my instructions. Could it be
> a security issue? (although I am getting no warning or error messages). The
> owner of the database is sa.
> There is 50GB of space left on the disk - no problem there.
> We are now stuck in a situation where we cannot modify our DTS packages.
> Any help appreciated.
> Les
> "Tibor Karaszi" wrote:
> > Try changing the size using ALTER DATABASE and see if you get any error message. Also see if you
> > have autoshrink turned on for the database.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> > Blog: http://solidqualitylearning.com/blogs/tibor/
> >
> >
> > "Les Russell" <LesRussell@.discussions.microsoft.com> wrote in message
> > news:4D994768-4737-4CA9-9817-BFE42C083317@.microsoft.com...
> > > Hello,
> > >
> > > I am getting this error on the database msdb when trying to update a DTS
> > > package.
> > >
> > > When I look at msdb, it is set to automatically grow and autoshrink is off.
> > > I changed the auto-grow on the MDF file to 10% but that didn't help.
> > >
> > > I then tried changing the space allocated - on the Data Files tab in the
> > > Properties menu after right-clicking the database in Enterprise Manager. As
> > > soon as I hit "OK", the size changes back to the original (too small) value
> > > and nothing has changed. What else could be wrong, and why can't I change
> > > the size'
> > >
> > > Thanks
> > > Les
> >
> >

Preview has data but no columns returned

In my SSIS package, I connect to an external SQL server database. This external database supports a stored procedure that I need to execute to "retrieve data". So in my package, I set the DataAccess Mode property of my OLEDB datasource to "SQL Command" and I provide the command EXEC <proc_name> <Param>,<output_param>. (The proc has an output parameter). The preview shows all the columns and data, but somehow no columns are returned....so when I try to link this data source to a copy column task, I get an error saying the source does not have any columns...any idea why this could be happening. Thanks - Manmeet

I just ran a test on my side and it actually worked. I create a simple sp with a query (select * from adventureworks.production.productcategory) and the use it in the OLE DB. The preview worked fine and when connecting to a copy column transform I was able to see all the columns. However there is another thread going on now about the same issue and they say there are some work arounds:

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

In case you need it this is the xml code of my package

<?xml version="1.0"?><DTS:Executable xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:ExecutableType="MSDTS.Package.1"><DTS:Property DTS:Name="PackageFormatVersion">2</DTS:Property><DTS:Property DTS:Name="VersionComments"></DTS:Property><DTS:Property DTS:Name="CreatorName">MARINER\rsalas</DTS:Property><DTS:Property DTS:Name="CreatorComputerName">MARINERLAPTOP14</DTS:Property><DTS:Property DTS:Name="CreationDate" DTS:DataType="7">10/4/2006 8:05:36 AM</DTS:Property><DTS:Property DTS:Name="PackageType">5</DTS:Property><DTS:Property DTS:Name="ProtectionLevel">1</DTS:Property><DTS:Property DTS:Name="MaxConcurrentExecutables">-1</DTS:Property><DTS:Property DTS:Name="PackagePriorityClass">0</DTS:Property><DTS:Property DTS:Name="VersionMajor">1</DTS:Property><DTS:Property DTS:Name="VersionMinor">0</DTS:Property><DTS:Property DTS:Name="VersionBuild">3</DTS:Property><DTS:Property DTS:Name="VersionGUID">{28EC0411-DE09-4FBE-AA35-FA11901A0511}</DTS:Property><DTS:Property DTS:Name="EnableConfig">0</DTS:Property><DTS:Property DTS:Name="CheckpointFileName"></DTS:Property><DTS:Property DTS:Name="SaveCheckpoints">0</DTS:Property><DTS:Property DTS:Name="CheckpointUsage">0</DTS:Property><DTS:Property DTS:Name="SuppressConfigurationWarnings">0</DTS:Property>

<DTS:ConnectionManager><DTS:Property DTS:Name="DelayValidation">0</DTS:Property><DTS:Property DTS:Name="ObjectName">ETLRafLab</DTS:Property><DTS:Property DTS:Name="DTSID">{82682565-2AB7-4AE8-AB84-A06FBB937955}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName">OLEDB</DTS:Property><DTS:ObjectData><DTS:ConnectionManager><DTS:Property DTS:Name="Retain">0</DTS:Property><DTS:Property DTS:Name="ConnectionString">Data Source=MARINERLAPTOP14;Initial Catalog=ETLRafLabDev;Provider=SQLNCLI.1;Integrated Security=SSPI;</DTS:Property></DTS:ConnectionManager></DTS:ObjectData></DTS:ConnectionManager>

<DTS:PackageVariable><DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8">&lt;Package xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"&gt;&lt;dwd:DtsControlFlowDiagram&gt;&lt;dwd:BoundingTop&gt;2593&lt;/dwd:BoundingTop&gt;&lt;dwd:Layout&gt;&lt;dds&gt;

&lt;diagram fontclsid="{0BE35203-8F91-11CE-9DE3-00AA004BB851}" mouseiconclsid="{0BE35204-8F91-11CE-9DE3-00AA004BB851}" defaultlayout="Microsoft.DataWarehouse.Layout.GraphLayout" defaultlineroute="Microsoft.DataWarehouse.Layout.GraphLayout" version="7" nextobject="4" scale="100" pagebreakanchorx="0" pagebreakanchory="0" pagebreaksizex="0" pagebreaksizey="0" scrollleft="0" scrolltop="0" gridx="150" gridy="150" marginx="1000" marginy="1000" zoom="100" x="29951" y="19156" backcolor="15334399" defaultpersistence="2" PrintPageNumbersMode="3" PrintMarginTop="0" PrintMarginBottom="635" PrintMarginLeft="0" PrintMarginRight="0" marqueeselectionmode="1" mousepointer="0" snaptogrid="0" autotypeannotation="1" showscrollbars="0" viewpagebreaks="0" donotforceconnectorsbehindshapes="1" backpictureclsid="{00000000-0000-0000-0000-000000000000}"&gt;

&lt;font&gt;

&lt;ddsxmlobjectstreamwrapper binary="01010000900180380100065461686f6d61" /&gt;

&lt;/font&gt;

&lt;mouseicon&gt;

&lt;ddsxmlobjectstreamwrapper binary="6c74000000000000" /&gt;

&lt;/mouseicon&gt;

&lt;/diagram&gt;

&lt;layoutmanager&gt;

&lt;ddsxmlobj /&gt;

&lt;/layoutmanager&gt;

&lt;ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.1" tooltip="Data Flow Task" left="7594" top="2593" logicalid="3" controlid="3" masterid="0" hint1="0" hint2="0" width="3598" height="1164" noresize="0" nomove="0" nodefaultattachpoints="0" autodrag="1" usedefaultiddshape="1" selectable="1" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"&gt;

&lt;control&gt;

&lt;ddsxmlobjectstreaminitwrapper binary="000800000e0e00008c040000" /&gt;

&lt;/control&gt;

&lt;layoutobject&gt;

&lt;ddsxmlobj&gt;

&lt;property name="LogicalObject" value="{B59730D8-9045-4EB3-A965-95240CC97D28}" vartype="8" /&gt;

&lt;property name="ShowConnectorSource" value="0" vartype="2" /&gt;

&lt;/ddsxmlobj&gt;

&lt;/layoutobject&gt;

&lt;shape groupshapeid="0" groupnode="0" /&gt;

&lt;/ddscontrol&gt;

&lt;/dds&gt;&lt;/dwd:Layout&gt;&lt;/dwd:DtsControlFlowDiagram&gt;&lt;/Package&gt;</DTS:Property><DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property><DTS:Property DTS:Name="ObjectName">{5B6C56E1-90E4-4B8E-85C0-E150AF873875}</DTS:Property><DTS:Property DTS:Name="DTSID">{D803BA1C-C91E-408F-9D6F-1226D9333CD0}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable>

<DTS:PackageVariable><DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8">&lt;ConnectionManager xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"&gt;&lt;dwd:DataSourceID&gt;ETLRafLab&lt;/dwd:DataSourceID&gt;&lt;/ConnectionManager&gt;</DTS:Property><DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property><DTS:Property DTS:Name="ObjectName">{82682565-2AB7-4AE8-AB84-A06FBB937955}</DTS:Property><DTS:Property DTS:Name="DTSID">{CDC092B4-325E-4283-8E03-88E168C82372}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable>

<DTS:PackageVariable><DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8">&lt;TaskHost xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"&gt;&lt;dwd:DtsDataFlowDiagram&gt;&lt;dwd:BoundingTop&gt;1667&lt;/dwd:BoundingTop&gt;&lt;dwd:Layout&gt;&lt;dds&gt;

&lt;diagram fontclsid="{0BE35203-8F91-11CE-9DE3-00AA004BB851}" mouseiconclsid="{0BE35204-8F91-11CE-9DE3-00AA004BB851}" defaultlayout="Microsoft.DataWarehouse.Layout.GraphLayout" defaultlineroute="Microsoft.DataWarehouse.Layout.GraphLayout" version="7" nextobject="9" scale="100" pagebreakanchorx="0" pagebreakanchory="0" pagebreaksizex="0" pagebreaksizey="0" scrollleft="0" scrolltop="0" gridx="150" gridy="150" marginx="1000" marginy="1000" zoom="100" x="29951" y="18230" backcolor="15334399" defaultpersistence="2" PrintPageNumbersMode="3" PrintMarginTop="0" PrintMarginBottom="635" PrintMarginLeft="0" PrintMarginRight="0" marqueeselectionmode="1" mousepointer="0" snaptogrid="0" autotypeannotation="1" showscrollbars="0" viewpagebreaks="0" donotforceconnectorsbehindshapes="0" backpictureclsid="{00000000-0000-0000-0000-000000000000}"&gt;

&lt;font&gt;

&lt;ddsxmlobjectstreamwrapper binary="01010000900180380100065461686f6d61" /&gt;

&lt;/font&gt;

&lt;mouseicon&gt;

&lt;ddsxmlobjectstreamwrapper binary="6c74000000000000" /&gt;

&lt;/mouseicon&gt;

&lt;/diagram&gt;

&lt;layoutmanager&gt;

&lt;ddsxmlobj /&gt;

&lt;/layoutmanager&gt;

&lt;ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.1" tooltip="OLE DB Source" left="11192" top="1667" logicalid="4" controlid="4" masterid="0" hint1="0" hint2="0" width="3598" height="1164" noresize="0" nomove="0" nodefaultattachpoints="0" autodrag="1" usedefaultiddshape="1" selectable="1" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"&gt;

&lt;control&gt;

&lt;ddsxmlobjectstreaminitwrapper binary="000800000e0e00008c040000" /&gt;

&lt;/control&gt;

&lt;layoutobject&gt;

&lt;ddsxmlobj&gt;

&lt;property name="LogicalObject" value="{B59730D8-9045-4EB3-A965-95240CC97D28}/components/1" vartype="8" /&gt;

&lt;property name="ShowConnectorSource" value="0" vartype="2" /&gt;

&lt;/ddsxmlobj&gt;

&lt;/layoutobject&gt;

&lt;shape groupshapeid="0" groupnode="0" /&gt;

&lt;/ddscontrol&gt;

&lt;ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.1" tooltip="Copies columns." left="11192" top="4233" logicalid="7" controlid="7" masterid="0" hint1="0" hint2="0" width="3598" height="1164" noresize="0" nomove="0" nodefaultattachpoints="0" autodrag="1" usedefaultiddshape="1" selectable="1" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"&gt;

&lt;control&gt;

&lt;ddsxmlobjectstreaminitwrapper binary="000800000e0e00008c040000" /&gt;

&lt;/control&gt;

&lt;layoutobject&gt;

&lt;ddsxmlobj&gt;

&lt;property name="LogicalObject" value="{B59730D8-9045-4EB3-A965-95240CC97D28}/components/83" vartype="8" /&gt;

&lt;property name="ShowConnectorSource" value="0" vartype="2" /&gt;

&lt;/ddsxmlobj&gt;

&lt;/layoutobject&gt;

&lt;shape groupshapeid="0" groupnode="0" /&gt;

&lt;/ddscontrol&gt;

&lt;ddscontrol controlprogid="MSDDS.Polyline" left="12592" top="2432" logicalid="8" controlid="8" masterid="0" hint1="0" hint2="0" width="799" height="2301" noresize="0" nomove="0" nodefaultattachpoints="1" autodrag="0" usedefaultiddshape="0" selectable="1" showselectionhandles="0" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"&gt;

&lt;control&gt;

&lt;ddsxmlobj&gt;

&lt;polyline endtypedst="3" endtypesrc="1" usercolor="32768" linestyle="0" linerender="1" customendtypedstid="0" customendtypesrcid="0" adornsvisible="1" /&gt;

&lt;/ddsxmlobj&gt;

&lt;/control&gt;

&lt;layoutobject&gt;

&lt;ddsxmlobj&gt;

&lt;property name="LogicalObject" value="{B59730D8-9045-4EB3-A965-95240CC97D28}/paths/86" vartype="8" /&gt;

&lt;property name="Virtual" value="0" vartype="11" /&gt;

&lt;property name="VisibleAP" value="0" vartype="3" /&gt;

&lt;/ddsxmlobj&gt;

&lt;/layoutobject&gt;

&lt;connector lineroutestyle="Microsoft.DataWarehouse.Layout.GraphLayout" sourceid="4" destid="7" sourceattachpoint="7" destattachpoint="6" segmenteditmode="0" bendpointeditmode="0" bendpointvisibility="2" relatedid="0" virtual="0"&gt;

&lt;point x="12991" y="2831" /&gt;

&lt;point x="12991" y="4233" /&gt;

&lt;/connector&gt;

&lt;/ddscontrol&gt;

&lt;/dds&gt;&lt;/dwd:Layout&gt;&lt;/dwd:DtsDataFlowDiagram&gt;&lt;dwd:DtsComponentDesignerPropertiesList&gt;&lt;dwd:DtsComponentDesignTimeProperty&gt;&lt;dwd:key xsi:type="xsd:string"&gt;1 DataSourceViewID&lt;/dwd:key&gt;&lt;/dwd:DtsComponentDesignTimeProperty&gt;&lt;/dwd:DtsComponentDesignerPropertiesList&gt;&lt;/TaskHost&gt;</DTS:Property><DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property><DTS:Property DTS:Name="ObjectName">{B59730D8-9045-4EB3-A965-95240CC97D28}</DTS:Property><DTS:Property DTS:Name="DTSID">{A8831B5B-654F-43D4-BAA9-605512FCCDB0}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable>

<DTS:PackageVariable><DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8">&lt;PipelinePath xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"&gt;&lt;dwd:DestinationName&gt;Copy Column Input&lt;/dwd:DestinationName&gt;&lt;dwd:SourceName&gt;OLE DB Source Output&lt;/dwd:SourceName&gt;&lt;/PipelinePath&gt;</DTS:Property><DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property><DTS:Property DTS:Name="ObjectName">{B59730D8-9045-4EB3-A965-95240CC97D28}-86</DTS:Property><DTS:Property DTS:Name="DTSID">{2C57A2EE-F8A6-4FD6-8BAF-54C95E9E30B7}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable><DTS:Property DTS:Name="ForceExecValue">0</DTS:Property><DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property><DTS:Property DTS:Name="ForceExecutionResult">-1</DTS:Property><DTS:Property DTS:Name="Disabled">0</DTS:Property><DTS:Property DTS:Name="FailPackageOnFailure">0</DTS:Property><DTS:Property DTS:Name="FailParentOnFailure">0</DTS:Property><DTS:Property DTS:Name="MaxErrorCount">1</DTS:Property><DTS:Property DTS:Name="ISOLevel">1048576</DTS:Property><DTS:Property DTS:Name="LocaleID">1033</DTS:Property><DTS:Property DTS:Name="TransactionOption">1</DTS:Property><DTS:Property DTS:Name="DelayValidation">0</DTS:Property>

<DTS:LoggingOptions><DTS:Property DTS:Name="LoggingMode">0</DTS:Property><DTS:Property DTS:Name="FilterKind">1</DTS:Property><DTS:Property DTS:Name="EventFilter" DTS:DataType="8"></DTS:Property></DTS:LoggingOptions>

<DTS:Executable DTS:ExecutableType="DTS.Pipeline.1"><DTS:Property DTS:Name="ExecutionLocation">0</DTS:Property><DTS:Property DTS:Name="ExecutionAddress"></DTS:Property><DTS:Property DTS:Name="TaskContact">Performs high-performance data extraction, transformation and loading;Microsoft Corporation; Microsoft SQL Server v9; (C) 2004 Microsoft Corporation; All Rights Reserved;http://www.microsoft.com/sql/support/default.asp;1</DTS:Property><DTS:Property DTS:Name="ForceExecValue">0</DTS:Property><DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property><DTS:Property DTS:Name="ForceExecutionResult">-1</DTS:Property><DTS:Property DTS:Name="Disabled">0</DTS:Property><DTS:Property DTS:Name="FailPackageOnFailure">0</DTS:Property><DTS:Property DTS:Name="FailParentOnFailure">0</DTS:Property><DTS:Property DTS:Name="MaxErrorCount">1</DTS:Property><DTS:Property DTS:Name="ISOLevel">1048576</DTS:Property><DTS:Property DTS:Name="LocaleID">-1</DTS:Property><DTS:Property DTS:Name="TransactionOption">1</DTS:Property><DTS:Property DTS:Name="DelayValidation">0</DTS:Property>

<DTS:LoggingOptions><DTS:Property DTS:Name="LoggingMode">0</DTS:Property><DTS:Property DTS:Name="FilterKind">1</DTS:Property><DTS:Property DTS:Name="EventFilter" DTS:DataType="8"></DTS:Property></DTS:LoggingOptions><DTS:Property DTS:Name="ObjectName">Data Flow Task</DTS:Property><DTS:Property DTS:Name="DTSID">{B59730D8-9045-4EB3-A965-95240CC97D28}</DTS:Property><DTS:Property DTS:Name="Description">Data Flow Task</DTS:Property><DTS:Property DTS:Name="CreationName">DTS.Pipeline.1</DTS:Property><DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property><DTS:ObjectData><pipeline id="0" name="pipelineXml" description="pipelineXml" defaultBufferMaxRows="10000" engineThreads="5" defaultBufferSize="10485760" BLOBTempStoragePath="" bufferTempStoragePath="" runInOptimizedMode="true">

<components>

<component id="1" name="OLE DB Source" componentClassID="{2C0A8BE5-1EDC-4353-A0EF-B778599C65A0}" description="OLE DB Source" localeId="-1" usesDispositions="true" validateExternalMetadata="True" version="7" pipelineVersion="0" contactInfo="OLE DB Source;Microsoft Corporation;Microsoft SqlServer v9; (C) 2005 Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;7">

<properties>

<property id="2" name="CommandTimeout" dataType="System.Int32" state="default" isArray="false" description="The number of seconds before a command times out. A value of 0 indicates an infinite time-out." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">0</property>

<property id="3" name="OpenRowset" dataType="System.String" state="default" isArray="false" description="Specifies the name of the database object used to open a rowset." typeConverter="" UITypeEditor="" containsID="false" expressionType="None"></property>

<property id="4" name="OpenRowsetVariable" dataType="System.String" state="default" isArray="false" description="Specifies the variable that contains the name of the database object used to open a rowset." typeConverter="" UITypeEditor="" containsID="false" expressionType="None"></property>

<property id="5" name="SqlCommand" dataType="System.String" state="default" isArray="false" description="The SQL command to be executed." typeConverter="" UITypeEditor="Microsoft.DataTransformationServices.Controls.ModalMultilineStringEditor, Microsoft.DataTransformationServices.Controls, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" containsID="false" expressionType="None">Exec sp_SelectFromProductCategory</property>

<property id="6" name="SqlCommandVariable" dataType="System.String" state="default" isArray="false" description="The variable that contains the SQL command to be executed." typeConverter="" UITypeEditor="" containsID="false" expressionType="None"></property>

<property id="7" name="DefaultCodePage" dataType="System.Int32" state="default" isArray="false" description="Specifies the column code page to use when code page information is unavailable from the data source." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">1252</property>

<property id="8" name="AlwaysUseDefaultCodePage" dataType="System.Boolean" state="default" isArray="false" description="Forces the use of the DefaultCodePage property value when describing character data." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">false</property>

<property id="9" name="AccessMode" dataType="System.Int32" state="default" isArray="false" description="Specifies the mode used to access the database." typeConverter="AccessMode" UITypeEditor="" containsID="false" expressionType="None">2</property>

<property id="15" name="ParameterMapping" dataType="System.String" state="default" isArray="false" description="The mappings between the parameters in the SQL command and variables." typeConverter="" UITypeEditor="" containsID="false" expressionType="None"></property></properties>

<connections>

<connection id="10" name="OleDbConnection" description="The OLE DB runtime connection used to access the database." connectionManagerID="{82682565-2AB7-4AE8-AB84-A06FBB937955}"/></connections>

<outputs>

<output id="11" name="OLE DB Source Output" description="" exclusionGroup="0" synchronousInputId="0" deleteOutputOnPathDetached="false" hasSideEffects="false" dangling="false" isErrorOut="false" isSorted="false" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed"><outputColumns>

<outputColumn id="32" name="ProductCategoryID" description="" lineageId="32" precision="0" scale="0" length="0" dataType="i4" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="Conversion" errorRowDisposition="FailComponent" truncationRowDisposition="FailComponent" externalMetadataColumnId="31"/>

<outputColumn id="35" name="Name" description="" lineageId="35" precision="0" scale="0" length="50" dataType="wstr" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="Conversion" errorRowDisposition="FailComponent" truncationRowDisposition="FailComponent" externalMetadataColumnId="34"/>

<outputColumn id="38" name="rowguid" description="" lineageId="38" precision="0" scale="0" length="0" dataType="guid" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="Conversion" errorRowDisposition="FailComponent" truncationRowDisposition="FailComponent" externalMetadataColumnId="37"/>

<outputColumn id="41" name="ModifiedDate" description="" lineageId="41" precision="0" scale="0" length="0" dataType="dbTimeStamp" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="Conversion" errorRowDisposition="FailComponent" truncationRowDisposition="FailComponent" externalMetadataColumnId="40"/></outputColumns><externalMetadataColumns isUsed="True">

<externalMetadataColumn id="31" name="ProductCategoryID" description="" precision="0" scale="0" length="0" dataType="i4" codePage="0"/>

<externalMetadataColumn id="34" name="Name" description="" precision="0" scale="0" length="50" dataType="wstr" codePage="0"/>

<externalMetadataColumn id="37" name="rowguid" description="" precision="0" scale="0" length="0" dataType="guid" codePage="0"/>

<externalMetadataColumn id="40" name="ModifiedDate" description="" precision="0" scale="0" length="0" dataType="dbTimeStamp" codePage="0"/></externalMetadataColumns></output>

<output id="12" name="OLE DB Source Error Output" description="" exclusionGroup="0" synchronousInputId="0" deleteOutputOnPathDetached="false" hasSideEffects="false" dangling="false" isErrorOut="true" isSorted="false" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed"><outputColumns>

<outputColumn id="33" name="ProductCategoryID" description="" lineageId="33" precision="0" scale="0" length="0" dataType="i4" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/>

<outputColumn id="36" name="Name" description="" lineageId="36" precision="0" scale="0" length="50" dataType="wstr" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/>

<outputColumn id="39" name="rowguid" description="" lineageId="39" precision="0" scale="0" length="0" dataType="guid" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/>

<outputColumn id="42" name="ModifiedDate" description="" lineageId="42" precision="0" scale="0" length="0" dataType="dbTimeStamp" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/>

<outputColumn id="13" name="ErrorCode" description="" lineageId="13" precision="0" scale="0" length="0" dataType="i4" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="1" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/>

<outputColumn id="14" name="ErrorColumn" description="" lineageId="14" precision="0" scale="0" length="0" dataType="i4" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="2" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/></outputColumns><externalMetadataColumns isUsed="False"/></output>

</outputs>

</component>

<component id="83" name="Copy Column" componentClassID="{9A9C066E-59CB-4332-B899-8783F6049B08}" description="Copies columns." localeId="-1" usesDispositions="false" validateExternalMetadata="True" version="0" pipelineVersion="0" contactInfo="Copy Column;Microsoft Corporation;Microsoft SqlServer v9; (C) 2005 Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;0">

<inputs>

<input id="84" name="Copy Column Input" description="" hasSideEffects="false" dangling="false" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed"><externalMetadataColumns isUsed="False"/></input>

</inputs>

<outputs>

<output id="85" name="Copy Column Output" description="" exclusionGroup="0" synchronousInputId="84" deleteOutputOnPathDetached="false" hasSideEffects="false" dangling="false" isErrorOut="false" isSorted="false" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed"><externalMetadataColumns isUsed="False"/></output>

</outputs>

</component>

</components>

<paths>

<path id="86" name="OLE DB Source Output" description="" startId="11" endId="84"/>

</paths></pipeline></DTS:ObjectData></DTS:Executable><DTS:Property DTS:Name="ObjectName">OLEDB source with SP</DTS:Property><DTS:Property DTS:Name="DTSID">{5B6C56E1-90E4-4B8E-85C0-E150AF873875}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName">MSDTS.Package.1</DTS:Property><DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property></DTS:Executable>

|||yeah I have a similar package connecting to another database (and executing a stored procedure) and that works just fine. But the package that I described in my last email just refuses to get going...btw..thanks for the other link - let me check if tweaking the procedure helps - appreciate your help!!|||

Manmeet Panigrahi wrote:

In my SSIS package, I connect to an external SQL server database. This external database supports a stored procedure that I need to execute to "retrieve data". So in my package, I set the DataAccess Mode property of my OLEDB datasource to "SQL Command" and I provide the command EXEC <proc_name> <Param>,<output_param>. (The proc has an output parameter). The preview shows all the columns and data, but somehow no columns are returned....so when I try to link this data source to a copy column task, I get an error saying the source does not have any columns...any idea why this could be happening. Thanks - Manmeet

I think the root cause of the problem here is that sprocs don't own their own metadata like tables and views do. It is theoretically possible to return completely different result sets from the same sproc depending on what parameters you pass it so there is no real metadata to bind to. Adam Machanic has a great discussion about this here:

Stored procedures are not parameterized views
(http://www.sqljunkies.com/WebLog/amachanic/archive/2006/05/29/21482.aspx)

Try the workaround that I suggested on the other thread.

-Jamie

|||

ok...now there is a new twist to the tale. I just found out that the select statement in the stored procedure that returns the data does a select on a temp table. And that is the reason why the columns are not being detected correctly. I also found out from another developer that they had the same issue in DTS (2000) and hence they built the DTS package using disconnected edit and it works fine!

So assuming that we are not able to change the proc, is there a way we can do the disconnected edit kind of workaround in SSIS? -Manmeet

|||

Manmeet Panigrahi wrote:

ok...now there is a new twist to the tale. I just found out that the select statement in the stored procedure that returns the data does a select on a temp table. And that is the reason why the columns are not being detected correctly. I also found out from another developer that they had the same issue in DTS (2000) and hence they built the DTS package using disconnected edit and it works fine!

So assuming that we are not able to change the proc, is there a way we can do the disconnected edit kind of workaround in SSIS? -Manmeet

There's similar. You could set the sql dynamically at runtime using an expression. That might be a very clever workaround actually. I'd be interested to see if it works.

-Jamie

|||Unfortunately that didnt work too...this is what I did...I now have this variable that stores the command to execute the proc that returns the data (EXEC pr_....). (This variable is populated at run time). Also this variable becomes the Data Access Mode of my OLEDB data source. Now at design time, I have this variable populated with a select statement that returns data in the correct schema format..so that I am able to setup my source destination mappings. So when the package runs, the variable gets populated (at runtime) with the EXEC statement...but unfortunately SSIS revalidates the schema (and detects that the EXEC does not return any columns) and gives me the "VS_NEEDSNEWMETADATA" errror....sigh...Unfortunately the proc is owned by a different set of ppl and it will take a lot of time for me to convince them to change it..

Preview has data but no columns returned

In my SSIS package, I connect to an external SQL server database. This external database supports a stored procedure that I need to execute to "retrieve data". So in my package, I set the DataAccess Mode property of my OLEDB datasource to "SQL Command" and I provide the command EXEC <proc_name> <Param>,<output_param>. (The proc has an output parameter). The preview shows all the columns and data, but somehow no columns are returned....so when I try to link this data source to a copy column task, I get an error saying the source does not have any columns...any idea why this could be happening. Thanks - Manmeet

I just ran a test on my side and it actually worked. I create a simple sp with a query (select * from adventureworks.production.productcategory) and the use it in the OLE DB. The preview worked fine and when connecting to a copy column transform I was able to see all the columns. However there is another thread going on now about the same issue and they say there are some work arounds:

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

In case you need it this is the xml code of my package

<?xml version="1.0"?><DTS:Executable xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:ExecutableType="MSDTS.Package.1"><DTS:Property DTS:Name="PackageFormatVersion">2</DTS:Property><DTS:Property DTS:Name="VersionComments"></DTS:Property><DTS:Property DTS:Name="CreatorName">MARINER\rsalas</DTS:Property><DTS:Property DTS:Name="CreatorComputerName">MARINERLAPTOP14</DTS:Property><DTS:Property DTS:Name="CreationDate" DTS:DataType="7">10/4/2006 8:05:36 AM</DTS:Property><DTS:Property DTS:Name="PackageType">5</DTS:Property><DTS:Property DTS:Name="ProtectionLevel">1</DTS:Property><DTS:Property DTS:Name="MaxConcurrentExecutables">-1</DTS:Property><DTS:Property DTS:Name="PackagePriorityClass">0</DTS:Property><DTS:Property DTS:Name="VersionMajor">1</DTS:Property><DTS:Property DTS:Name="VersionMinor">0</DTS:Property><DTS:Property DTS:Name="VersionBuild">3</DTS:Property><DTS:Property DTS:Name="VersionGUID">{28EC0411-DE09-4FBE-AA35-FA11901A0511}</DTS:Property><DTS:Property DTS:Name="EnableConfig">0</DTS:Property><DTS:Property DTS:Name="CheckpointFileName"></DTS:Property><DTS:Property DTS:Name="SaveCheckpoints">0</DTS:Property><DTS:Property DTS:Name="CheckpointUsage">0</DTS:Property><DTS:Property DTS:Name="SuppressConfigurationWarnings">0</DTS:Property>

<DTS:ConnectionManager><DTS:Property DTS:Name="DelayValidation">0</DTS:Property><DTS:Property DTS:Name="ObjectName">ETLRafLab</DTS:Property><DTS:Property DTS:Name="DTSID">{82682565-2AB7-4AE8-AB84-A06FBB937955}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName">OLEDB</DTS:Property><DTS:ObjectData><DTS:ConnectionManager><DTS:Property DTS:Name="Retain">0</DTS:Property><DTS:Property DTS:Name="ConnectionString">Data Source=MARINERLAPTOP14;Initial Catalog=ETLRafLabDev;Provider=SQLNCLI.1;Integrated Security=SSPI;</DTS:Property></DTS:ConnectionManager></DTS:ObjectData></DTS:ConnectionManager>

<DTS:PackageVariable><DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8">&lt;Package xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"&gt;&lt;dwd:DtsControlFlowDiagram&gt;&lt;dwd:BoundingTop&gt;2593&lt;/dwd:BoundingTop&gt;&lt;dwd:Layout&gt;&lt;dds&gt;

&lt;diagram fontclsid="{0BE35203-8F91-11CE-9DE3-00AA004BB851}" mouseiconclsid="{0BE35204-8F91-11CE-9DE3-00AA004BB851}" defaultlayout="Microsoft.DataWarehouse.Layout.GraphLayout" defaultlineroute="Microsoft.DataWarehouse.Layout.GraphLayout" version="7" nextobject="4" scale="100" pagebreakanchorx="0" pagebreakanchory="0" pagebreaksizex="0" pagebreaksizey="0" scrollleft="0" scrolltop="0" gridx="150" gridy="150" marginx="1000" marginy="1000" zoom="100" x="29951" y="19156" backcolor="15334399" defaultpersistence="2" PrintPageNumbersMode="3" PrintMarginTop="0" PrintMarginBottom="635" PrintMarginLeft="0" PrintMarginRight="0" marqueeselectionmode="1" mousepointer="0" snaptogrid="0" autotypeannotation="1" showscrollbars="0" viewpagebreaks="0" donotforceconnectorsbehindshapes="1" backpictureclsid="{00000000-0000-0000-0000-000000000000}"&gt;

&lt;font&gt;

&lt;ddsxmlobjectstreamwrapper binary="01010000900180380100065461686f6d61" /&gt;

&lt;/font&gt;

&lt;mouseicon&gt;

&lt;ddsxmlobjectstreamwrapper binary="6c74000000000000" /&gt;

&lt;/mouseicon&gt;

&lt;/diagram&gt;

&lt;layoutmanager&gt;

&lt;ddsxmlobj /&gt;

&lt;/layoutmanager&gt;

&lt;ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.1" tooltip="Data Flow Task" left="7594" top="2593" logicalid="3" controlid="3" masterid="0" hint1="0" hint2="0" width="3598" height="1164" noresize="0" nomove="0" nodefaultattachpoints="0" autodrag="1" usedefaultiddshape="1" selectable="1" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"&gt;

&lt;control&gt;

&lt;ddsxmlobjectstreaminitwrapper binary="000800000e0e00008c040000" /&gt;

&lt;/control&gt;

&lt;layoutobject&gt;

&lt;ddsxmlobj&gt;

&lt;property name="LogicalObject" value="{B59730D8-9045-4EB3-A965-95240CC97D28}" vartype="8" /&gt;

&lt;property name="ShowConnectorSource" value="0" vartype="2" /&gt;

&lt;/ddsxmlobj&gt;

&lt;/layoutobject&gt;

&lt;shape groupshapeid="0" groupnode="0" /&gt;

&lt;/ddscontrol&gt;

&lt;/dds&gt;&lt;/dwd:Layout&gt;&lt;/dwd:DtsControlFlowDiagram&gt;&lt;/Package&gt;</DTS:Property><DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property><DTS:Property DTS:Name="ObjectName">{5B6C56E1-90E4-4B8E-85C0-E150AF873875}</DTS:Property><DTS:Property DTS:Name="DTSID">{D803BA1C-C91E-408F-9D6F-1226D9333CD0}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable>

<DTS:PackageVariable><DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8">&lt;ConnectionManager xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"&gt;&lt;dwd:DataSourceID&gt;ETLRafLab&lt;/dwd:DataSourceID&gt;&lt;/ConnectionManager&gt;</DTS:Property><DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property><DTS:Property DTS:Name="ObjectName">{82682565-2AB7-4AE8-AB84-A06FBB937955}</DTS:Property><DTS:Property DTS:Name="DTSID">{CDC092B4-325E-4283-8E03-88E168C82372}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable>

<DTS:PackageVariable><DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8">&lt;TaskHost xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"&gt;&lt;dwd:DtsDataFlowDiagram&gt;&lt;dwd:BoundingTop&gt;1667&lt;/dwd:BoundingTop&gt;&lt;dwd:Layout&gt;&lt;dds&gt;

&lt;diagram fontclsid="{0BE35203-8F91-11CE-9DE3-00AA004BB851}" mouseiconclsid="{0BE35204-8F91-11CE-9DE3-00AA004BB851}" defaultlayout="Microsoft.DataWarehouse.Layout.GraphLayout" defaultlineroute="Microsoft.DataWarehouse.Layout.GraphLayout" version="7" nextobject="9" scale="100" pagebreakanchorx="0" pagebreakanchory="0" pagebreaksizex="0" pagebreaksizey="0" scrollleft="0" scrolltop="0" gridx="150" gridy="150" marginx="1000" marginy="1000" zoom="100" x="29951" y="18230" backcolor="15334399" defaultpersistence="2" PrintPageNumbersMode="3" PrintMarginTop="0" PrintMarginBottom="635" PrintMarginLeft="0" PrintMarginRight="0" marqueeselectionmode="1" mousepointer="0" snaptogrid="0" autotypeannotation="1" showscrollbars="0" viewpagebreaks="0" donotforceconnectorsbehindshapes="0" backpictureclsid="{00000000-0000-0000-0000-000000000000}"&gt;

&lt;font&gt;

&lt;ddsxmlobjectstreamwrapper binary="01010000900180380100065461686f6d61" /&gt;

&lt;/font&gt;

&lt;mouseicon&gt;

&lt;ddsxmlobjectstreamwrapper binary="6c74000000000000" /&gt;

&lt;/mouseicon&gt;

&lt;/diagram&gt;

&lt;layoutmanager&gt;

&lt;ddsxmlobj /&gt;

&lt;/layoutmanager&gt;

&lt;ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.1" tooltip="OLE DB Source" left="11192" top="1667" logicalid="4" controlid="4" masterid="0" hint1="0" hint2="0" width="3598" height="1164" noresize="0" nomove="0" nodefaultattachpoints="0" autodrag="1" usedefaultiddshape="1" selectable="1" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"&gt;

&lt;control&gt;

&lt;ddsxmlobjectstreaminitwrapper binary="000800000e0e00008c040000" /&gt;

&lt;/control&gt;

&lt;layoutobject&gt;

&lt;ddsxmlobj&gt;

&lt;property name="LogicalObject" value="{B59730D8-9045-4EB3-A965-95240CC97D28}/components/1" vartype="8" /&gt;

&lt;property name="ShowConnectorSource" value="0" vartype="2" /&gt;

&lt;/ddsxmlobj&gt;

&lt;/layoutobject&gt;

&lt;shape groupshapeid="0" groupnode="0" /&gt;

&lt;/ddscontrol&gt;

&lt;ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.1" tooltip="Copies columns." left="11192" top="4233" logicalid="7" controlid="7" masterid="0" hint1="0" hint2="0" width="3598" height="1164" noresize="0" nomove="0" nodefaultattachpoints="0" autodrag="1" usedefaultiddshape="1" selectable="1" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"&gt;

&lt;control&gt;

&lt;ddsxmlobjectstreaminitwrapper binary="000800000e0e00008c040000" /&gt;

&lt;/control&gt;

&lt;layoutobject&gt;

&lt;ddsxmlobj&gt;

&lt;property name="LogicalObject" value="{B59730D8-9045-4EB3-A965-95240CC97D28}/components/83" vartype="8" /&gt;

&lt;property name="ShowConnectorSource" value="0" vartype="2" /&gt;

&lt;/ddsxmlobj&gt;

&lt;/layoutobject&gt;

&lt;shape groupshapeid="0" groupnode="0" /&gt;

&lt;/ddscontrol&gt;

&lt;ddscontrol controlprogid="MSDDS.Polyline" left="12592" top="2432" logicalid="8" controlid="8" masterid="0" hint1="0" hint2="0" width="799" height="2301" noresize="0" nomove="0" nodefaultattachpoints="1" autodrag="0" usedefaultiddshape="0" selectable="1" showselectionhandles="0" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"&gt;

&lt;control&gt;

&lt;ddsxmlobj&gt;

&lt;polyline endtypedst="3" endtypesrc="1" usercolor="32768" linestyle="0" linerender="1" customendtypedstid="0" customendtypesrcid="0" adornsvisible="1" /&gt;

&lt;/ddsxmlobj&gt;

&lt;/control&gt;

&lt;layoutobject&gt;

&lt;ddsxmlobj&gt;

&lt;property name="LogicalObject" value="{B59730D8-9045-4EB3-A965-95240CC97D28}/paths/86" vartype="8" /&gt;

&lt;property name="Virtual" value="0" vartype="11" /&gt;

&lt;property name="VisibleAP" value="0" vartype="3" /&gt;

&lt;/ddsxmlobj&gt;

&lt;/layoutobject&gt;

&lt;connector lineroutestyle="Microsoft.DataWarehouse.Layout.GraphLayout" sourceid="4" destid="7" sourceattachpoint="7" destattachpoint="6" segmenteditmode="0" bendpointeditmode="0" bendpointvisibility="2" relatedid="0" virtual="0"&gt;

&lt;point x="12991" y="2831" /&gt;

&lt;point x="12991" y="4233" /&gt;

&lt;/connector&gt;

&lt;/ddscontrol&gt;

&lt;/dds&gt;&lt;/dwd:Layout&gt;&lt;/dwd:DtsDataFlowDiagram&gt;&lt;dwd:DtsComponentDesignerPropertiesList&gt;&lt;dwd:DtsComponentDesignTimeProperty&gt;&lt;dwd:key xsi:type="xsd:string"&gt;1 DataSourceViewID&lt;/dwd:key&gt;&lt;/dwd:DtsComponentDesignTimeProperty&gt;&lt;/dwd:DtsComponentDesignerPropertiesList&gt;&lt;/TaskHost&gt;</DTS:Property><DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property><DTS:Property DTS:Name="ObjectName">{B59730D8-9045-4EB3-A965-95240CC97D28}</DTS:Property><DTS:Property DTS:Name="DTSID">{A8831B5B-654F-43D4-BAA9-605512FCCDB0}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable>

<DTS:PackageVariable><DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8">&lt;PipelinePath xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"&gt;&lt;dwd:DestinationName&gt;Copy Column Input&lt;/dwd:DestinationName&gt;&lt;dwd:SourceName&gt;OLE DB Source Output&lt;/dwd:SourceName&gt;&lt;/PipelinePath&gt;</DTS:Property><DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property><DTS:Property DTS:Name="ObjectName">{B59730D8-9045-4EB3-A965-95240CC97D28}-86</DTS:Property><DTS:Property DTS:Name="DTSID">{2C57A2EE-F8A6-4FD6-8BAF-54C95E9E30B7}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable><DTS:Property DTS:Name="ForceExecValue">0</DTS:Property><DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property><DTS:Property DTS:Name="ForceExecutionResult">-1</DTS:Property><DTS:Property DTS:Name="Disabled">0</DTS:Property><DTS:Property DTS:Name="FailPackageOnFailure">0</DTS:Property><DTS:Property DTS:Name="FailParentOnFailure">0</DTS:Property><DTS:Property DTS:Name="MaxErrorCount">1</DTS:Property><DTS:Property DTS:Name="ISOLevel">1048576</DTS:Property><DTS:Property DTS:Name="LocaleID">1033</DTS:Property><DTS:Property DTS:Name="TransactionOption">1</DTS:Property><DTS:Property DTS:Name="DelayValidation">0</DTS:Property>

<DTS:LoggingOptions><DTS:Property DTS:Name="LoggingMode">0</DTS:Property><DTS:Property DTS:Name="FilterKind">1</DTS:Property><DTS:Property DTS:Name="EventFilter" DTS:DataType="8"></DTS:Property></DTS:LoggingOptions>

<DTS:Executable DTS:ExecutableType="DTS.Pipeline.1"><DTS:Property DTS:Name="ExecutionLocation">0</DTS:Property><DTS:Property DTS:Name="ExecutionAddress"></DTS:Property><DTS:Property DTS:Name="TaskContact">Performs high-performance data extraction, transformation and loading;Microsoft Corporation; Microsoft SQL Server v9; (C) 2004 Microsoft Corporation; All Rights Reserved;http://www.microsoft.com/sql/support/default.asp;1</DTS:Property><DTS:Property DTS:Name="ForceExecValue">0</DTS:Property><DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property><DTS:Property DTS:Name="ForceExecutionResult">-1</DTS:Property><DTS:Property DTS:Name="Disabled">0</DTS:Property><DTS:Property DTS:Name="FailPackageOnFailure">0</DTS:Property><DTS:Property DTS:Name="FailParentOnFailure">0</DTS:Property><DTS:Property DTS:Name="MaxErrorCount">1</DTS:Property><DTS:Property DTS:Name="ISOLevel">1048576</DTS:Property><DTS:Property DTS:Name="LocaleID">-1</DTS:Property><DTS:Property DTS:Name="TransactionOption">1</DTS:Property><DTS:Property DTS:Name="DelayValidation">0</DTS:Property>

<DTS:LoggingOptions><DTS:Property DTS:Name="LoggingMode">0</DTS:Property><DTS:Property DTS:Name="FilterKind">1</DTS:Property><DTS:Property DTS:Name="EventFilter" DTS:DataType="8"></DTS:Property></DTS:LoggingOptions><DTS:Property DTS:Name="ObjectName">Data Flow Task</DTS:Property><DTS:Property DTS:Name="DTSID">{B59730D8-9045-4EB3-A965-95240CC97D28}</DTS:Property><DTS:Property DTS:Name="Description">Data Flow Task</DTS:Property><DTS:Property DTS:Name="CreationName">DTS.Pipeline.1</DTS:Property><DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property><DTS:ObjectData><pipeline id="0" name="pipelineXml" description="pipelineXml" defaultBufferMaxRows="10000" engineThreads="5" defaultBufferSize="10485760" BLOBTempStoragePath="" bufferTempStoragePath="" runInOptimizedMode="true">

<components>

<component id="1" name="OLE DB Source" componentClassID="{2C0A8BE5-1EDC-4353-A0EF-B778599C65A0}" description="OLE DB Source" localeId="-1" usesDispositions="true" validateExternalMetadata="True" version="7" pipelineVersion="0" contactInfo="OLE DB Source;Microsoft Corporation;Microsoft SqlServer v9; (C) 2005 Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;7">

<properties>

<property id="2" name="CommandTimeout" dataType="System.Int32" state="default" isArray="false" description="The number of seconds before a command times out. A value of 0 indicates an infinite time-out." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">0</property>

<property id="3" name="OpenRowset" dataType="System.String" state="default" isArray="false" description="Specifies the name of the database object used to open a rowset." typeConverter="" UITypeEditor="" containsID="false" expressionType="None"></property>

<property id="4" name="OpenRowsetVariable" dataType="System.String" state="default" isArray="false" description="Specifies the variable that contains the name of the database object used to open a rowset." typeConverter="" UITypeEditor="" containsID="false" expressionType="None"></property>

<property id="5" name="SqlCommand" dataType="System.String" state="default" isArray="false" description="The SQL command to be executed." typeConverter="" UITypeEditor="Microsoft.DataTransformationServices.Controls.ModalMultilineStringEditor, Microsoft.DataTransformationServices.Controls, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" containsID="false" expressionType="None">Exec sp_SelectFromProductCategory</property>

<property id="6" name="SqlCommandVariable" dataType="System.String" state="default" isArray="false" description="The variable that contains the SQL command to be executed." typeConverter="" UITypeEditor="" containsID="false" expressionType="None"></property>

<property id="7" name="DefaultCodePage" dataType="System.Int32" state="default" isArray="false" description="Specifies the column code page to use when code page information is unavailable from the data source." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">1252</property>

<property id="8" name="AlwaysUseDefaultCodePage" dataType="System.Boolean" state="default" isArray="false" description="Forces the use of the DefaultCodePage property value when describing character data." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">false</property>

<property id="9" name="AccessMode" dataType="System.Int32" state="default" isArray="false" description="Specifies the mode used to access the database." typeConverter="AccessMode" UITypeEditor="" containsID="false" expressionType="None">2</property>

<property id="15" name="ParameterMapping" dataType="System.String" state="default" isArray="false" description="The mappings between the parameters in the SQL command and variables." typeConverter="" UITypeEditor="" containsID="false" expressionType="None"></property></properties>

<connections>

<connection id="10" name="OleDbConnection" description="The OLE DB runtime connection used to access the database." connectionManagerID="{82682565-2AB7-4AE8-AB84-A06FBB937955}"/></connections>

<outputs>

<output id="11" name="OLE DB Source Output" description="" exclusionGroup="0" synchronousInputId="0" deleteOutputOnPathDetached="false" hasSideEffects="false" dangling="false" isErrorOut="false" isSorted="false" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed"><outputColumns>

<outputColumn id="32" name="ProductCategoryID" description="" lineageId="32" precision="0" scale="0" length="0" dataType="i4" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="Conversion" errorRowDisposition="FailComponent" truncationRowDisposition="FailComponent" externalMetadataColumnId="31"/>

<outputColumn id="35" name="Name" description="" lineageId="35" precision="0" scale="0" length="50" dataType="wstr" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="Conversion" errorRowDisposition="FailComponent" truncationRowDisposition="FailComponent" externalMetadataColumnId="34"/>

<outputColumn id="38" name="rowguid" description="" lineageId="38" precision="0" scale="0" length="0" dataType="guid" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="Conversion" errorRowDisposition="FailComponent" truncationRowDisposition="FailComponent" externalMetadataColumnId="37"/>

<outputColumn id="41" name="ModifiedDate" description="" lineageId="41" precision="0" scale="0" length="0" dataType="dbTimeStamp" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="Conversion" errorRowDisposition="FailComponent" truncationRowDisposition="FailComponent" externalMetadataColumnId="40"/></outputColumns><externalMetadataColumns isUsed="True">

<externalMetadataColumn id="31" name="ProductCategoryID" description="" precision="0" scale="0" length="0" dataType="i4" codePage="0"/>

<externalMetadataColumn id="34" name="Name" description="" precision="0" scale="0" length="50" dataType="wstr" codePage="0"/>

<externalMetadataColumn id="37" name="rowguid" description="" precision="0" scale="0" length="0" dataType="guid" codePage="0"/>

<externalMetadataColumn id="40" name="ModifiedDate" description="" precision="0" scale="0" length="0" dataType="dbTimeStamp" codePage="0"/></externalMetadataColumns></output>

<output id="12" name="OLE DB Source Error Output" description="" exclusionGroup="0" synchronousInputId="0" deleteOutputOnPathDetached="false" hasSideEffects="false" dangling="false" isErrorOut="true" isSorted="false" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed"><outputColumns>

<outputColumn id="33" name="ProductCategoryID" description="" lineageId="33" precision="0" scale="0" length="0" dataType="i4" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/>

<outputColumn id="36" name="Name" description="" lineageId="36" precision="0" scale="0" length="50" dataType="wstr" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/>

<outputColumn id="39" name="rowguid" description="" lineageId="39" precision="0" scale="0" length="0" dataType="guid" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/>

<outputColumn id="42" name="ModifiedDate" description="" lineageId="42" precision="0" scale="0" length="0" dataType="dbTimeStamp" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/>

<outputColumn id="13" name="ErrorCode" description="" lineageId="13" precision="0" scale="0" length="0" dataType="i4" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="1" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/>

<outputColumn id="14" name="ErrorColumn" description="" lineageId="14" precision="0" scale="0" length="0" dataType="i4" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="2" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/></outputColumns><externalMetadataColumns isUsed="False"/></output>

</outputs>

</component>

<component id="83" name="Copy Column" componentClassID="{9A9C066E-59CB-4332-B899-8783F6049B08}" description="Copies columns." localeId="-1" usesDispositions="false" validateExternalMetadata="True" version="0" pipelineVersion="0" contactInfo="Copy Column;Microsoft Corporation;Microsoft SqlServer v9; (C) 2005 Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;0">

<inputs>

<input id="84" name="Copy Column Input" description="" hasSideEffects="false" dangling="false" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed"><externalMetadataColumns isUsed="False"/></input>

</inputs>

<outputs>

<output id="85" name="Copy Column Output" description="" exclusionGroup="0" synchronousInputId="84" deleteOutputOnPathDetached="false" hasSideEffects="false" dangling="false" isErrorOut="false" isSorted="false" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed"><externalMetadataColumns isUsed="False"/></output>

</outputs>

</component>

</components>

<paths>

<path id="86" name="OLE DB Source Output" description="" startId="11" endId="84"/>

</paths></pipeline></DTS:ObjectData></DTS:Executable><DTS:Property DTS:Name="ObjectName">OLEDB source with SP</DTS:Property><DTS:Property DTS:Name="DTSID">{5B6C56E1-90E4-4B8E-85C0-E150AF873875}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName">MSDTS.Package.1</DTS:Property><DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property></DTS:Executable>

|||yeah I have a similar package connecting to another database (and executing a stored procedure) and that works just fine. But the package that I described in my last email just refuses to get going...btw..thanks for the other link - let me check if tweaking the procedure helps - appreciate your help!!|||

Manmeet Panigrahi wrote:

In my SSIS package, I connect to an external SQL server database. This external database supports a stored procedure that I need to execute to "retrieve data". So in my package, I set the DataAccess Mode property of my OLEDB datasource to "SQL Command" and I provide the command EXEC <proc_name> <Param>,<output_param>. (The proc has an output parameter). The preview shows all the columns and data, but somehow no columns are returned....so when I try to link this data source to a copy column task, I get an error saying the source does not have any columns...any idea why this could be happening. Thanks - Manmeet

I think the root cause of the problem here is that sprocs don't own their own metadata like tables and views do. It is theoretically possible to return completely different result sets from the same sproc depending on what parameters you pass it so there is no real metadata to bind to. Adam Machanic has a great discussion about this here:

Stored procedures are not parameterized views
(http://www.sqljunkies.com/WebLog/amachanic/archive/2006/05/29/21482.aspx)

Try the workaround that I suggested on the other thread.

-Jamie

|||

ok...now there is a new twist to the tale. I just found out that the select statement in the stored procedure that returns the data does a select on a temp table. And that is the reason why the columns are not being detected correctly. I also found out from another developer that they had the same issue in DTS (2000) and hence they built the DTS package using disconnected edit and it works fine!

So assuming that we are not able to change the proc, is there a way we can do the disconnected edit kind of workaround in SSIS? -Manmeet

|||

Manmeet Panigrahi wrote:

ok...now there is a new twist to the tale. I just found out that the select statement in the stored procedure that returns the data does a select on a temp table. And that is the reason why the columns are not being detected correctly. I also found out from another developer that they had the same issue in DTS (2000) and hence they built the DTS package using disconnected edit and it works fine!

So assuming that we are not able to change the proc, is there a way we can do the disconnected edit kind of workaround in SSIS? -Manmeet

There's similar. You could set the sql dynamically at runtime using an expression. That might be a very clever workaround actually. I'd be interested to see if it works.

-Jamie

|||Unfortunately that didnt work too...this is what I did...I now have this variable that stores the command to execute the proc that returns the data (EXEC pr_....). (This variable is populated at run time). Also this variable becomes the Data Access Mode of my OLEDB data source. Now at design time, I have this variable populated with a select statement that returns data in the correct schema format..so that I am able to setup my source destination mappings. So when the package runs, the variable gets populated (at runtime) with the EXEC statement...but unfortunately SSIS revalidates the schema (and detects that the EXEC does not return any columns) and gives me the "VS_NEEDSNEWMETADATA" errror....sigh...Unfortunately the proc is owned by a different set of ppl and it will take a lot of time for me to convince them to change it..