BUG? Extracting Data Stored in Multiple Languages

Hi,

I have an access database that is storing text in english and chinese characters. I am importing the data using SSIS and it is putting a 'square' character in place of all the chinese characters. Can I associate multiple code pages to a control? If this is not possible, I would just like to Extract the data and Load it into a table, and i will just handle the errors and write the key fields to an error log. The problem is that the error handler is not redirecting the row. It just simply fails. I have changed all the error actions to Redirect Row too. I currently have a script checking if the 'square' character exists but i feel this should be handled a better way? Has anyone run into this issue or have any suggestions?

This is the error code i get

Data conversion failed. The data conversion for column "FDCDOPS0" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

Access stores string data as Unicode. Is it possible to keep it that way? Where do you transfer data to? If you are moving it to SQL Server tables, is it possible to use Unicode string types (nchar/nvarchar), and avoid conversions?

Thanks.

|||

I am storing the field in question as nvarchar(256). I'm transferring data to a SQL Server 2005 database. I still have had no luck in solving the issue. Unicode is the answer i think. I am using a Data Source and Data Source View in SSIS. When I view the data in the data view the conversion has already taken place. Is there a way to set the field types in the data source or view?

Thanks

|||

It is not quite clear to me what you are doing. Could you explain what components you are using in your package or even better attach a simplified version of it.

Thanks.

sql

Bug in the SSIS?

When viewing the flat file through the viewer , certain rows showing up as a small black square where there is a line feed and these rows are getting concatenated and being counted as one row.

Anybody knows why this is happening?

Thanks

Karthika

Make sure you have the correct row delimiter chosen. Unix files, for instance, typically only have a LF as the row delimiter. Windows files typically have a CR/LF as the row delimiter. Try choosing different values.

Bug in SSIS?

I am trying to convert a string to a specific date format by using a simple select like this:

SELECT CONVERT(VARCHAR(3), CONVERT(DATETIME, ?, 103),107)

First of all the Parameter ? is not recognized and when i run the Preview it would trought the following error even if i change the ? with '20050101' for example:

===================================

There was an error displaying the preview. (Microsoft Visual Studio)

===================================

Undefined function 'CONVERT' in expression. (Microsoft JET Database Engine)

Program Location:

at Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.Connections.SQLTaskConnectionOleDbClass.ExecuteStatement(Int32 resultType, Boolean isStoredProc, UInt32 dwTimeOut)
at Microsoft.DataTransformationServices.Design.PipelineUtils.ShowDataPreview(String sqlStatement, ConnectionManager connectionManager, Control parentWindow, IServiceProvider serviceProvider, IDTSExternalMetadataColumnCollection90 externalColumns)
at Microsoft.DataTransformationServices.DataFlowUI.DataFlowConnectionPage.previewButton_Click(Object sender, EventArgs e)

Anyone experiencing this kind of problem? Solutions?

Best Regards,

Luis Sim?es

Well the error clearly indicates that Jet doesn't support the function so this would not be an SSIS bug. Have you attempted to look at the Jet docs to see if it supports convert. The last time I used jet it did not.

Thanks,

Matt

|||

Yes you are right.

I have figured it out pretty quickly and i tried to delete this post without success sorry...

It was my mistake! Not really looking into it :P

But thanks :)

Best Regards,

PS: Merry Christmas to All of YOU!

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 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 in BIDS when developing SSIS packages

There seems to be a BUG in BIDS when developing SSIS packages using the Import/Export Data wizard.

If you use the wizard to import a large number of tables, and then select all the tables, and then choose to delete exisiting data in each table, the PrologueSQL file does NOT get built correctly. Instead of having a

TRUNCATE tablename Go

for each table, it just has a bunch of "Go"s with nothing between them. In the step immediately prior, where you confirm what the wizard will do, it tells you, after each table, that it will delete any existing data...but it doesn't do this.

If, during the wizard, I select each individual table one at a time and tell it to delete existing data, then it will get built correctly, but not if I select them all at once...YET, if I do select the whole block, choose delete existing data, and then select any single table, it shows that table as being set up to delete existing rows.

