Bug?

When I have two connection managers in the DFT and OLEDB source and destination component.

Associating the different conneciton managers to source and destination seem to go through fine with the code below, but the package generated does not have the connection manager associated with the destination.

I get this error on the destionation on opening up the package in the designer.

<Error> A connection Manager has not been assigned to the runtime connection (24) </Error>

SourceOrDestinationComponent.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(pkg.Connections[ConnMgrName]);

However when I assign the ID using the code below it seem to work fine. The documentation doesn't mention needing this step, so is this a bug or a documentation issue?

SourceOrDestinationComponent.RuntimeConnectionCollection[0].ConnectionManagerID = pkg.Connections[ConnMgrName].ID;

Also is the BOL documentation being updated for how to program/configure various tasks in SSIS? Without which it is extremely time consuming with a trial and error method if at all impossible to use them (programmatically)

-tim

Tim,

That step is definately needed and the documentation should say so. If it doesn't say it then use the Send feedback link to tell them. You may be surprised at how effective this is.

If you need anything extra in BOL then ask them for it. It is constantly being updated.

-Jamie

|||

Thanks Jamie, I know the folks working on BOL content appreciate the comments.

This seems like a great opportunity to point out the recent/July refresh of BOL

>>>>>>>>>>>>>>>>>>>>>>>>>>>

Download an updated version of the documentation and tutorials for Microsoft SQL Server 2005. See the "Additional Information" section for an update on SQL Server Express Books Online.

http://www.microsoft.com/downloads/details.aspx?FamilyId=BE6A2C5D-00DF-4220-B133-29C1E0B6585F&displaylang=en

There is a separate download for the sample packages, programs, and databases, at http://www.microsoft.com/downloads/details.aspx?familyid=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en.

|||

Thank you both. The new documentation still does not include the required step.

Craig, BOL seem to have provided clearified some of the existing samples , but it still does not contain samples to how to programmatically most components eg: SCD. Can we expect more samples to be updated to BOL going forward?

Till that time what is the recommended way for people to figure out how to use these apart from trial and error

|||

We are continually updating BOL and I will pass along a note on this one and as Jamie noted, using the feedback mechanism for BOL is really the best way. I can certainly suggest/ask for doc changes but requests from customers always carry more weight. One complication is that every connection mgr may behave differently. Connections and data providers are tricky as each has its own behavior.

sql

Bug Report: Expressions not deleted

I've noticed that if you create a data flow task (such as a DataReader source) and then bind an expression to it, if you rename or delete the task the expressions bound to the task will disappear but still be in the DTSX itself. So, if your expressions stop parsing (if you remove a variable, etc.) you will get errors for these expressions which you thought should have been removed. The workaround is to re-add the tasks with the old names and go delete the expressions from the expression list.
I suggest you raise a bug at http://lab.msdn.microsoft.com/ProductFeedback|||I could reproduce this problem and filed a bug.

