Hi everyone,
I have to extract, dayly a list of contacts on a exchange server in a table on our EDW on sql server 2005. Is it possible to get the information directly from a dataflow or i will have to developpe a script task ?
Need help desperatly !!!
Can I connect directly to Outlook using SSIS? If so is there any white paper or walkthroughs I could follow?
TIA
Tom
Hi All,
I want to export data from SQL Server2005 to an Excel spreadsheet thru "Data Flow Task". I am using OLE DB for SQL Server for the source connection and a Connection To Excel as my destination source. The Excel spreadsheet (2003) exists and has the first row with column names. I don't have any warnings before trying to execute.
The SQL datable fileds are
i) ID - Int
ii) RefID
iii) txtRemarks - nvarchar(MAX)
iv) ddlWaterLevel - nvarchar(50)
While executing the tasks, I got the error
Error: 0xC0202025 at Data Flow Task, Excel Destination [427]: Cannot create an OLE DB accessor. Verify that the column metadata is valid.
Error: 0xC004701A at Data Flow Task, DTS.Pipeline: component "Excel Destination" (427) failed the pre-execute phase and returned error code 0xC0202025.
After analysing I found in the DataFlow --> Excel destination --> Advanced Editor for Excel Destination, the default data type for txtRemarks shows as "Unicode string [DT_WSTR]". But this is supposed to be "Unicode text stream [DT_NTEXT]". Even if I change the data type in the design time, It doesn't accept.
Please do help me out.
thanks
Sanra
hi lads,
what i need to do for my project are as following:
a mobile user send data via GPRS to SQL server Database. then i need to have a method to detect while a particular table is being inserted. and then extract data from table construct a report dynamiclly. what should i do to achieve this goal? e.g. window application, store procedure or trigger ?
PS : client side is a mobile application developed using MCL. i don't in which way he will send all data to SQL server Database, so what i need to do is to monitor new data inserting.
2) how to auto generate and print report directly after record been inserted into the table ? Do i need to import report web service API ? if yes, which one? or i can use other methods e.g. predefine report control view in my window application, turn off pop-up menu while printing a report(I guess)
thanks
Guys
Does anyone know of a 3rd party module/plug-in for VS or even some
downloadable code that enables the import of Outlook/ACT and similar contact
information through an ASP.NET page into a SQL Server table with ongoing
1-click button sync.
I need to replicate something similar to the Intellisync tool used in Yahoo
Mail.
regards
Andy
i have a report that is on a subscription to my team members that i want to be rendered in the actual email that they open up. i do not want them to have to click on the pdf or excel attachment to open it up, i just want the content of the report to be in the email itself in outlook.
is this possible in SSRS 05?
Hi, all here,
Thank you very much for your kind attention.
I am wondering if it is possible to use SSIS to sample data set to training set and test set directly to my data mining models without saving them somewhere as occupying too much space? Really need guidance for that.
Thank you very much in advance for any help.
With best regards,
Yours sincerely,
Hello,
Is it possible to use existing data flow components (Merge Join, aggregation,...) in a custom data flow component?
Thanks,
Yoann
Can anyone take me through synchronization of contacts within Business Contacts Outlook into Microsoft Small Business Accounts?
I run a stand alone PC with NO network. When SBA came SQL was also installed. Apparently you can synchronise Contacts within Business Contacts with SBA but both SBA & Outlook should work through the same SQL server.
Has anyone tried this?
Can someone walk me through the process?
Thanks
Debbie
sql 2005 table ?
Hi everyone,
I need to assemble information from my outlook account into a database table. Is it possible to insert "title" of a set of mails recieved into a table ? sender's name ? recipient's name? body? etc...
thanks
I am trying to get counts of contacts.
So how do I subset, to gain a the accounts with the products then query to gain the contact counts from those accounts?
But for some reason it's giving me counts of products.
select distinct
a.region__c as [Region],
a.SFTX_ID as [SFTRX ID],
count(c.Contact_ID) as [Contact Count]
from vwContact c
[Code] .....
Hi,
I'm trying to implement an incremental data pull (Oracle to SQL) based on Andy's blog:
http://sqlblog.com/blogs/andy_leonard/archive/2007/07/09/ssis-design-pattern-incremental-loads.aspx
My development machine is decent: 1.86 GHz, Intel core 2 CPU, 3 GB of RAM.
However it seems the data flow task gets hung whenever I test the package against the ~6 million row source, as can be seen from these screenshots. I have no memory limitations on the lookup transformation. After the rows have been cached nothing happens. Memory for the dtsdebug process hovers around 1.8 GB and it uses 1-6 percent of CPU resources continuously. I am not using fast load to insert new records into my sql target table. (I am right clicking Sequence Container 3 and executing this container NOT the entire package in the screenshots)
http://i248.photobucket.com/albums/gg168/boston_sql92/1.jpg
http://i248.photobucket.com/albums/gg168/boston_sql92/2.jpg
http://i248.photobucket.com/albums/gg168/boston_sql92/3.jpg
http://i248.photobucket.com/albums/gg168/boston_sql92/4.jpg
http://i248.photobucket.com/albums/gg168/boston_sql92/5.jpg
http://i248.photobucket.com/albums/gg168/boston_sql92/6.jpg
The same package works fine against a similar test table with 150k rows.
http://i248.photobucket.com/albums/gg168/boston_sql92/7.jpg
http://i248.photobucket.com/albums/gg168/boston_sql92/8.jpg
The weird thing is it only takes 24 minutes for a full refresh of the entire source table from Oracle to the SQL target table.
Any hints,advice would be appreciated.
i got this error message in my browser
SELECT permission denied on object 'Contacts', database 'Contacts', owner
'dbo'.
this is the code where it stopped
sqlDataAdapter1.Fill(dataSet11);
//Update the data grid
DataGrid1.DataBind();
Now i finally i grant the access to the database for user ASPNET but it
seems not working yet.
I am running SQL Server
What i am missing ?
Thanks
Hi, All,
I need to pass a parameter from control flow to data flow. The data flow will use this parameter to get data from a Oracle source.
I have an Execute SQL task in control flow to assign value to the Parameter, next step is a data flow which will need take a parameter in the SQL statement to query the Oracle source,
The SQL Looks like this:
select * from ccst_acctsys_account
where to_char(LAST_MODIFIED_DATE, 'YYYYMMDD') >?
THe problem is the OLE DB source Edit doesn€™t have anything for mapping parameter.
Thanks in Advance
Hello all,
I am trying to build some sort of interface either directly in MS Outlook on the client comp. or a stand-alone app. The requirement works somewhat like what is done between a PDA and MS Outlook, with the exception that the data only goes one way - SQL Server --> Outlook.
I want to allow for the user to choose from their data in SQL Server and have it bring that data down into their contacts folder. It will need to work over the internet, and MS Exchange is not a preferred method.
Clients are not on the same network as the server, they only connect to the internet via IP and use simple POP mail accounts.
Thanks ahead of time.
Dave
I have an Execute SQL Task that returns a Full Rowset from a SQL Server table and assigns it to a variable objRecs. I connect that to a foreach container with an ADO enumerator using objRecs variable and Rows in first table mode. I defined variables and mapped them to the columns.
I tested this by placing a Script task inside the foreach container and displaying the variables in a messagebox.
Now, for each row, I want to write a record to an MS Access table and then update a column back in the original SQL Server table where I retreived data in the Execute SQL task (i have the primary key). If I drop a Data Flow Task inside my foreach container, how do I pass the variables as input to an OLE DB Destination on the Data Flow?
Also, how would I update the original source table where source.id = objRects.id?
Thank you for your assistance. I have spent the day trying to figure this out (and thought it would be simple), but I am just not getting SSIS. Sorry if this has been covered.
Thanks,
Steve
Dear All!
My package has a Data Flow Task. In Data Flow Task, I use a Script Component and a OLE BD Destination to transform data from txt file to database.
Within Data Flow Task, I want to call File System Task to move file to a folder or any Task of "Control Flow" Tab. So, Does SSIS support this task? Please show me if any
Thanks
Hi everyone,
Primary platform is 64 bit cluster.
How to move information allocated in SSIS variables from Data Flow to Control Flow layers??
We've got a SSIS package which load a value into a variable inside a Data Flow. Going back to Control Flow how could we retrive that value again????
Thanks in advance and regards,
I'm currently setting variables at the package level with an ExecuteSQL task. This works fine. However, I'm now starting to think about restartability midway through a package. It would be nice to have the variable(s) needed in a data flow set within the data flow so that I only have to restart that task.
Is there a way to do that using an SQL statement as the source of the value in a data flow?
OR, when using checkpoints will it save variable settings so that they are available when the package is restarted? This would make my issue a moot point.
First let me give you a little back ground on me. I'm very new to the ASP, ASP.NET, Visual Studio, Sql Server, Frameworks....thing. I am coming over from a PHP/MySql background of over 5 years. The change over to VBScript and VB has been to tough and I have a basic liking for the Visual Studio 2005 and the ease of putting things together. However, I have come across something that to me seems like it should be relatively simple, but haven't been able to find the documentation or samples to describe what I'm looking to do.
Rough Need:
1. Start with a form will a couple labels and a singe textbox to get the lookup date from an user.
2. Query one table/view based on the users choice of date and select only one field of returned data.(Doing this by itself is not a problem and I can display my results in a Gridview, but this is where it starts getting tricky and the gridviews won't work for me.)
3. If there is something returned, I need to start a HTML table layout or possibly some form of a Gridview(I don't see how I would use the Gridview) and start a loop, adding the first returned row from this query into the first cell.
4. Now, based on that same User Date and the returned row value from the previous query, I need to query another Table/view and return another single field, which might return 1 or multiple rows, which I need to start a loop to display unique items in cells under the first on above.
5. Based on the original User Date and each returned row from the first query, I need to query two other Table/views and get some additional information. 4-5 fields will be returned and be displayed on one row in the number of necessary columns.
6. This would finish the first row from the first query, so I would need to loop back up to see if there were any further results and continue looping until the get to the last of the results from the first query.
7. Finally, I would just need to close up the Table/gridview.
The basic results I'm looking for would be similar to the following:
Results for 10/11/2005
Field name 1
Field name 2
Field name 3
Field name 4
Field name 5
First Result from query 11 of 4 results from Query 2, but only 1 unique result
Info from Query 3
Info from Query 3
Info from Query 3
Info from Query 4
Info from Query 4
Info from Query 3
Info from Query 3.
Info from Query 3
Info from Query 4
Info from Query 4
Info from Query 3
Info from Query 3
Info from Query 3
Info from Query 4
Info from Query 4
Info from Query 3
Info from Query 3
Info from Query 3
Info from Query 4
Info from Query 4
Second Result from query 1First result from Query 2 after looping though the first query Second result from Query 2 after looping though the first query
Info from Query 3
Info from Query 3
Info from Query 3
Info from Query 4
Info from Query 4
Info from Query 3
Info from Query 3.
Info from Query 3
Info from Query 4
Info from Query 4
Info from Query 3
Info from Query 3
Info from Query 3
Info from Query 4
Info from Query 4
Info from Query 3
Info from Query 3
Info from Query 3
Info from Query 4
Info from Query 4
It seems me from what I have read and slowly figuring out, is that I should be able to directly access the DataSet returned by four SqlDataSources, one for each of the above querys and then just write my own VB to handle the necessary looping, table format and such. I can easily add the for SqlDataSources to the page and add a Gridview for each one and get 4 separate chunks of info, but can't see a way with GridViews to intermingle the info like displayed above.
So, if it can be done with Gridviews, then I would love to see how that is done. But, if someone could explain to me how I access the DataSets directly that I get from the 4 SqlDataSources, then that would be ok too. I have figured enough out with VB, that I can write the code to do my looping requirements, if I can just access the information I get back. Thanks for reading all the way through this long post.
Jack
hi
im new to Sql server so this might be a daft question
is there something where i can put data directly into a table (i.e entering data into a grid) or do i have to write the sql every time
I have thousands of records, which are .txt format, to be loaded to the DB and need to mining them. The only way for that is to write a program and read them to the DB or can I load them directly? And also I have to design the tables based on these data myself, right? Thank you.
Hello,
I have noticed that for one of my data-flows, the process is really long during the phase "the final commit data insertion has started".
To be accurate, the process is fast until it reaches this phase. It happens often when I load millions of lines.
The extraction is done from a database SQL Server 2005 to a database SQL Server 2005, on the same server (with the SQL Server native provider).
I used a SQL Server destination but I have tried with an OLE DB destination and it is the same situation.
Why the process could be so long during this phase?
There is a way to optimised my package to avoid that?
Any idea is welcome.
Thanks.
Guillaume
I need to see inside a SSIS 2012 project a new SSIS installed component, but in the SSDT 2010 I cannot see the SSIS Data Flow Items tab for adding data source/data destination respect to the choose toolbox items pane.
View 4 Replies View RelatedHi, all experts here,
Do we always have to use SCD component for the loading of data into data warehouse to handle changes of rows?
I am looking forward to hearing from you and thank you very much in advance for your help.
With best regards,
I need to call a stored procedure to insert data into a table in SQL Server from SSIS data flow task.
I am currently trying to use OLe Db Destination, but I am not sure how to map inputs to OLE DB Destination to my stored procedure insert.
Thanks
Good morning, all,
I am working on importing an Excel workbook, saved as multiple CSV flat files, that has both group level data and related detail row on the same sheet. I have been able to import the group data into a table. As part of the Data Flow task, I want to be able to save the key value for the group, which I will use when I insert the detail rows.
My Data Flow has the following components: The flat file with the data, which goes to a derived column transformation to strip out extraneous dashes, which leads to the OLEDB Destination component.
I want to save the value as a package level variable, so that I can reference it in another dataflow.
Is this possible, and if so, at what point do I save the value?
Thanks,
Kathryn
I am getting the following error running a data flow that splits the input data into multiple streams and writes the results of each stream to the same destination table:
"This operation conflicts with another pending operation on this transaction. The operation failed."
The flow starts with a single source table with one row per student and multiple scores for that student. It does a few lookups and then splits the stream (using Multicast) in several layers, ultimately generating 25 destinations (one for each score to be recorded), all going to the same table (like a fact table). This all is running under a transaction at the package level, which is distributed to a separate machine.
Apparently, I cannot have all of these streams inserting data into the same table at one time. I don't understand why not. In an OLTP system, many transactions are inserting records into the same table at once. Why can't I do that within the same transaction?
I suppose I can use a UnionAll to join them back together before writing to a single destination, but that seems like an unnecessary waste and clutters the flow. Can anyone offer a different solution or a reason why this fails in the first place?
Thanks in advance.
I need to know what a table's max row Identity is part way thru a data flow. I can't get it at the beginning of the data flow. I need to either (1) add it to the data buffer part way thru or (2) set it into a package variable and then reference the var in a script component.
I've not found a way to add a database column to the data buffer without doing a lookup for each row (too slow and not appropriate here) or some goofy oledb source and then merge join into the data buffer on a contrived join.
I've read questions about referencing package vars in scripts but I can't get that to work. DTS.Variables("varname").Value isn't recognised when I code it up.
Anyone have an idea or solution for either one of these? If you're gonna explain the script code, please include the entire snipet including the INCLUDEs, etc.
is it possible to write a script for Microsoft outlook that will execute a query to an sql server 2005 and populate a public "contacts" folder?
i know there are expensive syncing applications, but i would just like to script and buttonize the manual export-import process.
is it possible? macros?
what is required? ADO/DAO/other
is there a basic example of this or just a script sample of connecting and retrieving a dataset from the server?
thanks,
Leon
Hi all! I recently started working with SSIS and one of the things that is puzzling me the most is what's the best way to go:
A small control flow, with large data flow tasks
A control flow with more, but smaller, data flow tasksAny help will be greatly appreciated.
Thanks,
Ricardo
I am new to SSIS programming, so bear with me if my question seems naive to you gurus. I have a situation that needs to set the data source for a data flow from external .NET application ('external' means that the application will run on different process than the SSIS). I am trying to set the data source on which the data flow works from my C# application in a DataSet format. Ideal solution is not to save the DataSet to any file on harddisk (I know that will work, but has the overhead of writing, reading and managing the temp file). What I want to achive is that the business logic of picking data for SSIS Data Flow to process is controlled inside my C# application, the Data Flow just does what it does best - Transformation. Have any of you successfully done this before?. Thanks!
View 1 Replies View RelatedI have a fairly simple data flow task that loads data from one table (OLE DB Source) into another table (OLE DB Destination). The data type for one of the pairs of columns is nVarChar(120) and it contains version information that looks like a decimal. When I run the export, the destination has a trailing zero added after the decimal point as if it were a numeric column which invalidates our comparisons (string 1.0 is not the same as string 1.00). There is no cast or convert done to this column, it is a straight copy. Any ideas what could be causing this or how to fix?
View 6 Replies View Related