i am transfering the table from one database to csv file format..i did it.. again i want to shift that csv files to another databse as tables. how to do this task.. pls help me.. its very urgent..out TL had given me the dead line.. send reply soon....
View 5 RepliesGood Afternoon,
So this one has been bugging me for a while and I am ready to punt...
Is it possible to dynamically create a text file destination in SSIS and then pump the results of a query stored in a variable to this text file?
So my package looks like this
1) SQL task that pulls back a list of tables to be exported
2) For Each Loop ADO enum that passes the table name to a SQL Task that builds the select...ie select * from <DTS.Variables()>
3) Data flow task that sets the command from variable from step 2
4) Text File destinaiton that is built using a varable as the connectionstring
I am delaying validation in steps3 and 4 above without any luck...basically I am curious if i can even do what I am thinking I should be able to do here...I get as far as getting metadata errors because SSIS can't seem to handle dynamically filling the pipeline with the columns from the variable/SQL statement.
Am I missing something? Is this possible?!
Thanks in advance.
Dave
I have a select Script as follows:
SELECT c.ABC AS 'ABC'
, a.Qty AS 'Quantity_Recived'
, b.PC AS 'PC'
, b.PC AS 'PC'
, 'I' AS 'Flag'
FROM TNRInventory.dbo.tInventoryAlloc AS a
LEFT OUTER JOIN vwInventoryAllocMapping AS vwMap ON a.TNRAllocTypeID = vwMap.TNRInventoryAllocID
LEFT OUTER JOIN ABC.dbo.ZREFRESHTAB AS b ON a.DispenserID = b.Asset
LEFT OUTER JOIN ABC.dbo.TableJoinKey AS c ON a.TitleID = c.TITLE_ID
WHERE (vwMap.DataSourceID = 3) and vwMap.[DataSourceAllocName] = 'I'
group by c.SKU_NO , vwMap.[DataSourceAllocName],a.Qty , b.Profit_Center
order by c.SKU_NO,vwMap.[DataSourceAllocName]
GO
i have to send the result of aforesaid script in batch of 300 records per file (tab delimited text file)
now the file name must be dynamically created as each file will contain 300 records.
I have found some document related to same issue on this url
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1238184&SiteID=17
but still there is a catch.
Can any one guide/suggest me better way to do the aforesaid.
Thanks
In my SSIS package I have a loop container that I am running the same code against 4 servers.
I have the package export the SQL data to an Excel spreadsheet that has multiple tabs.
Is there a way I can change the tab on the fly or do I need to create a Connection for the same spreadsheet 4 times
Each Connection pointing to a different tab?
I tried to set up a expression for the Excel Connection Manager to use the InitialCatalog for the tab and change it
based on the script in the loop however this causes the following error:
An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft Native Client" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
Cannot create an OLE DB accessor. Verify that the column metadata is valid.
Thanks in advance
Hi,
I have a small problem. I've gone through the SSIS wizard and created a dtsx file which imports data from an access file into a SQL Server 2005 database. It has been set to delete existing rows and enable identity insert.
I then edited the .dtsx package in SQL Server Management Studio and added an environment variable configuration to allow me to change the destination database. In the script which runs the dtsx, here is what I have (it's an x64 system, so hi have to use DTExec):
"C:Program Files (x86)Microsoft SQL Server90DTSBinnDTExec.exe" /file e: estimport.dtsx /set Package.Connections[DestinationConnectionOLEDB].Properties[InitialCatalog];newdatabasename
and here is the error I get:
Description: The configuration environment variable was not found. The envir
onment variable was: "InitialCatalog". This occurs when a package specifies an e
nvironment variable for a configuration setting but it cannot be found. Check th
e configurations collection in the package and verify that the specified environ
ment variable is available and valid.
I got the package.connections.etc etc path from originally creating the environment variable as an xml config file, then I could open the config file and see what the path was...
Any help would be appreciated :)
I have an source file and i have to load it into the data base by changing datatype of the columns in ssis
View 1 Replies View RelatedI have a stored procedure that (after selecting, incrementing and updating)returns the next sequential filename.I haven't been able to plug it in to the destination textfile filename in myDTS package.I know it has something to do with Dynamic Properties and I can navigate tothe property I want to set, but just can't seem to get the result of mystored procedure into the filename.I know somebody must have done this before.ThanksJoe
View 1 Replies View Related
I want to write a SSIS which picks up the source and destination tables at runtime is it possible. As we have a SSIS which is used to pull data from oracle but the source and destination table name changes.
I am trying to create a text file from an SQL query on a SQL table. I would like the SSIS package to prompt for the file name and path. The text file is tab delimited and the text qualifier is a double quote.
Thanks,
Fred
HI,
I'm programmatically able to import data between tables when the Destination table already exists but when Detination table has to be created on the fly (Name will be provided), I'm not successful in doing so.
Basically the requirement is to dump the resultset from the source in to a temp table so that the temp (Destination) table matches the Source's Schema exactly.
Has anybody done that?
Any help in this regard is greatly appreciated.
Pavan
Hi All,
I am struck at one point. I am trying to this operation and not able to go further.
1. I have got the dataset to a variable in the control flow.
2. I am looping through the dataset based on a coloumn.
3. Now inside my For each loop i have a dataflow task.
4. In the data flow task i am trying to build a dynamic query using the OLEDB Source and i have selected SQL Command from variable. And the variable build the Query as select * from @othervariable.
Now my question is
Can i send the data of each of the Query resultset to an out put text file using Flat File Connection? If yes pls guide me how? I have tried to create a flat file connection but i am failing how to map the data comming from step 4 dynamically for every query, since every query gives you a different resultset with different coloumns.
Thanks in advance..
Regards,
Dev.
My requirement is to have my report display on one 11 by 8.5 page. Is there a way to programmatically change the PageHeight report property based on the number of rows returned in a table? i.e., if the rowcount for table x >= 10, change PageHeight to 9.5.
Any ideas?
Thanks.
Brad
We have a query in which we have data in fields called TS1Min, TS1Max, TS1Avg, TS2Min, TS2Max, TS2Avg etc.
We have a different table in which we define that vale of TS1, TS2, as an example TS1 might equal RED, TS2 might equal BLUE.
We have written a query that puts TS1Min, TS1Max, TS1Avg, TS2Min, TS2Max, TS2Avg in a temp table #TEMPA
and we also put the values of RED and BLUE in another temp table #TEMPB
now we want to select * from #TEMPA but rename the headings TS1Min to display RED-TS1Min, TS1Max as RED-TS1Max, TS1Avg as RED-TS1Avg etc...
any ideas on how to do this
I am creating SSRS reports on top of SSAS cubes. I want the default value of parameter to change dynamically based on the current year or it should select the last of the parameter values.
Can this be done?
Hi all,
Is there a way to change the dataset being used by a table dynamically ?
Regards,
Neil
Hi,
In my report, I've to print the address of the customer in a form which is in a pre-defined format. So while printing, the line number and position should be specified by the user. Is there any ways in SSRS to change the position of a textbox/any control dynamically by inputing the x and y coordinates? If there is a way please let me know that.
Thanks in Advance,
Leks
I want to transfer data from one server to another by using SSIS. i want the connection string to be dynamic and also according to the some other variable, the transforming data is changing.
Could you provide me the solution thet how i am able to change my connetction string dynamically and the other variable too.
i am using VS 2003 as front end and SQL server 2005 as a backhand.
Due to VS.NET 2003 i am able to create DTS packages but i have to migrate it and then anly i am able to use it in JOB in SQL server agent of SQL server 2005.
is that any code or any stored procedure from which i am able to migrate DTS packages to SSIS packages.
Thank you
Hi Everyone,I have a question about dynamically changing the length of a varchar(n)field, in case the value I'm trying to insert is too big and will givea "truncated" error, but before the error is given! i.e. Is there somekind of a way to "test" the length of the field while Inserting thevalue into it, and to have it automatically increase its length to thelength of the value being inserted, in case the value is too big?I've been able to do this in a "primitive" way, simply by identifyingthe specific error number in case the value is being truncated, andthen increasing the length of the varchar(n) field by using the ALTERcommand, and then duplicating the insert statement, but is there astandard (shorter) way of doing this?Here is my code (I'm working in an ASP environment):<%var_txt = "abcdefghijklmnopqrstuvwxyz12345678789"sql = "Insert Into Table1 (text) Values ('" & var_txt & "')"On Error Resume Nextconn.Execute sqlIf err = -2147217833 ThenResponse.Write "Error Recognized Successfully!<br /><br />"sql = "ALTER TABLE Table1 ALTER COLUMN text VARCHAR(" &Len(var_txt) &") NOT NULL"On Error Resume Nextconn.Execute sqlIf err<>0 ThenResponse.Write "Error while trying to alter Column:<br/>" & err & "= " & err.description & "<br />"ElseResponse.Write "Column altered successfully to: " &Len(var_txt) &"<br />"sql = "Insert Into Table1 (text) Values ('" & var_txt &"')"On Error Resume Nextconn.Execute sqlIf err<>0 ThenResponse.Write "<br />Error number 2:<br />" &err.description &"<br />"ElseResponse.Write "Now it was added successfully!HaHa!<br />"End IfEnd IfElseResponse.Write "Success."End If%>Thanks in advance!
View 2 Replies View RelatedBefore I launch into a long description of the issue, I was wondering whether its possible to dynamically amend the file path held in a configuration string? (I'm looking at the Package Configurations Organizer)
Basically recreating the /CONF switch in the dtexec utility
So a parent package could somehow tell a child package to pull its configuration from a selection of different xml config files at run time... I couldn't see a variable in the child package to set from the former
My only idea at the moment is to replace the Execute Package Task with an Execute Process Task, and directly invoke the child using dtexec & an expression to dynamically set the /CONF switch..
thanks
James
I created a package using the Import Data wizard and everything works fine when I re-execute it. I needed to change the name of the destination table so I went into SQL Server and renamed the table. I then went in and edited the DTSX file via a text editor and changed the OpenRowset setting from [CMBS].[dbo].[raw_Note] -> [CMBS].[dbo].[T_Raw_Note]
<property id="104" 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">[CMBS].[dbo].[T_Raw_Note]</property>
There are no other references to the table in the DTSX, i saved the file and then re-ran.
When I run, I get invalid object name dbo.raw_Note (old table name). Is there some data being cached somewhere or hidden elsewhere that it would reference the old table? When I go back into the DTSX file, the correct name is in there so I don't know where it is getting the old name from? Any help would be appreciated.
Tim
I allowed the SQL Destination Editor to design my table from Output of
a flat text file. Everything was varchar(50), but that was cool,
because I got to see the data in the new staging tables it created. I
went back and tweaked the data types and sizes for various columns to
be more appropriate through the table designer in SQL Server Management
Studio.
After doing so I get an error trying to edit the package, specifically
parts of the destination in the data flow. I get the error "An error
occurred due to no connection. A connection is required when requesting
metadata... blah blah". I pick the TEST CONNECTION in the Connection
Manager, and it works fine.
I am sure this is probably a basic issue of mechanics of use that I
don't yet know because I am completely new to SSIS. Can someone please
provide a hint, perhaps what I did wrong, and also, if you can see it
how to redeem the error. Thanks!
how do you load the multiple flat files to into destination dynamically?
View 9 Replies View RelatedHello. What I'm trying to do should be fairly simple, but after an hour of searching and trials, I can't seem to get it working.When I run my website on my test machine, I would like it to use a certain value for my connectionString. But when it's run on the live server, it should use a different connection string. This way, when it's on the test server it'll use the test database.In my Web.Config file, I have this:<connectionStrings> <add name="myConnectionString" connectionString="Data Source=TEST_DB;Initial Catalog=tester;Integrated Security=True" providerName="System.Data.SqlClient" /></connectionStrings>I would like to be able to change the connectionString in something like the Global.asax file, when the application is loaded. I'd check for a certain environment variable, or some other condition, and change the value of the connectionString.I tried this in my Global.asax, but it told me the value was read_only: ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString = "Data Source=LIVE_DB;Initial Catalog=live;Integrated Security=True" Does anyone have a good way of doing this, so I don't need to juggle Web.Config files when I publish my site? Thanks.
Okay, this one might stump you guys.
View 3 Replies View RelatedI discovered that everytime I need to add a database to my backup Maintenance Plan, after I select the new database from the drop-down of databases, the Destination automatically resets back to a default location.
I'm assuming this is a bug that will be resolved at some point, but in the meantime, I need to see if there is a way I can deal with this permanently. I am not the only one adding databases to the backup routine so I can't verify that this setting is properly changed every time we have a new database (which is about once a week).
Thanks in advance.
Does anyone know how to change the task name displayed within a ForEach Loop Container (or of the ForEach Loop Container task itself) based on a variable. I am pretty familiar with setting variable values during task execution and using expressions to alter task properties based on variables. I have tried using an expression to alter the value of the Name property of the ForEach Loop Container but the name of the ForEach Loop Container does not change during execution. Since the color of the various tasks change during execution, I would think that the task names could be changed as well.
I have a scenario where we have to handle dynamically changing source columns.
For example , some times in the source files the number of columns will be increased or decreased, new columns can be added in the middle or in the end of the source file.
How to handle this kind of scenario in the SSIS ?
Hello everyone,I need advice of how to accomplish the following:Loop though records in a table and send an email per record. Emailrecipient, message text and attachment file name - that's all changesrecord by record.Is it doable from a stored procedure (easily I mean, or am I better offwriting a VB app)? There are so many options of sending mail from SQLserver - CDONTS, SQL MAIL TASK, xp_sendmail. What's easier to implementand set up?Thanks a lot!!!(links and fragments of sample code would be greatlyappreciated)Larisa
View 2 Replies View RelatedHello,
In a Data Flow Task, I have an insert that occurs into a SQL Server 2000 table from a fixed width flat file. The SQL Server table that the data goes into is accessed through an OLE DB connection manager that uses the Native OLE DBMicrosoft OLE DB Provider for SQL Server.
In the OLE DB Destination, I changed the access mode from Table or View - fast load to Table or View because I needed to implement OLE DB Destination Error Output. The Error output goes to a SQL Server 2000 table that uses the same connection manager.
The OLE DB Destination Editor Error Output 'Error' option is configured to 'Redirect' the row. 'Set this value to selected cells' is set to 'Fail component'.
Was changing the access mode the simple reason why the insert from the flat file takes so much longer, or could there be other problems?
Thank you for your help!
cdun2
I have added a Slowly Changing Dimension transformation to an SSIS package and have launched the Wizard to edit it. After selecting the source (a SQL Server 2005 instance), if I select a very large table (9+ million rows), I'm encountering two strange behaviors:
1. The wizard hangs for several minutes before displaying the columns from that table.
2. The wizard does not display the primary key column. This, of course, is the column I want to designate as the "business key", but can't because it's not displayed.
I know this is more like a fact table than a dimension, but this is not a data warehouse. This is just a very large table, and I need to update a field in certain records based on the contents of a source text file. Is there another transformation I should use to perform updates?
Hi,
We are trying to schedule a SSIS package as a job in the SQL Agent.
However, we need to schedule the job dynamically.
There is no fixed date (or period) when this job runs.
Is it possible to dynamically schedule the job?
The next execution date can be stored in a database table or a config file, etc.
If not, then perhaps we can include a task at the top of the Control Flow, which, will check if the package needs to execute "today". For this the job will have to be scheduled on a daily basis.
Thanks in advance.
B@ns
Sorry for my bad english.
I have an ssis package that contains a DateTime variable named RefDate
its value saved in the package is 8/31/2006. I setted up a Package
configuration for read the value of RefDate from the parent package.I
inserted a script task with message box to verify that the package
configuration works. When I launch the parent package the message box
shows the value of RefDate setted in the parent package. The problem
is that when I use RefDate to dynamically set the connection string of
the log connection of the SSIS log provider for XML files for the
child package, the connection string contains the child value of
RefDate.
Thanks in advance.
Renzo.
Hi,
I am new to SSIS and i have to develop a ssis package which will run in a production machine through VB.Net(2003) exe.I am facing a problem while setting connection string of SSIS package dynamically.Can anybody help me on this?