Hi, How can I show a Connection String at Run Time using MessageBox.Show()? Thenks, Levogiro.Benedito R. Almeida Filho - BI

Hi levogiro, assuming you are on SSIS 2008 and coding in C# and you have a variable storing the conn string: String ConnString = Dts.Variables["User::ConnString"].Value.ToString(); MessageBox.Show(ConnString); The above is in a Script Task Arthur My Blog

Hi, ArthurZ What I meant was how do I get the value of a Connection String of a Connection Manager component? Thanks, Levogiro.Benedito R. Almeida Filho - BI

You can - but you shouldn't. MessageBox.Show() will cause a modal dialog to pop up at runtime. SSIS packages are (typically) run on a server without an interactive console - so causing a modal dialog to show will essentially hang your package execution. You should use Package Logging to record information like that. You can use a Script Task to call a logging event like FireInformation, and reference a Connection Manager property in that call: bool fireAgain = true; Dts.Events.FireInformation(0, "", "Connection String:" + Dts.Connections["Conn"].ConnectionString, "", 0, ref fireAgain); Talk to me now on

I am now thinking you set your Conn String dynamically using a SSIS Expression or a variable, thus you want to set a breakpoint and inspect its content. In this case you better use a slightly more elegant approach: the Watch Window. This way you do not need to code anything, just drag and drop your variable to it. Here are some more details: http://consultingblogs.emc.com/jamiethomson/archive/2005/12/05/2462.aspxArthur My Blog

ArthurZ, I've checked all variables and they are getting the right values. I even verified the SQL command I sending to Oracle server using PLSQL Developer and it returns some data, but my package is not inserting any record on the SQL Server table. I mentioned using MessageBox.Show just to see if the Manager Connection component is getting the right value. Thanks, Levogiro.Benedito R. Almeida Filho - BI

So my understanding: in your DFT you query Oracle (source), get data, and these data you supposed to insert into SQL Server (destination). Please tell us if you get an error. Arthur My Blog

I'm getting no error message. It looks like the query isn't returning any data at all. But if I run it in PLSQL Developer the query returns data.Benedito R. Almeida Filho - BI

Do you get data in preview or Data Viewer?Arthur My Blog

No such luck. I put a data viewer in the data flow and got nothing. I even fixed a SQL command that I know returns data and nothing too.Benedito R. Almeida Filho - BI

If so something is wrong with the way you establish the connection, could be the credentials or even Oracle itself. How about you use the Data Export Import Wizard just for the sake of proving your ability to get the data. If you do not know how use this link: http://www.databasejournal.com/features/mssql/article.php/3580216/SQL-Server-2005-Import--Export-Wizard.htm Besides, since you have a very simple package to do you can then merely save the result of the wizard for later as a ready to use package.Arthur My Blog

Sorry, ArthurZ, When I try the preview it brings data from Oracle server. Thanks, Levogiro.Benedito R. Almeida Filho - BI

Still do the Data Import and let's see what is going onArthur My Blog

I tried the Data Import and got this error message: --------------------------- SQL Server Import and Export Wizard --------------------------- Unexpected Unrecoverable Error --------------------------- Anular Repetir Ignorar --------------------------- Also, I checked the Progress tab in the package and this message appears a lot of times: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done." I parsed the query (it's very simple; it returns a fields to be used in a for each loop)Benedito R. Almeida Filho - BI

Inspect for more errors, we want to see more specifics, and at what stage it fails. I would also add logging to the package up just to be more aggressive with the situation.Arthur My Blog

It doesn't fail. The package processes until the end and doesn't input any data on the SQL Serve table.Benedito R. Almeida Filho - BI

My wild guess it is the code page issue. Could be security and even your connection. What driver did you use to connect?? Arthur My Blog

As a last try , for this error ""Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done." http://social.msdn.microsoft.com/Forums/en-US/sqlgetstarted/thread/86ea5891-fe7e-427e-a93a-96d70c01245b http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/afd34c3f-ff48-486c-aadb-934862b0e652 Basically talks about modifying the connection string for the source connection.Happy to help! Thanks. Regards and good Wishes, Deepak.

Here's what I've done so far: Changed "AlwaysUseDefaultCodePage" to "False" (I was using "True"); Removed "Persist Security Info=True;" from the connection string; The driver I'm using is "Provider=OraOLEDB.Oracle.1" (Native OLE DB\Oracle Provider for OLE DB). Man, this is driving me crazy... Thanks again for your help.Benedito R. Almeida Filho - BI

Hi levogiro, Did you check my references, one of them included "Persist Security Info=false;"Happy to help! Thanks. Regards and good Wishes, Deepak.

Set AlwaysUseDefaultCodePage=TRUEArthur My Blog

I already tried this option.Benedito R. Almeida Filho - BI

How about using a different driver? Microsoft Connectors for Oracle and Teradata by Attunity: http://www.microsoft.com/downloads/en/details.aspx?FamilyID=d9cb21fe-32e9-4d34-a381-6f9231d84f1e&displaylang=enArthur My Blog

A last option is to install all the updates, service packs, hot fixes whatever available. What is the version of SSIS/SQL used and Visual Studio?Happy to help! Thanks. Regards and good Wishes, Deepak.

It worked!!! I changed the driver from "Provider=OraOLEDB.Oracle.1" to "MSORA.1" and now the package is working fine. I just don't understanted why it stopped working. Thanks, ArthurZ, for you help and patience. Levogiro.Benedito R. Almeida Filho - BI