This is very frustrating when trying to import large numbers of tables.

Am I missing something? or is this really a bug?

Thanks, Jeff

Jeff,

We've noticed the same behavior here. If we edit the mappings for each of the tables individually we see that the 'Delete rows in destination table' is selected (after selecting that option for the group). If we click OK for the mappings dialog the TRUNCATE options is then correctly added to the package.

I vote for bug.

Ray

|||Post it at:

http://connect.microsoft.com/sqlserver/feedback|||

Yes, this is a known bug.

The best workaround I can offer is to unselect the "Optimize for Many Tables" checkbox. The transfer will probably succeed if the number of tables is resonable (say less than 100) for your hardware (transfers will go in parallel). If it fails, try to go in multiple batches with smaller number of tables.

Thanks.

BUG in BIDS when developing SSIS packages

There seems to be a BUG in BIDS when developing SSIS packages using the Import/Export Data wizard.

If you use the wizard to import a large number of tables, and then select all the tables, and then choose to delete exisiting data in each table, the PrologueSQL file does NOT get built correctly. Instead of having a

TRUNCATE tablename Go

for each table, it just has a bunch of "Go"s with nothing between them. In the step immediately prior, where you confirm what the wizard will do, it tells you, after each table, that it will delete any existing data...but it doesn't do this.

If, during the wizard, I select each individual table one at a time and tell it to delete existing data, then it will get built correctly, but not if I select them all at once...YET, if I do select the whole block, choose delete existing data, and then select any single table, it shows that table as being set up to delete existing rows.

This is very frustrating when trying to import large numbers of tables.

Am I missing something? or is this really a bug?

Thanks, Jeff

Jeff,

We've noticed the same behavior here. If we edit the mappings for each of the tables individually we see that the 'Delete rows in destination table' is selected (after selecting that option for the group). If we click OK for the mappings dialog the TRUNCATE options is then correctly added to the package.

I vote for bug.

Ray

|||Post it at:

http://connect.microsoft.com/sqlserver/feedback|||

Yes, this is a known bug.

The best workaround I can offer is to unselect the "Optimize for Many Tables" checkbox. The transfer will probably succeed if the number of tables is resonable (say less than 100) for your hardware (transfers will go in parallel). If it fails, try to go in multiple batches with smaller number of tables.

Thanks.

Buffer overflow exception in SSIS

I am running a SSIS package which inserts records in 8 tables. After inserting about 280 records I get an error "Buffer overflow". Any help is greatly appreciated.

We'll need more info. What are the sources and destinations? What providers are being used? What transformation objects does the package use? What OS platform are you running on? etc.

Thanks

Donald Farmer

Bring File In As Binary Field

I want to have an SSIS package that processes a file in the normal insert, update style. But at the end I want to store the file as a binary field to another table for archive purposes. I am having trouble finding a good way to do this. Any samples, ideas, or articles would be appreciated.I haven't tried this, but my first thought would be reading the file into a variable through a script task, and then using that variable to write the blob to the destination table.|||That is the only method I could come up with as well.|||

Import Column transform is supposed to do this. Could you give it a try? It is not easy to configure as it deos not have a custom UI, but see if the BOL has some info.

Thanks.

|||It sounds like this might do what I need but books on line (like many things) is worthless for explanation of how/what to setup and use this component. Are you aware of other resources for this component?|||A quick Google search turned this up. See if it helps:

http://mgopinath.blogspot.com/search/label/Integration%20Services|||Thanks Phil... my google skills were poor as I was unable to find an article like that.|||

1Dave wrote:

Thanks Phil... my google skills were poor as I was unable to find an article like that.

ssis "import column"

Breakpoint in child-package triggered on first execution only.

I have a child package which is executed several times within the same SSIS ETL. I have placed a break point on one of the child package's tasks, set to trigger on a PreExecute() event. The first time the child package is invoked, the breakpoint is triggered. However, on each successive invocation the breakpoint is ignored. Does anybody know if this behaviour is normal? Thanks in advance!

This is a known issue and we are aware of the bug. Thanks