Bug is com.microsoft.sqlserver.jdbc.SQLServerDataSource (2005 Beta

The following JUnit code fails:
// elided ...
public void testSelectMethod()
{
SQLServerDataSource source = new SQLServerDataSource();
source.setSelectMethod("cursor");
assertEquals("cursor", source.getSelectMethod());
}
// elided ...
with:
junit.framework.ComparisonFailure: Select method not set to cursor
expected:<cursor> but was:<null>
at junit.framework.Assert.assertEquals(Assert.java:81 )
at test.foo.assumptions.JTASQLTest.testSelectMethod(J TASQLTest.java:49)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(Native MethodAccessorImpl.java:39)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(De legatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at junit.framework.TestCase.runTest(TestCase.java:154 )
at junit.framework.TestCase.runBare(TestCase.java:127 )
at junit.framework.TestResult$1.protect(TestResult.ja va:106)
at junit.framework.TestResult.runProtected(TestResult .java:124)
at junit.framework.TestResult.run(TestResult.java:109 )
at junit.framework.TestCase.run(TestCase.java:118)
at
org.eclipse.jdt.internal.junit.runner.RemoteTestRu nner.runTests(RemoteTestRunner.java:478)
at
org.eclipse.jdt.internal.junit.runner.RemoteTestRu nner.run(RemoteTestRunner.java:344)
at
org.eclipse.jdt.internal.junit.runner.RemoteTestRu nner.main(RemoteTestRunner.java:196)
jef - integralpath.blogs.com
Yes we changed the name of the SelectMethod property to
forwardReadOnlyMethod.
So you would say:
forwardReadOnlyMethod=direct
or
forwardReadOnlyMethod=serverCursor
Unfortunately we have not yet updated the SQLServerDataSource class to work
with this new setting yet.
Matt Neerincx [MSFT]
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.
"Joe Weinstein" <joeNOSPAM@.bea.com> wrote in message
news:evctk3ozFHA.3312@.TK2MSFTNGP09.phx.gbl...
>
> jef wrote:
>
>
> Check the docs. I believe SelectMethod=cursor is now functionally
> meaningless with this version of the driver, and maybe they allow
> you to make the set call, just so it doesn't break existing code
> that was written for the previous driver, that shares zero code
> with this new one. They might also document that getSelectMethod()
> will do what it does...
> I think the best course is to verify my first contention, and
> then just not call either method for this one...
> Joe Weinstein at BEA
>
|||Note we are planning on changing this back to allowing selectMethod due to
huge customer demand.
Matt Neerincx [MSFT]
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.
"jef" <jef@.discussions.microsoft.com> wrote in message
news:5FB92280-7932-4722-84AC-72A3102980E5@.microsoft.com...
> The following JUnit code fails:
> // elided ...
> public void testSelectMethod()
> {
> SQLServerDataSource source = new SQLServerDataSource();
> source.setSelectMethod("cursor");
> assertEquals("cursor", source.getSelectMethod());
> }
> // elided ...
> with:
> junit.framework.ComparisonFailure: Select method not set to cursor
> expected:<cursor> but was:<null>
> at junit.framework.Assert.assertEquals(Assert.java:81 )
> at test.foo.assumptions.JTASQLTest.testSelectMethod(J TASQLTest.java:49)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(Native MethodAccessorImpl.java:39)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(De legatingMethodAccessorImpl.java:25)
> at java.lang.reflect.Method.invoke(Method.java:585)
> at junit.framework.TestCase.runTest(TestCase.java:154 )
> at junit.framework.TestCase.runBare(TestCase.java:127 )
> at junit.framework.TestResult$1.protect(TestResult.ja va:106)
> at junit.framework.TestResult.runProtected(TestResult .java:124)
> at junit.framework.TestResult.run(TestResult.java:109 )
> at junit.framework.TestCase.run(TestCase.java:118)
> at
> org.eclipse.jdt.internal.junit.runner.RemoteTestRu nner.runTests(RemoteTestRunner.java:478)
> at
> org.eclipse.jdt.internal.junit.runner.RemoteTestRu nner.run(RemoteTestRunner.java:344)
> at
> org.eclipse.jdt.internal.junit.runner.RemoteTestRu nner.main(RemoteTestRunner.java:196)
>
> --
> jef - integralpath.blogs.com

BUG in SSIS OLE DB Source Build Query

Can anyone help me with this ?

I am trying to extract data from oracle 9i server and
pushing it onto the SQL Server 2005 using Data Flow Task.
Details for OLE DB Source are :

OLE DB Source Editor Details:

OLE DB Connection Manager - Oracle Source

Data Access Mode - SQL Command

SQL Command Text -

SELECT EMPNO, EMPNAME, JOB, HIREDATE
FROM EMP_DETAILS
WHERE (HIREDATE > TRUNC(SYSDATE) - 1)

SSIS parses this query succesfully but when i build the query it shows query

SELECT EMPNO, EMPNAME, JOB, HIREDATE
FROM EMP_DETAILS
WHERE (HIREDATE > TRUNC("SYSDATE") - 1)

Please note :- SYSDATE IN " "

This query returns no Result Set.

Try using GETDATE() instead of SYSDATE, that is the Oracle function for returning the current date.|||Hi there,

i don't find any such function in oracle, getdate() is there in MS SQL Server.

Thanks.
|||

I don't think there is any. AFAIK Sysdate is the equivalent to GetDate() in Oracle. I am having the exact same problem as you using an OLE DB Source to access an Oracle DB - and it annoys me to no end that I cannot get the system date for comparison. If anyone else has a solution for this it would be much appreciated.

|||I am having the same issue in a similar situation... I change my query in the .xsd dataset to include a clause that references SYSDATE, but when VS tries to rebuild the code it ALWAYS adds the double-quotes around sysdate and it treats it as an identifier, which of course does not exist.

SELECT HZ.LOCATION_ID, HZ.DESCRIPTION, HZ.ADDRESS1, HZ.ADDRESS2
FROM AR.HZ_LOCATIONS HZ INNER JOIN
CSI.CSI_A_LOCATIONS CSI ON HZ.LOCATION_ID = CSI.LOCATION_ID
WHERE (NVL(TRUNC(CSI.ACTIVE_END_DATE), '01-JAN-4000') > TRUNC(SYSDATE))

Always becomes

SELECT HZ.LOCATION_ID, HZ.DESCRIPTION, HZ.ADDRESS1, HZ.ADDRESS2
FROM AR.HZ_LOCATIONS HZ INNER JOIN
CSI.CSI_A_LOCATIONS CSI ON HZ.LOCATION_ID = CSI.LOCATION_ID
WHERE (NVL(TRUNC(CSI.ACTIVE_END_DATE), '01-JAN-4000') > TRUNC("SYSDATE"))

Has anyone found a way to prevent VS from doing this?
|||Hi Anthony,

There is only one way around, Don't build the query !
In my application i am just parsing the query n its working fine.
you try to build the query n query builder will make sysdate famous by quoting it.

BUG in SSIS OLE DB Source Build Query

Can anyone help me with this ?

I am trying to extract data from oracle 9i server and
pushing it onto the SQL Server 2005 using Data Flow Task.
Details for OLE DB Source are :

OLE DB Source Editor Details:

OLE DB Connection Manager - Oracle Source

Data Access Mode - SQL Command

SQL Command Text -

SELECT EMPNO, EMPNAME, JOB, HIREDATE
FROM EMP_DETAILS
WHERE (HIREDATE > TRUNC(SYSDATE) - 1)

SSIS parses this query succesfully but when i build the query it shows query

SELECT EMPNO, EMPNAME, JOB, HIREDATE
FROM EMP_DETAILS
WHERE (HIREDATE > TRUNC("SYSDATE") - 1)

Please note :- SYSDATE IN " "

This query returns no Result Set.

Try using GETDATE() instead of SYSDATE, that is the Oracle function for returning the current date.|||Hi there,

i don't find any such function in oracle, getdate() is there in MS SQL Server.

Thanks.
|||

I don't think there is any. AFAIK Sysdate is the equivalent to GetDate() in Oracle. I am having the exact same problem as you using an OLE DB Source to access an Oracle DB - and it annoys me to no end that I cannot get the system date for comparison. If anyone else has a solution for this it would be much appreciated.

|||I am having the same issue in a similar situation... I change my query in the .xsd dataset to include a clause that references SYSDATE, but when VS tries to rebuild the code it ALWAYS adds the double-quotes around sysdate and it treats it as an identifier, which of course does not exist.

SELECT HZ.LOCATION_ID, HZ.DESCRIPTION, HZ.ADDRESS1, HZ.ADDRESS2
FROM AR.HZ_LOCATIONS HZ INNER JOIN
CSI.CSI_A_LOCATIONS CSI ON HZ.LOCATION_ID = CSI.LOCATION_ID
WHERE (NVL(TRUNC(CSI.ACTIVE_END_DATE), '01-JAN-4000') > TRUNC(SYSDATE))

Always becomes

SELECT HZ.LOCATION_ID, HZ.DESCRIPTION, HZ.ADDRESS1, HZ.ADDRESS2
FROM AR.HZ_LOCATIONS HZ INNER JOIN
CSI.CSI_A_LOCATIONS CSI ON HZ.LOCATION_ID = CSI.LOCATION_ID
WHERE (NVL(TRUNC(CSI.ACTIVE_END_DATE), '01-JAN-4000') > TRUNC("SYSDATE"))

Has anyone found a way to prevent VS from doing this?
|||Hi Anthony,

There is only one way around, Don't build the query !
In my application i am just parsing the query n its working fine.
you try to build the query n query builder will make sysdate famous by quoting it.

BUG in SSIS OLE DB Source Build Query

Can anyone help me with this ?

I am trying to extract data from oracle 9i server and
pushing it onto the SQL Server 2005 using Data Flow Task.
Details for OLE DB Source are :

OLE DB Source Editor Details:

OLE DB Connection Manager - Oracle Source

Data Access Mode - SQL Command

SQL Command Text -

SELECT EMPNO, EMPNAME, JOB, HIREDATE
FROM EMP_DETAILS
WHERE (HIREDATE > TRUNC(SYSDATE) - 1)

SSIS parses this query succesfully but when i build the query it shows query

SELECT EMPNO, EMPNAME, JOB, HIREDATE
FROM EMP_DETAILS
WHERE (HIREDATE > TRUNC("SYSDATE") - 1)

Please note :- SYSDATE IN " "

This query returns no Result Set.

Try using GETDATE() instead of SYSDATE, that is the Oracle function for returning the current date.|||Hi there,

i don't find any such function in oracle, getdate() is there in MS SQL Server.

Thanks.
|||

I don't think there is any. AFAIK Sysdate is the equivalent to GetDate() in Oracle. I am having the exact same problem as you using an OLE DB Source to access an Oracle DB - and it annoys me to no end that I cannot get the system date for comparison. If anyone else has a solution for this it would be much appreciated.

|||I am having the same issue in a similar situation... I change my query in the .xsd dataset to include a clause that references SYSDATE, but when VS tries to rebuild the code it ALWAYS adds the double-quotes around sysdate and it treats it as an identifier, which of course does not exist.

SELECT HZ.LOCATION_ID, HZ.DESCRIPTION, HZ.ADDRESS1, HZ.ADDRESS2
FROM AR.HZ_LOCATIONS HZ INNER JOIN
CSI.CSI_A_LOCATIONS CSI ON HZ.LOCATION_ID = CSI.LOCATION_ID
WHERE (NVL(TRUNC(CSI.ACTIVE_END_DATE), '01-JAN-4000') > TRUNC(SYSDATE))

Always becomes

SELECT HZ.LOCATION_ID, HZ.DESCRIPTION, HZ.ADDRESS1, HZ.ADDRESS2
FROM AR.HZ_LOCATIONS HZ INNER JOIN
CSI.CSI_A_LOCATIONS CSI ON HZ.LOCATION_ID = CSI.LOCATION_ID
WHERE (NVL(TRUNC(CSI.ACTIVE_END_DATE), '01-JAN-4000') > TRUNC("SYSDATE"))

Has anyone found a way to prevent VS from doing this?
|||Hi Anthony,

There is only one way around, Don't build the query !
In my application i am just parsing the query n its working fine.
you try to build the query n query builder will make sysdate famous by quoting it.

BUG in SSIS OLE DB Source Build Query

Can anyone help me with this ?

I am trying to extract data from oracle 9i server and
pushing it onto the SQL Server 2005 using Data Flow Task.
Details for OLE DB Source are :

OLE DB Source Editor Details:

OLE DB Connection Manager - Oracle Source

Data Access Mode - SQL Command

SQL Command Text -

SELECT EMPNO, EMPNAME, JOB, HIREDATE
FROM EMP_DETAILS
WHERE (HIREDATE > TRUNC(SYSDATE) - 1)

SSIS parses this query succesfully but when i build the query it shows query

SELECT EMPNO, EMPNAME, JOB, HIREDATE
FROM EMP_DETAILS
WHERE (HIREDATE > TRUNC("SYSDATE") - 1)

Please note :- SYSDATE IN " "

This query returns no Result Set.

Try using GETDATE() instead of SYSDATE, that is the Oracle function for returning the current date.|||Hi there,

i don't find any such function in oracle, getdate() is there in MS SQL Server.

Thanks.
|||

I don't think there is any. AFAIK Sysdate is the equivalent to GetDate() in Oracle. I am having the exact same problem as you using an OLE DB Source to access an Oracle DB - and it annoys me to no end that I cannot get the system date for comparison. If anyone else has a solution for this it would be much appreciated.

|||I am having the same issue in a similar situation... I change my query in the .xsd dataset to include a clause that references SYSDATE, but when VS tries to rebuild the code it ALWAYS adds the double-quotes around sysdate and it treats it as an identifier, which of course does not exist.

SELECT HZ.LOCATION_ID, HZ.DESCRIPTION, HZ.ADDRESS1, HZ.ADDRESS2
FROM AR.HZ_LOCATIONS HZ INNER JOIN
CSI.CSI_A_LOCATIONS CSI ON HZ.LOCATION_ID = CSI.LOCATION_ID
WHERE (NVL(TRUNC(CSI.ACTIVE_END_DATE), '01-JAN-4000') > TRUNC(SYSDATE))

Always becomes

SELECT HZ.LOCATION_ID, HZ.DESCRIPTION, HZ.ADDRESS1, HZ.ADDRESS2
FROM AR.HZ_LOCATIONS HZ INNER JOIN
CSI.CSI_A_LOCATIONS CSI ON HZ.LOCATION_ID = CSI.LOCATION_ID
WHERE (NVL(TRUNC(CSI.ACTIVE_END_DATE), '01-JAN-4000') > TRUNC("SYSDATE"))

Has anyone found a way to prevent VS from doing this?
|||Hi Anthony,

There is only one way around, Don't build the query !
In my application i am just parsing the query n its working fine.
you try to build the query n query builder will make sysdate famous by quoting it.

Bug in Report Server?

I am running a report using RS2005. It connects to a web service using the XML data source. It works correctly in my development environment. When I put it up on the server and run the report, I get the error message:

An error occurred during rendering of the report.

An error occurred during rendering of the report.

Item has already been added. Key in dictionary: '1' Key being added: '1'

If I run the web service from the server, it returns correct data, so I know error is not coming from my web service. The only answer I can think of is that there is a bug in the Report Server's or XML Data Source's code.

Has anyone else seen this problem?

Thank you,

Jessica

Hi Jessica, could you please repro the bug and send me most recent RS logs to almineev. At microsoft. Com.

RS logs are located at location similar to this one: C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\LogFiles

|||

Jessica, did you ever find out the solution for your problem? I am experiencing a similar issue now.

Thanks,

James

|||It hapened to me, more or less the same.

For what I know it can be a problem with the default values of the

Report Parameters... they should not be originated by a query.

I had this problem and puting this as static or calculated values solved the issue...

Not the best way as by default the user's are not getting the data shown, but that's a solution.

Hope Microsoft solves the issue.. as this is a serious bug.

Regards,

Jose|||

Our problem was two report items on top of each other. Once we moved one of them over by just a little bit, the error went away.

I'm assuming it was something in the server code that wasn't handling the scenario of the same positional coordinates being inserted into a hash table, but that's just a guess.

We solved it by recreating the report one item at a time, uploading after each item. When we layered the second item on top of the first, that's when we received that message.

Good luck with everyone else trying to get this to work!

-Jessica

Bug in Report Server?

I am running a report using RS2005. It connects to a web service using the XML data source. It works correctly in my development environment. When I put it up on the server and run the report, I get the error message:

An error occurred during rendering of the report.

An error occurred during rendering of the report.

Item has already been added. Key in dictionary: '1' Key being added: '1'

If I run the web service from the server, it returns correct data, so I know error is not coming from my web service. The only answer I can think of is that there is a bug in the Report Server's or XML Data Source's code.

Has anyone else seen this problem?

Thank you,

Jessica

Hi Jessica, could you please repro the bug and send me most recent RS logs to almineev. At microsoft. Com.

RS logs are located at location similar to this one: C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\LogFiles

|||

Jessica, did you ever find out the solution for your problem? I am experiencing a similar issue now.

Thanks,

James

|||It hapened to me, more or less the same.

For what I know it can be a problem with the default values of the

Report Parameters... they should not be originated by a query.

I had this problem and puting this as static or calculated values solved the issue...

Not the best way as by default the user's are not getting the data shown, but that's a solution.

Hope Microsoft solves the issue.. as this is a serious bug.

Regards,

Jose|||

Our problem was two report items on top of each other. Once we moved one of them over by just a little bit, the error went away.

I'm assuming it was something in the server code that wasn't handling the scenario of the same positional coordinates being inserted into a hash table, but that's just a guess.

We solved it by recreating the report one item at a time, uploading after each item. When we layered the second item on top of the first, that's when we received that message.

Good luck with everyone else trying to get this to work!

-Jessica

Bug in Report Server?

I am running a report using RS2005. It connects to a web service using the XML data source. It works correctly in my development environment. When I put it up on the server and run the report, I get the error message:

An error occurred during rendering of the report.

An error occurred during rendering of the report.

Item has already been added. Key in dictionary: '1' Key being added: '1'

If I run the web service from the server, it returns correct data, so I know error is not coming from my web service. The only answer I can think of is that there is a bug in the Report Server's or XML Data Source's code.

Has anyone else seen this problem?

Thank you,

Jessica

Hi Jessica, could you please repro the bug and send me most recent RS logs to almineev. At microsoft. Com.

RS logs are located at location similar to this one: C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\LogFiles

|||

Jessica, did you ever find out the solution for your problem? I am experiencing a similar issue now.

Thanks,

James

|||It hapened to me, more or less the same.
For what I know it can be a problem with the default values of the Report Parameters... they should not be originated by a query.
I had this problem and puting this as static or calculated values solved the issue...
Not the best way as by default the user's are not getting the data shown, but that's a solution.
Hope Microsoft solves the issue.. as this is a serious bug.
Regards,
Jose|||

Our problem was two report items on top of each other. Once we moved one of them over by just a little bit, the error went away.

I'm assuming it was something in the server code that wasn't handling the scenario of the same positional coordinates being inserted into a hash table, but that's just a guess.

We solved it by recreating the report one item at a time, uploading after each item. When we layered the second item on top of the first, that's when we received that message.

Good luck with everyone else trying to get this to work!

-Jessica

Bug in Report Server?

I am running a report using RS2005. It connects to a web service using the XML data source. It works correctly in my development environment. When I put it up on the server and run the report, I get the error message:

An error occurred during rendering of the report.

An error occurred during rendering of the report.

Item has already been added. Key in dictionary: '1' Key being added: '1'

If I run the web service from the server, it returns correct data, so I know error is not coming from my web service. The only answer I can think of is that there is a bug in the Report Server's or XML Data Source's code.

Has anyone else seen this problem?

Thank you,

Jessica

Hi Jessica, could you please repro the bug and send me most recent RS logs to almineev. At microsoft. Com.

RS logs are located at location similar to this one: C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\LogFiles

|||

Jessica, did you ever find out the solution for your problem? I am experiencing a similar issue now.

Thanks,

James

|||It hapened to me, more or less the same.

For what I know it can be a problem with the default values of the

Report Parameters... they should not be originated by a query.

I had this problem and puting this as static or calculated values solved the issue...

Not the best way as by default the user's are not getting the data shown, but that's a solution.

Hope Microsoft solves the issue.. as this is a serious bug.

Regards,

Jose|||

Our problem was two report items on top of each other. Once we moved one of them over by just a little bit, the error went away.

I'm assuming it was something in the server code that wasn't handling the scenario of the same positional coordinates being inserted into a hash table, but that's just a guess.

We solved it by recreating the report one item at a time, uploading after each item. When we layered the second item on top of the first, that's when we received that message.

Good luck with everyone else trying to get this to work!

-Jessica

BUG in Integration Services

Problem

When you have a SSIS package that contains a connection from a data source, this connection is not updated when the data source changes based on a configuration change.

Situation :

A SSIS solution contains 3 configurations : Development, Test, Production. You can create those configurations in configuration manager of the solution.

The SSIS project contains one Data source. It doesn't really matter what type but I take SQL Server. The database server in development is SQL_DEV, in test is SQL_TEST and in production is SQL_PROD. Initially they are for all configurations the same. You can specify those values by changing the active configuration and then editing the Data source.

In the SSIS package (DTSX), you can create a connection manager based on a Data source.

If you change the Data source, the connection manager is also changed. If you change the Data source by changing the active configuration, the connection manager is not being updated.

If you think this isn't a big issue think big. We have 4 configuration, 10 shared Data sources and 25 DTSX packages. That would give a maximum of 1000 settings (4 x 10 x 25). Using this method it can be reduced to 40 (4 x 10). Of course this is a theoretical but it is very common to have the destination data source re-used on all packages, which still would be 100 settings (4 x 25)

Steps to reproduce

- create a new SSIS project

- In the solution explorer, create a new Data source named TestSource.

- In the connection managers window of Package.dtsx, create a new connection from a Data source.

- Make some changes in to TestSource.ds under the Data Sources. For example change the server or the database.

- Verify that those changes are also in the package.

- in the solution explorer, right click the solution and select configuration manager

- under active solution configuration, create a new configuration named test.

- Set the copy settings from : development

- Verify that Create new project configuration is checked.

- click OK and close.

- Notice that the active configuration is now Test

- Make some changes the Testsource.ds like a different server.

- Verify that those changes are also in the package.

- Make the development configuration as active.

- Notice that the Testsource.ds contains now the original settings.

- You will notice that the connection manager still contains the "test" settings and not the development settings.

- If you create a deployment utility it will still contains the wrong values.

with regards,

Constantijn Enders

Don't use Data Sources. Just use the connection managers and use package configurations to configure them.

BUG : Data source not appearing

Hello,

I am trying make some imports and I am using the wizard.

I have created a new project and added 2 new datasources. One for a DB2 database and one for a SQL Server database

I select Project | SS Import and Export Wizard and the wizard launches.

When I look under datasource I can't find my 2 created datasources. I can create new ones but this is not something I want.

Any help ?

Constantijn Enders

Are you trying to create data sources, via the 'Data Sources' in the BI Project? if so, the data sources created in there will not be shared/visible in import/export wizard or the package.

Import/Export wizard has no access to project level artifacts, but is simply a way to auto-generate packages. Please try creating a package for your solution.

|||

It would be handy if the import/export wizard could access the project items. Perhaps for a next version.

I am importing from a DB2 system and setting up the oledb connection requires at least 3 screens.

You can only import multiple tables and/or views OR a single query. (not multiple queries)

If you have 10 queries, you have to setup that connection 10 times

Just my 2 cents

CE

Bringing data into a flat file

I am trying to bring in the result fo a query to a flat file. The source and destination connections are oK, the mapping is also correct. However I get the following error during execution:

[Flat File Destination [507]] Error: No column was specified to allow the component to advance through the file.

followed by this:

[DTS.Pipeline] Error: component "Flat File Destination" (507) failed the pre-execute phase and returned error code 0xC02020F0.

What is the fix?

Hmmm... never seen that error before. Can you share your package? I'd be interested in seeing it. Perhaps email me via http://blogs.conchango.com/jamiethomson/contact.aspx

I'm not promising that I'll be able to fix it

-Jamie

|||

Jayaram Krishnaswamy wrote:

I am trying to bring in the result fo a query to a flat file. The source and destination connections are oK, the mapping is also correct. However I get the following error during execution:

[Flat File Destination [507]] Error: No column was specified to allow the component to advance through the file.

followed by this:

[DTS.Pipeline] Error: component "Flat File Destination" (507) failed the pre-execute phase and returned error code 0xC02020F0.

What is the fix?

Jay,

Thanks for sending your package.

You don't have anything mapped to [Column 0] in the destination. I am guessing that this is the problem.

-Jamie

|||

Thanks. Perhaps I have to just test various possibilities.

|||

Jayaram Krishnaswamy wrote:

Thanks. Perhaps I have to just test various possibilities.

No. You simply have to put something into that first column.