Hi
In SSIS i have an execute SQL task where i get the job_id as the output which i set it to a package variable.
Now what I need to do is i have to map this value to one of the columns of the OLEDB destination.
How can I do it? Please help me ASAP.
Thanks
Sai
I have a requirment which i have partly accomplished , but could not get through completely
i have a file which comes in a standard format ending with date and seq number ,
suppose , the file name is abc_yyyymmdd_01 , for first copy , if it is copied more then once the sequence number changes to 02 and 03 and keep going on .
then i need to transform those in to new file comma delimited destination file with a name abc_yyyymmdd,txt and others counting file counting record abc_count_yyyymmdd.txt. and move it to a designated folder. and the source file is then moved to archived folder
what i have taken apprach is
script task select source file --------------------> data flow task------------------------------------------> script task to destination file
dataflow task -------------------------> does count and copy in delimited format
what is happening here is i can accomlish a regular source file convert it to delimited destination file --------> and move it to destination folder with script task .
but cannot work the dynamic pick of a source file.
please advise with your comments or solution you have
Hello everybody .
I am building DTS transfer data from
SQL server into Excel file
source query constant ,but destination will be supplied by parameter
At design time I created destination
excel file and saved a copy of it like
C: empl_excel.xls
presently dts work in following order
1. set datasource of destination
from global varaibale(@@X)
2. execute xp_cmdshell to copy
C: empl_execel.xls to file in @@X
3.Run transformation
How to eliminate step 2 ?
If I run step 1 and 3 ,I get error "table does not exist"
How dynamicly create table in excel and map columns for transfer
Thank you
alex
How to create a new table dynamically in OLE DB destination.
This is what i am doing
I am reading multiple flat files in loop and saving file name to a variable. Then i have a source script component which read and transforms data .Now how can I push the data to SQL table. I want to create a new table with name saved in a variable. I tried using OLE DB destination and assigning table name from variable. Does'nt work.
Thanks in advance for any insight on how to make this work.
-Amar
Hi,
I am building SSIS for 3 different files that have identical
schema and mapping logic.
In my OLE DB Source (object name - "OLEDBSource_SourceTable")
Data Access mode is "Variable name".
As soon as I swithced to this Data Acces mode
it started to give me an error:
[OLEDBSource_SourceTable [1]] Warning: The external metadata column collection is out of synchronization
with the data source columns.
The column "DEAL_NUM" needs to be updated in the external metadata column collection.
The "external metadata column "DEAL_NUM_Flag" (34529)" needs to be removed
from the external metadata column collection.
The "external metadata column "recordID" (33740)"
needs to be removed from the external metadata column collection.
Hi,
Is there anyway to dynamically change the Sql Server Destination? Let's imagine i want the data to go to a table based on a specific parameter like year for example...
Imagine tables like table1_2005, table1_2006 and i want to dinamically change the SQL Server Destination so it would insert 2005 or 2006 and so on....
I can't seem to find that option... It was really a good idea for microsoft to implement Expressions in such components... Dynamics it's all about it :)
Best Regards,
Hello,
I am a beginner for the SSIS and would like to know how to modify the OLDEB Destination connectionString property at run time like using "for each loop container".
My requirement is that I have a single source which would be Sql Server 2005 and my destination is in MS-Access database residing in 100 places. I do not want to manually design in the data flow to these 100 destinations.
I have all the destinations stored in a table and would like to pick these destinations from the table and loop through the same at run time by modifying destination connection string.
I have planned using dts but the for each loop container does not work through as it works with flat file connection manager , but does not go well with OLDEB connection.
Highly appreciate any help in this regard.
Regards
Sameer
Hi,
I am trying to access from OLE DB source. And based on one of the columns, I need to write the data to a Flat File Destination.
For Example,
CustID, ProductID, Product Name, Product Description
Say I am going to write to a different Flat File for every product. So if there are 10 products in the data. There should be 10 Flat Files. Also the file name should include the Product Name And Product ID.
It is being done in a single Data Flow Task.
Right now the Property Expression for the File Name is which is not working)
Code Snippet@DestFolder + [Data Conversion].ProductID + @TodaysDate + ".txt"
The ProductIDs are in the ascending order. Any help or guidance?
Thanks
-Leo
Hello,
What I'm trying to accomplish is to have a variable names "SourceTable" and "DestinationTable". So for each SourceTable, the DestinationTable will have the same columns. All I need is to auto-map these columns between source and destination via code?
Is this possible?
Thanks,
awiora
Anyone knows how to give the name of a flat file specifying today's week number??
filename_ww
for today it should be filename_16 (since week number is 16)
I need to take 5 or 6 select statements to excel. Here are my limitations...
- each of the queries (thankfully) have the same data format
- each of the queries could return more than 65k, so a new worksheet needs to be generated dynamically.
- the names of the excel worksheets need to be custom, but a naming scheme would have to be developed for queries that ran over into multiple worksheets.
What's the smartest way to do this?
I'm having a hard time getting my head around this. I would love any help... I know I'm not breaking any new ground here. I've found pieces of what I'm doing on lots of forums, but never the exact thing. The complexities compound quickly when dealing with dynamic excel worksheets. =)
I have a database app, and we're implementing various data export features using SSIS.
Basically, it's a couple of straight extracts of various recordsets/views, etc. to CSV (flat files) from our SQL Server 2005 database, so I'm creating an SSIS package for each of these datasets.
So far, so good, but my problem comes here: My requirements call for users to select from a list of available columns the fields that they want to include in their exported file. Then, the package should run, but only output the columns specified by the user.
Does anyone have any idea as to the best way to accomplish this? To recap, at design time, I know which columns the users will have to choose from, but at run time, they will specify the columns to export to the flat file.
Any help or guidance here is greatly appreciated
Hi,
I am using VS.net 2003 as a front end and SQL server 2005 backend.
i am creating SSIS packages for Datatransformation programically in .NET.
but the package created is compatible to the previous version of SQL server ie SQL server 2000.
So i need to migrate it in SSIS package compatible to SQL server 2005.
it is migrate also using Data Transformation migration wizard.
But i want to migrate my DTS package programically or by using stored procedure.
Is there any stored procedure or any code is there from which i can migrate DTS into SSIS ?
Thank you
Here's what I want to do -
Dynamically load a flat file from a dynamic source table-
The source table metadata is known via the SYSOBJECTS and SYSCOLUMNS tables- I can pull the column names, type and lengths from these tables based on the table name. (my goal is pretty simple- pull data from a table in our database, and save it down to a flat file)
Would this be enough to dynamically create the destination flat file? If so, how do I do it?
Thanks
-rob
I created a data flow with complaicated SQL. There is "type" field in the output column.
I would like to created excel files for each "type" value
E.g. If there is 3 "type" values (A, B, C), I would like to create 3 excel files to store type A, type B, and type C data respectively.
Since the number of possibe value of "type" field is various, how can I create the xls destination dynamic and move the correct type to the corresponding excel file?
The conditional split has fixed conditions, it is not suitable for by dynamic number of value
For Loop condition is not a good choice because I need to run the complicated SQL for many time.
Thanks.
Hi Craig/Kamal,
I got your email address from your web cast. I really enjoyed the web cast and found it to be
very informative.
Our company is planning to use SSIS (VS 2005 / SQL Server 2005). I have a quick question
regarding the product. I have looked for the information on the web, but was not able to find
relevant information.
We are getting Source data from two of our client in the form of Excel Sheet. These Excel sheets
Are generated using reporting services. On examining the excel sheet, I found out that the name
Of the columns contain data itself, so the names are not static such as Jan 2007 Sales, Feb 2007 Sales etc etc.
And even the number of columns are not static. It depends upon the range of date selected by the user.
I wanted to know, if there is a way to import Excel sheet using Integration Services by defining the position
Of column, instead of column name and I am not sure if there is a way for me to import excel with dynamic
Number of columns.
Your help in this respect is highly appreciated!
Thanks,
Hi Anthony, I am glad the Web cast was helpful.
Kamal and I have both moved on to other teams in MSFT and I am a little rusty in that area, though in general dynamic numbers of columns in any format is always tricky. I am just assuming its not feasible for you to try and get the source for SSIS a little closer to home, e.g. rather than using Excel output from Reporting Services, use the same/some form of the query/data source that RS is using.
I suggest you post a question on the SSIS forum on MSDN and you should get some good answers.
http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1
http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1
Thanks
Craig Guyer
SQL Server Reporting Services
Posted - 09/10/2007 : 15:53:26
Hey all - got a problem that seems like it would be simple (and probably is : )
I'm importing a csv file into a SQL 2005 table and would like to add 2 columns that exist in the table but not in the csv file. I need these 2 columns to contain the current month and year (columns are named CM and CY respectively). How do I go about adding this data to each row during the transformation? A derived column task? Script task? None of these seem to be able to do this for me.
Here's a portion of the transformation script I was using to accomplish this when we were using SQL 2000 DTS jobs:
'**********************************************************************
' Visual Basic Transformation Script
'************************************************************************
' Copy each source column to the destination column
Function Main()
DTSDestination("CM") = Month(Now)
DTSDestination("CY") = Year(Now)
DTSDestination("Comments") = DTSSource("Col031")
DTSDestination("Manufacturer") = DTSSource("Col030")
DTSDestination("Model") = DTSSource("Col029")
DTSDestination("Last Check-in Date") = DTSSource("Col028")
Main = DTSTransformStat_OK
End Function
***********************************************************
Hopefully this question isnt answered somewhere else, but I did a quick search and came up with nothing. I've actually tried to utilize the script component and the "Row" object, but the only properties I'm given with that are the ones from the source data.
thanks in advance!
jm
Hi, I was wondering how I can complete a column (which doesnt have an input one) with data.
For example:
I have a sql query which bring data of 3 columns
ID | FISRT NAME | LAST NAME
1 MIKE MORGAN
2 SARA JOHANES
So, I will insert that data in a FLAT FILE CONNECTION MANAGER, which I configured with 3 columns and I did the corresponding mapping in the FLAT FILE DESTINTATION.
Now, If I add one more column in the FLAT FILE CONNECTION MANAGER, I will not have it mapped to a input one, obviously. So, what I need is to add one more column to the flat file destination and complete it with zeros values in it.
Probably I can solve this part by introducing a DERIVED COLUMN and there I can configure the zeros that I want to add to the column. But I'm not sure if I can do that without having a input column.
So, the question will be, how can I add one column to a flat file which doesnt have a input and introduce any value that I want to it?
Hope I was clear
Thanks for your help.
Beli
I have searched the archives and haven't found a solution that works.
I started out with a package based on the Excel import wizard and have modified it to include a for each loop for processing more than one Excel source file and also modified it to do some dynamic SQL like dropping and creating the tables with names that are variable based. The package is cabable of processing more than one file, and each file has the same data elements or columns in it, but each file has to go in it's own table. The drop and preperation tasks use string variable SQL that does dynamically create the SQL and runs great. The data flow task and it's OLE destination task (which the wizard did intially create) does not perform as expected. There were some posts in the archives stating that for dynamic destination table names it can be done in the OLE destination but I can't get it to work. I tried using an access mode of table/view name variable and select a variable that is string expression based. But this variable isn't fully populated until run time and so when I'm trying to configure this I get a msg stating the table name object doesn't exist and I can't save the task.
Am I doing something wrong, or trying to do something that wrong way or trying to do something that isn't possible?
I have a requirement to take xml file, in case the number of column changes, it should not fail the package, rather it should load the data in destination table. Destination table could be altered separately depending on xml schema by the DB team in production.
View 3 Replies View RelatedI have a stored proc I am updating in an OLEDB Command from the results of a Transform Script Component. The Stored Proc has over 65 input parameters, most of them have a NULL passed in. I dont want to create output columns in the Transform Script Component for all of them to map them from the "Available Input Columns" to "Available Destination Columns".
I want to create 3 or 4 generic Output columns for their data type - say IntegerOutput (datatype Int), DateTimeOut (datatype datetime) and so on. The I want to map these generic columns in the OLEDB Command as Available Input Columns" to multiple "Available Destination Columns" - wherever the datatype matches the input column.
But the OLEDB Command Column Mappings let me map One to One only. This will create a huge and unnecessary workload for me to develop and maintain - when I tell you I have 3 such stored procedures, all of whose interfaces are exactly same and for which I can create similar Output columns in the Transform Script Component.
So how do I go about doing this the smart way?
thanks in advance!
Hi there,
I am a new member of this site and I am not very much aware of T-sql's working.
My question is what if I need to get one column's data to be the heading of another column.
To be very exact I have a school's database. The table I am talking about is of the results of students. The table contains Student ID, Subject ID, Total marks of the subject, Marks obtained in the subject. Now I want to print a report by generating data from this table. Right now the data is something like this
StuID - - - SubID - - - -Tot - - -Obt
1 - - - - - - -1 - - - - - - -50 - - - 38
1 - - - - - - -2 - - - - - - -50 - - - 41
1 - - - - - - -3 - - - - - - -50 - - - 42
1 - - - - - - -4 - - - - - - -50 - - - 40
2 - - - - - - -1 - - - - - - -50 - - - 35
2 - - - - - - -2 - - - - - - -50 - - - 40
2 - - - - - - -3 - - - - - - -50 - - - 42
2 - - - - - - -4 - - - - - - -50 - - - 41
StudentID and SubjectID fields are related to other tables so I can get the names from there but when I need the report I need the data in the form of
StuID - Sub 1 - - - Sub 2 - - - Sub 3 - - - -Sub4
1 - - - - 38 - - - - - - 41 - - - - - - 42 - - - - - - 40
2 - - - - 35 - - - - - - 40 - - - - - - 42 - - - - - - 41
The Subjects can be different for different students so the query should be dynamic instead of hard coding the names of the subjects. I hope I am clear with my question. The subjectIDs or their names will become the headings and they will contain the obtained marks for that subjects in their columns just for the reports. I have also checked the PIVOT function but was not able to do what I wanted.
Thanks.
I have worked with straight-forward simplistic SSIS up to now, so I apologize if this is a simple question. I have a SQL table destination that accepts daily data from an AS400 table. We now have a need to datestamp the incoming data so I added a new column to the destination table where I want today's date. Is there a data flow transformation that I need to use to default the destination column to today's date? Time is not a consideration and not needed, just the date.
Thanks for the information.
Need to know how I can get the dynamic filename created in the FlatFile destination for insert into a package audit table?
Scenario: Have created a package that successfully outputs Dynamiclly named flat files { Format: C:Test’Comms_File_’ + ‘User::FileNumber’+’_’+Date +’.txt’
E.g.: Comms_File_1_20150724.txt, Comms_File_2_20150724.txt etc} using Foreach Loop Container :
* Enumerator Set to: “Foreach ADO Enumerator” with the ADO object source variable selected to identify how many total loop iterations there are i.e. Let’s say 4 thus 4 files to be created
*Variable Mappings : added the User::FileNumber – indicates which file number current loop iteration is i.e. 1,2,3,4
For the DataFlow task have a OLDBSource and a FlatFile Destination where Flat File ConnectionString is set up as:
@[User::Output_Path] + "Comms_File"+ @[User:: FileNumber] +"_" + replace((DT_WSTR, 10) (DT_DBDATE) GETDATE(),"-","")+ ".txt"
All this successfully creates these 4 files:
Comms_File_1_20150724.txt, Comms_File_2_20150724.txt, Comms_File_3_20150724.txt, Comms_File_4_20150724.txt
Now the QUESTION is how do I get these filenames as I need to insert them into a DB Audittable. The audit table looks like this:
CREATE TABLE dbo.MMMAudit
(
AuditID INT IDENTITY(1, 1) NOT NULL,
PackageName VARCHAR(100) NULL,
FileName VARCHAR(100) NULL,
LoadTime DATETIME NULL,
NumberofRecords INT NULL
)
To save the Filename & how many records in each file in our Audit Table, am using an Execute SQL Task and configuring it as this:
Execute SQL Task
Parameter mapping - Mapped the User Variable (RecordsInserted) and System Variable( PackageName) to Insert statement as shown below
SQLStatement: INSERT INTO [dbo].[MMMAudit] (
PackageName,NumerofRecords,LoadTime)
(?,?.GETDATE)
Again this all works terrific & populates the dbo.MMMAudit table as shown below BUT I also need to insert the respsctive file name – How do I do that?
AuditID PackageName FileName NumberOfRecords
1 MMM NULL 12
2 MMM NULL 23
3 MMM NULL 14
4 MMM NULL 1
I am having an issue with the File System Task.
I was wondering if there is a way to 'Move File' with the File System Task inside of a For Each Loop container but to dynamically set the Destination path variable.
Currently, this is what I have:
FileDestinationPath variable - set to C:TestFiles
FileSourcePath variable - set to C:TestFiles
FileNameAndLocation variable - set to blank
For Each Loop Container Iterates through a folder C:TestFiles that has .txt files in it with dates in the file name. Ex: Test_09142006.txt. Sets the file path (fully qualified) to the Variable Mapping FileNameAndLocation.
Script Task (within For Each Loop, first step) Sets the FileDestinationPath to the correct dated folder within C:TestFiles. For example, if the text files I want to move are for the 14th of September, it takes FileDestinationPath and appends the date folder to the end of it. The text files have a date in the file name (test_09142006.txt) and I am picking this apart (from FileNameAndLocation in the For Each Loop) to get the folder date. (dts.Variables(User::FileDestinationPath?).Value = dts.Variables(User::FileDestinationPath?).Value & ? Month & _? & Day & _? & Year & ?) which gives me C:TestFiles 9_14_2006?.
File System Task (within For Each Loop, second step) This is where the action is supposed to occur. I want it to take the FileDestinationPath and move the FileNameAndLocation file (from the For Loop) into this folder for each run.
Now as for my problem. I want this package to run everyday but it has to set the FileDestinationPath variable dynamically according to that days date. Basically, how do I get this to work since I cant hard code the destination path variable from the start? I have the DestinationVariable on the File System Task set to the FileDestinationPath variable, after the script task builds it. However, using FileNameAndLocation as the SourceVariable on my File System Task tells me that the Variable FileNameAndLocation? is used as a source or destination and is empty.?
Let me know if I need to clarify further...I may be missing something very simple. Any help would be greatly appreciated!
I have been searching for the answer to something I thought would be easy to find. But, no luck.
I need to load a csv file into a SQL Server table. The rub is that I need to also parse the filename for a couple of pieces of data also.
Example filename: c:importCustomerX_LocationY_1234.csv
For each record in the csv file I will call a stored procedure the has a parameter for each column in the file plus a parameter for the customer name and a colummn for the location name.
I assume that I will need to use a Script Component to parse the filename fro the information. What is not clear is how I get the filename from the csv connection object and how to get to result of the script component to the inputs of the OLE DB command component that I am using to call the stored procedure.
I have succeeded in using the Derived Column component to put constant values in for the customer and location. But, I cannot figure out how to get to the next step of deriving those two values from the input filename.
Thank you n advance for any assistance,
Jim
I am using Advantage ODBC 6.2 to connect to a Advantage Data Server to
push data from a SQL table into this server. I can view the data from
the ADS with DTS and I can insert data by using "Insert into TempTable ("Last
Name","First Name") values ('test','test)" from a Execute SQL Statement. The issue is when I build
up a DTS package to pull from SQL into the Advantage ODBC, I get a
"missing closing ")"" error. I have narrowed it down to the column
names in the destination table having spaces (Last Name, First Name,
and a bunch of others. DBA 101 here). How do I beat this?
I have tried editing the destination column names in disconnected edit
by adding the double quotes but get a "Column Name '"Last Name"' not
found" error. Wrapping them with [] doesn't work. I think this is a
limit on Advantage.
When I try to use Advantage OLE DB, I get an 'Ace32.dll must be newer
then the other dll" error. I am afraid of upgrading Ace32.dll and not
break other things.
So with this said my two questions:
How do I get the destniation column names wrapped in double quotes when using transformations?
or
Is there a way I can do an Insert into Advantage connection(column names) values (Select Values from SQL Server connection)?
Hello everyone,
I am having a little problem with a simple package and I do not know if this is a known issue or that I am missing something.
I have a data flow task, a simple one, with an oledb source pulling data, using a select statement, from a sql server 2005 instance, and an ole db destination pointing to a table in a sql server 2000 instance. Both intances are standard edition. The table in the destination has a column which allow null values and has also a default constraint (getdate()), and this column is not present in the source. When I map the columns in the destination, I leave this column as "ignore", not being mapped to any column from the source. The problem is that when I execute the task, SSIS is trying to insert NULL value into this column, so the package fail with the error "can not insert NULL value into column myColumn". I wonder why is it trying to insert NULL value if the column is not mapped to any column from the source.
Is this a known issue or I am nissing something in the settings?
If the destination table has rowversion or identity columns, there is no problem ar all. I ignore those columns in the mapping and SQL Server feeds them as expected.
Thanks in advance,
Alejandro Mesa
I have created a package which transfers data from a SQL server source to an Excel Destination. The DataFlow Task works fine , if i pre-define the column names in the Excel Destination... But i run into an error when i give the blank excel sheet as my destination. I am unable to map any columns
A sample example is as shown above .. In the column mappings field only one column in the Excel shows up for mapping and eventually throws the error "[Excel Destination [42]] Error: The number of columns is incorrect. "
How do we proceed in this case , where in we do not want to give pre-defined coulmn names in the Excel Destination sheet.
Thanks,
D
Hi,
I have an excel export with numeric values. When the SSIS package writes into the excel it treats all data types as strings and attaches an apostrophe in the beginning. I tried formatting the excel sheet with the number data type and saving it . It doesnt work.
Other strange thing is that when I go into the advanced editor for the excel destination and look into the properties of the external columns all of them have the data type as Unicode String[DT_WSTR] irrespective of what the data type is from the input.The UI allows me to change it to numeric for numeric columns without any error but the value is not saved at all which is very frustrating. I also tried changing it in the xml file of the package, it some how seems to overwrite it after validation. It would be really nice if it threw an error saying that it cannot be changed.
Anyways there seems to be no way of changing the destination data type if it s an existing file and if I create a new excel sheet there is no way of formatting data. Is there any way out?
Thanks
how do I make the output columns padded with extra space ? I intentionally set my output width larger than the input width, but the generated file is still jamming all the columns next to each other
View 2 Replies View RelatedHello guys,
here is my issue.
I created a ssis package which exports the data from oledb source to flat file (csv format). For this i have OLEDB source and Flat File as destination. I generate the file and filename dynamically with the column names in the first row. So if the dynamically generated file name already exists , then i want to append the data in the same existing file. But I dont want to append the column names again. I just want to append the rows to the existing rows.
so lets say first time i generate a file called File1_3132008.csv.
Col1, Col2
1,2
3,4
After some days if my ssis package generates the same file name i.e. File1_3132008.csv, this time i just want to append the rows to the existing file. So the file should look like this-
Col1, Col21,23,45,67,8
But instead my file looks like this if i set Overwrite propery to false
Col1,Col2
1,2
3,4
Col1,Col2
5,6
7,8
Can anyone help me to get the file as shown in the highlighed
Any help would be appreciated .
Thanks
Hi all,
I am passing flat file source as a variable to Dtexec Utility. (like package.variables[User::varFileName].Value;"D:sourcedata.txt).
Destination table is having one more column.
I want to add custom value in that column at run time by parameter to Dtexec(User::varDate)
I dont know how to do it, please help me.
Madhukar