.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

SSIS Create table using variable

Posted By:      Posted Date: September 28, 2010    Points: 0   Category :Sql Server
Im working on my case study about inserting all the records of a flat file source to a OLEDB Destination. The problem is that I want to create a table using a sql query stored in a variable. How will I do that? Thanks

View Complete Post

More Related Resource Links

How to load a new table with the value of a variable from SSIS package?

Hi, i have two variables in a SSIS package Var1 and Var2. Both of these variables have values. is there any way i can put the values of these two variables in a new table? e.g In New table col1 having value of Var1 and col2 having value of Var2. Thanks

How to send record(which is a weblink) from a table to the value of the variable in SSIS package and

Hi Folks, I have table called Table1 with columns, col1 and col2 with col1 having weblinks for the report and col2 the name of the report. Now, i have a package with a variables var1 and var2 which should get the col1 and col2 values respectively from table1 and send it through an email. if the weblink gets updated in the table, package should send the updated link. i know the reverse way of it but trying to do somethig like this. Appreciate any help from you guys. Thanks

How to declare a table variable in SSIS and then insert rows into it

Hello everyone,   I'll try to explain my problem as clearly as I can: 1)I have an Execute SQL Task that's inside a Foreach Loop Container. 2)Inside the Execute SQL Task, I have an int OUTPUT column. 3)With each iteration, I need to insert the data from the INT output colum into a table variable. 4)After the Foreach Loop finishes the iterations, I need to use the table variable to create a report based on the data inside.   My question is the following: How can I declare a table variable so that I can do all of the above?   Thank you, CostinP

can create table statement be used in and ssis expresssion?

I need to modify the preparation sql task built by the import wizard. I wanted to create an expression  using the create table sql but insert the variable to append the date and another descriptor  to make it dynamic. The source is Excel The expression would look something like this "Create Table" + @user::descriptor +  @user::todaysdate+" ( name varchar(50) ..........." SSIS cannot evaluate the sql although it runs fine in ssms The column mappings will always be the same but the table name needs to be dynamic

SSIS 2005 - Dynamically generate create table statement from source



I have a requirement where i had to migrate the table structure and data from ODBC database to SQL Server. I have dynamic source reader that uses sql query from variable to get the data and the next step is to get the create statement and create table and then to insert data. how can i get Create Statement the destination produces and use sql task to create the table and then insert the data using data flow task.

Please suggest



using table name variable in create function


anybody help me, i wanna create function using table name parameter.

this is my function code

ALTER function [dbo].[CreateRecID]( @TableName varchar(100))
returns bigInt
declare @RecID bigint
declare @Sql varchar(2000)
set @Sql = (select case when max(RecID) is null then 0 else max(RecID) + 1 end as RecID from @TableName)

return @Sql

when i run this code

i got error "Must declare the table variable "@TableName".

I have already declare a @TableName Parameter.

can you show me the way to solve my problem?

Thanks so much

In SSIS, can I write the value of a variable to a SQL table?


This seems like it should be easy to do, but I can't figure it out.

In my Control Flow I have an Execute SQL task that populates an ADO Recordset variable. That works fine, the variable holds a list of server names.

Then I have that connected to a Foreach Loop Container, which loops through the server names, connects to each server, reads some info, and writes that to a table. That also works, except for those servers that it can't connect to.

So here's what I'd like to do: before I connect to the server, write the value of the variable (which contains the server name) to a table in SQL, along with getdate() so I know when it started trying to connect.

How do I just write the value of a variable to a table? If I just use an OLE DB Destination, there are no input columns.

My best thought was to use a Script Component and somehow get that value, and then somehow make it an output column, which I then connect to the OLE DB Destination as an input column??

If I do this in a C# Script Component:



it works just fine, so I know my variable is there an available.

Any suggestio

How to Create a Table from XML Schema File (.xsd) in SSIS 2005

I have a .xsd schema file from an Informix table. I want to create the same table in the SQL Server 2005 using SSIS 2005. Is there any work arround that i can do this from a file?
If could do this using any other SQL Server 2005 file Objects.

Immidiate Help would be appriciated.

Create new sql table at runtime VB.Net


I want to create a new table to sql database at runtime.  The column names would be "Name", "Date", "Event".  I would also like to have one more column name "Image" but the problem is that since the amount of images will vary, I want the application to count the amount of files in the Multiple fileuploader and then add a image column to the table for the respected amount of files. 

For example if i am uploading 3 files in the multiple fileuploader, the names should be "Name", "Date", "Event", "Image1", "image2", Image3"

I also would like that table name to be name dynamically from the textbox Name when the user enters the information.

I have the following code to make a new table, but I dont know how to name the fields at runtime like previously asked please help!!!

Dim objConn As New SqlConnection("Server=<servername>;uid=<userid>;pwd=<password>;database=master")


Create/add table from code to database



I create table:

                  DataSet data=new DataSet();

                  DataTable myTable = new DataTable("NewTable");

                   DataColumn[] keys = new DataColumn[1];

                   // create column
                   keys[0] = new DataColumn();
                   keys[0].DataType = System.Type.GetType("System.String");
                   keys[0].ColumnName = "PointID";
                   myTable.Columns.Add(keys[0]);    &nb

Create table in the database using management studio


 I have database already on the server. However, when I created new table, the new created table Schema is not dbo. it is my user name. How to solve this?

It shows the new created table Schema is my username but not dbo.

Even though I rename it, it couldn't change. I can see this in table property.



generate create script of table using c# and SQL server 7.0, can anyone help me?


I want to generate create table script using c#.net, I want to connect sql server 7.0 and generate table create script. Its urgent kindly help me urgently.

How to create scrolling table for columns in a SharePoint Designer Data View


I have a fairly complex SharePoint Designer 2007 Data View they have added two more requirements.

1. Have the columns on the right side be able to scroll left to right as there is a large number of columns, while the first 3 columns on the left side stay locked in place.  This is only one list not two, and because of some other requirements I have it must remain to be just one list.

2. Then they also need to have scrolling up and down of the entire list with the headers locked in place.

The first requirement is more important than the second, but preferrably I need to do both.

I am new to XSL so not familar with the syntax. Is this something I can do on a custom Data View? Does anyone have any examples of how?

I have a drawing that show how it needs to look but I don't see an option here to upload a graphic.

So here is a link to my graphic example uploaded to my blog area: http://lindachapman.blogspot.com/  or just the image here: http://4.bp.blogspot.com/_aB01ue__NvQ/TGL42MOvrGI/AAAAAAAAAA4/c3xP4uCy4BU/s1600/Scrolling.png

I failed to mention that 3/4 of the columns are HTML Calculated fields wh

Can't perform Create, Update or Delete operations on 'Table(outbox)' because it has no primary ke



I'm a beginner in asp.net and I have some problem inserting data into sql server 2005 with linq.


my code is : 


        Dim Result As New SMS.SendResult
        If Result.status = 0 Then
            Label1.Text = "Your Message Sent Successfuly"
            Dim db As New IGSSDataContext
            Dim out As New outbox With {.ClientID = intRandomNumber, .From = User.Identity.Name, .TO = TextBox1.TabIndex, _
            .Time = Now, .Message = Message}
            out.ID = (ID)
            TextBox1.Text = ""

Want to Create Table Dynamically With text Control in WinForm Csharp

Hello To All... from Many Days i m tryng to create a simple table using csharp in WinForm.Which will include textbox in each cell according the Passed No of Col & Row. but not getting the proper result. Actually i want to trace each cell data and want to save each cell data to data base : eg. in cell 11(1col & 1row) i m having Combobox which having some item list and when the user select the list item the data will save to the database. Regards, Praveen    What is the Key of Life ... Its Sharing Your Thought & Knowledge ...

SSIS Package Transaction locks entire table

Hello, I have SSIS package that using transactions and working as expected. But this locks all the tables involved in the data flow that are get inserted/loaded. If I query those tables during package execution the query waits till SSIS releases the lock. But I can still query those tables using NOLOCK hint. Is this possible SSIS will lock only the rows that it insert/update/delete etc? So that other user can still use that table or another instance of same SSIS Package can load data into those tables? My Package: In the package level I have TransactionOption = Required. The Sequence (Data Flow is inside this sequence) TransactionOption = Supported. I had some issue in my package that I posted here: http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/dfb5a8b9-d43c-452b-bb21-d6aea192fc97 and also resolved. Now I descovered this issue and trying to resolve. Thanks, Prabhat

Create Table from MDX query

Hi,I want create a table from the result of the MDX query. When I finish the wizard of import data in SQL Server Environment. at the last stage, the error is : Pre-execute (Error) Messages Information 0x4004300b: Data Flow Task 1: "component "Destination - Query" (31)" wrote 0 rows.(SQL Server Import and Export Wizard) please guid  me.Thank you a lot.Naeimeh
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend